1. Yes. Result set is correct. It all depends on the predicate value.
2. Sure, here is the query with the DGTT definition given below.
(Thanks much for the help!)
DGTT definition:
DECLARE GLOBAL TEMPORARY TABLE
SESSION."#ts930a_scan_eq"
( scnr_rpt_ln_nr smallint not null
, cnsst_seq_nr smallint not null
, eq_init char(4) not null
, eq_nr char (10) not null
, eq_display_tp char(1) not null
, equipid char(15) not null
, scnr_vehicle_tp char(1) not null
, orientation char(1) not null
, tag_stat char(1) not null
, eq_id numeric(14)
, empty_ind char(1)
, curr_gals smallint
, kwh int
, wgt int
, pos_on_tk smallint
, indus_nr char(4)
, sub_tk_or_spot char(2)
, spot smallint
, eq_loc_id numeric(14)
, tk_id char(6)
, actv_trn_id numeric(14,0)
, plnned_ic_rcpt_id numeric(14)
, lst_rpt_dtm char(26)
, lst_rpt_loc_id numeric(14)
, lst_rpt_evt_tp char(4)
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED !
INSERT INTO SESSION."#ts930a_scan_eq"
( scnr_rpt_ln_nr
, cnsst_seq_nr
, eq_init
, eq_nr
, eq_display_tp
, equipid
, scnr_vehicle_tp
, orientation
, tag_stat
, eq_id
, empty_ind
, curr_gals
, kwh
, wgt
, pos_on_tk
, indus_nr
, sub_tk_or_spot
, spot
, sub_tk_or_spot
, spot
, eq_loc_id
, tk_id
, actv_trn_id
, plnned_ic_rcpt_id
, lst_rpt_dtm
, lst_rpt_loc_id
, lst_rpt_evt_tp)
SELECT a.scnr_rpt_ln_nr
, a.cnsst_seq_nr
, a.eq_init
, a.eq_nr
, CASE WHEN a.scnr_vehicle_tp = 'D' THEN 'E'
WHEN a.scnr_vehicle_tp = 'E' THEN 'X'
ELSE ' '
END
, a.eq_init ||
CASE WHEN a.scnr_vehicle_tp = 'D' THEN 'E'
WHEN a.scnr_vehicle_tp = 'E' THEN 'X'
ELSE ' '
END ||
a.eq_nr
, a.scnr_vehicle_tp
, a.orientation
, a.tag_stat
, b.eq_id
, b.empty_ind
, a.curr_gals
, a.kwh
, a.wgt
, b.pos_on_tk
, b.indus_nr
, b.sub_tk_or_spot
, b.spot
, b.eq_loc_id
, b.tk_id
, b.actv_trn_id
, 0
, b.lst_rpt_dtm
, b.lst_rpt_loc_id
, b.lst_rpt_evt_tp
FROM
tyes.scnr_rpt_eq AS a left outer join
tyes.actv_eq AS b on
a.eq_init = b.eq_init and a.eq_nr = b.eq_nr
and (CASE WHEN a.scnr_vehicle_tp = 'D' THEN 'E'
WHEN a.scnr_vehicle_tp = 'E' THEN 'X'
ELSE ' '
WHEN a.scnr_vehicle_tp = 'E' THEN 'X'
ELSE ' '
END) = b.eq_display_tp
WHERE a.stor_dtm = '2005-06-03-16.37.09.260000'
!
The actual Query is:
SELECT distinct cast(rtrim(c.trn_nr)
|| rtrim(COALESCE(c.crew_distr, ''))
|| right('00'
|| cast(c.origin_day as char(2)),2)
as char(10))
, b.cnsst_h_id
FROM SESSION."#ts930a_scan_eq" as a
, tyes.trn_event_h as b
, tyes.eq_event_h as c
WHERE a.actv_trn_id = b.actv_trn_id
AND b.evt_tp = 'DFLC'
AND b.cnsst_h_id = c.cnsst_h_id
AND c.source_cd in (4, 5)
AND c.evt_dtm =
(SELECT MAX (r.evt_dtm)
FROM tyes.trn_event_h r
, tyes.eq_event_h s
WHERE r.actv_trn_id = b.actv_trn_id
AND r.evt_tp = 'DFLC'
AND r.cnsst_h_id = s.cnsst_h_id
AND s.source_cd in (4, 5) )
AND ( 2 * (SELECT count(1)
FROM SESSION."#ts930a_scan_eq" x
WHERE x.actv_trn_id = b.actv_trn_id)
(SELECT count(1)
FROM tyes.eq_event_h y
WHERE y.cnsst_h_id = c.cnsst_h_id
FROM tyes.eq_event_h y
WHERE y.cnsst_h_id = c.cnsst_h_id
AND y.eq_tp NOT IN ('T', 'C', 'Z'))
)
!
Thanks!
Vijay
