August 4, 2015 at 1:15 am
Hi Folks,
I'm getting an error as "Incorrect syntax near the keyword 'CASE' ". When trying to run this query. Please help on this.
CREATE PROCEDURE dbo.SP_CDB_EA2
@RiskRef varchar(100)
--drop table #Tmp
AS
BEGIN
SELECT L1PS.ORIG_TRAN_NO,
L1PS.ASSURED,
F108.ACCTG_RISK_REF,
F108.CURR_ISO_CODE_TR,
F108.TECH_TRAN_NUM,
F108.TECH_TRAN_VERSN,
Max(L1PS.POST_INST_NO) POST_INST_NO,
F234.TECH_TRAN_TYPE,
L1PS.ENTRY_NO
CASE
F234.TECH_TRAN_TYPE
WHEN 'ADJ'THEN 'A' + L1PS.ENTRY_NO
WHEN 'BAL'THEN 'A' + L1PS.ENTRY_NO
WHEN 'CLR'THEN 'A' + L1PS.ENTRY_NO
WHEN 'BAD'THEN 'A' + L1PS.ENTRY_NO
WHEN 'CP'THEN 'P' + L1PS.ENTRY_NO
WHEN 'CRD'THEN 'R' + L1PS.ENTRY_NO
WHEN 'SGB'THEN 'S' + L1PS.ENTRY_NO
ELSE 'E'
END AS 'ENTRY_NO_Prefix'
F036.TECH_TYPE_DESC,
F234.ACCTG_EFF_DT
CASE
WHEN MAX(F036.TECH_TRAN_TYPE) <> 'BKG'
then SUM(POST.POST_NETT_AMT)
else 0
end 'Client Nett Amount'
CASE
F234.TECH_TRAN_TYPE IN ('CRB','CRD','CPB','CPB')
WHEN SUM(POST.POST_BASE_AMT) 'Underwriter O/S',
SUM(POST.POST_SETT_AMT) 'Client O/S',
CONVERT(NUMERIC(18,3),0.000) 'Commission Amt O/S',
CONVERT(NUMERIC(18,3),0) 'Other Amt O/S'
F234.TRAN_DESC_1
INTO #Tmp
FROM F108
INNER JOIN F234 ON F108.ACCTG_TRAN_NO = F234.ACCTG_TRAN_NO
INNER JOIN F036 ON F036.TECH_TRAN_TYPE = F234.TECH_TRAN_TYPE
INNER JOIN L1PS ON L1PS.ORIG_TRAN_NO = F234.ACCTG_TRAN_NO
INNER JOIN POST ON POST.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO
AND POST.POSTING_LVL1_KEY = L1PS.POSTING_KEY
WHERE F108.ACCTG_RISK_REF = @RiskRef
F108.ACCTG_RISK_REF LIKE 'DV3%'
group by L1PS.ORIG_TRAN_NO,
L1PS.ASSURED,
F108.ACCTG_RISK_REF,
F108.CURR_ISO_CODE_TR,
F108.TECH_TRAN_NUM,
F108.TECH_TRAN_VERSN,
L1PS.POST_INST_NO,
F234.TECH_TRAN_TYPE ,
L1PS.ENTRY_NO,
F036.TECH_TYPE_DESC,
F234.ACCTG_EFF_DT,
F234.TRAN_DESC_1
order by F108.ACCTG_RISK_REF,
ISNULL(F108.TECH_TRAN_NUM,999),
ISNULL(F108.TECH_TRAN_VERSN,999),
L1PS.ENTRY_NO
August 4, 2015 at 1:30 am
Case x when 'y' then 'z' end
You need the 'then' and you need the 'end'.
August 4, 2015 at 1:54 am
Hi Phil,
Thanks for checking my query.
Can you please let me know where i need to do changes.
Thank you.
August 4, 2015 at 2:29 am
Sorry, your CASE construct is generally OK.
CASE F234.TECH_TRAN_TYPE IN (
'CRB'
,'CRD'
,'CPB'
,'CPB'
)
Should be CASE WHEN ...
I suggest that you cut back the proc definition to one that works and then start adding chunks until it breaks.
August 4, 2015 at 2:31 am
reddychaitanyakrishna (8/4/2015)
Hi Phil,Thanks for checking my query.
Can you please let me know where i need to do changes.
Thank you.
Many issues. . . comments inline.
CREATE PROCEDURE dbo.SP_CDB_EA2 @RiskRef VARCHAR(100)
--drop table #Tmp
AS
BEGIN
SELECT L1PS.ORIG_TRAN_NO,
L1PS.ASSURED,
F108.ACCTG_RISK_REF,
F108.CURR_ISO_CODE_TR,
F108.TECH_TRAN_NUM,
F108.TECH_TRAN_VERSN,
MAX(L1PS.POST_INST_NO) POST_INST_NO,
F234.TECH_TRAN_TYPE,
L1PS.ENTRY_NO
-- ADD A COMMA!
,
CASE F234.TECH_TRAN_TYPE
WHEN 'ADJ' THEN 'A' + L1PS.ENTRY_NO
WHEN 'BAL' THEN 'A' + L1PS.ENTRY_NO
WHEN 'CLR' THEN 'A' + L1PS.ENTRY_NO
WHEN 'BAD' THEN 'A' + L1PS.ENTRY_NO
WHEN 'CP' THEN 'P' + L1PS.ENTRY_NO
WHEN 'CRD' THEN 'R' + L1PS.ENTRY_NO
WHEN 'SGB' THEN 'S' + L1PS.ENTRY_NO
ELSE 'E'
END AS 'ENTRY_NO_Prefix'
-- ADD A COMMA!
,
F036.TECH_TYPE_DESC,
F234.ACCTG_EFF_DT
-- ADD A COMMA!
,
CASE WHEN MAX(F036.TECH_TRAN_TYPE) <> 'BKG'
THEN SUM(POST.POST_NETT_AMT)
ELSE 0
END 'Client Nett Amount'
-- ADD A COMMA!
,
-- ENTIRELY INCORRECT
--CASE
--F234.TECH_TRAN_TYPE IN ('CRB','CRD','CPB','CPB')
--WHEN SUM(POST.POST_BASE_AMT) 'Underwriter O/S',
SUM(POST.POST_SETT_AMT) 'Client O/S',
CONVERT(NUMERIC(18, 3), 0.000) 'Commission Amt O/S',
CONVERT(NUMERIC(18, 3), 0) 'Other Amt O/S'
-- ADD A COMMA!
,
F234.TRAN_DESC_1
INTO #Tmp
FROM F108
INNER JOIN F234 ON F108.ACCTG_TRAN_NO = F234.ACCTG_TRAN_NO
INNER JOIN F036 ON F036.TECH_TRAN_TYPE = F234.TECH_TRAN_TYPE
INNER JOIN L1PS ON L1PS.ORIG_TRAN_NO = F234.ACCTG_TRAN_NO
INNER JOIN POST ON POST.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO
AND POST.POSTING_LVL1_KEY = L1PS.POSTING_KEY
WHERE F108.ACCTG_RISK_REF = @RiskRef
-- ADD A CONDITIONAL, EITHER "AND" OR "OR", I'VE ASSUMED "OR"!
OR F108.ACCTG_RISK_REF LIKE 'DV3%'
GROUP BY L1PS.ORIG_TRAN_NO,
L1PS.ASSURED,
F108.ACCTG_RISK_REF,
F108.CURR_ISO_CODE_TR,
F108.TECH_TRAN_NUM,
F108.TECH_TRAN_VERSN,
L1PS.POST_INST_NO,
F234.TECH_TRAN_TYPE,
L1PS.ENTRY_NO,
F036.TECH_TYPE_DESC,
F234.ACCTG_EFF_DT,
F234.TRAN_DESC_1
ORDER BY F108.ACCTG_RISK_REF,
ISNULL(F108.TECH_TRAN_NUM, 999),
ISNULL(F108.TECH_TRAN_VERSN, 999),
L1PS.ENTRY_NO;
-- ADD END!!
END;
August 4, 2015 at 2:40 am
Thanks for doing a proper analysis :blush:
August 4, 2015 at 2:44 am
Thanks buddies..
I will check and let u know.
August 4, 2015 at 3:43 am
Hi Buddy,
I tried your query but i'm getting Syntax error as
Incorrect syntax near the keyword 'IN'.
Please help on this.
August 4, 2015 at 4:08 am
Phil Parkin (8/4/2015)
Thanks for doing a proper analysis :blush:
I just found myself with a spare few minutes whilst trying to reproduce a customer issue in-house 😉
reddychaitanyakrishna (8/4/2015)
Hi Buddy,I tried your query but i'm getting Syntax error as
Incorrect syntax near the keyword 'IN'.
Please help on this.
Well then, you didn't try "my" query then since I commented out the "CASE IN" section with the comment "ENTIRELY INCORRECT".
Let's take a look at it. . .
CASE
F234.TECH_TRAN_TYPE IN ('CRB','CRD','CPB','CPB')
WHEN SUM(POST.POST_BASE_AMT) 'Underwriter O/S',
That makes no syntactical sense.
At a total guess, did you mean something like: -
CASE WHEN F234.TECH_TRAN_TYPE IN ( 'CRB', 'CRD', 'CPB', 'CPB' )
THEN SUM(POST.POST_BASE_AMT)
ELSE 0
END 'Underwriter O/S'
??
August 4, 2015 at 6:39 am
Thanks folks it got executed.....
Sorry for the late reply.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply