Simplify the oracle query without UNION syntax

I already post the same with mistake. So asking the same question with correction. Is it possible to avoid UNION in this query? Can anyone help on this query? Thanks in advance

SELECT A.M_TITLE, 
       A.M_NUMBER, 
       B.M_NAME, 
       B.M_DESCRIPTION, 
       A.FORM_TYPE, 
       B.SUBMISSION_DATE, 
       B.CREATON_DATE, 
       A.R_OBJECT_ID AS FORM_ID,
       B.R_OBJECT_ID AS FORMDATA_ID, 
       ''            AS ATTACHMENTS 
FROM M_FORM A, 
     M_FORMDATA B 
WHERE A.M_NUMBER = B.M_NUMBER 
AND ANY B.ATTACHMENTS IN 
    (SELECT ATTACHMENTS FROM M_FORMDATA C WHERE B.M_NUMBER = C.M_NUMBER) 
AND ACTION_ID = 'XXXX' 
AND SUBMISSION_DATE >= DATE('XXXX') 
AND SUBMISSION_DATE <= DATE('XXXX') 

UNION 

SELECT A.M_TITLE, 
       A.M_NUMBER, 
       B.M_NAME, 
       B.M_DESCRIPTION, 
       A.FORM_TYPE, 
       B.SUBMISSION_DATE, 
       B.CREATION_DATE, 
       A.R_OBJECT_ID AS FORM_ID,
       B.R_OBJECT_ID AS FORMDATA_ID, 
       'null'        AS ATTACHMENTS 
 FROM M_FORM A, 
      M_FORMDATA 
 WHERE A.M_NUMBER = B.M_NUMBER 
 AND NOT ANY B.ATTACHMENTS IN 
      (SELECT ATTACHMENTS FROM M_FORMDATA C WHERE B.M_NUMBER = C.M_NUMBER) 
 AND ACTION_ID= 'XXXX' 
 AND SUBMISSION_DATE >= DATE('XXXX') 
 AND SUBMISSION_DATE <= DATE('XXXX')

Answers


If I understand the query correctly, the only difference is in how ATTACHMENTS is defined. You can make that conditional:

SELECT A.M_TITLE, 
       A.M_NUMBER, 
       B.M_NAME, 
       B.M_DESCRIPTION, 
       A.FORM_TYPE, 
       B.SUBMISSION_DATE, 
       B.CREATON_DATE, 
       A.R_OBJECT_ID AS FORM_ID,
       B.R_OBJECT_ID AS FORMDATA_ID, 
       (case when B.ATTACHMENTS IN (SELECT ATTACHMENTS FROM M_FORMDATA C WHERE B.M_NUMBER = C.M_NUMBER) 
             then ''
        end) AS ATTACHMENTS 
FROM M_FORM A join
     M_FORMDATA B 
     on A.M_NUMBER = B.M_NUMBER 
WHERE ACTION_ID = 'XXXX' AND
      SUBMISSION_DATE >= DATE('XXXX') AND SUBMISSION_DATE <= DATE('XXXX') ;

I also changed the join syntax to use explicit joins (with the on clause).


Need Your Help

How to upload arules package in R

r arules

I am not able to upload arules package

make jQuery table sorter sort in ascending order everytime a new column is clicked

jquery jquery-plugins

I would like to ask if there's a way to make table sorter sort in ascending order everytime a new column is clicked?