August 28, 2018 at 4:58 am
Need to modify the Update statement to include combination two new fields instead of one field:
Current table cp_inv structure:
ID, cst1, cst2,cst3,prc1,prc2,prc3, chk_amt1, chk_amt2, chk_amt3
New table cp_chk structure:
ID, cst, prc, chk_amt, seq_num
basically 9 columns replaced by 4 columns. Now they removed 1,2,3 and want to bring two column combination cst and seq_num combination for example
Update ProdStg
set unit_cost=case when ci.totcstperacct<ci.cst1 then ci.chk_amt1
when ci.totcstperacct>=ci.prc2 and ci.totcstperacct<ci.cst2 then ci.chk_amt2
when ci.totcstperacct>ci.prc3 then ci.chk_amt3
from ProdStg ps
inner join cp_inv ci on ps.cus_cd=ci.arid
Something like below: i need to write in better way:
Update ProdStg
set unit_cost=case when ci.totcstperacct<(select ci.cst from cp_chk chk where ch.seqnum=1) then (select ch.chk_amt from cp_chk chk where ch.seqnum=1)
when ci.totcstperacct>=(select ci.prc from cp_chk chk where ch.seqnum=2) and ci.totcstperacct<(select ci.cst from cp_chk chk where ch.seqnum=2) then (select ch.chk_amt from cp_chk chk where ch.seqnum=2)
when ci.totcstperacct>(select ci.prc from cp_chk chk where ch.seqnum=3) then (select ch.chk_amt from cp_chk chk where ch.seqnum=3)
from ProdStg ps
inner join cp_chk ch on ps.cus_cd=ch.arid
above code is working fine. but taking so much time. i want to write in better way. please advise.
August 28, 2018 at 5:12 am
saptek9 - Tuesday, August 28, 2018 4:58 AMNeed to modify the Update statement to include combination two new fields instead of one field:Current table cp_inv structure:
ID, cst1, cst2,cst3,prc1,prc2,prc3, chk_amt1, chk_amt2, chk_amt3New table cp_chk structure:
ID, cst, prc, chk_amt, seq_num
basically 9 columns replaced by 4 columns. Now they removed 1,2,3 and want to bring two column combination cst and seq_num combination for exampleUpdate ProdStg
set unit_cost=case when ci.totcstperacct<ci.cst1 then ci.chk_amt1
when ci.totcstperacct>=ci.prc2 and ci.totcstperacct<ci.cst2 then ci.chk_amt2
when ci.totcstperacct>ci.prc3 then ci.chk_amt3
from ProdStg ps
inner join cp_inv ci on ps.cus_cd=ci.aridSomething like below: i need to write in better way:
Update ProdStg
set unit_cost=case when ci.totcstperacct<(select ci.cst from cp_chk chk where ch.seqnum=1) then (select ch.chk_amt from cp_chk chk where ch.seqnum=1)
when ci.totcstperacct>=(select ci.prc from cp_chk chk where ch.seqnum=2) and ci.totcstperacct<(select ci.cst from cp_chk chk where ch.seqnum=2) then (select ch.chk_amt from cp_chk chk where ch.seqnum=2)
when ci.totcstperacct>(select ci.prc from cp_chk chk where ch.seqnum=3) then (select ch.chk_amt from cp_chk chk where ch.seqnum=3)
from ProdStg ps
inner join cp_chk ch on ps.cus_cd=ch.aridabove code is working fine. but taking so much time. i want to write in better way. please advise.
You are joining to cp_chk ch on ps.cus_cd=ch.arid then you are including a lookup in your update on
SELECT ch.chk_amt FROM cp_chk chk WHERE ch.seqnum = n
But you aren't also joining on arid.
Is that the correct logic because it seems a bit odd?
It would be better if you described what you wanted it to do instead of pasting in some dodgy SQL that people then have to reverse engineer.
What does this do?:select ci.cst
from cp_chk chk
where ch.seqnum=2
You have three different aliases in one lookup!
August 28, 2018 at 6:00 am
Update ProdStg
set unit_cost=case when ch.totcstperacct<(select ch.cst from cp_chk where ch.seqnum=1) then (select ch.chk_amt from cp_chk where ch.seqnum=1)
when ch.totcstperacct>=(select ch.prc from cp_chk where ch.seqnum=2) and ch.totcstperacct<(select ch.cst from cp_chk where ch.seqnum=2) then (select ch.chk_amt from cp_chk chk where ch.seqnum=2)
when ci.totcstperacct>(select ch.prc from cp_chk where ch.seqnum=3) then (select ch.chk_amt from cp_chk where ch.seqnum=3)
from ProdStg ps
inner join cp_chk ch on ps.cus_cd=ch.arid
yes i have to lookup in same table. Please advise.
August 28, 2018 at 6:26 am
saptek9 - Tuesday, August 28, 2018 6:00 AMUpdate ProdStg
set unit_cost=case when ch.totcstperacct<(select ch.cst from cp_chk where ch.seqnum=1) then (select ch.chk_amt from cp_chk where ch.seqnum=1)
when ch.totcstperacct>=(select ch.prc from cp_chk where ch.seqnum=2) and ch.totcstperacct<(select ch.cst from cp_chk where ch.seqnum=2) then (select ch.chk_amt from cp_chk chk where ch.seqnum=2)
when ci.totcstperacct>(select ch.prc from cp_chk where ch.seqnum=3) then (select ch.chk_amt from cp_chk where ch.seqnum=3)
from ProdStg ps
inner join cp_chk ch on ps.cus_cd=ch.aridyes i have to lookup in same table. Please advise.
You have: SELECT ch.chk_amt
FROM cp_chk
WHERE ch.seqnum = 2
So what's the point of table cp_chk when you aren't looking up against it and using values from a different part of the query?
August 28, 2018 at 8:27 am
Please post sample data and expected results as outlined in the first link in my signature.
Also, is your question about how to change the table structure, or how to change the query once you have the new table structure?
BTW, it looks like your new table structure is a multi-column "unpivot".
SELECT ID, cst, prc, chk_amt, seq_num
FROM cp_inv
CROSS APPLY ( VALUES(cst1, prc1, chk_amt1, 1), (cst2, prc2, chk_amt2, 2), (cst3, prc3, chk_amt3, 3) ) cp_chk(cst, prc, chk_amt, seq_num)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 29, 2018 at 6:38 am
Current table cp_inv structure:
finnum, cst1, cst2,cst3,prc1,prc2,prc3, chk_amt1, chk_amt2, chk_amt3
Logic to update:
Update ProdStg
set unit_cost=case when ci.totcstperacct<ci.cst1 then ci.chk_amt1
when ci.totcstperacct>=ci.prc2 and ci.totcstperacct<ci.cst2 then ci.chk_amt2
when ci.totcstperacct>ci.prc3 then ci.chk_amt3
from ProdStg ps
inner join cp_inv ci on ps.cus_cd=ci.arid
new table structure cp_chk
finnum, cst, prc, chk_amt, seqnum, grpnum
i need to do same above logic with seqnum and grpnum
for example cst1= select ch.cst from cp_chk where ch.seqnum=1 group by grpnum
prc1=select ch.prc from cp_chk where ch.seqnum=1 group by grpnum
in previous table structure we were having only 3 seqnum i.e. cst1,cst2,cst3
now in new table data we can have "n" seqnum 1 to 7 or 1 to 12...
now i need to write some dynamic sql to replace above logic. i tried write in following way. but that's completely wrong. Please help me.
Update ProdStg
set unit_cost=case when ch.totcstperacct<(select ch.cst from cp_chk where ch.seqnum=1) then (select ch.chk_amt from cp_chk where ch.seqnum=1)
when ch.totcstperacct>=(select ch.prc from cp_chk where ch.seqnum=2) and ch.totcstperacct<(select ch.cst from cp_chk where ch.seqnum=2) then (select ch.chk_amt from cp_chk chk where ch.seqnum=2)
when ci.totcstperacct>(select ch.prc from cp_chk where ch.seqnum=3) then (select ch.chk_amt from cp_chk where ch.seqnum=3)
from ProdStg ps
inner join cp_chk ch on ps.cus_cd=ch.arid
August 29, 2018 at 7:46 am
saptek9 - Wednesday, August 29, 2018 6:38 AMCurrent table cp_inv structure:
finnum, cst1, cst2,cst3,prc1,prc2,prc3, chk_amt1, chk_amt2, chk_amt3
Logic to update:
Update ProdStg
set unit_cost=case when ci.totcstperacct<ci.cst1 then ci.chk_amt1
when ci.totcstperacct>=ci.prc2 and ci.totcstperacct<ci.cst2 then ci.chk_amt2
when ci.totcstperacct>ci.prc3 then ci.chk_amt3
from ProdStg ps
inner join cp_inv ci on ps.cus_cd=ci.aridnew table structure cp_chk
finnum, cst, prc, chk_amt, seqnum, grpnum
i need to do same above logic with seqnum and grpnumfor example cst1= select ch.cst from cp_chk where ch.seqnum=1 group by grpnum
prc1=select ch.prc from cp_chk where ch.seqnum=1 group by grpnumin previous table structure we were having only 3 seqnum i.e. cst1,cst2,cst3
now in new table data we can have "n" seqnum 1 to 7 or 1 to 12...now i need to write some dynamic sql to replace above logic. i tried write in following way. but that's completely wrong. Please help me.
Update ProdStg
set unit_cost=case when ch.totcstperacct<(select ch.cst from cp_chk where ch.seqnum=1) then (select ch.chk_amt from cp_chk where ch.seqnum=1)
when ch.totcstperacct>=(select ch.prc from cp_chk where ch.seqnum=2) and ch.totcstperacct<(select ch.cst from cp_chk where ch.seqnum=2) then (select ch.chk_amt from cp_chk chk where ch.seqnum=2)
when ci.totcstperacct>(select ch.prc from cp_chk where ch.seqnum=3) then (select ch.chk_amt from cp_chk where ch.seqnum=3)
from ProdStg ps
inner join cp_chk ch on ps.cus_cd=ch.arid
1.) Your provided code is not "consumable" because we don't have your tables or data. It's also not in a SQL Code block, making it much harder to read.
2.) You have only provided the existing working code, and the changes to the columns in the table, and NOT provided any real explanation as to why this change to the table is being made, nor even sample data to be able to demonstrate what a given row would have looked like in the old structure, as well as what it would look like in the new structure, and without any details on why, no one is going to be able to help much.
3.) You have enough posts in this forum to know that we need consumable code to be able to be effective at helping you, so please provide CREATE TABLE statements for all the tables involved, along with sample data as INSERT statements, along with an explanation of what the UPDATE statement is supposed to accomplish, and why, and then how that will change with the new structure. And don't forget to select your block of SQL code and click on the SQL Code region that when you mouse over, shows a black box with white text "SQL Code". It's between IF Code and Add File...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 29, 2018 at 8:33 am
before they were having only three types charges. Now they can be having having multiple number of charges. that's the reason they removed 1,2,3 concept and generalized with seqnum and grpnum
August 29, 2018 at 8:56 am
You still haven't provided sample data and expected results. I'm not planning on spending any more time on this until you do.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 29, 2018 at 9:27 am
First, we are volunteers giving of our own time to help. We can't see what you see nor can we read your mind (even though there are times it seems some of us have that ability).
If you want our help, you have to help us. You have been asked for the DDL (CREATE TABLE statement) for the tables involved, sample data for the tables as INSERT statements, and expected results based on the sample data.
August 30, 2018 at 5:39 am
saptek9 - Wednesday, August 29, 2018 8:33 AMbefore they were having only three types charges. Now they can be having having multiple number of charges. that's the reason they removed 1,2,3 concept and generalized with seqnum and grpnum
Are you planning to provide what was asked for or not? We can't read your mind or your data. We'd love to help, but YOU have to do at least SOME of the work, by at least making it possible for us to understand the problem well enough to KNOW what to do. That task can't happen if you can't explain the problem in more detail, and take the time to give us the benefit of enough knowledge to know what you know about this particular problem. Right now, we simply do NOT have enough information. The ball is in your court, so to speak...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply