Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2012
    Posts
    24

    Unanswered: How to segregate CSV values into individual row values

    I have one requirment like below.

    slno batchno
    1 111,222
    2 145,235,335
    3 777
    4 888,999


    My desired output would be like this.

    Slno batchno
    1 111
    1 222
    2 145
    2 235
    2 335
    3 777
    4 888
    4 999

    I am using DB2 for Z/OS V10.

    Can anyone please help me to find the desired output?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have a look at
    Unpivot Query
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2012
    Posts
    3

    Smile Question

    For my personnal interest, can you tell me how your data is organised? What I understand, is that you have a table with to fields: slno and batchno. One is an integer or whatever, and the second field is a string contaning "111,222". Or is it simply a CSV file structured like:"1", "111,222".

    Regards,

    Louis

  4. #4
    Join Date
    Aug 2012
    Posts
    24
    Hi Louis,

    These are the dummy value what i kept for example but my requirement is same.

    But my output would be same as below needed.

    Slno batchno
    1 111
    1 222
    2 145
    2 235
    2 335
    3 777
    4 888
    4 999

    I need the Query in DB@ 10 Z/OS to segregate the CSV values into individual row values.

  5. #5
    Join Date
    Sep 2012
    Posts
    3
    Hi rkp,

    First I've created a simple table:

    CREATE TABLE XDATA (
    SLNO CHAR(5),
    BATCH1 CHAR(5),
    BATCH2 CHAR(5),
    BATCH3 CHAR(5),
    BATCH4 CHAR(5))

    I've imported your values from a CSV file formatted like : 1, "111", "222" using IMPORT utility function. As you notice, the table can receive 4 separate batchno. To be adjusted regarding your needs...

    And then, I've created an unpivot query as stated above.

    SELECT S.SLNO, Q.BatchPos, Q.Batchno
    FROM XDATA AS S ,
    TABLE (VALUES(1, S.BATCH1),
    (2, S.BATCH2),
    (3, S.BATCH3),
    (4, S.BATCH4))
    AS Q(BatchPos, Batchno)
    WHERE Q.Batchno IS NOT NULL

    This solution is offering the desired output.

    Louis

    NB What I don't understand is that you've already have the desired output as mentionned by you in one of your previous post.
    Last edited by LouFeq; 12-25-12 at 17:42.

  6. #6
    Join Date
    Aug 2012
    Posts
    24
    Hi Louis,

    Thanks for the query but one constraint is that i do not have the priviledge to create new physical tables in DB.

    So i want a query which will use the existing table to unpivot the values.

    Probably we can use some XML functions if there available for this to get the desired output.

    And the desired output are dummy values created by me as mentioned in the earlier post.

  7. #7
    Join Date
    Sep 2012
    Posts
    3
    Hi,

    I know I'm late but here something:

    ************************************************** ***
    CREATE FUNCTION elemIdx ( string CLOB(64K) )
    RETURNS TABLE ( ordinal INTEGER, index INTEGER )
    NO EXTERNAL ACTION
    F1: BEGIN ATOMIC
    RETURN
    WITH t(ordinal, index) AS
    ( VALUES ( 0, 0 )
    UNION ALL
    SELECT ordinal+1, COALESCE(NULLIF(
    -- find the next delimiter ','
    LOCATE(',', string, index+1), 0),
    LENGTH(string)+1)
    FROM t
    -- to prevent a warning condition for infinite
    -- recursions, we add the explicit upper
    -- boundary for the "ordinal" values
    WHERE ordinal < 10000 AND
    -- terminate if there are no further delimiters
    -- remaining
    LOCATE(',', string, index+1) <> 0 )
    SELECT ordinal, index
    FROM t
    UNION ALL
    -- add indicator for the end of the string
    SELECT MAX(ordinal)+1, LENGTH(string)+1
    FROM t ;
    END

    ***************************************
    CREATE FUNCTION elements ( string CLOB(64K) )
    RETURNS TABLE ( elements CLOB(64K) )
    NO EXTERNAL ACTION
    F1: BEGIN ATOMIC
    RETURN
    WITH t(ordinal, index) AS
    ( SELECT ordinal, index
    FROM TABLE ( elemIdx(string) ) AS x )
    SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1)
    -- the join below makes sure that we have the lower and
    -- upper index where we can find each of the ',' delimiters
    -- that are separating the elements. (For this, we exploit
    -- the additional indexes pointing to the beginning and end
    -- of the string.)
    FROM t AS t1 JOIN t AS t2 ON
    ( t2.ordinal = t1.ordinal+1 );
    END
    ************************************************** ******
    SELECT VARCHAR(elem, 20)
    FROM YOURTABLENAME, TABLE ( elements(BATCH_NO) ) AS t(elem);
    ************************************************** *********

    I've tried it and it worked just fine. You should paste my code because there was very little syntax mistakes on the site.

    Taken out from this site:Parsing Strings in SQL

    Regards,

    Louis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •