Re: SQL must returns 1 row

You must specify the outer join everywhere that you join to table2...

and A.num_evt between nvl(B.num_evt_first (+), A.num_evt)  and nvl(B.num_evt_end (+), A.num_evt)

Richard

Olivier Marcou wrote:
>
> Hi,
>
> I've 2 tables with the following structure
>
> TABLE1
>
> Id_cli CHAR(6)
>
> Cd_doc CHAR(3)
>
> Num_evt NUMBER
>
> TABLE2
>
> Cd_doc CHAR(3)
>
> Num_evt_first NUMBER
>
> Num_evt_end NUMBER
>
> Cd_typ CHAR(7)
>
> For each row in Table1, I can have 0 or N rows in Table2
>
> And this select
>
> Select nvl(B.cd_typ, 'NO DATA')
>
> From Table1 A,
>
> Table2 B
>
> Where A.id_cli = '&1'
>
> And B.cd_doc(+) = A.cd_doc
>
> And A.num_evt between nvl(B.num_evt_first, A.num_evt)
>
> and nvl(B.num_evt_end, A.num_evt)
>
> How can I change my select to obtain 'NO DATA' if I have the following rows
> in tables
>
> Table1
>
> Id_cli
> Cd_doc
> Num_evt
>
> 01487
> 0125
> 6
>
> Table2
>
> Cd_doc
> Num_evt_first
> Num_evt_end
> Cd_typ
>
> 0125
> 1
> 3
> TYP1
>
> 0125
> 4
> 5
> TYP2
>
> 0125
> 7
> 8
> TYP3
>
> My select returns 'no rows selected', but I want 1 row with 'NO DATA'. How
> can I do ?
> Thanks
Received on Mon Nov 12 2001 - 18:20:19 CST