Results 1 to 10 of 10

Thread: Merging data

  1. #1
    Join Date
    Sep 2008
    Posts
    6

    Unanswered: Merging data

    I want run a sql query where I want to select different fields from the same table via different selection criteria, a common field being requestid. Please indicate how one could do this in db2? Could I use Common Table Expression? For example, a very crude example of what I want:

    Query 01:
    Select requestid, tstamp, submitedby, dstid, source from logging where date(tstamp) = '2008-10-01'

    Query 02:
    Select requestid, desitination, replaykey, status from logging where status = '1' and date(tstamp) = '2008-10-01'

    And I want then to select requestid, tstamp, submitedby, desitination, replaykey from the above two tables connected by request id.

  2. #2
    Join Date
    Sep 2008
    Posts
    6

    Heres a better example of what I am trying to do

    I am only interested in messages that have a Submit with status of 1 during a certain period (usually going to be a month). For each entry within this time period there will be a tstamp entry relating to the Submit 1. Relating to each Submit 1 will be a record in the table that has an event of Request with a status of 1. Each Request 1 line will also have a tstamp. The common factor in both of these lines is a reqID

    I cant seem to post the table neatly distributed

    Tstamp ReqID Source Dest Event Status

    2008-09-02-09.00.00 1000 SysA - Request 1
    2008-09-02-09.00.01 1000 SysA Lon Valid -
    2008-09-02-09.00.02 1000 SysA Lon Submit 1
    2008-09-02-09.30.10 2222 SRV3 - Request 1
    2008-09-02-09.30.11 2222 SRV3 Dor Valid -
    2008-09-02-09.30.12 2222 SRV3 Dor Submit 1



    What I want to extract is

    ReqID Event Tstamp Event Tstamp

    1000 Request 2008-09-02-09.00.00 Submit 2008-09-02-09.00.02
    2222 Request 2008-09-02-09.30.10 Submit 2008-09-02-09.30.12

    Any help would be greatly apprecaited

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Join the table with itself and then select from the left join partner the "request" information and from the right join partner the "submit" information. The join condition is on the request-id. You may need a full outer join if one of the two pieces is not available.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    Try this:


    SELECT .... ( the required fields )
    FROM
    ( Select requestid, tstamp, submitedby, dstid, source from logging where date(tstamp) = '2008-10-01' ) A
    LEFT JOIN
    ( Select requestid, desitination, replaykey, status from logging where status = '1' and date(tstamp) = '2008-10-01' ) B
    ON A.requestid = B.requestID

  5. #5
    Join Date
    Sep 2008
    Posts
    6

    Tried but got errors

    umayer, I tried your suggestion and i got an error:


    db2 "(select requestid,tstamp,event,status from db2inst1.logging where date(tstamp)>= '2008-09-01' AND date(tstamp)<= '2008-09-01' AND event = 'Request' AND status = 1) A LEFT JOIN (select requestid,tstamp,event,status from db2inst1.logging where date(tstamp)>= '2008-09-01' AND date(tstamp)<= '2008-09-01' AND event = 'Submit' AND status = 1) B ON A.requestid = B.requestid"
    SQL0104N An unexpected token "JOIN" was found following "". Expected tokens
    may include: "FROM". SQLSTATE=42601

    I also tried a couple of other options and I got similar results:


    db2 "with temp1 as (select requestid,tstamp,event,status from db2inst1.logging q1 where date(tstamp)>= '2008-09-01' AND date(tstamp)<= '2008-09-01' AND event = 'Request' AND status = 1), temp2 as (select requestid,tstamp,event,status from db2inst1.logging where date(tstamp)>= '2008-09-01' AND date(tstamp)<= '2008-09-01' AND event = 'Submit' AND status = 1) select temp1.tstamp, temp2.tstamp where temp1.requestid = temp2.requestid)
    "
    SQL0104N An unexpected token "temp1" was found following ", temp2.tstamp
    where". Expected tokens may include: "FROM". SQLSTATE=42601


    db2 "select requestid,tstamp,event,status from db2inst1.logging q1 where date(tstamp)>= '2008-09-01' AND date(tstamp)<= '2008-09-01' AND event = 'Request' AND status = 1, table(select requestid,tstamp,event,status from db2inst1.logging where date(tstamp)>= '2008-09-01' AND date(tstamp)<= '2008-09-01' AND event = 'Submit' AND status = 1) as temp1 t where q1.requestid = t.requestid"
    SQL0104N An unexpected token "," was found following "uest' AND status = 1".
    Expected tokens may include: "UNION". SQLSTATE=42601


    Any help will be greatly appreciated.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You forgot the outer SELECT part that umayer posted.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Sep 2008
    Posts
    6
    Thank you stolze for pointing out the mistake I had made. Thank you umayer for providing me with the solution to my problem. Your help was greatly appreciated.
    Last edited by gugs; 10-22-08 at 08:03.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I recommend that you try to understand exactly how the SQL statement works. This is just basic SQL, so you may run into similar questions in the future.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Feb 2008
    Posts
    5
    You can get the result without join:

    WITH EV_INTERVAL(EVENT_TSTAMP, REQID,EVENT_STATUS) AS
    (VALUES('2008-09-02-09.00.00','1000','Request'),
    ('2008-09-02-09.00.01','1000','1Valid'),
    ('2008-09-02-09.00.02','1000','Submit'),
    ('2008-09-02-09.30.10','2222','Request'),
    ('2008-09-02-09.30.11','2222','Valid'),
    ('2008-09-02-09.30.12','2222','2Submit'),
    ('2008-09-02-10.25.12','3333','0Request'),
    ('2008-09-02-10.25.13','3333','Valid'),
    ('2008-09-02-10.25.17','3333','Submit')),
    EV_NUMBER(EVENT_TSTAMP, REQID,EVENT_STATUS,RN) AS
    (SELECT EVENT_TSTAMP, REQID,EVENT_STATUS,ROW_NUMBER() OVER(PARTITION BY REQID)
    FROM EV_INTERVAL)
    SELECT REQID,MIN(EVENT_TSTAMP) AS START_DT,STRIP(SUBSTR(MIN(CHAR(RN)||EVENT_STATUS), 3)) AS BEG_EVENT,
    MAX(EVENT_TSTAMP) AS END_DT, STRIP(SUBSTR(MAX(CHAR(RN)||EVENT_STATUS),3)) AS END_EVENT
    FROM EV_NUMBER
    GROUP BY REQID;

    REQID START_DT BEG_EVENT END_DT END_EVENT
    ----- ------------------- ----------- ------------------- ---------------
    1000 2008-09-02-09.00.00 Request 2008-09-02-09.00.02 Submit
    2222 2008-09-02-09.30.10 Request 2008-09-02-09.30.12 Submit
    3333 2008-09-02-10.25.12 Request 2008-09-02-10.25.17 Submit

    3 record(s) selected.

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You are using groups where each request-id defines a single group. Then you apply min/max to find the first/last entry in the group. That is a nice idea, however, you have the following problems:
    1. ou have no order in the groups, so there is no guarantee at all that the begin/end timestamps of the groups are for the respective Request/Submit entry, respectively. That can be fixed by adding "ORDER BY EVENT_STATUS" to the "OVER()" clause. And that just happens to work because Request comes before Submit in the lexicographical ordering. (The whole business of concatenating RN and EVENT_STATUS does not! fix that at all.)
    2. This approach becomes rather complicated as soon as you have more than 2 events.


    So I would still stick with the join because that is a straight-forward concept and easier to understand. (And I believe that the OP has different issues to tackle than to get the last bit of performance out of the system.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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