Sql Query

  • Krishh

    Ten Centuries

    Points: 1028

    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

  • Phil Parkin

    SSC Guru

    Points: 244733

    Case x when 'y' then 'z' end

    You need the 'then' and you need the 'end'.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Krishh

    Ten Centuries

    Points: 1028

    Hi Phil,

    Thanks for checking my query.

    Can you please let me know where i need to do changes.

    Thank you.

  • Phil Parkin

    SSC Guru

    Points: 244733

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Cadavre

    SSC-Forever

    Points: 41690

    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;


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Phil Parkin

    SSC Guru

    Points: 244733

    Thanks for doing a proper analysis :blush:

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Krishh

    Ten Centuries

    Points: 1028

    Thanks buddies..

    I will check and let u know.

  • Krishh

    Ten Centuries

    Points: 1028

    Hi Buddy,

    I tried your query but i'm getting Syntax error as

    Incorrect syntax near the keyword 'IN'.

    Please help on this.

  • Cadavre

    SSC-Forever

    Points: 41690

    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'

    ??


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Krishh

    Ten Centuries

    Points: 1028

    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