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.

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

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.

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

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.

You forgot the outer SELECT part that umayer posted.

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.

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.

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.

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.)