Filter results from two joins

Database

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.

Tags (4)
3 REPLIES
Enthusiast

Re: Filter results from two joins

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

Senior Apprentice

Re: Filter results from two joins

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
;

Re: Filter results from two joins

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