April 17, 2012 at 2:14 am
Hi,
I meant Cursor Query and not Case in the Topic Header!
I need to identify a list of items from a database based unique set of other fields. We would then like to create a new item in the system for each of these unique sets with a new item code and then have reference the old item codes to the new one in a new field.
Example
Itemcode Field 1 Field 2 Field 3 Field 4
A100 A B C D
B100 B C D E
C100 A B C D
The unique items are thus A100 & C100. We now would like to create item AC100 and reference it accordingly:
ItemCode Field 1 Field 2 Field 3 Field 4 Field 5
AC100 A B C D NULL
A100 A B C D AC100
C100 A B C D AC100
The below query (#1) produces the list of all the item Codes with their unique Fields 1 - 4:
SELECT DISTINCT ItemCode,
U_PLU, U_Brand, U_Crt_Pallet, U_LabelType, U_PalleBase, U_InvCode, U_Pack, U_Cultivar
FROM OITM AS T0
where TreeType <> 'N'
The below query (#2) produces the unique distinct list without the itemcodes as I do not how to produce this.
SELECT DISTINCT U_PLU, U_Brand, U_Crt_Pallet, U_LabelType, U_PalleBase, U_InvCode, U_Pack, U_Cultivar
FROM OITM AS T0
where TreeType <> 'N'
My thoughts are to select the TOP 1 itemcode with a where clause using Query (#2) somewhere and then update this with the new itemcode and then do a second query using the same logic but adding a NULL for Field5 to get the second and third itemd etc.
I am not sure if this will work or not. The New Item (AC100) will only be created in the database after all the referencing have been finalised.
Thanks
Jacques
April 17, 2012 at 3:25 am
"Example
Itemcode Field 1 Field 2 Field 3 Field 4
A100 A B C D
B100 B C D E
C100 A B C D
The unique items are thus A100 & C100. We now would like to create item AC100."
On what basis are you deciding that A100 and C100 are unique??...All three seem unique to me. Or do you mean that A100 and C100 are SAME(not unique) because they have the same field values???
April 17, 2012 at 3:32 am
Hello and welcome to SSC!
There's no need to use a cursor for this, which is great because a cursor solution would be slow! 🙂
I'd like to be able to help you, but it seems you've forgot to post readily consumable sample data and ddl scripts.
If you could read this article[/url] about the best way to post DDL and sample data then edit your post to include it, it would be extremely useful for the unpaid volunteers of this site.
Thanks!
April 17, 2012 at 3:33 am
Hi,
Yes, I mean are unique in the sense of Field Values excluding Item Code. Thus for field 1 - 4.
Regards,
Jacques
April 17, 2012 at 3:51 am
If you mean that A100 and C100 are same on the basis of field values, then instead of using cursor then can be selected in a SET based manner as follows:
;With CTE
As
(Select *, Count(*) Over (Partition By Field1, Field2, Field3, Field4)
As Count From Ex)
Select * From CTE Where Count > 1
"We now would like to create item AC100 and reference it accordingly:
ItemCode Field 1 Field 2 Field 3 Field 4 Field 5
AC100 A B C D NULL
A100 A B C D AC100
C100 A B C D AC100"
Is the above table you mentioned a new table or the same table with B100 removed and an added field(Field5)??
Will be needing a little elaboration about the problem so that I can move forward. Check my post and answer the questions I asked.
Till then maybe the above mentioned query might be a head-start for you.
April 17, 2012 at 3:53 am
Thanks for the information and will do accordingly.
Regards,
Jacques
April 17, 2012 at 4:38 am
From whatever I could understand I came up with this script. Try this and tell me if it meets your requirement.
--Create Table
Create Table Ex
(Itemcode varchar(10),
Field1 varchar(10),
Field2 varchar(10),
Field3 varchar(10),
Field4 varchar(10) )
--Insert Values into table
Insert Into Ex
Select 'A100', 'A', 'B', 'C', 'D'
Union ALL
Select 'B100', 'B', 'C', 'D', 'E'
Union ALL
Select 'C100', 'A', 'B', 'C', 'D'
--Create required table
Create Table Ex1
(Itemcode varchar(10),
Field1 varchar(10),
Field2 varchar(10),
Field3 varchar(10),
Field4 varchar(10),
Field5 varchar(10) )
--Insert required values from old table into new table
;With CTE
As
(Select *, Count(*) Over (Partition By Field1, Field2, Field3, Field4)
As Count From Ex)
Insert Into Ex1(Itemcode, Field1, Field2, Field3, Field4)
Select Itemcode, Field1, Field2, Field3, Field4 From CTE Where Count > 1
--Update Field5
Update Ex1 Set Field5 = 'AC100'
Where Itemcode = 'A100' OR Itemcode = 'C100'
--Inserting new Item in table
Declare @Itemcode varchar(10) = 'AC100'
Declare @Field1 varchar(10)
Declare @Field2 varchar(10)
Declare @Field3 varchar(10)
Declare @Field4 varchar(10)
Select @Field1 = Field1 From Ex1
Select @Field2 = Field2 From Ex1
Select @Field3 = Field3 From Ex1
Select @Field4 = Field4 From Ex1
Insert Into Ex1(Itemcode, Field1, Field2, Field3, Field4)
Values(@Itemcode, @Field1, @Field2, @Field3, @Field4)
--Viewing Table
Select * From Ex1
I hope this helps you. This may be a little complex(raw), you can refine it.
April 17, 2012 at 8:07 pm
First of all Jacques, welcome to the SSC forum!
I'm not 100% sure exactly which rows you want to return so the query below returns 3 sets (separated by UNION ALL):
1. The non-duplicates
2. The duplicates with the new combined code in the NewCode column
3. The new records with NULL in the NewCode column
DECLARE @t TABLE
(Itemcode varchar(10), F1 varchar(10), F2 varchar(10), F3 varchar(10), F4 varchar(10))
Insert Into @t
Select 'A100', 'A', 'B', 'C', 'D'
Union ALL Select 'B100', 'B', 'C', 'D', 'E'
Union ALL Select 'C100', 'A', 'B', 'C', 'D'
UNION ALL Select 'D100', 'A', 'F', 'C', 'D'
Union ALL Select 'E100', 'B', 'C', 'F', 'E'
Union ALL Select 'F100', 'A', 'F', 'C', 'D'
;WITH NonDups AS (
SELECT F1, F2, F3, F4
FROM @t
GROUP BY F1, F2, F3, F4
HAVING COUNT(*) = 1)
,Dups AS (
SELECT F1, F2, F3, F4
FROM @t
GROUP BY F1, F2, F3, F4
HAVING COUNT(*) > 1)
,Dups2 AS (
SELECT ItemCode, t.F1, t.F2, t.F3, t.F4
FROM @t t INNER JOIN Dups n ON n.F1 = t.F1 and n.F2 = t.F2 and n.F3 = t.F3 and n.F4 = t.F4
)
,Dups3 AS (
SELECT ItemCode, F1, F2, F3, F4
,REPLACE((SELECT ','+SUBSTRING(ItemCode, 1, 1)
FROM Dups2 d
WHERE n.F1 = d.F1 and n.F2 = d.F2 and n.F3 = d.F3 and n.F4 = d.F4
FOR XML PATH('')), ',', '')+SUBSTRING(ItemCode, 2, LEN(ItemCode)) As NewCode
FROM Dups2 n
)
SELECT ItemCode, t.F1, t.F2, t.F3, t.F4, NULL AS NewCode
FROM @t t INNER JOIN NonDups n ON n.F1 = t.F1 and n.F2 = t.F2 and n.F3 = t.F3 and n.F4 = t.F4
UNION ALL
SELECT ItemCode, F1, F2, F3, F4, NewCode FROM Dups3
UNION ALL
SELECT DISTINCT NewCode AS ItemCode, F1, F2, F3, F4, NULL As ItemCode
FROM Dups3
The results returned by this are:
ItemCode F1F2F3F4NewCode
B100 BCDENULL
E100 BCFENULL
A100 ABCDAC100
C100 ABCDAC100
D100 AFCDDF100
F100 AFCDDF100
AC100 ABCDNULL
DF100 AFCDNULL
You can remove the ones you don't want. I also expanded your record set a little to show that it works for more than one combination.
If you want to (for example) insert only the dups (sets 2 and 3) into a new table:
1) Remove the NonDups CTE
2) Replace the SELECT/FROM/UNION ALL lines (first set) with an INSERT to your new table
Edit: One other note. I am not sure that your representation of how you want to construct the new item code is 100% accurate or just an example. In my SQL, it turned out the ordering of the records resulted in exactly the new item codes you wanted, however since that is not guaranteed, you can also include the following just prior to FOR XML ROOT('') in the subquery:
ORDER BY SUBSTRING(ItemCode, 1, 1)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 17, 2012 at 11:48 pm
Thank you for your replies. I will review the answer over the coming weekend.
Regards,
Jacques
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply