MAX dont know what type is returning

Hi guys,

I'm doing some function in the SQLExpress using the max func and I cant figure out if its returning null or empty string. It always return null even it's returning index.

Please help.

Here's is the sample data of the table that I'm manipuilating

code name

code name

code_1 name_1

code_2 name_2

tues_2 name_4

code_4 name_3

>>> What am trying to get is the maximum index of the code

Below is my function

declare @.codeIndex varchar(20)

select @.codeIndex = isnull(max(isnull(substring([code],len('code')+2), len'code')) ,0)),0) from table where [code] like 'code%'

print @.codeIndex

if @.codeIndex is null

begin

print 'Code index is null

end

else

begin

print 'we got an index!'

end

>>> the output of this is always null even it's returning an index

Can you post a full repro? You code didn't compile (not correct number of parameters etc). Either against the pubs database or also post CREATE TABLE and some INSERT statements. I changed the code so it at least compiled (but I doubt it has correct logic), and there doesn't seem to anything wrong with the MAX function per se.: declare @.codeIndex varchar(20) select @.codeIndex = isnull(max(isnull(substring([au_lname],len('code')+2, len('code')) ,0)),0) from authors print @.codeIndex if @.codeIndex is null begin print 'Code index is null' end else begin print 'we got an index!' end -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:b4844e9a-27a2-49d0-8a11-482daa3423db@.discussions.microsoft.com...
> Hi guys, >
> I'm doing some function in the SQLExpress using the max func and I
> cant figure out if its returning null or empty string. It always return
> null even it's returning index. >
> Please help. >
> Here's is the sample data of the table that I'm manipuilating >
> code name >
> code name >
> code_1 name_1 >
> code_2 name_2 >
> tues_2 name_4 >
> code_4 name_3 > > > >>>> What am trying to get is the maximum index of the code >
> Below is my function >
> declare @.codeIndex varchar(20) >
> select @.codeIndex = isnull(max(isnull(substring([code],len('code')+2),
> len'code')) ,0)),0) from table where [code] like 'code%' > > >
> print @.codeIndex >
> if @.codeIndex is null >
> begin >
> print 'Code index is null >
> end >
> else >
> begin >
> print 'we got an index!' >
> end > >>>> the output of this is always null even it's returning an index >
>|||

Hi NNTP,

Thanks! I already figured it out; maybe, I'm just exhausted last friday.

Max date from three different columns

I have a query that returns three different columns
ex: select a.date1, b.date2, c.date3
from table1 a,
table2 b,
table3 c
I need to return the result of the largest of the dates
I thought something like this would work, but it doesn't
select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
unionselect b.date1 [thisdate]
from table1 b
unionselect c.date1 [thisdate]
from table1 c)
I cannot change the database structure, and I am hoping that a huge if
statement can be avoided.
Thanks
EricAre all these columns in the one table? If so, you want to unpivot:
select
max (case x.seq
when 1 then Col1
when 2 then Col2
when 3 then Col3
end)
from
(
select 1 union all
select 2 union all
select 3
) x (seq)
cross join
MyTable
If these are across 3 tables, try:
select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
union
select b.date1 [thisdate]
from table1 b
union
select c.date1 [thisdate]
from table1 c
) as x
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
I have a query that returns three different columns
ex: select a.date1, b.date2, c.date3
from table1 a,
table2 b,
table3 c
I need to return the result of the largest of the dates
I thought something like this would work, but it doesn't
select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
unionselect b.date1 [thisdate]
from table1 b
unionselect c.date1 [thisdate]
from table1 c)
I cannot change the database structure, and I am hoping that a huge if
statement can be avoided.
Thanks
Eric|||> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
Eeks, is this supposed to be a cross join? How are these three tables
related? Did you mean to query against columns in three different tables,
or against three different columns in the same table? Did you want the MAX
date from any of the three columns in ALL the rows of the table, or the
greater of the three column values in every row in the table?
Can you provide decent requirements so that we don't have to ask 40
questions to figure out what you're talking about? Please see
http://www.aspfaq.com/5006|||select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
union
select b.date1 [thisdate]
from table1 b
union
select c.date1 [thisdate]
from table1 c
) as x
BEAUTIFUL!!!1
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
> Are all these columns in the one table? If so, you want to unpivot:
> select
> max (case x.seq
> when 1 then Col1
> when 2 then Col2
> when 3 then Col3
> end)
> from
> (
> select 1 union all
> select 2 union all
> select 3
> ) x (seq)
> cross join
> MyTable
> If these are across 3 tables, try:
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> union
> select b.date1 [thisdate]
> from table1 b
> union
> select c.date1 [thisdate]
> from table1 c
> ) as x
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>|||Okay, this works great in the small test case, but now I am attempting to
put it into my larger query, and it looks something similar to this
select a.column1, a.column2, a.date
,b.column1, b.column2, b.date
,c.column1, c.column2
,max([thisdate])
from
table1 a
,table2 b
,table3 c
,(select a.date1 [thisdate]
union
select b.date1 [thisdate]
union
select c.date1 [thisdate]
) as x
where
......
And it states:
The column prefix 'a' does not match with a table name or alias name used in
the query
The column prefix 'b' does not match with a table name or alias name used in
the query
The column prefix 'c' does not match with a table name or alias name used in
the query
if I put table1.date1 it gives a similar error
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
> Are all these columns in the one table? If so, you want to unpivot:
> If these are across 3 tables, try:
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> union
> select b.date1 [thisdate]
> from table1 b
> union
> select c.date1 [thisdate]
> from table1 c
> ) as x
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>|||What is it that you're really trying to achieve? We're seeing cross joins
over 3 tables + 1 derived table. Give us a spec + DDL + INSERT statements
of sample data + desired results.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:%23Yim%23VmXGHA.4988@.TK2MSFTNGP05.phx.gbl...
Okay, this works great in the small test case, but now I am attempting to
put it into my larger query, and it looks something similar to this
select a.column1, a.column2, a.date
,b.column1, b.column2, b.date
,c.column1, c.column2
,max([thisdate])
from
table1 a
,table2 b
,table3 c
,(select a.date1 [thisdate]
union
select b.date1 [thisdate]
union
select c.date1 [thisdate]
) as x
where
......
And it states:
The column prefix 'a' does not match with a table name or alias name used in
the query
The column prefix 'b' does not match with a table name or alias name used in
the query
The column prefix 'c' does not match with a table name or alias name used in
the query
if I put table1.date1 it gives a similar error
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
> Are all these columns in the one table? If so, you want to unpivot:
> If these are across 3 tables, try:
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> union
> select b.date1 [thisdate]
> from table1 b
> union
> select c.date1 [thisdate]
> from table1 c
> ) as x
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>|||I am joining 6 different tables, and for each row that is selected, I need
to get the latest of the two dates along with Janurary 1 06 and return the
latest of the three dates mentioned as part of the query.
Unfortunately getting tables/data would be impractical, sorry.
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eZfHlamXGHA.1200@.TK2MSFTNGP03.phx.gbl...
> What is it that you're really trying to achieve? We're seeing cross joins
> over 3 tables + 1 derived table. Give us a spec + DDL + INSERT statements
> of sample data + desired results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:%23Yim%23VmXGHA.4988@.TK2MSFTNGP05.phx.gbl...
> Okay, this works great in the small test case, but now I am attempting to
> put it into my larger query, and it looks something similar to this
> select a.column1, a.column2, a.date
> ,b.column1, b.column2, b.date
> ,c.column1, c.column2
> ,max([thisdate])
> from
> table1 a
> ,table2 b
> ,table3 c
> ,(select a.date1 [thisdate]
> union
> select b.date1 [thisdate]
> union
> select c.date1 [thisdate]
> ) as x
> where
> ......
> And it states:
> The column prefix 'a' does not match with a table name or alias name used
> in
> the query
> The column prefix 'b' does not match with a table name or alias name used
> in
> the query
> The column prefix 'c' does not match with a table name or alias name used
> in
> the query
> if I put table1.date1 it gives a similar error
> Eric
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
>|||Well, cross joins certainly aren't the answer. If you can't produce a
simplified DDL then we can't be of much help.
Help us help you.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:u9HCy3mXGHA.1200@.TK2MSFTNGP03.phx.gbl...
I am joining 6 different tables, and for each row that is selected, I need
to get the latest of the two dates along with Janurary 1 06 and return the
latest of the three dates mentioned as part of the query.
Unfortunately getting tables/data would be impractical, sorry.
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eZfHlamXGHA.1200@.TK2MSFTNGP03.phx.gbl...
> What is it that you're really trying to achieve? We're seeing cross joins
> over 3 tables + 1 derived table. Give us a spec + DDL + INSERT statements
> of sample data + desired results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:%23Yim%23VmXGHA.4988@.TK2MSFTNGP05.phx.gbl...
> Okay, this works great in the small test case, but now I am attempting to
> put it into my larger query, and it looks something similar to this
> select a.column1, a.column2, a.date
> ,b.column1, b.column2, b.date
> ,c.column1, c.column2
> ,max([thisdate])
> from
> table1 a
> ,table2 b
> ,table3 c
> ,(select a.date1 [thisdate]
> union
> select b.date1 [thisdate]
> union
> select c.date1 [thisdate]
> ) as x
> where
> ......
> And it states:
> The column prefix 'a' does not match with a table name or alias name used
> in
> the query
> The column prefix 'b' does not match with a table name or alias name used
> in
> the query
> The column prefix 'c' does not match with a table name or alias name used
> in
> the query
> if I put table1.date1 it gives a similar error
> Eric
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
>|||I would use a case expression:
case
when b.date1<=a.date1 and c.date1<=a.date1 then a.date1
when a.date1<=b.date1 and c.date1<=b.date1 then b.date1
when b.date1<=c.date1 and a.date1<=c.date1 then c.date1
end
BTW, as a rule of thumb, use UNION ALL, not UNION whenever possible -
usually performs better.|||CREATE TABLE C1(F DATETIME)
INSERT INTO C1 SELECT '2005-04-05'
CREATE TABLE C2(F DATETIME)
INSERT INTO C2 SELECT '2004-02-01'
CREATE TABLE C3(F DATETIME)
INSERT INTO C3 SELECT '2006-4-13'
SELECT MAX(X.F) AS F FROM
(
SELECT MAX(C1.F) AS F FROM C1
UNION ALL
SELECT MAX(C2.F) AS F FROM C2
UNION ALL
SELECT MAX(C3.F) AS F FROM C3
) X
DROP TABLE C1
DROP TABLE C2
DROP TABLE C3
"Eric Stott" wrote:

> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>
>