Sql Query

  • Krishh

    SSC Eights!

    Points: 929

    Hi Folks,

    Need to put this into single SQL statement without update command.

    UPDATE T

    --SET T.[Client Nett Amount]= POST_BASE_AMT_DEL

    SET T.[Client Nett Amount] = POST_NETT_AMT_DEL --> Need to verify

    FROM #tmp T

    INNER JOIN L1PS ON T.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO

    INNER JOIN F142 ON L1PS.SYS_ACC_KEY = F142.SYS_ACC_KEY AND F142.CURRENT_ACC_REC='Y'

    INNER JOIN F193 ON F142.ACC_SET_NO = F193.ACC_SET_NO AND ACC_TYPE_CODE = '0001'

    WHERE POST_BASE_AMT_DEL<> 0

    UPDATE T

    SET T.[Commission Amt O/S]= POST_BASE_AMT_NONDEL

    FROM #tmp T

    INNER JOIN L1PS ON T.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO

    INNER JOIN F142 ON L1PS.SYS_ACC_KEY = F142.SYS_ACC_KEY AND F142.CURRENT_ACC_REC='Y'

    INNER JOIN F193 ON F142.ACC_SET_NO = F193.ACC_SET_NO AND ACC_TYPE_CODE = '0004'

    where POST_BASE_AMT_NONDEL<>0

    UPDATE T

    SET T.[Other Amt O/S]= (L1PS.POST_NETT_AMT_NONDEL)

    FROM #tmp T

    INNER JOIN L1PS ON T.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO

    INNER JOIN F142 ON L1PS.ACCOUNT_REF = F142.ACCOUNT_REF AND F142.CURRENT_ACC_REC='Y'

    INNER JOIN F193 ON F142.ACC_SET_NO = F193.ACC_SET_NO AND ACC_TYPE_CODE IN ( '0006')

    WHERE POST_NETT_AMT_NONDEL<>0

    Please help on this ?

  • pietlinden

    SSC Guru

    Points: 62628

    Can't help without understanding the question. What does this mean?

    "Need to put this into single SQL statement without update command."

    What is the query supposed to do? Is it supposed to be a SELECT statement?

  • Krishh

    SSC Eights!

    Points: 929

    Hi Dude,

    I need to make this in to single SQL statement without updation.

  • SQLRNNR

    SSC Guru

    Points: 281233

    reddychaitanyakrishna (8/4/2015)


    Hi Folks,

    Need to put this into single SQL statement without update command.

    UPDATE T

    --SET T.[Client Nett Amount]= POST_BASE_AMT_DEL

    SET T.[Client Nett Amount] = POST_NETT_AMT_DEL --> Need to verify

    FROM #tmp T

    INNER JOIN L1PS ON T.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO

    INNER JOIN F142 ON L1PS.SYS_ACC_KEY = F142.SYS_ACC_KEY AND F142.CURRENT_ACC_REC='Y'

    INNER JOIN F193 ON F142.ACC_SET_NO = F193.ACC_SET_NO AND ACC_TYPE_CODE = '0001'

    WHERE POST_BASE_AMT_DEL<> 0

    UPDATE T

    SET T.[Commission Amt O/S]= POST_BASE_AMT_NONDEL

    FROM #tmp T

    INNER JOIN L1PS ON T.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO

    INNER JOIN F142 ON L1PS.SYS_ACC_KEY = F142.SYS_ACC_KEY AND F142.CURRENT_ACC_REC='Y'

    INNER JOIN F193 ON F142.ACC_SET_NO = F193.ACC_SET_NO AND ACC_TYPE_CODE = '0004'

    where POST_BASE_AMT_NONDEL<>0

    UPDATE T

    SET T.[Other Amt O/S]= (L1PS.POST_NETT_AMT_NONDEL)

    FROM #tmp T

    INNER JOIN L1PS ON T.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO

    INNER JOIN F142 ON L1PS.ACCOUNT_REF = F142.ACCOUNT_REF AND F142.CURRENT_ACC_REC='Y'

    INNER JOIN F193 ON F142.ACC_SET_NO = F193.ACC_SET_NO AND ACC_TYPE_CODE IN ( '0006')

    WHERE POST_NETT_AMT_NONDEL<>0

    Please help on this ?

    SELECT ACC_TYPE_CODE

    , L1PS.POST_NETT_AMT_NONDEL

    , POST_BASE_AMT_NONDEL

    , POST_NETT_AMT_DEL

    FROM #tmp T

    INNER JOIN L1PS

    ON T.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO

    INNER JOIN F142

    ON L1PS.ACCOUNT_REF = F142.ACCOUNT_REF

    AND F142.CURRENT_ACC_REC = 'Y'

    INNER JOIN F193

    ON F142.ACC_SET_NO = F193.ACC_SET_NO

    WHERE ACC_TYPE_CODE IN ( '0006', '0004', '0001' )

    AND (POST_NETT_AMT_NONDEL <> 0

    OR POST_BASE_AMT_NONDEL<>0

    OR POST_BASE_AMT_DEL<> 0);

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Cadavre

    SSC-Forever

    Points: 41582

    Again, not very clear what you want. . . maybe something like: -

    SELECT T.*,

    CASE WHEN ACC_TYPE_CODE = '0001' THEN POST_NETT_AMT_DEL

    ELSE [Client Nett Amount]

    END AS [Client Nett Amount],

    CASE WHEN ACC_TYPE_CODE = '0004' THEN POST_BASE_AMT_NONDEL

    ELSE [Commission Amt O/S]

    END AS [Commission Amt O/S],

    CASE WHEN ACC_TYPE_CODE = '0006' THEN L1PS.POST_NETT_AMT_NONDEL

    ELSE [Other Amt O/S]

    END AS [Commission Amt O/S]

    FROM #tmp T

    INNER JOIN L1PS ON T.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO

    INNER JOIN F142 ON L1PS.SYS_ACC_KEY = F142.SYS_ACC_KEY

    AND F142.CURRENT_ACC_REC = 'Y'

    INNER JOIN F193 ON F142.ACC_SET_NO = F193.ACC_SET_NO

    AND ACC_TYPE_CODE IN ( '0001', '0004', '0006' )

    WHERE POST_NETT_AMT_NONDEL <> 0

    OR POST_BASE_AMT_NONDEL <> 0

    OR POST_BASE_AMT_DEL <> 0;


    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

    SSC Eights!

    Points: 929

    Thanks Cadavre 🙂

    Its working .

  • SQLRNNR

    SSC Guru

    Points: 281233

    Cadavre (8/4/2015)


    Again, not very clear what you want. . . maybe something like: -

    Look at that, getting lucky. 😀

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Jeff Moden

    SSC Guru

    Points: 995494

    Might have to add the 3 different WHERE clauses to that... they are all different.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • SQLRNNR

    SSC Guru

    Points: 281233

    Jeff Moden (8/4/2015)


    Might have to add the 3 different WHERE clauses to that... they are all different.

    Minor things. Who needs a correct filter (as I went back and fixed mine :hehe: )?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Cadavre

    SSC-Forever

    Points: 41582

    SQLRNNR (8/4/2015)


    Cadavre (8/4/2015)


    Again, not very clear what you want. . . maybe something like: -

    Look at that, getting lucky. 😀

    "Why hasn't anyone killed him yet?"

    "Dumb luck," Wit said. "In that I'm lucky you're all so dumb."

    Jeff Moden (8/4/2015)


    Might have to add the 3 different WHERE clauses to that... they are all different.

    Whoops, didn't see that.

    SQLRNNR (8/4/2015)


    Jeff Moden (8/4/2015)


    Might have to add the 3 different WHERE clauses to that... they are all different.

    Minor things. Who needs a correct filter (as I went back and fixed mine :hehe: )?

    Cheat! 😛

    Edited: Today @ 3:54:14 PM by Cadavre - :Whistling:


    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/

  • SQLRNNR

    SSC Guru

    Points: 281233

    Cadavre (8/4/2015)


    Cheat! 😛

    Who me?:crazy:

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply