3707 Syntax error,expected something like a '"||"'or a 'FROM' keyword between a string or a Unicode character literal

Database

3707 Syntax error,expected something like a '"||"'or a 'FROM' keyword between a string or a Unicode character literal

Hi,

I am getting the following syntax error when I am trying to run my Query:

3707 Syntax error,expected something like a '"||"'or a 'FROM' keyword between a string or a Unicode character literal and the integer '20132014'.

I have narrowed down the error to the following portion of the WHERE clause in the SQL:

a11.BRDCAST_QTR_ID in (SELECT BRDCAST_QUARTER_ID FROM CALENDAR_BROADCAST_QUARTER WHERE BRDCAST_YEAR_ID = (substring('a11.DEAL_YEAR_CD in ('20132014')' from POSITION('(' IN 'a11.DEAL_YEAR_CD in ('20132014')') + 1 for 8) )) 

Please Help..

3 REPLIES
Teradata Employee

Re: 3707 Syntax error,expected something like a '"||"'or a 'FROM' keyword between a string or a Unicode character literal

Hi,

you might want to put double '' around 20132014, so that you include the whole a11.DEAL_YEAR_CD in ('20132014') string inside a character literal (if that is what you are trying to achieve).

Regards,

Vlad.

Teradata Employee

Re: 3707 Syntax error,expected something like a '"||"'or a 'FROM' keyword between a string or a Unicode character literal

The following query is thorwing an error 3707 Syntax error,expected something like a name or a unicode delimited identifier or an UDFCALLNAME Keyword between ) and ;

SELECT

CASE WHEN NONFUNDED <= 0 THEN NONFUNDED ELSE NONFUNDED * -1 END NONFUNDED,

CASE WHEN FUNDED <= 0 THEN FUNDED ELSE FUNDED * -1 END FUNDED

FROM

(

--NONFUNDED-

SELECT 

SYS.Business_Segment,

SYS.REGION_NAME AS Region,

SYS.AREA_NAME AS Area,

SYS.SYS_BRANCH_CODE AS Branch_Cd,

SYS.SYS_BRANCH_NAME AS Branch_name,

SUBSTR(APH.PARTY_ID,6) AS Account_Officer,

SUBSTR(AG.PRIMARY_ASSOC_PARTY_ID,6) AS Customer_ID,

PSD.Party_Score_Value AS Customer_Rating,

CUST.PARTY_NAME AS Customer_Name,--PARTY CUST

AG.ACCOUNT_NUM AS Account_Number,

SUBSTR(AP.PRODUCT_ID,3) AS Limit_ID,--ACCOUNT_PRODUCT ID

ACL.Acct_Limit_Amt AS NonFunded_Limit_Amount,

CAST (0 AS DECIMAL(18,4) ) AS Funded_Limit_Amt,

ACL2.ACCT_LIMIT_AMT AS NonFunded_Group_Limit,

CAST (0 AS DECIMAL(18,4) ) AS Funded_Group_Limit_Amt,

SUBSTR(AP.PRODUCT_ID,3) AS LIMIT_Product_ID,

LR.PRODUCT_NAME AS Contract_Prodcut_Name,

AG.CONTRACT_EXPIRATION_DT AS Maturity_Date,

PRD.Product_Name AS Product_Description,

SUBSTR(AG.product_id,3) AS PRODUCT_ID,

ASD.SBP_Cat_Desc AS SBP_Cat_Desc,

ASD.SBP_Company_Desc AS SBP_Company_Desc,

ASD.SBP_Sector_Desc AS SBP_Sector_Desc,

ASD.SBP_Seg_Desc AS SBP_Seg_Desc,

ASD.SBP_SS_Desc AS SBP_SS_Desc,

ASD.SBP_Sub_Cat_Desc AS SBP_Sub_Cat_Desc,

ASD.SSECTOR_Desc AS SSECTOR_Desc,

AG.Account_Currency_Cd AS Account_Currency_Cd,

SUBSTR(PRH.RELATED_Party_Id,6) AS LI_LIABILITY_NUMBER,

CAST (0 AS DECIMAL(18,4) ) AS funded,

ASDD.Acct_Crncy_End_Trn_Bl_Amt AS NonFunded_Foreign_Currency,

CAST (0 AS DECIMAL(18,4) )  AS Funded_Foreign_Currency,

ASDD.ending_trn_bal_amt AS Nonfunded,

GRP.PARTY_NAME AS Group_Name, --PARTY GRP-

COALESCE(ACL.Acct_Limit_Expiry_Dt, ACL2.ACCT_LIMIT_EXPIRY_DT) AS LI_EXPIRY_DATE,

GRL.party_limit_amT AS Customer_Revolving_limit,

GRL1.PARTY_LIMIT_AMT AS Customer_NonRevolving_Limit,

--GRL.rev_Non AS Customer_Revolving,

--GRL1.rev_Non AS Customer_NonRevolving,

GRA.party_limit_amt AS Group_Revolving_limit,

GRA1.PARTY_LIMIT_AMT AS Group_NonRevolving_Limit

--GRA.rev_Non AS Group_Revolving,

--GRA1.REV_NON AS  Group_NonRevolving

FROM 

ut_view.agreement ag

LEFT JOIN 

ut_view.account_credit_limit acl

ON 

ag.account_num=acl.account_num

AND

ag.account_modifier_num=acl.account_modifier_num

AND acl.credit_Limit_end_dt IS NULL

AND ACL.LIMIT_TYPE_CD = '1'

LEFT JOIN UT_VIEW.ACCOUNT_CREDIT_LIMIT AS ACL2

ON 

ag.account_num=acl2.account_num

AND

ag.account_modifier_num=acl2.account_modifier_num

AND acl2.credit_Limit_end_dt IS NULL

AND ACL2.LIMIT_TYPE_CD = '2'

LEFT JOIN ut_view.PRODUCT AS PRD

ON 

AG.PRODUCT_ID = PRD.PRODUCT_ID

AND

PRD.Product_Type_Cd = '1'

LEFT JOIN DP_MDB_LAYER2.ACCOUNT_SBP_DETAIL AS ASD

ON

AG.ACCOUNT_NUM = ASD.ACCOUNT_NUM

AND

AG.ACCOUNT_MODIFIER_NUM = ASD.ACCOUNT_MODIFIER_NUM

LEFT JOIN UT_HLP.SYS_BRANCH AS SYS

ON

AG.BRANCH_PARTY_ID = SYS.SYS_BRANCH_CODE

LEFT JOIN 

ut_view.account_product_hist ap

ON 

ag.account_num=ap.account_num

AND

ag.account_modifier_num=ap.account_modifier_num

AND

ap.Agreement_Product_Role_Cd=4

AND 

ap.Acct_Product_Assoc_End_Dt IS NULL

LEFT JOIN UT_VIEW.PRODUCT AS LR

ON

AP.PRODUCT_ID = LR.PRODUCT_ID

AND

LR.Product_Type_Cd = '6'

LEFT JOIN ut_view.AGREEMENT_PARTY_HIST AS APH

ON

AG.ACCOUNT_NUM = APH.ACCOUNT_NUM

AND

AG.ACCOUNT_MODIFIER_NUM = APH.ACCOUNT_MODIFIER_NUM

AND APH.Agreement_Party_Role_Cd = 2

AND APH.Agreement_Party_End_Dt IS NULL

LEFT JOIN 

ut_view.party_related_hist prh

ON 

ag.primary_assoc_party_id=prh.relates_party_id

AND PRH.Party_Relationship_End_Dt IS NULL 

AND 

PRH.Party_Relationship_Role_Cd='7'

LEFT JOIN 

ut_view.party cust

ON 

ag.primary_assoc_party_id=cust.party_id

LEFT JOIN UT_VIEW.PARTY_SCORE_DD AS PSD

ON

CUST.PARTY_ID = PSD.PARTY_ID

LEFT JOIN 

ut_view.party grp

ON 

prh.related_party_id=grp.party_id

LEFT JOIN 


SEL 

--global

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%010000%' THEN 'Revolving' ELSE 'NonRevolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='1'

and

limit_id  LIKE '%010000%' 

) a 

WHERE lim=1 

--group by 1,2,3

) GRL

ON 

prh.related_party_id=grl.party_id

LEFT JOIN 


SEL 

--global

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%020000%' THEN 'NonRevolving' ELSE 'Revolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='1'

and

limit_id  LIKE '%020000%' 

) a 

WHERE lim=1 

--group by 1,2,3

) GRL1

ON 

prh.related_party_id=grl1.party_id

LEFT JOIN 


SEL 

 ---group

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%010000%' THEN 'Revolving' ELSE 'NonRevolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='2'

and

 limit_id LIKE '%010000%'

) a 

WHERE lim=1 

--group by 1,2,3

) GRa

ON 

prh.relates_party_id=gra.party_id

LEFT JOIN 


SEL 

 ---group

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%020000%' THEN 'NonRevolving' ELSE 'Revolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='2'

and

 limit_id LIKE '%020000%'

) a 

WHERE lim=1 

--group by 1,2,3

) GRA1

ON 

prh.relates_party_id=gra1.party_id

LEFT JOIN ut_view.ACCOUNT_SUMMARY_DD AS ASDD

ON

AG.ACCOUNT_NUM = ASDD.ACCOUNT_NUM

AND

AG.ACCOUNT_MODIFIER_NUM = ASDD.ACCOUNT_MODIFIER_NUM

AND

ASDD.ACCOUNT_SUMMARY_DT = CAST( '2013-08-26' AS DATE)

WHERE 

--ag.account_modifier_num='F-LC'

--and

COALESCE(account_close_dt, CAST('2013-09-11' AS DATE))>= CAST('2013-09-11' AS DATE) 

AND  ag.product_id IN

(

'f-1124',

'f-1125',

'f-1170',

'f-23008',

'f-23010',

'f-23020',

'f-23110',

'f-23115',

'f-23125',

'f-23127',

'f-23130',

'f-23133',

'f-28005',

'f-28010',

'f-28015',

'f-28051',

'f-28061',

'f-28067'

) AND COALESCE(ending_Trn_bal_amt,0) <>0

UNION ALL

--UNFUNDED WITH PD---

SELECT 

SYS.Business_Segment,

SYS.REGION_NAME AS Region,

SYS.AREA_NAME AS Area,

SYS.SYS_BRANCH_CODE AS Branch_Cd,

SYS.SYS_BRANCH_NAME AS Branch_name,

SUBSTR(APH.PARTY_ID,6) AS Account_Officer,

SUBSTR(AG.PRIMARY_ASSOC_PARTY_ID,6) AS Customer_ID,

PSD.Party_Score_Value AS Customer_Rating,

CUST.PARTY_NAME AS Customer_Name,--PARTY CUST

AG.ACCOUNT_NUM AS Account_Number,

SUBSTR(AP.PRODUCT_ID,3) AS Limit_ID,--ACCOUNT_PRODUCT ID

ACL.Acct_Limit_Amt AS NonFunded_Limit_Amount,

CAST (0 AS DECIMAL(18,4) ) AS Funded_Limit_Amt,

ACL2.ACCT_LIMIT_AMT AS NonFunded_Group_Limit,

CAST (0 AS DECIMAL(18,4) ) AS Funded_Group_Limit_Amt,

SUBSTR(AP.PRODUCT_ID,3) AS LIMIT_Product_ID,

LR.PRODUCT_NAME AS Contract_Prodcut_Name,

AG.CONTRACT_EXPIRATION_DT AS Maturity_Date,

PRD.Product_Name AS Product_Description,

SUBSTR(AG.product_id,3) AS PRODUCT_ID,

ASD.SBP_Cat_Desc AS SBP_Cat_Desc,

ASD.SBP_Company_Desc AS SBP_Company_Desc,

ASD.SBP_Sector_Desc AS SBP_Sector_Desc,

ASD.SBP_Seg_Desc AS SBP_Seg_Desc,

ASD.SBP_SS_Desc AS SBP_SS_Desc,

ASD.SBP_Sub_Cat_Desc AS SBP_Sub_Cat_Desc,

ASD.SSECTOR_Desc AS SSECTOR_Desc,

AG.Account_Currency_Cd AS Account_Currency_Cd,

SUBSTR(PRH.RELATED_Party_Id,6) AS LI_LIABILITY_NUMBER,

 CAST (0 AS DECIMAL(18,4) ) AS funded,

ASDD.Acct_Crncy_End_Trn_Bl_Amt AS NonFunded_Foreign_Currency,

CAST (0 AS DECIMAL(18,4) )  AS Funded_Foreign_Currency,

ASDD.ending_trn_bal_amt AS Nonfunded,

GRP.PARTY_NAME AS Group_Name, --PARTY GRP-

COALESCE(ACL.Acct_Limit_Expiry_Dt, ACL2.ACCT_LIMIT_EXPIRY_DT) AS LI_EXPIRY_DATE,

GRL.party_limit_amT AS Customer_Revolving_limit,

GRL1.PARTY_LIMIT_AMT AS Customer_NonRevolving_Limit,

--GRL.rev_Non AS Customer_Revolving,

--GRL1.rev_Non AS Customer_NonRevolving,

GRA.party_limit_amt AS Group_Revolving_limit,

GRA1.PARTY_LIMIT_AMT AS Group_NonRevolving_Limit

--GRA.rev_Non AS Group_Revolving,

--GRA1.REV_NON AS  Group_NonRevolving

FROM 

ut_view.agreement ag

LEFT JOIN 

ut_view.account_credit_limit acl

ON 

ag.account_num=acl.account_num

AND

ag.account_modifier_num=acl.account_modifier_num

AND acl.credit_Limit_end_dt IS NULL

AND ACL.LIMIT_TYPE_CD = '1'

LEFT JOIN UT_VIEW.ACCOUNT_CREDIT_LIMIT AS ACL2

ON 

ag.account_num=acl2.account_num

AND

ag.account_modifier_num=acl2.account_modifier_num

AND acl2.credit_Limit_end_dt IS NULL

AND ACL2.LIMIT_TYPE_CD = '2'

LEFT JOIN ut_view.PRODUCT AS PRD

ON 

AG.PRODUCT_ID = PRD.PRODUCT_ID

AND

PRD.Product_Type_Cd = '1'

LEFT JOIN DP_MDB_LAYER2.ACCOUNT_SBP_DETAIL AS ASD

ON

AG.ACCOUNT_NUM = ASD.ACCOUNT_NUM

AND

AG.ACCOUNT_MODIFIER_NUM = ASD.ACCOUNT_MODIFIER_NUM

LEFT JOIN UT_HLP.SYS_BRANCH AS SYS

ON

AG.BRANCH_PARTY_ID = SYS.SYS_BRANCH_CODE

LEFT JOIN 

ut_view.account_product_hist ap

ON 

ag.account_num=ap.account_num

AND

ag.account_modifier_num=ap.account_modifier_num

AND

ap.Agreement_Product_Role_Cd=4

AND 

ap.Acct_Product_Assoc_End_Dt IS NULL

LEFT JOIN UT_VIEW.PRODUCT AS LR

ON

AP.PRODUCT_ID = LR.PRODUCT_ID

AND

LR.Product_Type_Cd = '6'

LEFT JOIN ut_view.AGREEMENT_PARTY_HIST AS APH

ON

AG.ACCOUNT_NUM = APH.ACCOUNT_NUM

AND

AG.ACCOUNT_MODIFIER_NUM = APH.ACCOUNT_MODIFIER_NUM

AND APH.Agreement_Party_Role_Cd = 2

AND APH.Agreement_Party_End_Dt IS NULL

LEFT JOIN 

ut_view.party_related_hist prh

ON 

ag.primary_assoc_party_id=prh.relates_party_id

AND PRH.Party_Relationship_End_Dt IS NULL 

AND 

PRH.Party_Relationship_Role_Cd='7'

LEFT JOIN 

ut_view.party cust

ON 

ag.primary_assoc_party_id=cust.party_id

LEFT JOIN UT_VIEW.PARTY_SCORE_DD AS PSD

ON

CUST.PARTY_ID = PSD.PARTY_ID

LEFT JOIN 

ut_view.party grp

ON 

prh.related_party_id=grp.party_id

LEFT JOIN 


SEL 

--global

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%010000%' THEN 'Revolving' ELSE 'NonRevolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='1'

and

limit_id  LIKE '%010000%' 

) a 

WHERE lim=1 

--group by 1,2,3

) GRL

ON 

prh.related_party_id=grl.party_id

LEFT JOIN 


SEL 

--global

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%020000%' THEN 'NonRevolving' ELSE 'Revolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='1'

and

limit_id  LIKE '%020000%' 

) a 

WHERE lim=1 

--group by 1,2,3

) GRL1

ON 

prh.related_party_id=grl1.party_id

LEFT JOIN 


SEL 

 ---group

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%010000%' THEN 'Revolving' ELSE 'NonRevolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='2'

and

 limit_id LIKE '%010000%'

) a 

WHERE lim=1 

--group by 1,2,3

) GRa

ON 

prh.relates_party_id=gra.party_id

LEFT JOIN 


SEL 

 ---group

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%020000%' THEN 'NonRevolving' ELSE 'Revolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='2'

and

 limit_id LIKE '%020000%'

) a 

WHERE lim=1 

--group by 1,2,3

) GRA1

ON 

prh.relates_party_id=gra1.party_id

LEFT JOIN ut_view.ACCOUNT_SUMMARY_DD AS ASDD

ON

AG.ACCOUNT_NUM = ASDD.ACCOUNT_NUM

AND

AG.ACCOUNT_MODIFIER_NUM = ASDD.ACCOUNT_MODIFIER_NUM

AND

ASDD.ACCOUNT_SUMMARY_DT = CAST( '2013-08-26' AS DATE)

WHERE

AG.account_modifier_num = 'f-pd'

/*and

 asdd.ending_trn_bal_amt<>0*/

AND

SYS.SYS_BRANCH_CODE <>'0900'

AND

ag.product_id IN

('f-1124',

'f-1125',

'f-1170',

'f-23008',

'f-23010',

'f-23020',

'f-23110',

'f-23115',

'f-23125',

'f-23127',

'f-23130',

'f-23133',

'f-28005',

'f-28010',

'f-28015',

'f-28051',

'f-28061',

'f-28067')

UNION ALL

SELECT 

SYS.Business_Segment,

SYS.REGION_NAME AS Region,

SYS.AREA_NAME AS Area,

SYS.SYS_BRANCH_CODE AS Branch_Cd,

SYS.SYS_BRANCH_NAME AS Branch_name,

SUBSTR(APH.PARTY_ID,6) AS Account_Officer,

SUBSTR(AG.PRIMARY_ASSOC_PARTY_ID,6) AS Customer_ID,

PSD.Party_Score_Value AS Customer_Rating,

CUST.PARTY_NAME AS Customer_Name,--PARTY CUST

AG.ACCOUNT_NUM AS Account_Number,

SUBSTR(AP.PRODUCT_ID,3) AS Limit_ID,--ACCOUNT_PRODUCT ID

 CAST (0 AS DECIMAL(18,4) ) AS Nonfunded_Limit_Amt,

ACL.Acct_Limit_Amt AS Funded_Limit_Amount,

CAST (0 AS DECIMAL(18,4) ) AS NonFunded_Group_Limit,

 ACL2.ACCT_LIMIT_AMT AS Funded_Group_Limit_Amt,

SUBSTR(AP.PRODUCT_ID,3) AS LIMIT_Product_ID,

LR.PRODUCT_NAME AS Contract_Prodcut_Name,

AG.CONTRACT_EXPIRATION_DT AS Maturity_Date,

PRD.Product_Name AS Product_Description,

SUBSTR(AG.product_id,3) AS PRODUCT_ID,

ASD.SBP_Cat_Desc AS SBP_Cat_Desc,

ASD.SBP_Company_Desc AS SBP_Company_Desc,

ASD.SBP_Sector_Desc AS SBP_Sector_Desc,

ASD.SBP_Seg_Desc AS SBP_Seg_Desc,

ASD.SBP_SS_Desc AS SBP_SS_Desc,

ASD.SBP_Sub_Cat_Desc AS SBP_Sub_Cat_Desc,

ASD.SSECTOR_Desc AS SSECTOR_Desc,

AG.Account_Currency_Cd AS Account_Currency_Cd,

SUBSTR(PRH.RELATED_Party_Id,6) AS LI_LIABILITY_NUMBER,

 ASDD.ending_trn_bal_amt AS funded,

CAST (0 AS DECIMAL(18,4) ) AS NonFunded_Foreign_Currency,

ASDD.Acct_Crncy_End_Trn_Bl_Amt  AS Funded_Foreign_Currency,

 CAST (0 AS DECIMAL(18,4) ) AS Nonfunded,

GRP.PARTY_NAME AS Group_Name, --PARTY GRP-

COALESCE(ACL.Acct_Limit_Expiry_Dt, ACL2.ACCT_LIMIT_EXPIRY_DT) AS LI_EXPIRY_DATE,

GRL.party_limit_amT AS Customer_Revolving_limit,

GRL1.PARTY_LIMIT_AMT AS Customer_NonRevolving_Limit,

--GRL.rev_Non AS Customer_Revolving,

--GRL1.rev_Non AS Customer_NonRevolving,

GRA.party_limit_amt AS Group_Revolving_limit,

GRA1.PARTY_LIMIT_AMT AS Group_NonRevolving_Limit

--GRA.rev_Non AS Group_Revolving,

--GRA1.REV_NON AS  Group_NonRevolving

FROM 

ut_view.agreement ag

LEFT JOIN 

ut_view.account_credit_limit acl

ON 

ag.account_num=acl.account_num

AND

ag.account_modifier_num=acl.account_modifier_num

AND acl.credit_Limit_end_dt IS NULL

AND ACL.LIMIT_TYPE_CD = '1'

LEFT JOIN UT_VIEW.ACCOUNT_CREDIT_LIMIT AS ACL2

ON 

ag.account_num=acl2.account_num

AND

ag.account_modifier_num=acl2.account_modifier_num

AND acl2.credit_Limit_end_dt IS NULL

AND ACL2.LIMIT_TYPE_CD = '2'

LEFT JOIN ut_view.PRODUCT AS PRD

ON 

AG.PRODUCT_ID = PRD.PRODUCT_ID

AND

PRD.Product_Type_Cd = '1'

LEFT JOIN DP_MDB_LAYER2.ACCOUNT_SBP_DETAIL AS ASD

ON

AG.ACCOUNT_NUM = ASD.ACCOUNT_NUM

AND

AG.ACCOUNT_MODIFIER_NUM = ASD.ACCOUNT_MODIFIER_NUM

LEFT JOIN UT_HLP.SYS_BRANCH AS SYS

ON

AG.BRANCH_PARTY_ID = SYS.SYS_BRANCH_CODE

LEFT JOIN 

ut_view.account_product_hist ap

ON 

ag.account_num=ap.account_num

AND

ag.account_MODIFIER_num=ap.account_modifier_num

AND

ap.Agreement_Product_Role_Cd=4

AND 

ap.Acct_Product_Assoc_End_Dt IS NULL

LEFT JOIN UT_VIEW.PRODUCT AS LR

ON

AP.PRODUCT_ID = LR.PRODUCT_ID

AND

LR.Product_Type_Cd = '6'

LEFT JOIN ut_view.AGREEMENT_PARTY_HIST AS APH

ON

AG.ACCOUNT_NUM = APH.ACCOUNT_NUM

AND

AG.ACCOUNT_MODIFIER_NUM = APH.ACCOUNT_MODIFIER_NUM

AND APH.Agreement_Party_Role_Cd = 2

AND APH.Agreement_Party_End_Dt IS NULL

LEFT JOIN 

ut_view.party_related_hist prh

ON 

ag.primary_assoc_party_id=prh.relates_party_id

AND PRH.Party_Relationship_End_Dt IS NULL 

AND 

PRH.Party_Relationship_Role_Cd='7'

LEFT JOIN 

ut_view.party cust

ON 

ag.primary_assoc_party_id=cust.party_id

LEFT JOIN UT_VIEW.PARTY_SCORE_DD AS PSD

ON

CUST.PARTY_ID = PSD.PARTY_ID

LEFT JOIN 

ut_view.party grp

ON 

prh.related_party_id=grp.party_id

LEFT JOIN 


SEL 

--global

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%010000%' THEN 'Revolving' ELSE 'NonRevolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='1'

and

limit_id  LIKE '%010000%' 

) a 

WHERE lim=1 

--group by 1,2,3

) GRL

ON 

prh.related_party_id=grl.party_id

LEFT JOIN 


SEL 

--global

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%020000%' THEN 'NonRevolving' ELSE 'Revolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='1'

and

limit_id  LIKE '%020000%' 

) a 

WHERE lim=1 

--group by 1,2,3

) GRL1

ON 

prh.related_party_id=grl1.party_id

LEFT JOIN 


SEL 

 ---group

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%010000%' THEN 'Revolving' ELSE 'NonRevolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='2'

and

 limit_id LIKE '%010000%'

) a 

WHERE lim=1 

--group by 1,2,3

) GRa

ON 

prh.relates_party_id=gra.party_id

LEFT JOIN 


SEL 

 ---group

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%020000%' THEN 'NonRevolving' ELSE 'Revolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='2'

and

 limit_id LIKE '%020000%'

) a 

WHERE lim=1 

--group by 1,2,3

) GRA1

ON 

prh.relates_party_id=gra1.party_id

LEFT JOIN ut_view.ACCOUNT_SUMMARY_DD AS ASDD

ON

AG.ACCOUNT_NUM = ASDD.ACCOUNT_NUM

AND

AG.ACCOUNT_MODIFIER_NUM = ASDD.ACCOUNT_MODIFIER_NUM

AND

ASDD.ACCOUNT_SUMMARY_DT = CAST( '2013-08-26' AS DATE)

WHERE

AG.DATA_SOURCE_CD IN  (38)

AND

 asdd.ending_trn_bal_amT <>0

AND

SYS.SYS_BRANCH_CODE <>'0900'

AND

ag.product_id IN

('f-21071',

'f-1193',

'f-1205',

'f-25053',

'f-1126',

'f-1155',

'f-25035',

'f-25020',

'f-25039',

'f-25032',

'f-25034',

'f-25199',

'f-25024',

'f-21058',

'f-1001',

'f-1006',

'f-1010',

'f-1150',

'f-21051',

'f-1007',

'f-25052',

'f-1191',

'f-21066',

'f-21052',

'f-23018',

'f-1209',

'f-1212',

'f-25026',

'f-25038',

'f-1206',

'f-25033',

'f-25022',

'f-25029',

'f-25040',

'f-25030',

'f-25037',

'f-25011',

'f-25012',

'f-25013',

'f-25014',

'f-25015',

'f-25016',

'f-25103',

'f-25101',

'f-25102',

'f-25061',

'f-25062',

'f-1173',

'f-1171',

'f-1172',

'f-1176',

'f-1115',

'f-1106',

'f-1107',

'f-1116',

'f-1117',

'f-21056',

'f-1195',

'f-25023',

'f-25027',

'f-1196',

'f-25031',

'f-25021')

UNION ALL

SELECT 

SYS.Business_Segment,

SYS.REGION_NAME AS Region,

SYS.AREA_NAME AS Area,

SYS.SYS_BRANCH_CODE AS Branch_Cd,

SYS.SYS_BRANCH_NAME AS Branch_name,

SUBSTR(APH.PARTY_ID,6) AS Account_Officer,

SUBSTR(AG.PRIMARY_ASSOC_PARTY_ID,6) AS Customer_ID,

PSD.Party_Score_Value AS Customer_Rating,

CUST.PARTY_NAME AS Customer_Name,--PARTY CUST

AG.ACCOUNT_NUM AS Account_Number,

SUBSTR(AP.PRODUCT_ID,3) AS Limit_ID,--ACCOUNT_PRODUCT ID

 CAST (0 AS DECIMAL(18,4) ) AS Nonfunded_Limit_Amt,

ACL.Acct_Limit_Amt AS Funded_Limit_Amount,

 CAST (0 AS DECIMAL(18,4) ) AS NonFunded_Group_Limit,

 ACL2.ACCT_LIMIT_AMT AS Funded_Group_Limit_Amt,

SUBSTR(AP.PRODUCT_ID,3) AS LIMIT_Product_ID,

LR.PRODUCT_NAME AS Contract_Prodcut_Name,

AG.CONTRACT_EXPIRATION_DT AS Maturity_Date,

PRD.Product_Name AS Product_Description,

SUBSTR(AG.product_id,3) AS PRODUCT_ID,

ASD.SBP_Cat_Desc AS SBP_Cat_Desc,

ASD.SBP_Company_Desc AS SBP_Company_Desc,

ASD.SBP_Sector_Desc AS SBP_Sector_Desc,

ASD.SBP_Seg_Desc AS SBP_Seg_Desc,

ASD.SBP_SS_Desc AS SBP_SS_Desc,

ASD.SBP_Sub_Cat_Desc AS SBP_Sub_Cat_Desc,

ASD.SSECTOR_Desc AS SSECTOR_Desc,

AG.Account_Currency_Cd AS Account_Currency_Cd,

SUBSTR(PRH.RELATED_Party_Id,6) AS LI_LIABILITY_NUMBER,

 ASDD.ending_trn_bal_amt AS funded,

CAST (0 AS DECIMAL(18,4) ) AS NonFunded_Foreign_Currency,

ASDD.Acct_Crncy_End_Trn_Bl_Amt  AS Funded_Foreign_Currency,

 CAST (0 AS DECIMAL(18,4) ) AS Nonfunded,

GRP.PARTY_NAME AS Group_Name, --PARTY GRP-

COALESCE(ACL.Acct_Limit_Expiry_Dt, ACL2.ACCT_LIMIT_EXPIRY_DT) AS LI_EXPIRY_DATE,

GRL.party_limit_amT AS Customer_Revolving_limit,

GRL1.PARTY_LIMIT_AMT AS Customer_NonRevolving_Limit,

--GRL.rev_Non AS Customer_Revolving,

--GRL1.rev_Non AS Customer_NonRevolving,

GRA.party_limit_amt AS Group_Revolving_limit,

GRA1.PARTY_LIMIT_AMT AS Group_NonRevolving_Limit

--GRA.rev_Non AS Group_Revolving,

--GRA1.REV_NON AS  Group_NonRevolving

FROM 

ut_view.agreement ag

LEFT JOIN 

ut_view.account_credit_limit acl

ON 

ag.account_num=acl.account_num

AND

ag.account_modifier_num=acl.account_modifier_num

AND acl.credit_Limit_end_dt IS NULL

AND ACL.LIMIT_TYPE_CD = '1'

LEFT JOIN UT_VIEW.ACCOUNT_CREDIT_LIMIT AS ACL2

ON 

ag.account_num=acl2.account_num

AND

ag.account_modifier_num=acl2.account_modifier_num

AND acl2.credit_Limit_end_dt IS NULL

AND ACL2.LIMIT_TYPE_CD = '2'

LEFT JOIN ut_view.PRODUCT AS PRD

ON 

AG.PRODUCT_ID = PRD.PRODUCT_ID

AND

PRD.Product_Type_Cd = '1'

LEFT JOIN DP_MDB_LAYER2.ACCOUNT_SBP_DETAIL AS ASD

ON

AG.ACCOUNT_NUM = ASD.ACCOUNT_NUM

AND

AG.ACCOUNT_MODIFIER_NUM = ASD.ACCOUNT_MODIFIER_NUM

LEFT JOIN UT_HLP.SYS_BRANCH AS SYS

ON

AG.BRANCH_PARTY_ID = SYS.SYS_BRANCH_CODE

LEFT JOIN 

ut_view.account_product_hist ap

ON 

ag.account_num=ap.account_num

AND

ag.account_MODIFIER_num=ap.account_modifier_num

AND

ap.Agreement_Product_Role_Cd=4

AND 

ap.Acct_Product_Assoc_End_Dt IS NULL

LEFT JOIN UT_VIEW.PRODUCT AS LR

ON

AP.PRODUCT_ID = LR.PRODUCT_ID

AND

LR.Product_Type_Cd = '6'

LEFT JOIN ut_view.AGREEMENT_PARTY_HIST AS APH

ON

AG.ACCOUNT_NUM = APH.ACCOUNT_NUM

AND

AG.ACCOUNT_MODIFIER_NUM = APH.ACCOUNT_MODIFIER_NUM

AND APH.Agreement_Party_Role_Cd = 2

AND APH.Agreement_Party_End_Dt IS NULL

LEFT JOIN 

ut_view.party_related_hist prh

ON 

ag.primary_assoc_party_id=prh.relates_party_id

AND PRH.Party_Relationship_End_Dt IS NULL 

AND 

PRH.Party_Relationship_Role_Cd='7'

LEFT JOIN 

ut_view.party cust

ON 

ag.primary_assoc_party_id=cust.party_id

LEFT JOIN UT_VIEW.PARTY_SCORE_DD AS PSD

ON

CUST.PARTY_ID = PSD.PARTY_ID

LEFT JOIN 

ut_view.party grp

ON 

prh.related_party_id=grp.party_id

LEFT JOIN 


SEL 

--global

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%010000%' THEN 'Revolving' ELSE 'NonRevolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='1'

and

limit_id  LIKE '%010000%' 

) a 

WHERE lim=1 

--group by 1,2,3

) GRL

ON 

prh.related_party_id=grl.party_id

LEFT JOIN 


SEL 

--global

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%020000%' THEN 'NonRevolving' ELSE 'Revolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='1'

and

limit_id  LIKE '%020000%' 

) a 

WHERE lim=1 

--group by 1,2,3

) GRL1

ON 

prh.related_party_id=grl1.party_id

LEFT JOIN 


SEL 

 ---group

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%010000%' THEN 'Revolving' ELSE 'NonRevolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='2'

and

 limit_id LIKE '%010000%'

) a 

WHERE lim=1 

--group by 1,2,3

) GRa

ON 

prh.relates_party_id=gra.party_id

LEFT JOIN 


SEL 

 ---group

party_id ,

party_limit_amt ,

rev_non

FROM 

(

SELECT 

party_id ,

party_limit_amt ,

CASE WHEN limit_id LIKE '%020000%' THEN 'NonRevolving' ELSE 'Revolving'

END rev_Non

, ROW_NUMBER() OVER (PARTITION BY party_id,rev_non ORDER BY limit_id ASC)  lim

FROM 

ut_view.Party_limit 

WHERE limit_type_cd='2'

and

 limit_id LIKE '%020000%'

) a 

WHERE lim=1 

--group by 1,2,3

) GRA1

ON 

prh.relates_party_id=gra1.party_id

LEFT JOIN ut_view.ACCOUNT_SUMMARY_DD AS ASDD

ON

AG.ACCOUNT_NUM = ASDD.ACCOUNT_NUM

AND

AG.ACCOUNT_MODIFIER_NUM = ASDD.ACCOUNT_MODIFIER_NUM

AND

ASDD.ACCOUNT_SUMMARY_DT = CAST( '2013-08-26' AS DATE)

WHERE

AG.DATA_SOURCE_CD IN  (39,35,30)

AND

 asdd.ending_trn_bal_amt<0

AND

ag.product_id IN

('f-21071',

'f-1193',

'f-1205',

'f-25053',

'f-1126',

'f-1155',

'f-25035',

'f-25020',

'f-25039',

'f-25032',

'f-25034',

'f-25199',

'f-25024',

'f-21058',

'f-1001',

'f-1006',

'f-1010',

'f-1150',

'f-21051',

'f-1007',

'f-25052',

'f-1191',

'f-21066',

'f-21052',

'f-23018',

'f-1209',

'f-1212',

'f-25026',

'f-25038',

'f-1206',

'f-25033',

'f-25022',

'f-25029',

'f-25040',

'f-25030',

'f-25037',

'f-25011',

'f-25012',

'f-25013',

'f-25014',

'f-25015',

'f-25016',

'f-25103',

'f-25101',

'f-25102',

'f-25061',

'f-25062',

'f-1173',

'f-1171',

'f-1172',

'f-1176',

'f-1115',

'f-1106',

'f-1107',

'f-1116',

'f-1117',

'f-21056',

'f-1195',

'f-25023',

'f-25027',

'f-1196',

'f-25031',

'f-25021')

)


Junior Contributor

Re: 3707 Syntax error,expected something like a '"||"'or a 'FROM' keyword between a string or a Unicode character literal

Are you an Oracle user? :-)

You need to add an alias name before the final ";".

Dieter