May 13, 2011 at 1:08 pm
I am having some trouble with the code below. I have a table variable for invoices, and another for line items. I load @lineitems with the line items from an invoice, and if a line item does not contain an ItemID that is like '9515%', I process those line items. Essentially, I am trying to loop through the ItemID's so that I can concatenate each ItemID, while seperating each ItemID value with the string ' zz '. The problem is that my results end up as itemid1 zz , as opposed to itemid1 zz itemid2 zz itemid3 zz... I have a bit column on both @invoices and @lineitems that I set = 1 after processing, and I believe I am looping through all of the rows of each table variable, but I cannot seem to get the concatenated ItemID value that I want.
WHILE ((SELECT Count(InvcKey) FROM @invoices WHERE Processed IS NULL) > 0)
BEGIN
SELECT @invckey = MIN(InvcKey) FROM @invoices WHERE Processed IS NULL
--begin procedure to process multi-primary item invoice
SELECT @itemid = ItemID FROM @results WHERE InvcKey=@invckey AND CommodityCodeKey=4
IF (RTRIM(@itemid) NOT LIKE '9515%' AND ((SELECT COUNT(ItemID) FROM @results WHERE InvcKey=@invckey) > 1))
BEGIN
--reset @lineitems
DELETE
FROM @lineitems
INSERT INTO @lineitems (ID, InvcKey, ItemID)
SELECT ID, InvcKey, ItemID
FROM @results
WHERE InvcKey = @invckey
DECLARE @tempitemidchar(30),
@idint
--reset
UPDATE @lineitems
SET Processed = NULL
WHILE (SELECT COUNT(ItemID) FROM @lineitems WHERE Processed IS NULL) > 0
BEGIN
SELECT @tempitemid=ItemID, @id=ID
FROM @lineitems
WHERE ID =
(
SELECT MIN(ID)
FROM @lineitems
WHERE Processed IS NULL
)
IF @id = (SELECT MIN(ID) FROM @lineitems)
SELECT @itemid = RTRIM(@tempitemid) + ' zz '
ELSE IF (SELECT COUNT(ItemID) FROM @lineitems WHERE Processed IS NULL) > 1
SELECT @itemid = (@itemid + RTRIM(@tempitemid) + ' zz ')
ELSE
SELECT @itemid = (@itemid + RTRIM(@tempitemid))
UPDATE @lineitems
SET Processed=1
WHERE ID=@id
END
UPDATE @results
SET ItemID = @itemid
WHERE ID =
(
SELECT MIN(ID)
FROM @results
WHERE InvcKey=@invckey AND CommodityCodeKey=4
)
END
END
DECLARE @results TABLE
(
IDint IDENTITY,
CustIDchar(12),
CustNamevarchar(40),
PostDatedatetime,
Quarterint,
ItemIDchar(100),
QtyShippeddecimal(9),
SalesAmtdecimal(9),
ImprintPhrasechar(250),
Coop_Numvarchar(30),
Typevarchar(64),
CustPONochar(15),
AddrNamevarchar(40),
AddrLine1varchar(40),
AddrLine2varchar(40),
AddrLine3varchar(40),
Citychar(20),
StateIDchar(3),
PostalCodechar(9),
InvcKeyint,
LineAmtdecimal(9),
CommodityCodeKeyint
)
DECLARE @invoices TABLE
(
InvcKeyint,
Processedbit
)
DECLARE @lineitemsTABLE
(
IDint,
InvcKeyint,
ItemIDchar(30),
LineAmtdecimal(9),
Processedbit
)
--to load @results
INSERT INTO @results (InvcKey, ItemID)
SELECT '304662', '421' UNION ALL
SELECT '304662', 'VD' UNION ALL
SELECT '304662', '421' UNION ALL
SELECT '304662', 'VD' UNION ALL
SELECT '304662', 'POSTAGE' UNION ALL
SELECT '315453', '9515-TEST' UNION ALL
SELECT '315453', 'IMPM FULL COLOR' UNION ALL
SELECT '315453', 'EXACT QUANTITY' UNION ALL
SELECT '315453', 'RUSH ORDER' UNION ALL
SELECT '316653', '5201' UNION ALL
SELECT '316653', 'IMPV 5201 2 CLR' UNION ALL
SELECT '316653', 'MAILQUAD'
--to load @invoices
INSERT INTO @invoices (InvcKey)
SELECT DISTINCT InvcKey
--to load @lineitems
INSERT INTO @lineitems (ID, InvcKey, ItemID)
SELECT ID, InvcKey, ItemID
FROM @results
WHERE InvcKey = @invckey
FROM @results
My expected ItemID's would then be: '421 zz VD zz 421 zz VD zz POSTAGE', '9515-TEST', 'IMPM FULL COLOR', 'EXACT QUANTITY', 'RUSH ORDER', '5201 zz IMPV 5021 2 CLR zz MAILQUAD'
May 13, 2011 at 1:13 pm
Please provide table def a nd sample data in a ready to use format together with your expected results (see the first link in my signature for an example).
I'm sure there is a set based solution that will not only work correctly but will outperform your current solution significantly.
May 16, 2011 at 10:51 am
My initial thoughts are that @id is not incrementing as I intended, or concatenating @itemid is not working properly. Is the following flawed logic in regards to TSQL:
SELECT @itemid = (@itemid + RTRIM(@tempitemid) + ' zz ')
**edit**
If anyone has any thoughts on how I can use set based code to improve this, let me know. Even a good example of procedural vs. set based would be appreciated given I am not having an easy time relating what I have found using Google searches to the code above.
May 17, 2011 at 9:21 am
Kind of stripped it down but something along this line might work.
DECLARE @results TABLE (
ItemID VARCHAR(100),
InvcKey INT,
Processed BIT DEFAULT 0
)
DECLARE @lineitems TABLE (
InvcKey INT,
ItemID VARCHAR(100)
)
INSERT INTO @results (InvcKey, ItemID)
SELECT '304662', '421' UNION ALL
SELECT '304662', 'VD' UNION ALL
SELECT '304662', '421' UNION ALL
SELECT '304662', 'VD' UNION ALL
SELECT '304662', 'POSTAGE' UNION ALL
SELECT '315453', '9515-TEST' UNION ALL
SELECT '315453', 'IMPM FULL COLOR' UNION ALL
SELECT '315453', 'EXACT QUANTITY' UNION ALL
SELECT '315453', 'RUSH ORDER' UNION ALL
SELECT '316653', '5201' UNION ALL
SELECT '316653', 'IMPV 5201 2 CLR' UNION ALL
SELECT '316653', 'MAILQUAD'
DECLARE @ItemID VARCHAR(100)
, @InvcKey INT
WHILE EXISTS (SELECT 1 FROM @results WHERE Processed = 0)
BEGIN
SELECT TOP 1 @InvcKey = InvcKey FROM @results WHERE Processed = 0
SET @ItemID = ''
SELECT@ItemID = @ItemID + isnull(ItemID,'NULL') + ' zz '
FROM@results
WHEREInvcKey = @InvcKey
ORDER BY InvcKey
SET @ItemID = LEFT(@ItemID, (LEN(@ItemID)-2))
INSERT INTO @lineitems (InvcKey, ItemID)
VALUES (@InvcKey, @ItemID)
UPDATE @results
SETProcessed = 1
WHERE InvcKey = @InvcKey
END
SELECT * FROM @lineitems
_____________________________________________________________________
- Nate
May 18, 2011 at 7:07 pm
To make Nate's solution 1 step shorter:
SET @ItemID = NULL -- needed only if there is a loop for multiple invoices
SELECT @ItemID = ISNULL(@ItemID + ' zz ', '') + isnull(ItemID,'NULL')
FROM @results
WHERE InvcKey = @InvcKey
--ORDER BY InvcKey -- does not make any sense, really
--SET @ItemID = LEFT(@ItemID, (LEN(@ItemID)-2))
🙂
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply