i'm getting sqlcode -104 when executing a select statement.
im getting below error:
SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: SELECTIVITY AND OR HAVING GROUP INTERSECT ORDER FETCH EXCEPT
its a big sql. might give a tough time to understand.
Code:
SELECT
T1.*,T2.*,
'<?xml version="1.0"?><com:FIMCC_ITEM_REQ xmlns:com='||
'"http://www.cigna.com/fi/1/" xmlns:contribns="http://www.cigna.com'||
'/fi/contrib/1/" xmlns:dclm'||
'="http://www.cigna.com/fi/dclm/1/" xmlns:tns="http://www.cigna.com/'||
'utility/1/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns'||
':xsi="'||
'http://www.w3.'||
'org/2001/XMLSchema-instance"><com:TrackingNumber><com:TraceNumber>'||
REPLACE(VARCHAR((NEXT VALUE FOR GOKH.CF_TRACE_NUM )),'.','')||
'</com:TraceNumber></com:TrackingNumber><com:SubscriberPersonID>'||
REPLACE(VARCHAR(T2.membr_persn_id),'.','')||
'</com:SubscriberPersonID><com:ClientAccountNumber>'||
T2.acct_num||
'</com:ClientAccountNumber><com:Scheduled>0</com:Scheduled><com:'||
'ResponseRequired>0</com:ResponseRequired>'||
CASE WHEN T1.DN <> T2.DN THEN
'<com:RestrictionItem><com:ReimbFundProdType>'||
RTRIM(T2.reimb_fund_ty)||
'</com:ReimbFundProdType><com:FundSubType>'||
RTRIM(T2.reimb_fund_sub_ty)||
'</com:FundSubType><com:PlanYearBeginDate>'||
CHAR(T2.clnt_fund_acct_e_dt)||
'</com:PlanYearBeginDate><com:PlanYearEndDate>'||
CHAR(T2.clnt_fund_acct_c_dt)||
'</com:PlanYearEndDate><com:ServiceCategory>DN</com:ServiceCategory>'||
'<com:AvailabilityIndicator>'||
CASE WHEN T1.DN = 'N' THEN '0'
ELSE '1' END ||'</com:AvailabilityIndicator><com:ServiceDate>'||
CHAR(T2.fund_Acct_e_dt)||
'</com:ServiceDate></com:RestrictionItem>'
ELSE '' END ||
CASE WHEN T1.GM <> T2.GM THEN
'<com:RestrictionItem><com:ReimbFundProdType>'||
RTRIM(T2.reimb_fund_ty)||
'</com:ReimbFundProdType><com:FundSubType>'||
RTRIM(T2.reimb_fund_sub_ty)||
'</com:FundSubType><com:PlanYearBeginDate>'||
CHAR(T2.clnt_fund_acct_e_dt)||
'</com:PlanYearBeginDate><com:PlanYearEndDate>'||
CHAR(T2.clnt_fund_acct_c_dt)||
'</com:PlanYearEndDate><com:ServiceCategory>GM</com:ServiceCategory>'||
'<com:AvailabilityIndicator>'||
CASE WHEN T1.GM = 'N' THEN '0'
ELSE '1' END ||'</com:AvailabilityIndicator><com:ServiceDate>'||
CHAR(T2.fund_Acct_e_dt)||
'</com:ServiceDate></com:RestrictionItem>'
ELSE '' END ||
CASE WHEN T1.RX <> T2.RX THEN
'<com:RestrictionItem><com:ReimbFundProdType>'||
RTRIM(T2.reimb_fund_ty)||
'</com:ReimbFundProdType><com:FundSubType>'||
RTRIM(T2.reimb_fund_sub_ty)||
'</com:FundSubType><com:PlanYearBeginDate>'||
CHAR(T2.clnt_fund_acct_e_dt)||
'</com:PlanYearBeginDate><com:PlanYearEndDate>'||
CHAR(T2.clnt_fund_acct_c_dt)||
'</com:PlanYearEndDate><com:ServiceCategory>RX</com:ServiceCategory>'||
'<com:AvailabilityIndicator>'||
CASE WHEN T1.RX = 'N' THEN '0'
ELSE '1' END ||'</com:AvailabilityIndicator><com:ServiceDate>'||
CHAR(T2.fund_Acct_e_dt)||
'</com:ServiceDate></com:RestrictionItem>'
ELSE '' END ||
CASE WHEN T1.VN <> T2.VN THEN
'<com:RestrictionItem><com:ReimbFundProdType>'||
RTRIM(T2.reimb_fund_ty)||
'</com:ReimbFundProdType><com:FundSubType>'||
RTRIM(T2.reimb_fund_sub_ty)||
'</com:FundSubType><com:PlanYearBeginDate>'||
CHAR(T2.clnt_fund_acct_e_dt)||
'</com:PlanYearBeginDate><com:PlanYearEndDate>'||
CHAR(T2.clnt_fund_acct_c_dt)||
'</com:PlanYearEndDate><com:ServiceCategory>VN</com:ServiceCategory>'||
'<com:AvailabilityIndicator>'||
CASE WHEN T1.VN = 'N' THEN '0'
ELSE '1' END ||'</com:AvailabilityIndicator><com:ServiceDate>'||
CHAR(T2.fund_Acct_e_dt)||
'</com:ServiceDate></com:RestrictionItem>'
ELSE '' END ||
'</com:FIMCC_ITEM_REQ>'
FROM (
SELECT A.acct_num,A.membr_persn_id,A.reimb_fund_ty,
A.reimb_fund_sub_ty,A.clnt_fund_acct_e_Dt,C.clnt_fund_acct_c_Dt,
A.fund_acct_e_Dt,membr_fund_stat_Cd,--b.last_timestmp,
MAX(CASE WHEN DEBIT_CARD_SVC_CATEG_CD = 'DN'
THEN CASE WHEN DEBIT_CARD_SVC_CATEG_AVAIL_IND = 'Y'
THEN 'N' ELSE 'Y' END END) AS DN,
MAX(CASE WHEN DEBIT_CARD_SVC_CATEG_CD = 'GM'
THEN CASE WHEN DEBIT_CARD_SVC_CATEG_AVAIL_IND = 'Y'
THEN 'N' ELSE 'Y' END END) AS GM,
MAX(CASE WHEN DEBIT_CARD_SVC_CATEG_CD = 'RX'
THEN CASE WHEN DEBIT_CARD_SVC_CATEG_AVAIL_IND = 'Y'
THEN 'N' ELSE 'Y' END END) AS RX,
MAX(CASE WHEN DEBIT_CARD_SVC_CATEG_CD = 'VN'
THEN CASE WHEN DEBIT_CARD_SVC_CATEG_AVAIL_IND = 'Y'
THEN 'N' ELSE 'Y' END END) AS VN
FROM
GOKH.MEMBR_FUND_ACCT a
LEFT JOIN GOKH.MFA_DEBIT_CARD_SVC_CATEG B on
A.membr_fund_acct_id = B.membr_fund_acct_id
LEFt JOIN GOKH.CLNT_FUND_ACCT C ON C.acct_num = A.acct_num
AND C.reimb_fund_ty = A.reimb_fund_ty
AND C.reimb_fund_sub_ty = A.reimb_fund_Sub_ty
AND c.clnt_fund_Acct_e_dt = A.clnt_fund_acct_e_dt
WHERE A.reimb_fund_sub_ty = 'BA'
AND A.reimb_fund_ty IN ('HRAGM','HRAHA')
AND membr_fund_stat_cd = 'A'
AND A.acct_num IN ('2465054','3155584','3174696','3177728',
'3192572','3210664','3324668','3333594','3333751','3333762',
'3334164','3313172')
--AND A.acct_num = &acct --'3315304'
AND a.clnt_fund_acct_e_dt = '2012-01-01'
--AND A.membr_persn_id = 486260380899308
GROUP BY A.acct_num,A.membr_persn_id,A.reimb_fund_ty,
A.reimb_fund_sub_ty,A.clnt_fund_acct_e_Dt,c.clnt_fund_acct_c_Dt,
A.fund_acct_e_Dt,membr_fund_stat_Cd--,b.last_timestmp
) T1 JOIN
(
SELECT A.acct_num,A.membr_persn_id,A.reimb_fund_ty,
A.reimb_fund_sub_ty,A.clnt_fund_acct_e_Dt,C.clnt_fund_acct_c_Dt,
A.fund_acct_e_Dt,membr_fund_stat_Cd,--b.last_timestmp,
MAX(CASE WHEN DEBIT_CARD_SVC_CATEG_CD = 'DN'
THEN CASE WHEN DEBIT_CARD_SVC_CATEG_AVAIL_IND = 'Y'
THEN 'N' ELSE 'Y' END END) AS DN,
MAX(CASE WHEN DEBIT_CARD_SVC_CATEG_CD = 'GM'
THEN CASE WHEN DEBIT_CARD_SVC_CATEG_AVAIL_IND = 'Y'
THEN 'N' ELSE 'Y' END END) AS GM,
MAX(CASE WHEN DEBIT_CARD_SVC_CATEG_CD = 'RX'
THEN CASE WHEN DEBIT_CARD_SVC_CATEG_AVAIL_IND = 'Y'
THEN 'N' ELSE 'Y' END END) AS RX,
MAX(CASE WHEN DEBIT_CARD_SVC_CATEG_CD = 'VN'
THEN CASE WHEN DEBIT_CARD_SVC_CATEG_AVAIL_IND = 'Y'
THEN 'N' ELSE 'Y' END END) AS VN
FROM
GOKH.MEMBR_FUND_ACCT a
LEFT JOIN GOKH.MFA_DEBIT_CARD_SVC_CATEG B on
A.membr_fund_acct_id = B.membr_fund_acct_id
LEFt JOIN GOKH.CLNT_FUND_ACCT C ON C.acct_num =
A.acct_num
AND C.reimb_fund_ty = A.reimb_fund_ty
AND C.reimb_fund_sub_ty = A.reimb_fund_Sub_ty
AND c.clnt_fund_Acct_e_dt = A.clnt_fund_acct_e_dt
WHERE A.reimb_fund_sub_ty = 'CO'
AND A.reimb_fund_ty IN ( 'HRAGM','HRAHA')
AND membr_fund_stat_cd = 'A'
AND A.acct_num IN ('2465054','3155584','3174696',
'3177728',
'3192572','3210664','3324668','3333594','3333751',
'3333762','3334164','3313172')
-- = &acct --'3315304'
AND a.clnt_fund_acct_e_dt = '2012-01-01'
--AND A.membr_persn_id = 486260380899308
GROUP BY A.acct_num,A.membr_persn_id,A.reimb_fund_ty,
A.reimb_fund_sub_ty,A.clnt_fund_acct_e_Dt,
c.clnt_fund_acct_c_Dt,A.fund_acct_e_Dt,membr_fund_stat_Cd
--,b.last_timestmp
) T2 ON T1.acct_num = T2.acct_num
AND T1.membr_persn_id = T2.membr_persn_id
AND T1.reimb_fund_ty = T2.reimb_fund_ty
AND T1.clnt_fund_acct_e_Dt =T2.clnt_fund_acct_e_dt
and t1.fund_acct_e_Dt = t2.fund_acct_e_Dt
AND (T1.DN<>T2.DN OR T1.GM<>T2.GM OR T1.RX<>T2.RX OR T1.VN<>T2.VN)
fetch first 2 rows only
WITH UR;
.jpg)