Littleghostface
asked on
Why isn't my sql scalar function not working within my case statement.
Hi,
Could some please tell me why doesn't the functions work within the case statement but work while outside of the case statement.
Code Below:
SELECT
cb1.BatchId
,cb1.TypeId
,'\\servername\e$\Letters\ '+ct1.[Key ]+'_'+CONV ERT(VARCHA R(8), GETDATE(), 112)+'_Id'+CONVERT(varchar (5),cb1.Ba tchId)+'.x ml' [XMLfilename]
, dbo.ufnGetDoctorLetterXML (cb1.BatchId) --- works here.
,case
when cb1.TypeId = 1 then
(select dbo.ufnGetDoctorLetterXML (cb1.BatchId) )
when cb1.TypeId = 2 then
(select dbo.ufnGetPatientLetterXML (cb1.Batch Id) )
END [Data]
FROM
Batch cb1
inner join Type ct1 on cb1.TypeId = ct1.TypeId
Where
cb1.IsProcessed = 0
Could some please tell me why doesn't the functions work within the case statement but work while outside of the case statement.
Code Below:
SELECT
cb1.BatchId
,cb1.TypeId
,'\\servername\e$\Letters\
, dbo.ufnGetDoctorLetterXML (cb1.BatchId) --- works here.
,case
when cb1.TypeId = 1 then
(select dbo.ufnGetDoctorLetterXML (cb1.BatchId) )
when cb1.TypeId = 2 then
(select dbo.ufnGetPatientLetterXML
END [Data]
FROM
Batch cb1
inner join Type ct1 on cb1.TypeId = ct1.TypeId
Where
cb1.IsProcessed = 0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I understand now. I believe that sas13's solution is correct. Have you tried it?
ASKER
@sas13 - Nope tried that already. The statement runs but it returns NULL for my function results. If I was to do this then it works.
Case
when cb1.Type = 1 then
dbo.ufnGetDoctorXML(225) END [DATA]
Case
when cb1.Type = 1 then
dbo.ufnGetDoctorXML(225) END [DATA]
test ;)
declare @t table (val int)
insert into @t values (1), (2), (3)
select Case
when 1 = 1 then SUBSTRING('abcdefgh', t.val, t.val) END [DATA] from @t t
i use SUBSTRING function instead of ufnGetDoctorLetterXML
declare @t table (val int)
insert into @t values (1), (2), (3)
select Case
when 1 = 1 then SUBSTRING('abcdefgh', t.val, t.val) END [DATA] from @t t
i use SUBSTRING function instead of ufnGetDoctorLetterXML
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I built some test tables and functions and this seems to work:
SELECT
cb1.BatchId
,cb1.TypeId
,'\\servername\e$\Letters\ '+ct1.[Key ]+'_'+CONV ERT(VARCHA R(8), GETDATE(), 112)+'_Id'+CONVERT(varchar (5),cb1.Ba tchId)+'.x ml' [XMLfilename]
, dbo.ufnGetDoctorLetterXML (cb1.BatchId) --- works here.
,[Data]= case
when cb1.TypeId = 1 then
dbo.ufnGetDoctorLetterXML (cb1.BatchId)
when cb1.TypeId = 2 then
dbo.ufnGetPatientLetterXML (cb1.Batch Id)
END
FROM
Batch cb1
inner join Type ct1 on cb1.TypeId = ct1.TypeId
Where
cb1.IsProcessed = 0
SELECT
cb1.BatchId
,cb1.TypeId
,'\\servername\e$\Letters\
, dbo.ufnGetDoctorLetterXML (cb1.BatchId) --- works here.
,[Data]= case
when cb1.TypeId = 1 then
dbo.ufnGetDoctorLetterXML (cb1.BatchId)
when cb1.TypeId = 2 then
dbo.ufnGetPatientLetterXML
END
FROM
Batch cb1
inner join Type ct1 on cb1.TypeId = ct1.TypeId
Where
cb1.IsProcessed = 0
I also went back and ran sas13's code and it works also. Can you check that you are passing in the correct BatchID (per ewangoya's suggestion above)?
ASKER
@Forefront_Data_Solutions - what works? Are you receiving an XML out under the case statement or when the function is used as a regular column?
Doing the temp variable table seems to work from @sas13. I'm still doing some testing.
Doing the temp variable table seems to work from @sas13. I'm still doing some testing.
I don't have any clue as to what your Functions are supposed to return. You had asked why the functions don't work in the case statement, so I created simple functions that return an integer. I verified that the Case statements provided by sas13 and myself work correctly. What we can't check is whether you are passing in the correct values, nor can we determine what your function is doing or what it is supposed to return. If you would provide that code, we would be glad to help.
ASKER
To everyone. Sorry for wasting everyone times. This was my mistake. After all my statement was correct. If you notice I am using two different functions for each Case. I was using the wrong function for the Case type. I had to flip flop them. I am now receiving my XML output. I going to accept all multiple answers if they will let me. Thanks Again.
I changed my test functions to return XML and they are also working correctly.
Results from my test data:
Capture.JPG
Results from my test data:
Capture.JPG
ASKER