Search code examples
sqloracle-databasecasesql-order-by

PLSQL query that returns rows when first condition is met

I have this query

SELECT *
FROM test
WHERE
(test.fee = 30003
AND test.date_from <= TRUNC(to_date('08/03/2020','dd/mm/yyyy'))
AND test.date_until >= TRUNC(to_date('08/03/2020','dd/mm/yyyy')));

That return these records enter image description here

I need to modify this query to return:

In case any row has single_portfolio = 100000885, return that row only, ELSE return all the other rows. (That is what I meant (edit))

I have tried a lot of approaches and none was working, I think nested selects might be needed.

Thank you.

Solution

  • As you want to fetch two result sets depending on whether portfolio exists in table or not, then one option is to union two similar queries.

    This is SQL*Plus example so I'm using substitution variable. You'd use parameter as is in your code (maybe a bind variable - :par_portfolio, or just parameter's name if you pass it to a stored procedure - par_portfolio).

    Sample data:

    SQL> with test (fee, date_from, date_until, single_portfolio) as
  2    (select 30003, date '2020-03-05', date '2020-03-08', 1885 from dual union all
  3     select 30003, date '2020-03-05', date '2020-03-09', null from dual union all
  4     select 30003, date '2020-03-05', date '2020-03-11', null from dual
  5    )
  6  -- PAR_PORTFOLIO exists in table
  7  select *
  8  from test a
  9  where fee = 30003
 10    and date '2020-03-08' between date_from and date_until
 11    and single_portfolio = &&par_portfolio
 12    and exists (select null from test where single_portfolio = &&par_portfolio)
 13  union all
 14  -- PAR_PORTFOLIO doesn't exist in table
 15  select *
 16  from test
 17  where fee = 30003
 18    and date '2020-03-08' between date_from and date_until
 19    and (single_portfolio <> &&par_portfolio or single_portfolio is null)
 20    and not exists (select null from test where single_portfolio = &&par_portfolio);

    Test #1: existing portfolio:

    Enter value for par_portfolio: 1885

       FEE DATE_FROM DATE_UNTI SINGLE_PORTFOLIO
---------- --------- --------- ----------------
     30003 05-MAR-20 08-MAR-20             1885

    Test #2: portfolio doesn't exist so query returns all rows:

    SQL> undefine par_portfolio
SQL> /
Enter value for par_portfolio: 1111

       FEE DATE_FROM DATE_UNTI SINGLE_PORTFOLIO
---------- --------- --------- ----------------
     30003 05-MAR-20 11-MAR-20
     30003 05-MAR-20 09-MAR-20
     30003 05-MAR-20 08-MAR-20             1885

SQL>