August 25, 2018 at 5:41 pm
i need to write stored proc following logic please help me the code.
i need to get distinct prodid from prod table and compare each prodid whether exists in prodstg table if exists do insert few columns from Prodstg table into NewProduct table. If not exists in prodstg table need to look in prodhistory table and insert records from prodhistory into NewProduct table. Please advise the code.
August 25, 2018 at 6:36 pm
saptek9 - Saturday, August 25, 2018 5:41 PMi need to write stored proc following logic please help me the code.
i need to get distinct prodid from prod table and compare each prodid whether exists in prodstg table if exists do insert few columns from Prodstg table into NewProduct table. If not exists in prodstg table need to look in prodhistory table and insert records from prodhistory into NewProduct table. Please advise the code.
IF OBJECT_ID('dbo.InsertNewProducts','P') IS NULL BEGIN
EXEC('CREATE PROCEDURE dbo.InsertNewProducts AS')
END
GO
ALTER PROCEDURE dbo.InsertNewProducts AS
BEGIN
;WITH CTE AS
(
SELECT ProdId,
Col1,
Col2
FROM ProdStg ps
WHERE EXISTS(SELECT *
FROM Prod p
WHERE p.ProdId = ps.ProdId)
UNION ALL
SELECT ProdId
Col1,
Col2
FROM dbo.ProdHistory ph
WHERE EXISTS(SELECT *
FROM Prod p
WHERE p.ProdId = ps.ProdId)
AND NOT EXISTS(SELECT *
FROM ProdStg ps
WHERE ps.ProdId = ph.ProdId)
)
INSERT INTO dbo.NewProduct
(
ProdId,
Col1,
Col2
)
SELECT ProdId,
Col1,
Col2
FROM CTE
WHERE NOT EXISTS(SELECT *
FROM dbo.NewProduct np
WHERE np.ProdId = CTE.ProdId
AND np.Col1 = CTE.Col1
AND np.Col2 = CTE.Col2)
END
GO
August 26, 2018 at 4:10 am
Thank you so much Jonathan!!!
August 26, 2018 at 6:39 am
I need another help.
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
August 26, 2018 at 9:39 am
saptek9 - Saturday, August 25, 2018 5:41 PM
>> I need to write stored proc following logic please help me the code. <<
Why did you fail to help us with the DDL, if you want us to do your work for you for free? I guess it's time that we start making guesses. And, unlike you, we will follow ISO 11179 naming rules, normalization and other simple practices that are not in your narrative.
>> I need to get distinct product_id from prod table and compare each product_id whether exists in Staged_Products table if exists do insert few columns from Staged_Products table into NewProduct table. <<
Just reading your narrative, this is how someone might try to implement a schema. Too bad it's wrong.
CREATE TABLE Products
(prod_id CHAR(15) NOT NULL PRIMARY KEY,
..);
CREATE TABLE Products(product_id)
(prod_id CHAR(15) NOT NULL PRIMARY KEY
REFERENCES Products(product_id),
..);
CREATE TABLE New_Products
(prod_id CHAR(15) NOT NULL PRIMARY KEY
REFERENCES Products(product_id),
..);
What you said by putting these as separate tables is they are is totally different as a squid and an automobile. But they're not! Why is a new product totally different from a mere product? Being staged is a status (a state of being of an entity), not a completely different creature.
What you are doing is mimicking in tables. What would have been done with paper files, back in the 1960s.
CREATE TABLE Products
(prod_id CHAR(15) NOT NULL PRIMARY KEY,
product_status CHAR(10) NOT NULL
CHECK (product_status IN ('staged', 'new', 'history',, ..)),
..);
>> If not exists in Staged_Products table need to look in Product_History table and insert records [sic] from Products_History into New_Product table. <<
There is no need to do all of this procedural logic, if you will simply get a good declarative schema design. I wish people would actually read the SQL standards or a good book on RDBMS and learn the difference between rows and records. When they do, they'll stop mimicking those paper files. Oh, where did this history table come from and what does it look like?
We don't even have sample data from which we can make guesses. Would you like to try again and give us something we can actually work with? Or do you just want to get a kludge.
[/code]What you said by putting these as separate tables is they are is totally different as a squid and an automobile. But they're not! Why is a new product totally different from a mere product? Being staged is a status (a state of being of an entity), not a completely different creature.What you are doing is mimicking in tables. What would have been done with paper files. Back in the 1960s.CREATE TABLE Products(prod_id CHAR(15) NOT NULL PRIMARY KEY, product_status CHAR() NOT NULL CHECK (product_status IN ('staged', 'new', 'history',, ..)), ..);>> If not exists in Staged_Products table need to look in Product_History table and insert records [sic] from Products_History into New_Product table. <<I wish people would actually read the SQL standards or a good book on RDBMS and learn the difference between rows and records. When they do, they'll stop mimicking those paper files. Oh, where did this history table come from and what does it look like?We don't even have sample data from which we can make guesses. Would you like to try again and give us something we can actually work with? Or do you just want to get a kludge?
Please post DDL and follow ANSI/ISO standards when asking for help.
August 26, 2018 at 9:42 am
>> I need another help. Need to modify the Update statement to include combination two new fields [sic] instead of one field [sic] <<
When you have a second question, then make a second posting. This is been considered polite netiquette for over 30 years in SQL forums. But looking at your attempt, I think you need to look up the problems with using the old, non-ANSI/ISO update statement syntax. It is literally dependent on the ordering of the data in physical storage, so not only is it not standard, it doesn't work.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply