CODEWITHSUNDEEP
sqloracle-database
sp_user123
sp_user123

Reputation: 502

Multiple optional conditions in oracle query

I have bellow data in a table .

AP_ID     | AP_VERSION_ID | ORDER_ACTION_ID | AP_ID_PARENT | MAIN_IND
----------+---------------+-----------------+--------------+---------
100000002 | 0             |                 |              | 0
100000003 | 0             | 5               | 100000002    | 1
100000012 | 0             | 12              | 100000010    | 0
100000013 | 0             | 12              | 100000012    | 0

I want the ap_id and ap_id parent with beloow query 

select AI.AP_ID || AI.AP_VERSION_ID,
   DOM.AP_ID || DOM.AP_VERSION_ID,
   AI.ORDER_ACTION_ID,
   DOM.ORDER_ACTION_ID,
   ai.main_ind
   from omsrepdb11.tbap_item ai ,omsrepdb11.tbap_item dom  
   where AI.AP_ID_Parent = DOM.AP_ID (+)
  AND AI.ORDER_ACTION_ID = DOM.ORDER_ACTION_ID(+)
   and ai.ap_id in (100000003,100000013);

But its not giving the required data .For ap_id 100000003 its giving null ap_id Parent as bellow.

AP_ID_VERSION | DOM_ID_VERSION | AI_OA_ID | DOM_OA_ID | MAIN_IND
--------------+----------------+----------+-----------+---------
1000000030    |                | 5        |           | 1
1000000130    | 1000000120     | 12       | 12        | 0

Upvotes: 1

Views: 137

Answers (3)

Boneist
Boneist

Reputation: 23588

The issue is that your join condition isn't taking account of when the dom.order_action_id is null.

I presume that you're after something like:

WITH tbap_item AS (SELECT 100000002 ap_id, 1 ap_version_id, NULL order_action_id, NULL ap_id_parent, 0 main_ind FROM dual UNION ALL
                   SELECT 100000003 ap_id, 0 ap_version_id, 5 order_action_id, 100000002 ap_id_parent, 1 main_ind FROM dual UNION ALL
                   SELECT 100000012 ap_id, 0 ap_version_id, 12 order_action_id, 100000010 ap_id_parent, 0 main_ind FROM dual UNION ALL
                   SELECT 100000013 ap_id, 0 ap_version_id, 12 order_action_id, 100000012 ap_id_parent, 0 main_ind FROM dual)
SELECT ai.ap_id || ai.ap_version_id ap_id_version,
       dom.ap_id || dom.ap_version_id dom_id_version,
       ai.order_action_id ai_oa_id,
       dom.order_action_id dom_oa_id,
       ai.main_ind
FROM   tbap_item ai
       LEFT OUTER JOIN tbap_item dom ON ai.ap_id_parent = dom.ap_id
                                        AND ai.order_action_id = NVL(dom.order_action_id, ai.order_action_id)
WHERE  ai.ap_id IN (100000003, 100000013);

AP_ID_VERSION DOM_ID_VERSION   AI_OA_ID  DOM_OA_ID   MAIN_IND
------------- -------------- ---------- ---------- ----------
1000000030    1000000021              5                     1
1000000130    1000000120             12         12          0

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95100

Your problem is that there is no entry with AP_ID = 100000002 and ORDER_ACTION_ID = 5 in your table, so there is no parent match for the 100000003 record. Hence dom.ap_id and dom.ap_version_id are null, because you've outer-joined this record. So the result you are getting is correct.

To get 1000000020 as a parent for 1000000030, though, you could use ai.ap_id_parentinstead of dom.ap_id and decide how to get the missing dom.ap_version_id. E.g.:

select 
  ai.ap_id || ai.ap_version_id,
  ai.ap_id_parent || coalesce(dom.ap_version_id, ai.ap_version_id),
  ai.order_action_id,
  dom.order_action_id,
  ai.main_ind
from omsrepdb11.tbap_item ai 
left join omsrepdb11.tbap_item dom on  dom.ap_id = ai.ap_id_parent
                                   and dom.order_action_id = ai.order_action_id
where ai.ap_id in (100000003,100000013);

But as mentioned, your result is correct. So think over what you really want. Maybe you don't even want to join on id + order_action_id at all.

Here is the query only joining on order_action_id when main_ind isn't 1.

select 
  ai.ap_id || ai.ap_version_id,
  dom.ap_id || dom.ap_version_id,
  ai.order_action_id,
  dom.order_action_id,
  ai.main_ind
from omsrepdb11.tbap_item ai 
left join omsrepdb11.tbap_item dom 
            on  dom.ap_id = ai.ap_id_parent
            and (ai.main_ind = 1 or dom.order_action_id = ai.order_action_id)
where ai.ap_id in (100000003,100000013);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Start by using proper explicit join syntax:

select . . .
from omsrepdb11.tbap_item ai left join
     omsrepdb11.tbap_item dom  
     on ai.AP_ID_Parent = dom.AP_ID and
        ai.ORDER_ACTION_ID = dom.ORDER_ACTION_ID
where ai.ap_id in (100000003, 100000013);

Upvotes: 0

Related Questions