October 12, 2018 at 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
October 12, 2018 at 1:08 pm
shummi21 - Friday, October 12, 2018 12:57 PMHello 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.
October 12, 2018 at 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
October 12, 2018 at 1:30 pm
shummi21 - Friday, October 12, 2018 1:23 PMHi 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)
October 12, 2018 at 2:10 pm
Still No Luck!!! Same error!!
October 12, 2018 at 2:20 pm
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy