Home » SQL & PL/SQL » SQL & PL/SQL » Filtering result from 2 PL/SQL UNION queries (PL/SQL 9.0)
Filtering result from 2 PL/SQL UNION queries [message #679893] Thu, 09 April 2020 14:49 Go to next message
gbuck
Messages: 1
Registered: April 2020
Junior Member
Hello,

I would like to filter the result of these 2 queries, connected by UNION. I think of adding a SELECT statement to filter the latest in terms of date, ID and name. The issue is that these 2 queries produce a duplicate after using UNION, which I have to avoid. Only need one latest date, name and ID.

(select  authorize_id,
purchase_authorizer_api.Get_Name(authorize_id) as "Authorizer Name",  
max(date_approved) as "Last Date Approved", 'PR' as "Approval Type"
from PURCH_REQ_LINE_APPROVAL
group by authorize_id )

union 

( select  authorize_id,
purchase_authorizer_api.get_name(authorize_id) as "Authorizer Name",
 max(date_approved) as "Last Date Approved", 'PO' as "Approval Type"
from PURCHASE_ORDER_APPROVAL
group by authorize_id ) 
Here is some sample data from the 2 queries:

AUTHORIZE_ID Authorizer Name Last Date Approved Approval Type
AANDR Alfredo A 2012-10-16 0:00 PR
AANDR Alfredo A 2016-09-06 0:00 PO
AESFA Arash E 2017-05-26 0:00 PO
AHAM Ahmed Ha 2019-12-04 0:00 PO
AJAH Al Jah 2012-11-02 0:00 PR
AJAH Al Jah 2013-10-29 0:00 PO


I would like to get only the latest date from these, so it will avoid duplicates.
Any help would be greatly appreciated. Thanks.
Re: Filtering result from 2 PL/SQL UNION queries [message #679894 is a reply to message #679893] Thu, 09 April 2020 15:45 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


Your query could be something like:
select a.authorize_id,
       decode(sign(a.date_approved-b.date_approved), 1, a.date_approved, b.date_approved) as "Last Date Approved",
       decode(sign(a.date_approved-b.date_approved), 1, a."Approval Type", b."Approval Type") "Approval Type",
       purchase_authorizer_api.get_name(a.authorize_id) as "Authorizer Name",
from (select authorize_id,
             max(date_approved) date_approved, 
             'PR' as "Approval Type"
      from PURCH_REQ_LINE_APPROVAL
      group by authorize_id) a,
     (select authorize_id,
             max(date_approved) date_approved, 
             'PO' as "Approval Type"
      from PURCHASE_ORDER_APPROVAL
      group by authorize_id b)
where a.authorize_id = b.authorize_id
/
If a authorize_id may not be in both tables then you have to use a FULL OUTER JOIN instead of an inner join and use NVL in the SELECT clause.
You have to determine which row you want to take if the dates are equal.

[Updated on: Thu, 09 April 2020 15:51]

Report message to a moderator

Previous Topic: Subquery returns more than one row - where clause -decode
Next Topic: create materialized view log with explicit constraint (merged)
Goto Forum:
  


Current Time: Fri Mar 29 04:03:09 CDT 2024