Hi all,
I have a query that I am struggling on and think I may need some conditional joining.
I have a table called RESULT_DOC_TERMINAL_ENTRIES which holds some documents information. This table only holds the documents that have been finalised / confirmed. Before the documents get finalised they are in a table called PSS_RESULT_DOC_TMNL_ENTRY which holds the draft documents. There may be documents that are in RESULT_DOC_TERMINAL_ENTRIES but not in PSS_RESULT_DOC_TMNL_ENTRY or a document that could be in both i.e. it has been finalised in the past but then sent back for editing so a draft is created from it.
The primary key for both tables is called RTE_ID which I am passing in as a parameter in my program. These 2 tables are then joined to a table called DOCUMENT_ELEMENTS by a fields called DOCE_ID.
I am writing a package that passes in RTE_ID and p_status (which determines whether to pass in the draft or the final document).
Here is the tricky bit
If the user passes an rte _id that is in both the draft and final tables then I ONLY WANT TO RETURN THE DRAFT RECORD.
If there is only a row in the final table then return the final version.
Hope that makes sense.
Here is the SQL I have drafted up but the joins aren't quite right which I think is because I think I need the join from rdte to de to be conditional on prdte join not returning anything?
WITH pss_result_doc_tmnl_entry AS (SELECT doce_doce_id as DOCE_ID
FROM pss_result_doc_tmnl_entry prdte
WHERE 1=1
AND prdte.rte_rte_id = :p_rte_id)
, result_doc_tmnl_entries AS (SELECT doce_doce_id as DOCE_ID
FROM result_doc_terminal_entries rdte
WHERE 1=1
AND rdtre.rte_rte_id = :p_rte_id)
, doc_element AS (SELECT
FROM document_elements de)
SELECT prdte.*
, rdte.*
, de.*
FROM pss_result_doc_tmnl_entry prdte
, result_doc_terminal_entry rdte
, do_element de
WHERE NVL(prdte.doce_id,rdte.doce_id) = de.doce_id
I only need to use the rdte when the top subquery returns no rows but am not sure about the joins. I have read a bit about conditional joining but can’t get it working…