Sql Query

Question

Post reply

Sql Query

Krishh

Ten Centuries

Points: 1088
More actions
August 4, 2015 at 7:24 am

#302974

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 ?

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

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

pietlinden SSC Guru Points: 63506 More actions · Answer 1

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 Ten Centuries Points: 1088 More actions · Answer 2

Hi Dude,

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

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: 41690 More actions · Answer 4

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 Ten Centuries Points: 1088 More actions · Answer 5

Krishh

Ten Centuries

Points: 1088

August 4, 2015 at 8:30 am

#1817133

Thanks Cadavre 🙂

Its working .

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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: 1004392 More actions · Answer 7

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.

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: 281334 More actions · Answer 8

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: 41690 More actions · Answer 9

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: 281334 More actions · Answer 10

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