All Forums Database
kiki_riki_miki 2 posts Joined 11/14
04 Dec 2014
Filter results from two joins

Hello,
I have an issue while trying to get only the record containing max(c.event_dtm) from the following query:
SEL     a.startclock_day,
           a.shacct_no,
           a.awb_no,
           b.ship_ref,
           b.lc_event_cd,
           c.event_dtm,
           c.gmt_event_dtm,
           b.dest_ctry,
           b.dest_stn,
           a.rcvr_city,
           a.rcvr_name
FROM opms2_gnsdw.t_shipmentdetail AS a
INNER JOIN opms2_gnsdw.t_shipmentdetail_calc AS b ON a.shp_rec_key = b.shp_rec_key
JOIN opms2_gnsdw.opms_event_calc AS c ON b.shp_rec_key = c.shp_rec_key

WHERE a.startclock_day BETWEEN '2014-11-25'AND '2014-11-28'
  AND b.startclock_day BETWEEN '2014-11-25'AND '2014-11-28'
  AND c.startclock_day BETWEEN '2014-11-25'AND '2014-11-28'

and a.awb_no in (....)
and c.event_dtm =
(
select max(event_dtm)
from opms2_gnsdw.opms_event_calc
);
 
Anyone can help me to filter the final results and to display only the line containing the max value of c.event_dtm?
 
Thank you for your help.

stahengik 15 posts Joined 10/14
04 Dec 2014

Are you getting 0 rows as a result of this query ?
If yes: May be the Max(event_dtm) value does not lie in the date ranges you selected for startclock_day.
So I would suggest 
Sel * from 

(

SEL     a.startclock_day,
           a.shacct_no,
           a.awb_no,
           b.ship_ref,
           b.lc_event_cd,
           c.event_dtm,
           c.gmt_event_dtm,
           b.dest_ctry,
           b.dest_stn,
           a.rcvr_city,
           a.rcvr_name
FROM opms2_gnsdw.t_shipmentdetail AS a
INNER JOIN opms2_gnsdw.t_shipmentdetail_calc AS b ON a.shp_rec_key = b.shp_rec_key
JOIN opms2_gnsdw.opms_event_calc AS c ON b.shp_rec_key = c.shp_rec_key

WHERE a.startclock_day BETWEEN '2014-11-25'AND '2014-11-28'
  AND b.startclock_day BETWEEN '2014-11-25'AND '2014-11-28'
  AND c.startclock_day BETWEEN '2014-11-25'AND '2014-11-28'

and a.awb_no in (....)
) 

where event_dtm = select max(event_dtm) from 

(

SEL     a.startclock_day,
           a.shacct_no,
           a.awb_no,
           b.ship_ref,
           b.lc_event_cd,
           c.event_dtm,
           c.gmt_event_dtm,
           b.dest_ctry,
           b.dest_stn,
           a.rcvr_city,
           a.rcvr_name
FROM opms2_gnsdw.t_shipmentdetail AS a
INNER JOIN opms2_gnsdw.t_shipmentdetail_calc AS b ON a.shp_rec_key = b.shp_rec_key
JOIN opms2_gnsdw.opms_event_calc AS c ON b.shp_rec_key = c.shp_rec_key

WHERE a.startclock_day BETWEEN '2014-11-25'AND '2014-11-28'
  AND b.startclock_day BETWEEN '2014-11-25'AND '2014-11-28'
  AND c.startclock_day BETWEEN '2014-11-25'AND '2014-11-28'

and a.awb_no in (....)
) 
;

 
I am sure there are better ways to write this code, but this is the first thing came to my mind. Please imporve this as it need to

 

dnoeth 4628 posts Joined 11/04
04 Dec 2014

You want only a single row?

SEL     a.startclock_day,
           a.shacct_no,
           a.awb_no,
           b.ship_ref,
           b.lc_event_cd,
           c.event_dtm,
           c.gmt_event_dtm,
           b.dest_ctry,
           b.dest_stn,
           a.rcvr_city,
           a.rcvr_name
FROM opms2_gnsdw.t_shipmentdetail AS a
INNER JOIN opms2_gnsdw.t_shipmentdetail_calc AS b ON a.shp_rec_key = b.shp_rec_key
JOIN opms2_gnsdw.opms_event_calc AS c ON b.shp_rec_key = c.shp_rec_key

WHERE a.startclock_day BETWEEN '2014-11-25'AND '2014-11-28'
  AND b.startclock_day BETWEEN '2014-11-25'AND '2014-11-28'
  AND c.startclock_day BETWEEN '2014-11-25'AND '2014-11-28'

and a.awb_no in (....)

QUALIFY RANK() OVER (ORDER BY c.event_dtm DESC) = 1
;
 

 

Dieter

kiki_riki_miki 2 posts Joined 11/14
05 Dec 2014

Thank you Dieter, this is the line I wanted to have as a result of this query.
 

You must sign in to leave a comment.