Jim Moss
asked on
In T-SQL cursor convert smallint to varchar
UNABLE TO CONVERT THE DLI_MAIN_CODE AND | CHAR
--SQL===================== ========== =
CREATE TABLE #PROOF
(
dli_barcode numeric NOT NULL,
all_dli_main_code varchar(10) NOT NULL
)
DECLARE cur CURSOR for
SELECT
U.DLI_BARCODE,
U.DLI_MAIN_CODE
FROM OMPROD..PS_DLI_ITEM_UC U, #AD_DAILY_REPORT D
WHERE U.DLI_MAIN_CODE IN (7,8,89)
AND U.DLI_BARCODE = D.CHILD_BARCODE
AND U.SETID = D.SETID
DECLARE @curDLI_BARCODE NUMERIC
DECLARE @lastDLI_BARCODE NUMERIC
DECLARE @curDLI_MAIN_CODE varchar(30)
DECLARE @DLI_MAIN_CODE SMALLINT
OPEN cur
FETCH NEXT FROM cur INTO
@curDLI_BARCODE, @curDLI_MAIN_CODE
SET @lastDLI_BARCODE = @curDLI_BARCODE
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@lastDLI_BARCODE != @curDLI_BARCODE)
BEGIN
INSERT INTO #PROOF(dli_barcode ,all_dli_main_code)
VALUES(@lastDLI_BARCODE, @DLI_MAIN_CODE ) --<<<<+ '| '
SET @lastDLI_BARCODE = @curDLI_BARCODE
SET @DLI_MAIN_CODE = NULL
END
IF (@DLI_MAIN_CODE IS NULL)
SET @DLI_MAIN_CODE = @curDLI_MAIN_CODE
ELSE
SET @DLI_MAIN_CODE = CONVERT(VARCHAR(10), @DLI_MAIN_CODE + N' '+ '| ' + @curDLI_MAIN_CODE) --<<<<20170428 PM
FETCH NEXT FROM cur INTO
@curDLI_BARCODE, @curDLI_MAIN_CODE
END
IF (@DLI_MAIN_CODE IS NOT NULL)
BEGIN
INSERT INTO #PROOF(dli_barcode, all_dli_main_code)
VALUES(@curDLI_BARCODE, CONVERT(VARCHAR(10), @DLI_MAIN_CODE) --<<<< 20170428 PM
END
CLOSE cur
DEALLOCATE cur
Proof.txt
--SQL=====================
CREATE TABLE #PROOF
(
dli_barcode numeric NOT NULL,
all_dli_main_code varchar(10) NOT NULL
)
DECLARE cur CURSOR for
SELECT
U.DLI_BARCODE,
U.DLI_MAIN_CODE
FROM OMPROD..PS_DLI_ITEM_UC U, #AD_DAILY_REPORT D
WHERE U.DLI_MAIN_CODE IN (7,8,89)
AND U.DLI_BARCODE = D.CHILD_BARCODE
AND U.SETID = D.SETID
DECLARE @curDLI_BARCODE NUMERIC
DECLARE @lastDLI_BARCODE NUMERIC
DECLARE @curDLI_MAIN_CODE varchar(30)
DECLARE @DLI_MAIN_CODE SMALLINT
OPEN cur
FETCH NEXT FROM cur INTO
@curDLI_BARCODE, @curDLI_MAIN_CODE
SET @lastDLI_BARCODE = @curDLI_BARCODE
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@lastDLI_BARCODE != @curDLI_BARCODE)
BEGIN
INSERT INTO #PROOF(dli_barcode ,all_dli_main_code)
VALUES(@lastDLI_BARCODE, @DLI_MAIN_CODE ) --<<<<+ '| '
SET @lastDLI_BARCODE = @curDLI_BARCODE
SET @DLI_MAIN_CODE = NULL
END
IF (@DLI_MAIN_CODE IS NULL)
SET @DLI_MAIN_CODE = @curDLI_MAIN_CODE
ELSE
SET @DLI_MAIN_CODE = CONVERT(VARCHAR(10), @DLI_MAIN_CODE + N' '+ '| ' + @curDLI_MAIN_CODE) --<<<<20170428 PM
FETCH NEXT FROM cur INTO
@curDLI_BARCODE, @curDLI_MAIN_CODE
END
IF (@DLI_MAIN_CODE IS NOT NULL)
BEGIN
INSERT INTO #PROOF(dli_barcode, all_dli_main_code)
VALUES(@curDLI_BARCODE, CONVERT(VARCHAR(10), @DLI_MAIN_CODE) --<<<< 20170428 PM
END
CLOSE cur
DEALLOCATE cur
Proof.txt
ASKER
I also would consider a set based option, thanks
can you show what you need exactly...
I dont get what you need here:
I dont get what you need here:
what I want is a table of barcodes with delimited dli_main_code column main codes are numeric seperated by pipe delimiter
ASKER
What I want is to take the data from the attached text file and concatenate all MAIN_CD each BARCODE separated by a | character, or a set based solution would be an option.
Output should look like
BARCODE MAIN_CD
1608163335001 7|8
Output should look like
BARCODE MAIN_CD
1608163335001 7|8
ASKER
that value is in attached Proof.txt
Thanks
Thanks
ASKER
This is a Sybase db
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How do I declare the temp table #Proof? the script bombs ERROR: Incorrect syntax near the keyword 'with'.
ASKER
ERROR: Incorrect syntax near 'PATH'.
sorry that one is not for sybase :) it is for MS SQL
ASKER
Thsnks for your help this code selects and I need something that concatenates multiple values for each barcode so we only have one row per barcode
Does you variant of Sybase support the LIST() aggregate function?
e.g.
SELECT LIST(cast(DLI_MAIN_CODE as varchar), '|')
FROM yourtable
GROUP BY DLI_BARCODE
;
e.g.
SELECT LIST(cast(DLI_MAIN_CODE as varchar), '|')
FROM yourtable
GROUP BY DLI_BARCODE
;
ASKER
I will check in the morning, thanks!
ASKER
The FUNCTION LIST() is not found. Thanks
ASKER
There was no "best solution", or even any solution, but Huseyin KAHRAMAN expended more effort. Thanks
ASKER