CASE condition inside openquery not working

Question

CASE condition inside openquery not working

shummi21

SSC Veteran

Points: 200
More actions
October 12, 2018 at 12:57 pm

#408920

Hello Everyone,
I need help with the below. When I am trying to run the below query using CASE condition, this is not working.
Query:-
select * from openquery(mc, 'select CMLN "LOAN NUMBER",substring(CMGLAC,1,2) "LOAN TYPE",CMHC1 "HoldCode1", SIFFUL "HoldCode Desc",
CMPAYO "PayOff Date", CMPBAL "Principal Balance",
@ISACTIVE = (CASE ([LOAN TYPE] = ''CL'' OR [LOAN TYPE] = ''PL'') AND ([PayOff Date] = ''0'' OR [Principal Balance] > ''0'')
WHEN [LOAN TYPE] = ''CL''
THEN
"Y"
ELSE
"N"
END),
from PMASTR INNER JOIN PINFO ON PMASTR.CMHC1 = PINFO.SIFCD where PINFO.SIFTID =''26'' and PMASTR.CMHC1 <> ''''')
Error:-
OLE DB provider "MSDASQL" for linked server "mc" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7973 - SQL create package for SQLSERAFBA in CPCTEST has failed.".
OLE DB provider "MSDASQL" for linked server "mc" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token = was not valid. Valid tokens: + - AS <IDENTIFIER>.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select CMLN "LOAN NUMBER",substring(CMGLAC,1,2) "LOAN TYPE",CMHC1 "HoldCode1", SIFFUL "HoldCode Desc",
CMPAYO "PayOff Date", CMPBAL "Principal Balance",
@ISACTIVE = (CASE ([LOAN TYPE] = 'CL' OR [LOAN TYPE] = 'PL') AND ([PayOff Date] = '0' OR [Principal Balance] > '0')
WHEN [LOAN TYPE] = 'CL'
THEN
"Y"
ELSE
"N"
END),
from PMASTR INNER JOIN PINFO ON PMASTR.CMHC1 = PINFO.SIFCD where PINFO.SIFTID ='26' and PMASTR.CMHC1 <> ''" for execution against OLE DB provider "MSDASQL" for linked server "mc".
Regards,
Aurobindo

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24285 More actions · Answer 1

shummi21 - Friday, October 12, 2018 12:57 PM
Hello Everyone,
I need help with the below. When I am trying to run the below query using CASE condition, this is not working.
Query:-
select * from openquery(mc, 'select CMLN "LOAN NUMBER",substring(CMGLAC,1,2) "LOAN TYPE",CMHC1 "HoldCode1", SIFFUL "HoldCode Desc",
CMPAYO "PayOff Date", CMPBAL "Principal Balance",
@ISACTIVE = (CASE ([LOAN TYPE] = ''CL'' OR [LOAN TYPE] = ''PL'') AND ([PayOff Date] = ''0'' OR [Principal Balance] > ''0'')
WHEN [LOAN TYPE] = ''CL''
THEN
"Y"
ELSE
"N"
END),
from PMASTR INNER JOIN PINFO ON PMASTR.CMHC1 = PINFO.SIFCD where PINFO.SIFTID =''26'' and PMASTR.CMHC1 <> ''''')
Error:-
OLE DB provider "MSDASQL" for linked server "mc" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7973 - SQL create package for SQLSERAFBA in CPCTEST has failed.".
OLE DB provider "MSDASQL" for linked server "mc" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token = was not valid. Valid tokens: + - AS <IDENTIFIER>.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select CMLN "LOAN NUMBER",substring(CMGLAC,1,2) "LOAN TYPE",CMHC1 "HoldCode1", SIFFUL "HoldCode Desc",
CMPAYO "PayOff Date", CMPBAL "Principal Balance",
@ISACTIVE = (CASE ([LOAN TYPE] = 'CL' OR [LOAN TYPE] = 'PL') AND ([PayOff Date] = '0' OR [Principal Balance] > '0')
WHEN [LOAN TYPE] = 'CL'
THEN
"Y"
ELSE
"N"
END),
from PMASTR INNER JOIN PINFO ON PMASTR.CMHC1 = PINFO.SIFCD where PINFO.SIFTID ='26' and PMASTR.CMHC1 <> ''" for execution against OLE DB provider "MSDASQL" for linked server "mc".
Regards,
Aurobindo

I have never used OPENQUERY, but I suspect that your error is due to the fact that you are trying to return fields and also update a variable at the same time. Try changing @ISACTIVE to ISACTIVE.

How to post data/code on a forum to get the best help.

shummi21 SSC Veteran Points: 200 More actions · Answer 2

Hi SSCoach,

Thanks for your response, I had tried the same also, that is using ISACTIVE instead of @ISACTIVE and still got the same error output.

Regards,
Aurobindo

DesNorton SSC-Insane Points: 24285 More actions · Answer 3

shummi21 - Friday, October 12, 2018 1:23 PM
Hi SSCoach,
Thanks for your response, I had tried the same also, that is using ISACTIVE instead of @ISACTIVE and still got the same error output.
Regards,
Aurobindo

Taking a closer look at the actual SQL, your CASE statement appears to be incorrect
Try this ..
ISACTIVE = (CASE WHEN ([LOAN TYPE] = ''CL'' OR [LOAN TYPE] = ''PL'') AND ([PayOff Date] = ''0'' OR [Principal Balance] > ''0'') THEN ''Y'' ELSE ''N'' END)

How to post data/code on a forum to get the best help.

shummi21 SSC Veteran Points: 200 More actions · Answer 4

shummi21

SSC Veteran

Points: 200

October 12, 2018 at 2:10 pm

#2009436

Still No Luck!!! Same error!!

frederico_fonseca SSCoach Points: 16041 More actions · Answer 5

a few possible issues
select CMLN "LOAN NUMBER"

, substring(CMGLAC,1,2) "LOAN TYPE" , CMHC1 "HoldCode1" , SIFFUL "HoldCode Desc" , CMPAYO "PayOff Date" , CMPBAL "Principal Balance" , @ISACTIVE = (CASE -- missing when clause ([LOAN TYPE] = 'CL' OR [LOAN TYPE] = 'PL') AND ([PayOff Date] = '0' OR [Principal Balance] > '0') -- missing then clause WHEN [LOAN TYPE] = 'CL' THEN "Y" ELSE "N" END) -- this may not work in DB2 - first its a variable, second XXX = YYY is T-SQL , -- extra comma from PMASTR INNER JOIN PINFO ON PMASTR.CMHC1 = PINFO.SIFCD where PINFO.SIFTID ='26' and PMASTR.CMHC1 <> ''

Possible solution
select CMLN "LOAN NUMBER" , substring(CMGLAC,1,2) "LOAN TYPE" , CMHC1 "HoldCode1" , SIFFUL "HoldCode Desc" , CMPAYO "PayOff Date" , CMPBAL "Principal Balance" , (CASE -- missing when clause

WHEN ([LOAN TYPE] = 'CL' OR [LOAN TYPE] = 'PL') AND ([PayOff Date] = '0' OR [Principal Balance] > '0') -- missing then clause THEN "Y" WHEN [LOAN TYPE] = 'CL' THEN "Y" ELSE "N" END) as ISACTIVE- this may not work in DB2 - first its a variable, second XXX = YYY is T-SQL -- , -- extra comma from PMASTR INNER JOIN PINFO ON PMASTR.CMHC1 = PINFO.SIFCD where PINFO.SIFTID ='26' and PMASTR.CMHC1 <> ''

when copying to your code escape the quotes above again