Case Statement

[#1]

Hi,

Can anyone please help me on the following. I get Syntax error.

eturnReasonsDescription:

LOAD

*,

CASE

WHEN

APPL_REFER_DESC = 'DCI (Debt Consolidation Illustrator)' or APPL_REFER_DESC = 'Appropriate Approval Authority required' or APPL_REFER_DESC = 'Full Lender Report required' or APPL_REFER_DESC = 'NAPS Illustration incorrect' or APPL_REFER_DESC = 'Emergence Period Template not included' or APPL_REFER_DESC = 'Referred to incorrect credit decisioning centre' or APPL_REFER_DESC = 'Pricing' or APPL_REFER_DESC = 'Other Process – see Comment' THEN 'Process'

WHEN

APPL_REFER_DESC = 'PFP' or APPL_REFER_DESC = 'Error in ADV 4' or APPL_REFER_DESC = 'Error in Discounted Cashflows' or APPL_REFER_DESC = 'Demos and/or A&L’s' or APPL_REFER_DESC = 'A&L differs to Inv / Equity' or APPL_REFER_DESC = 'Borrower/ Financial info' or APPL_REFER_DESC = 'Sector Code' or APPL_REFER_DESC = 'AMD (Asset Management Database)' or APPL_REFER_DESC = 'Other Incorrect Info - see Comment' THEN 'Incorrect Info'

WHEN

APPL_REFER_DESC = 'Special Condition Required' or APPL_REFER_DESC = 'Credit Structure Amendment proposed to RM' or APPL_REFER_DESC = 'Further discussion / clarification required' or APPL_REFER_DESC = 'Other Additional Info Required – see Comment' THEN 'Additional Info Required' ,

WHEN

APPL_REFER_DESC = 'Cons not addressed sufficiently' or APPL_REFER_DESC = 'ICB / Arrears / Unpaids' or APPL_REFER_DESC = 'Credit Grading' or APPL_REFER_DESC = 'Tax Confirmation' or APPL_REFER_DESC = 'F&L Checklist for New Money' or APPL_REFER_DESC = 'Exception to Policy not addressed' or APPL_REFER_DESC = 'Stress Testing' or APPL_REFER_DESC = 'Repayment Capacity' or APPL_REFER_DESC = 'Impairment Test Required' or APPL_REFER_DESC = 'Other Credit Assessment Info – see Comment' THEN 'Credit Assessment Information' ,

WHEN

APPL_REFER_DESC = 'SINs incorrect / not attached to NAPS' or APPL_REFER_DESC = 'Security Valuation incorrect' or APPL_REFER_DESC = 'Other Security- see Comment' THEN 'Security'

ELSE

''

END

Return_Reasons

;

SQL

SELECT

"APPL_REFER_DESC",

"APPL_REFER_GRP_CDE",

"APPL_REFER_RSN_CDE",

"CREATED_STAFF_ID",

"CREATED_TMSTMP"

FROM

DDd1P."REASONS_REF";

[#2]

Massimo thanks but i used the following it's gives me only dashes:

ReturnReasonsDescription:

LOAD

APPL_REFER_DESC,

pick

(wildmatch('DCI (Debt Consolidation Illustrator)','Appropriate Approval Authority required',

'Full Lender Report required'

,'NAPS Illustration incorrect','Emergence Period Template not included',

'Referred to incorrect credit decisioning centre'

,'Pricing','Other Process – see Comment','PFP','Error in ADV 4',

'Error in Discounted Cashflows'

,'Demos and/or A&L’s','A&L differs to Inv / Equity','Borrower/ Financial info','Sector Code',

'AMD (Asset Management Database)'

,'Other Incorrect Info - see Comment','Special Condition Required',

'Credit Structure Amendment proposed to RM'

,'Further discussion / clarification required',

'Other Additional Info Required – see Comment'

,'Cons not addressed sufficiently','ICB / Arrears / Unpaids',

'Credit Grading'

,'Tax Confirmation','F&L Checklist for New Money','Exception to Policy not addressed','Stress Testing',

'Repayment Capacity'

,'Impairment Test Required','Other Credit Assessment Info – see Comment',

'SINs incorrect / not attached to NAPS'

,'Security Valuation incorrect','Other Security- see Comment','*'),'Process','Process','Process',

'Process'

,'Process','Process','Process','Process','Incorrect Info','Incorrect Info','Incorrect Info','Incorrect Info',

'Incorrect Info'

,'Incorrect Info','Incorrect Info','Incorrect Info','Incorrect Info','Additional Info Required',

'Additional Info Required'

,'Additional Info Required','Additional Info Required','Credit Assessment Information',

'Credit Assessment Information'

,'Credit Assessment Information','Credit Assessment Information','Credit Assessment Information',

'Credit Assessment Information'

,'Credit Assessment Information','Credit Assessment Information','Credit Assessment Information',

'Credit Assessment Information'

,'Security','Security','Security','unknown')

;

SQL

SELECT

"APPL_REFER_DESC",

"APPL_REFER_GRP_CDE",

"APPL_REFER_RSN_CDE",

"CREATED_STAFF_ID",

"CREATED_TMSTMP"

[maxgro]

It seems you miss the field APPL_REFER_DESC after the  wildmatch(

And start simple, with 2 or 3 values, when the expression works, add the other

[anbu1984 · Accepted solution]

Error was due to no space before and after "Or". Try this

LOAD *,

If(

APPL_REFER_DESC = 'DCI (Debt Consolidation Illustrator)' or APPL_REFER_DESC = 'Appropriate Approval Authority required' or APPL_REFER_DESC = 'Full Lender Report required' or APPL_REFER_DESC = 'NAPS Illustration incorrect' or APPL_REFER_DESC = 'Emergence Period Template not included' or APPL_REFER_DESC = 'Referred to incorrect credit decisioning centre' or APPL_REFER_DESC = 'Pricing' or APPL_REFER_DESC = 'Other Process – see Comment' , 'Process',

If(

APPL_REFER_DESC = 'PFP' or APPL_REFER_DESC = 'Error in ADV 4' or APPL_REFER_DESC = 'Error in Discounted Cashflows' or APPL_REFER_DESC = 'Demos and/or A&L’s' or APPL_REFER_DESC = 'A&L differs to Inv / Equity' or APPL_REFER_DESC ='Borrower/ Financial info' or APPL_REFER_DESC = 'Sector Code' or APPL_REFER_DESC ='AMD (Asset Management Database)'or APPL_REFER_DESC = 'Other Incorrect Info - see Comment' ,'Incorrect Info',

If(

APPL_REFER_DESC = 'Special Condition Required' or APPL_REFER_DESC = 'Credit Structure Amendment proposed to RM' or APPL_REFER_DESC = 'Further discussion / clarification required' or APPL_REFER_DESC = 'Other Additional Info Required – see Comment' ,'Additional Info Required' ,

If(

APPL_REFER_DESC = 'Cons not addressed sufficiently' or APPL_REFER_DESC = 'ICB / Arrears / Unpaids' or APPL_REFER_DESC = 'Credit Grading' or APPL_REFER_DESC = 'Tax Confirmation' or APPL_REFER_DESC = 'F&L Checklist for New Money' or APPL_REFER_DESC = 'Exception to Policy not addressed' or APPL_REFER_DESC = 'Stress Testing' or APPL_REFER_DESC = 'Repayment Capacity' or APPL_REFER_DESC = 'Impairment Test Required' or APPL_REFER_DESC ='Other Credit Assessment Info – see Comment' ,'Credit Assessment Information' ,

If(

APPL_REFER_DESC = 'SINs incorrect / not attached to NAPS' or APPL_REFER_DESC = 'Security Valuation incorrect' or APPL_REFER_DESC = 'Other Security- see Comment' ,'Security','')

)))) As Return_Reasons

;

[#5]

thanks Anbu