x

SQL Cursor to tag column product id

link textlink textHi all,

Just need you help on this. I have a table that i need to populate then put another column (desired output) if what is the parent product_id based on invoice_link_id_link.

Thank you in advance.

alt text

cursor.jpg (24.4 kB)
sampledata.sql (193.3 kB)
more ▼

asked Jan 11 at 05:46 AM in Default

avatar image

genuiForex
61 6

How do you get the last line to be 384 rather than 284?

Jan 12 at 03:42 PM ThomasRushton ♦♦

Sorry, that should be 284 as the invoice_line_id_link is 0. Thank you ThomasRushton.

Jan 15 at 12:39 AM genuiForex
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Here's one method, but it uses a correlated subquery, so performance might be...suboptimal.

 declare @t table
 (id int, invoice_id int, product_id int, invoice_line_id_link int);
 
 insert into @t values
 (53,10,383,0),
 (54,10,344,53),
 (55,10,920,53),
 (57,10,384,0),
 (58,10,359,57),
 (59,10,242,57),
 (60,10,284,0);
 
 
 select t1.id, t1.invoice_id, t1.product_id, t1.invoice_line_id_link, 
 case when invoice_line_id_link = 0 then product_id ELSE (select t2.product_id FROM @t t2 WHERE t2.id = t1.invoice_line_id_link) end AS desired_output
 from @t t1
more ▼

answered Jan 12 at 04:07 PM

avatar image

ThomasRushton ♦♦
42.1k 20 57 53

Tried your solution and works. Though, can we achieve this through cursor? Or any method that is sql server optimized? Thanks again.

Jan 15 at 12:41 AM genuiForex

@thomasrushton, tried this in may actual table seems there duplicated invoice_line_id and invoice_line_id_link as there are two or more than terminal using the same number. I have attached the insert table for sample data. Thank you.

5 days ago genuiForex

@genuiForex You have posted the sample data but have you verified it by running it yourself? If you need help, then help us first by posting the "valid" sample data and desired output. Moreover, please also mention the SQL Server version, so you could get the answers accordingly.

4 days ago Usman Butt

@genuiForex There are 2 problems with sample data. First, it looks like Excel was used to generate the insert statements using the formula and it grabbed the headers row as well so first insert is trying to insert column headers as values. Second problem is that the table is defined with storeid as primary key but all inserts (except the first one) feature the same value of 1 for storeid. Please fix the problems with sample data and reattach. Thank you.

4 days ago Oleg

@Oleg and @Usman Butt, my bad and apology on that. I have attached the correct sampledata with select statement after insert. Version used, SQL Server 2014 Dev Version. Thanks for the help.

4 days ago genuiForex
(comments are locked)
10|1200 characters needed characters left

You can take the query from @ThomasRushton ♦♦ answer and just slightly restate it so that the "Subquery returned more than 1 value" error is not triggered. The reason that the error triggered is due to the fact that there are multiple records for the same invoice, invoice line and invoice line id link combination. Worse still, there seems to be impossible situation when there are multiple invoice_line_id for the same store, site, terminal, status where invoice_line_id_link is equal to zero. For example, rows 9 through 32 in the sample data are all for invoice_id = 2. All these rows should point to row 9 for their respective parent item, so the rows with invoice line id link 3 through 8 feature item code BC-105801 as their parent item. This makes perfect sense but then come in the rows # 33 through 36 with the same invoice_id = 2 but their invoice line id link is zero again even though it is the same invoice (?!?). This means that rows 33 - 36 have a different parent (BC-101222) and the rows 37 - 44 all have the same parent which is different from the rows 9 - 32 for the same invoice.

While it is not possible to understand the reason for this strange behaviour without knowing the details about the underlying system, it is possible to restate the query from @ThomasRushton ♦♦ answer while preserving its logic . Here is the script which will produce desired result (please have a look at the values in the rightmost column named ParentItemCode):

 select
     a.*, isnull(b.ParentItemCode, a.itemcode) ParentItemCode
     from dbo.sampletable a cross apply (
         select
             min(itemcode)
             from dbo.sampletable
             where invoice_id = a.invoice_id and invoice_line_id = a.invoice_line_id_link
     ) b(ParentItemCode)
     order by invoice_id, invoice_line_id;

Here are the abridged results from this query based on the sample data in question:

 invoice_id  invoice_line_id invoice_line_id_link itemcode   ParentItemCode
 ----------- --------------- -------------------- ---------- --------------
 2           3               0                    BC-105801  BC-105801
 2           3               0                    BC-105801  BC-105801
 2           3               0                    BC-105801  BC-105801
 2           3               0                    BC-105801  BC-105801
 2           4               3                    BC-103990  BC-105801
 2           4               3                    BC-103990  BC-105801
 2           4               3                    BC-103990  BC-105801
 2           4               3                    BC-103990  BC-105801
 --          --              --                   ---------  ---------
 2           8               3                    BC-100971  BC-105801
 2           8               3                    BC-100971  BC-105801
 2           8               3                    BC-100971  BC-105801
 2           8               3                    BC-100971  BC-105801
 2           9               0                    BC-101222  BC-101222
 2           9               0                    BC-101222  BC-101222
 2           9               0                    BC-101222  BC-101222
 2           9               0                    BC-101222  BC-101222
 2           10              9                    BC-102093  BC-101222
 2           10              9                    BC-102093  BC-101222
 2           10              9                    BC-102093  BC-101222
 2           10              9                    BC-102093  BC-101222
 2           11              9                    BC-101159  BC-101222
 2           11              9                    BC-101159  BC-101222
 2           11              9                    BC-101159  BC-101222
 2           11              9                    BC-101159  BC-101222

By the way, there is no reason whatsoever to use a cursor here. I am also puzzled about why there is a question about possibility of using a cursor when the quest is to find a "SQL Server optimized" solution. Cursors have their place in T-SQL, otherwise, they would not exist, but they should be used only for some procedural tasks when the set based solutions are not suitable. When it comes to the data selection tasks, the set based solutions are the way to go.

Hope this helps.

Oleg

more ▼

answered Jan 18 at 03:44 PM

avatar image

Oleg
19.5k 3 7 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1170
x443
x70

asked: Jan 11 at 05:46 AM

Seen: 63 times

Last Updated: 3 days ago

Copyright 2018 Redgate Software. Privacy Policy