Results 1 to 7 of 7

Thread: DB2 select

  1. #1
    Join Date
    Nov 2012
    Posts
    3

    Unanswered: DB2 select

    Hello,
    i am pretty new to DB2 ESE and i need to solve some selects.
    db2level:
    We are using "64" bits and DB2 code release "SQL09012" with level identifier "01030107". Informational tokens are "DB2 v9.1.200.98", "s070210", "WR21381", and Fix Pack "2".

    I have table with multiple columns and in one of them - subject - can have multiple values separated by semicolon. Subject data type is varchar(2000), every subject has 87 characters and must be there at least one value. Something like this:
    ID|started|completed|subject|registrator|gestor
    1253641|2011-01-15 00:00:00|2011-01-15 11:05:21|aaa;bbb;ccc;|Jaki Loki|Jokina
    1253642|2011-01-15 00:10:00|2011-01-15 14:15:261|ddd;|Jaki Loki|Jokina

    How can I separate the values from column subject to different columns or rows?
    I will be pretty happy to have something like this:
    1253641|2011-01-15 00:00:00|2011-01-15 11:05:21|aaa;|Jaki Loki|Jokina
    1253641|2011-01-15 00:00:00|2011-01-15 11:05:21|bbb;|Jaki Loki|Jokina
    1253641|2011-01-15 00:00:00|2011-01-15 11:05:21|ccc;|Jaki Loki|Jokina
    or this:
    ID|started|completed|subject|subject1|subject2|reg istrator|gestor
    1253641|2011-01-15 00:00:00|2011-01-15 11:05:21|aaa|bbb|ccc|Jaki Loki|Jokina

    Is there any way how to do that with my DB2 version?
    Thank you and sorry for my english.
    Chap.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Something like this, may be?
    Code:
    with test(str) as (values 'aaa;bbb;ccc;'), 
    t(s, pos, lvl) as (
      select 
        substr(str,1,locate(';',str,1)-1),
        locate(';',str,1)+1,
        1 
      from test 
      
      union all 
      
      select 
        substr(str,pos,locate(';',str,pos)-pos),
        locate(';',str,pos)+1,
        lvl+1 
      from t, test 
      where 
        t.pos <= length(test.str) and 
        lvl <=100
    ) select s from t
    You'll need to account for border cases (e.g. if the last character is not a semicolon).
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow

    or this:
    ID|started|completed|subject|subject1|subject2|reg istrator|gestor
    1253641|2011-01-15 00:00:00|2011-01-15 11:05:21|aaa|bbb|ccc|Jaki Loki|Jokina
    Just do replace(subject, ';', '|') ==>

    Code:
    SELECT     REPLACE(varchar(id) 
                             || '|' || char(started) 
                             || '|' || char(completed) 
                             || '|' || replace(subject, ';', '|') 
                             || '|' || registrator
                             || '|' || gestor, '||', '|')
    FROM YOUR_TABLE
    Lenny
    Last edited by Lenny77; 12-11-12 at 17:25.

  4. #4
    Join Date
    Nov 2012
    Posts
    3

    Error in select

    Hello,
    thank you for your quick answer. I am trying to make the final query, but I have this error:
    SQL0206N "POS" is not valid in the context where it is used. SQLSTATE=42703

    My querry is following:
    with t(s, pos, lvl) as (
    select
    substr(proc.subject,1,locate(';',proc.subject,1)-1),
    locate(';',proc.subject,1)+1,
    1
    from enr.proc as proc

    union all

    select
    substr(proc.subject,pos,locate(';',proc.subject,po s)-pos),
    locate(';',proc.subject,pos)+1,
    lvl+1
    from enr.proc as proc
    where
    pos <= length(proc.subject) and
    lvl <=100
    ) select s from t;

    proc is the table name and enr is account schema.
    What I am doing wrong?
    Thank you again.
    Chap.

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    pos is the column in result table t
    can only be used in final select: select s, pos .. from t....
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Chaplin67 View Post
    union all

    select
    substr(proc.subject,pos,locate(';',proc.subject,po s)-pos),
    locate(';',proc.subject,pos)+1,
    lvl+1
    from enr.proc as proc
    where
    pos <= length(proc.subject) and
    lvl <=100
    To make the query recursive you need to join it to itself. Look again at my example.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Nov 2012
    Posts
    3
    Hello,
    please, can someone show me , how should the whole query look like? proc is the table name and enr is account schema. I am dumb I can't make the query to work.
    My query is now:
    with t(s,pos,lvl) as (select
    substr(subject,1,locate(';',subject,1)-1),
    locate(';',subject,1)+1,
    1
    from enrole.proc as proc where proc.state='R'

    union all

    select
    substr(proc.subject,pos,locate(';',proc.subject,po s)-pos),
    locate(';',proc.subject,pos)+1,
    lvl+1
    from t
    where
    t.pos <= length(proc.subject) and lvl<=100
    ) select s from t

    The error is now:
    An unexpected token "100" was found following "h(subject) and lvl<=". Expected tokens may include: "IS NULL". SQL Code: -104, SQL State: 42601
    Thank you.
    Chap.

Posting Permissions

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