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'.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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:
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply