CODEWITHSUNDEEP
sqloracle-databasesubquery
Random Geo guy
Random Geo guy

Reputation: 166

Oracle multiple subqueries return all or nothing

I'm issuing a query with multiple subqueries. If one of the two subqueries fails, the query returns no rows. The only way anything comes back is if both queries succeed. Is there any way to get the results of a successful subquery if another fails? I've tried NVL in the top of the query as well as in the subqueries with no success. I know that scalar subqueries return null, but I'm needing multiple values. This is a reproducible sample query I've distilled from something much, much bigger where schema changes/UNION are not an option.(at least I don't think so)

create table testTBLA(
  product VARCHAR2(10),
  quantity NUMBER,
  code NUMBER
);

INSERT INTO testTBLA VALUES ( 'bottle', 10,3);
INSERT INTO testTBLA VALUES ( 'can', 17, 16);

create table testTBLB(
  fruit VARCHAR2(10),
  asize NUMBER,
  code NUMBER
)

INSERT INTO testTBLB VALUES ( 'melon', 3, 14);
INSERT INTO testTBLB VALUES ( 'apple', 5, 16);

Any way to get some results if others are null?

--say code inparam is 16
select fruit, asize, product, quantity  from 
(select product, quantity from testTBLA where code=16),
(select fruit, asize from testTBLB where code=16)

FRUIT      ASIZE                  PRODUCT    QUANTITY               
---------- ---------------------- ---------- ---------------------- 
apple      5                      can        17                    

--say code inparam is 3
select fruit, asize, product, quantity  from 
(select product, quantity from testTBLA where code=3),
(select fruit, asize from testTBLB where code=3)

FRUIT      ASIZE                  PRODUCT    QUANTITY               
---------- ---------------------- ---------- ---------------------- 

0 rows selected

Upvotes: 0

Views: 864

Answers (3)

PM 77-1
PM 77-1

Reputation: 13334

You question is not too clear.

See if the following approach works for you:

select fruit, asize, product, quantity  from 
(select product, quantity from testTBLA where code=3) FULL OUTER JOIN
(select fruit, asize from testTBLB where code=3) ON 1=1

Here's SQL Fiddle with your data and my code: http://sqlfiddle.com/#!4/8b70a/2

Upvotes: 0

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23747

select 
  fruit, asize, product, quantity
from 
  testTBLA
  full join testTBLB using(code)
where
  code = 16

fiddle

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231681

Assuming that either side can be missing

SQL> ed
Wrote file afiedt.buf

  1  select fruit, asize, product, quantity
  2    from testTBLA a
  3         full outer join testTBLB b on( a.code = b.code )
  4*  where coalesce(a.code,b.code) = 3
SQL> /

FRUIT           ASIZE PRODUCT      QUANTITY
---------- ---------- ---------- ----------
                      bottle             10

SQL> ed
Wrote file afiedt.buf

  1  select fruit, asize, product, quantity
  2    from testTBLA a
  3         full outer join testTBLB b on( a.code = b.code )
  4*  where coalesce(a.code,b.code) = 16
SQL> /

FRUIT           ASIZE PRODUCT      QUANTITY
---------- ---------- ---------- ----------
apple               5 can                17

Upvotes: 2

Related Questions