Current Position:Home > Merge the Query

Merge the Query

Update:11-30Source: network consolidation
Advertisement
SELECT
MAX(fndattdoc.LAST_UPDATE_DATE ) as LAST_UPDATE_DATE,
MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL)) as COMMENTS,
MAX(fnddoc.description) as REASON
FROM fnd_attachment_functions fndattfn,
fnd_doc_category_usages fndcatusg,
fnd_documents_vl fnddoc,
fnd_attached_documents fndattdoc,
fnd_documents_short_text st,
fnd_document_categories_tl fl,
WSH_NEW_DELIVERIES DLVRY
WHERE fndattfn.attachment_function_id = fndcatusg.attachment_function_id
AND fndcatusg.category_id = fnddoc.category_id
AND fnddoc.document_id = fndattdoc.document_id
AND fndattfn.function_name = 'WSHFSTRX'
AND fndattdoc.entity_name = 'WSH_NEW_DELIVERIES'
AND fl.CATEGORY_ID = fnddoc.category_id
AND fl.LANGUAGE = 'US'
AND fl.USER_NAME ='Delivery Failure'
AND fndattdoc.pk1_value =DLVRY.DELIVERY_ID
AND FNDDOC.DESCRIPTION NOT IN ('Corrected Actual Delivery Date','Corrected Promised Date')
AND fnddoc.media_id=st.media_id
GROUP BY fndattdoc.pk1_value
SELECT
MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL)) as CORRECTD_ACTUAL_DELIVERY_DATE,
MAX(fndattdoc.LAST_UPDATE_DATE ) as LAST_UPDATE_DATE
FROM fnd_attachment_functions fndattfn,
fnd_doc_category_usages fndcatusg,
fnd_documents_vl fnddoc,
fnd_attached_documents fndattdoc,
fnd_documents_short_text st,
fnd_document_categories_tl fl,
WSH_NEW_DELIVERIES DLVRY
WHERE fndattfn.attachment_function_id = fndcatusg.attachment_function_id
AND fndcatusg.category_id = fnddoc.category_id
AND fnddoc.document_id = fndattdoc.document_id
AND fndattfn.function_name = 'WSHFSTRX'
AND fndattdoc.entity_name = 'WSH_NEW_DELIVERIES'
AND fl.CATEGORY_ID = fnddoc.category_id
AND fl.LANGUAGE = 'US'
AND fl.USER_NAME ='Delivery Failure'
AND fndattdoc.pk1_value =DLVRY.DELIVERY_ID
AND FNDDOC.DESCRIPTION = 'Corrected Actual Delivery Date'
AND fnddoc.media_id=st.media_id
GROUP BY fndattdoc.pk1_value
SELECT
MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL) ) AS CORRECTD_PROMISE_DATE,
MAX(fndattdoc.LAST_UPDATE_DATE ) as LAST_UPDATE_DATE
FROM fnd_attachment_functions fndattfn,
fnd_doc_category_usages fndcatusg,
fnd_documents_vl fnddoc,
fnd_attached_documents fndattdoc,
fnd_documents_short_text st,
fnd_document_categories_tl fl,
WSH_NEW_DELIVERIES DLVRY
WHERE fndattfn.attachment_function_id = fndcatusg.attachment_function_id
AND fndcatusg.category_id = fnddoc.category_id
AND fnddoc.document_id = fndattdoc.document_id
AND fndattfn.function_name = 'WSHFSTRX'
AND fndattdoc.entity_name = 'WSH_NEW_DELIVERIES'
AND fl.CATEGORY_ID = fnddoc.category_id
AND fl.LANGUAGE = 'US'
AND fl.USER_NAME ='Delivery Failure'
AND fndattdoc.pk1_value =DLVRY.DELIVERY_ID
AND FNDDOC.DESCRIPTION = 'Corrected Promised Date'
AND fnddoc.media_id=st.media_id
GROUP BY fndattdoc.pk1_value
Hi I have above three select stetements, I have to merge those select statements into one select statements, all select statements having same conditions and filter conditions(in each select statement one filter condition different), I highlighted in the bold differencet filter conditions in each table, fianlly I should be get 7 coloumns like
LAST_UPDATE_DATE, COMMENTS, REASON, CORRECTD_ACTUAL_DELIVERY_DATE, LAST_UPDATE_DATE, CORRECTD_PROMISE_DATE, LAST_UPDATE_DATE
Please help on this
Thanks
Venki

The Best Answer

Advertisement
Use CASE?
Possibly something like this:
SELECT
MAX(CASE WHEN FNDDOC.DESCRIPTION NOT IN ('Corrected Actual Delivery Date','Corrected Promised Date')
         THEN fndattdoc.LAST_UPDATE_DATE
    END) as LAST_UPDATE_DATE,
MAX(CASE WHEN FNDDOC.DESCRIPTION NOT IN ('Corrected Actual Delivery Date','Corrected Promised Date')
         THEN DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL)
    END) as COMMENTS,
MAX(CASE WHEN FNDDOC.DESCRIPTION NOT IN ('Corrected Actual Delivery Date','Corrected Promised Date')
         THEN fnddoc.description
    END) as REASON,
MAX(CASE WHEN FNDDOC.DESCRIPTION = 'Corrected Actual Delivery Date'
         THEN DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL)
    END) as CORRECTD_ACTUAL_DELIVERY_DATE,
MAX(CASE WHEN FNDDOC.DESCRIPTION = 'Corrected Actual Delivery Date'
         THEN fndattdoc.LAST_UPDATE_DATE
    END) as LAST_UPDATE_DATE,
MAX(CASE WHEN FNDDOC.DESCRIPTION = 'Corrected Promised Date'
         THEN DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL)
    END) AS CORRECTD_PROMISE_DATE,
MAX(CASE WHEN FNDDOC.DESCRIPTION = 'Corrected Promised Date'
         THEN fndattdoc.LAST_UPDATE_DATE
    END) as LAST_UPDATE_DATE
FROM fnd_attachment_functions fndattfn,
fnd_doc_category_usages fndcatusg,
fnd_documents_vl fnddoc,
fnd_attached_documents fndattdoc,
fnd_documents_short_text st,
fnd_document_categories_tl fl,
WSH_NEW_DELIVERIES DLVRY
WHERE fndattfn.attachment_function_id = fndcatusg.attachment_function_id
AND fndcatusg.category_id = fnddoc.category_id
AND fnddoc.document_id = fndattdoc.document_id
AND fndattfn.function_name = 'WSHFSTRX'
AND fndattdoc.entity_name = 'WSH_NEW_DELIVERIES'
AND fl.CATEGORY_ID = fnddoc.category_id
AND fl.LANGUAGE = 'US'
AND fl.USER_NAME ='Delivery Failure'
AND fndattdoc.pk1_value =DLVRY.DELIVERY_ID
AND fnddoc.media_id=st.media_id
GROUP BY fndattdoc.pk1_value
  • Merge two query results Update:11-30

    Is it possible to merge 2 query results? I have a function that returns a querey result. < CFSET temp = query1> <CFSET FNCTransfers = temp> Now I want to change the query to return a merged query result < CFSET temp = query1> <CFSET t

  • Automatic Refresh of a Merged Power Query giving 'Table Not Registered' error Update:11-30

    Scenario: I have 5 tables in my Azure VM SQL database associated with my ERP system. I have loaded all 5 tables into a 'Build Excel' workbook, merged the tables together and created a dataset that my users will be using.  From that 'Build Excel' work

  • How to merg these query Update:11-30

    I have these 2 queries I am creating a summaries report Please help me how to merg these 2 queries? Q#1 : select f.ppno, f.subpp, sum(s.quantity) DQTY from sub_fabric_delivered s, fabric_delivered f where s.fdno = f.fdno group by f.ppno, f.subpp Q#2:

  • Need to merge the query's Update:11-30

    Hi, Ii need to merge the below 2 queries.could you please help some one. primary foreign key relation of major_id column major.major_id primary key. major_inactive_list.major_id referential integrity SELECT   m.major_inactive_id, m.institution_id, m.

  • Merge the Query Update:11-30

    SELECT MAX(fndattdoc.LAST_UPDATE_DATE ) as LAST_UPDATE_DATE, MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL)) as COMMENTS, MAX(fnddoc.description) as REASON FROM fnd_attachment_functions fndattfn, fnd_doc_category_usages fndc

  • How to improve the query performance or tune query from Explain Plan Update:10-11

    Hi The following is my explain plan for sql query. (The plan is generated by Toad v9.7). How to fix the query? SELECT STATEMENT ALL_ROWSCost: 4,160 Bytes: 25,296 Cardinality: 204                                               8 NESTED LOOPS Cost: 3 By

  • [oracle 10.2.0.4] My view is not merged Update:10-11

    Hi, I have a view which is not merged by the CBO. I mean the CBO decides to apply the filter predicate after the execution of the view. Here is the definition of the view CREATE OR REPLACE VIEW VUNSCP AS SELECT X.DASFM,X.COINT,X.NUCPT,X.RGCOD,X.RGCID

  • How to Merge Report of ManagedBy Attribute with Collection Membership Update:10-11

    I would like to merge the query below: SELECT   v_R_System.Name0   ,v_R_System.managedBy0   ,v_R_User.Full_User_Name0   ,v_R_User.Distinguished_Name0 FROM   v_R_System   INNER JOIN v_R_User     ON v_R_System.managedBy0 = v_R_User.Distinguished_Name0

  • SQL Server 2014 - ColumnStore index Clustered with Merge Join Update:10-11

    Hi All, I would like to know in SQL Server 2014, the new feature "Clustered Columnstore". I had inserted 9 millions of records in to a clustered columnstore table. Then i do a merge join query to insert and update to another table. The Merge joi

  • Select Query for Report Update:11-30

    Hi All I have a table called Agent. Each Agent processes requests. So accordingly Request is another table. Now the Agent can process the request successfully, fail or it could be in progress. So accordingly I have a status column in the Request tabl