Link to home
Start Free TrialLog in
Avatar of Littleghostface
LittleghostfaceFlag for United States of America

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]+'_'+CONVERT(VARCHAR(8), GETDATE(), 112)+'_Id'+CONVERT(varchar(5),cb1.BatchId)+'.xml' [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.BatchId) )
     END [Data]
FROM
      Batch cb1
      inner join Type ct1 on cb1.TypeId = ct1.TypeId
Where
      cb1.IsProcessed = 0
ASKER CERTIFIED SOLUTION
Avatar of Forefront_Data_Solutions
Forefront_Data_Solutions
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Littleghostface

ASKER

@Forefront_Data_Solutions - my statement is correct.  If I was substitute the cb1.BatchId with the actual integer then it works.  I want to use the current cb1.BatchId of the current row as my function variable.
I understand now.  I believe that sas13's solution is correct.  Have you tried it?
@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]
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
SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I built some test tables and functions and this seems to work:

SELECT
      cb1.BatchId
    ,cb1.TypeId
    ,'\\servername\e$\Letters\'+ct1.[Key]+'_'+CONVERT(VARCHAR(8), GETDATE(), 112)+'_Id'+CONVERT(varchar(5),cb1.BatchId)+'.xml' [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.BatchId)
     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)?
@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.
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.  
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