| Optimizing the Query [message #332388] |
Tue, 08 July 2008 07:32  |
itroome
Messages: 34
Registered: June 2005
Location: DUBAI, UAE
|
Member |
 
|
|
Dear Guros
Any One Suggest me to improve this Query with Best Explain. I want to this Query to populate hte Data into LOVs rECORD gROUP
Query:
SELECT job_ref, job_status, bol_number, department_code, import_export,
orig_ref
FROM (SELECT job_reference job_ref, job_status, bol_number,
department_code, import_export, job_reference orig_ref
FROM job_headers jh
WHERE jh.job_reference LIKE :booking.t_jobref || '%'
AND jh.job_status IN ('0', '1', '20')
AND ( jh.bol_number IS NULL
OR ((SELECT MAX (job_status)
FROM consolidation_status
WHERE consolidation_ref = jh.bol_number
AND department_code = jh.department_code
AND import_export = jh.import_export
AND event_date IS NOT NULL) = '0'
)
)
UNION ALL
SELECT external_job_reference job_ref, job_status, bol_number,
department_code, import_export, job_reference orig_ref
FROM job_headers_light jh
WHERE jh.external_job_reference LIKE :booking.t_jobref || '%'
AND jh.job_status IN ('0', '1')
UNION ALL
SELECT trpuh.purchase_ref job_ref, trpuh.status job_status,
trpuh.bol_number bol_number,
trpuh.department_code dedpartment_code, 'E' import_export,
trpuh.purchase_ref orig_ref
FROM purchase_header trpuh
WHERE trpuh.purchase_ref LIKE :booking.t_jobref || '%'
AND trpuh.status = '1')
ORDER BY job_ref;
_________________________
Muhammad Aurangzaib
itroome@yahoo.com
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
