April 23, 2008 at 2:44 pm
Hi folks,
This is very odd, I have a function which will return integer but it's returning NULL now. When I run the code directly in QA, it is returning the right result.
declare @appid int
set @appid = 442
select dbo.fnGetNewOrgID(@appid)
This returns NULL
Then I tried the other way, the following code is from the function, the only difference is I changed from "return @ret" to "select @ret" so I can see the result in QA.
By running this, I got the right result.
Why????
declare @l1 int
declare @l2 int
declare @l3 int
declare @l4 int
declare @ret int
select @l1 = l1, @l2 = l2, @l3 = l3, @l4 = l4 From Applications Where AppID = @AppID
if @l4 <> null or @l4 <> ''
select @ret = ID from Org o inner join org_lvl4 l4 on l4.Lvl4 = o.NodeName where l4.Org_Lvl4_ID = @l4
else
if @l3 <> null or @l3 <> ''
select @ret = ID from Org o inner join org_lvl3 l3 on l3.Lvl3 = o.NodeName where l3.Org_Lvl3_ID = @l3
else
if @l2 <> null or @l2 <> ''
select @ret = ID from Org o inner join org_lvl2 l2 on l2.Lvl2 = o.NodeName where l2.Org_Lvl2_ID = @l2
else
if @l1 <> null or @l1 <> ''
select @ret = ID from Org o inner join org_lvl1 l1 on l1.Lvl1 = o.NodeName where l1.Org_Lvl1_ID = @l1
else
set @ret = 0
select @ret
April 23, 2008 at 3:44 pm
April 24, 2008 at 7:27 am
That's not the problem, because like I said in my post, I copied all the code from function and run it directly from QA, there is no any problem and I can get the RIGHT return value. But if the code was run from function, then the result won't be returned, instead, NULL is returned.
April 24, 2008 at 7:45 am
halifaxdal (4/24/2008)
That's not the problem
Are you sure?
You may have different ANSI_NULL settings for function and for batch in QA.
_____________
Code for TallyGenerator
April 24, 2008 at 7:57 am
Can you post the actual create script for the function?
The way this is written, it should return 0 instead of null. Unless there's a problem with the way the create script was put together (wrong return type, for example).
The problem isn't the comparisons to null. Yes, those should be "is not null", but because of the "or" and the comparison to a zero length string, that part won't mess up the function.
It has to be something in how the function was written.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2008 at 8:22 am
This is my code to create the function:
Some friends suggest me replacing the <> null to is not null, I did it and it's no use.
CREATE FUNCTION fnGetNewOrgID (@AppID int) returns int
AS
BEGIN
declare @l1 int
declare @l2 int
declare @l3 int
declare @l4 int
declare @ret int
select @l1 = l1, @l2 = l2, @l3 = l3, @l4 = l4 From Applications Where AppID = @AppID
if @l4 <> null or @l4 <> ''
select @ret = ID from Org o inner join org_lvl4 l4 on l4.Lvl4 = o.NodeName where l4.Org_Lvl4_ID = @l4
else
if @l3 <> null or @l3 <> ''
select @ret = ID from Org o inner join org_lvl3 l3 on l3.Lvl3 = o.NodeName where l3.Org_Lvl3_ID = @l3
else
if @l2 <> null or @l2 <> ''
select @ret = ID from Org o inner join org_lvl2 l2 on l2.Lvl2 = o.NodeName where l2.Org_Lvl2_ID = @l2
else
if @l1 <> null or @l1 <> ''
select @ret = ID from Org o inner join org_lvl1 l1 on l1.Lvl1 = o.NodeName where l1.Org_Lvl1_ID = @l1
else
set @ret = 0
return @ret
END
April 24, 2008 at 8:28 am
return coalesce(@ret , 0)
because the SELECT parts may return NULL !
Or no row at all which case @ret is untouched and keeps it's orginal NULL value.
N 56°04'39.16"
E 12°55'05.25"
April 24, 2008 at 8:32 am
If your code looks complex that it's not correct.
Make it simple and it will work:
select @ret = COALESCE(I4.ID, I3.ID, I2.ID, I1.ID, 0)
from Org o
LEFT JOIN org_lvl4 l4 on l4.Lvl4 = o.NodeName where l4.Org_Lvl4_ID = @l4
LEFT JOIN org_lvl3 l3 on l3.Lvl3 = o.NodeName where l3.Org_Lvl3_ID = @l3
LEFT JOIN org_lvl2 l2 on l2.Lvl2 = o.NodeName where l2.Org_Lvl2_ID = @l2
LEFT JOIN org_lvl1 l1 on l1.Lvl1 = o.NodeName where l1.Org_Lvl1_ID = @l1
_____________
Code for TallyGenerator
April 24, 2008 at 8:45 am
How can alias wit prefix I work?
And the multiple WHEREs?
What I think Sergyi meant is this
SELECT@Ret = COALESCE(l4.ID, l3.ID, l2.ID, l1.ID, 0)
FROMOrg AS o
LEFT JOINOrg_Lvl4 l4 on l4.Lvl4 = o.NodeName
AND l4.Org_Lvl4_ID = @l4
LEFT JOINOrg_Lvl3 l3 on l3.Lvl3 = o.NodeName
AND l3.Org_Lvl3_ID = @l3
LEFT JOINOrg_Lvl2 l2 on l2.Lvl2 = o.NodeName
AND l2.Org_Lvl2_ID = @l2
LEFT JOINOrg_Lvl1 l1 on l1.Lvl1 = o.NodeName
AND l1.Org_Lvl1_ID = @l1
N 56°04'39.16"
E 12°55'05.25"
April 24, 2008 at 8:49 am
To do a test, I run the following:
declare @appid int
set @appid = 442
declare @l1 int
declare @l2 int
declare @l3 int
declare @l4 int
declare @ret int
select @l1 = l1, @l2 = l2, @l3 = l3, @l4 = l4 From Applications Where AppID = @AppID
select @ret = COALESCE(I4.ID, I3.ID, I2.ID, I1.ID, 0)
from Org o
LEFT JOIN org_lvl4 l4 on l4.Lvl4 = o.NodeName where l4.Org_Lvl4_ID = @l4
LEFT JOIN org_lvl3 l3 on l3.Lvl3 = o.NodeName where l3.Org_Lvl3_ID = @l3
LEFT JOIN org_lvl2 l2 on l2.Lvl2 = o.NodeName where l2.Org_Lvl2_ID = @l2
LEFT JOIN org_lvl1 l1 on l1.Lvl1 = o.NodeName where l1.Org_Lvl1_ID = @l1
select @ret
I got this result:
Server: Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'LEFT'.
April 24, 2008 at 9:02 am
I have posted an explanation why.
N 56°04'39.16"
E 12°55'05.25"
April 24, 2008 at 10:16 am
Replace your WHERE's with ON's...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 24, 2008 at 12:49 pm
I guess all the "WHERE" keywords in the LEFT JOINs should be AND:
LEFT JOIN org_lvl4 l4 on l4.Lvl4 = o.NodeName AND l4.Org_Lvl4_ID = @l4
LEFT JOIN org_lvl3 l3 on l3.Lvl3 = o.NodeName AND l3.Org_Lvl3_ID = @l3
LEFT JOIN org_lvl2 l2 on l2.Lvl2 = o.NodeName AND l2.Org_Lvl2_ID = @l2
LEFT JOIN org_lvl1 l1 on l1.Lvl1 = o.NodeName AND l1.Org_Lvl1_ID = @l1
April 24, 2008 at 1:47 pm
Huh?
Is this the twilight zone? I already posted that answer for both
1.5 and 3 hours ago...
N 56°04'39.16"
E 12°55'05.25"
April 24, 2008 at 2:03 pm
Missing data but non-null parameter. I didn't think of that and should have. Clever solution, you guys.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply