SELECT vts.stn_name stn_name ,DECODE(to_char(vvt.vet_date_cert_issued,'DD-MON-RRRR'),'01-JAN-0001',vtc.vtc_cert_issued_date,vvt.vet_date_cert_issued) vet_date_cert_issued ,vts.stn_id stn_id ,vvt.vet_test_certificate_srl_num vet_test_certificate_srl_num ,vtc.vtc_cert_serial_no vtc_cert_serial_no ,vtc.vtc_cert_issued_date vtc_cert_issued_date ,DECODE(vvt.vet_result_code,'S','PASS','R','PRS',NULL) test_result ,vvt.vet_veh_id vehicle_id FROM vt_test_station vts ,vt_vehicle_test vvt ,vt_test_certificate vtc WHERE vvt.vet_fkstn_id = vts.stn_id AND (vvt.vet_date_cert_issued ='25-JAN-07' OR vtc.vtc_cert_issued_date='25-JAN-07') AND (vts.stn_id = 'H00006' OR vts.stn_fkstn_id = 'H00006') AND vvt.vet_reservation_num = vtc.vtc_fk_vt_res_num(+) ORDER BY least(vtc.vtc_cert_serial_no, vvt.vet_test_certificate_srl_num) STN_NAME VET_DATE_CERT_ISSUED STN_ID VET_TEST_CERTIFICATE_SRL_NUM VTC_CERT_SERIAL_NO VTC_CERT_ISSUED_DATE TEST_RESULT VEHICLE_ID ------------------------------ -------------------- ------ ---------------------------- ------------------ -------------------- ----------- ---------- GILLINGHAM TEST STATION 25/01/2007 H00006 AA0258399 PASS 565250Z GILLINGHAM TEST STATION 25/01/2007 H00006 AA258400 25/01/2007 PASS 565854Z GILLINGHAM TEST STATION 25/01/2007 H00006 HQ4870J PASS MC03MMC GILLINGHAM TEST STATION 25/01/2007 H00006 VF5123E PASS C112590 GILLINGHAM TEST STATION 25/01/2007 H00006 PN0502C PASS FJ04ABN GILLINGHAM TEST STATION 25/01/2007 H00006 VF5131D PASS C130333 GILLINGHAM TEST STATION 25/01/2007 H00006 PL4990P PASS YN04EZA GILLINGHAM TEST STATION 25/01/2007 H00006 PN0521D PASS WK51FCM GILLINGHAM TEST STATION 25/01/2007 H00006 VF5124F PASS A240426 GILLINGHAM TEST STATION 25/01/2007 H00006 PL4977U PRS W66AMS GILLINGHAM TEST STATION 25/01/2007 H00006 PL4983R PASS X485MKP GILLINGHAM TEST STATION 25/01/2007 H00006 PL4988W PASS R66RAB GILLINGHAM TEST STATION 25/01/2007 H00006 PN0516H PASS X846VJN