Link to home
Start Free TrialLog in
Avatar of Jim Moss
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
Avatar of Jim Moss
Jim Moss

ASKER

what I want is a table of barcodes with delimited dli_main_code column main codes are numeric seperated by pipe delimiter
I also would consider a set based option, thanks
can you show what you need exactly...

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
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
that value is in attached Proof.txt

Thanks
This is a Sybase db
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How do I declare the temp table #Proof?  the script bombs ERROR: Incorrect syntax near the keyword 'with'.
ERROR: Incorrect syntax near 'PATH'.
sorry that one is not for sybase :) it is for MS SQL
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
Avatar of PortletPaul
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
;
I will check in the morning, thanks!
The FUNCTION LIST() is not found. Thanks
There was no "best solution", or even any solution, but Huseyin KAHRAMAN expended more effort. Thanks