SQL & PL/SQL

Hi SQL-gurus,

I try to build a query for two tables with 1:n relation. bericht (b_id is primary key) is 1 and ueber_produkt (b_id is foreign key) is n.

the query should return all rows from bericht in case :P15_PROD is null and all the matching rows in the other case.

so far it looks like this:

this attempt returns a good result but in case :P15_PROD is null this query returns only b_id's from bericht which are equal to b_id's in ueber_produkt, however I would like to return all b_id's from bericht. -->

select b.b_id,b.m_id,b.text,b.zeitaufwand,b.datum,b.kw_id
from bericht b
where b.b_id in (select distinct b_id from ueber_produkt where produktname in nvl(:P15_PROD, produktname))


this one returns error "ORA-01427: single-row subquery returns more than one row" -->

select b.b_id,b.m_id,b.text,b.zeitaufwand,b.datum,b.kw_id
from bericht b
where b.b_id in nvl( (select distinct b_id from ueber_produkt where produktname in :P15_PROD), b.b_id )

Why is it a single row subquery? I use IN operator fro multiple row subqueries!


I hope I explained it well.

Any help would be appreciated