🚀 go-pugleaf

----- Original Message ----- 
From: "Wayne Niddery (TeamB)" <wniddery@chaffaci.on.ca>
Newsgroups: borland.public.interbase.sql
Sent: Wednesday, April 23, 2008 9:29 PM
Subject: Re: Join - understand strange results


> That definitely is a puzzle, it looks like the data must be right, so 
> possibly there's a bug somewhere.

select distinct  a.abinp, a.fil , b.abinp, b.fil, b.ambname, b.filname,
b.aut_input, b.aut_celur
from giocoge a
left join  ABFL_USRSOCINP_UNO (:UTENTE, a.csoc, a.nreserc, a.abinp, a.fil) b
on (b.abinp = a.abinp and b.fil = a.fil )

Returns right data only for record that has  a.abinp = '' and a.fil = ''.
 for other records that have value in these fields procedure return nothing.

if I add     where not (a.abinp = '' and a.fil = '')
same result.

>Is the stored proc properly calling SUSPEND to return a result set rather 
>than a single set of return values? (Even if it is designed to always 
>return just one record, if you are going to use it as part of a select 
>statement it must call SUSPEND as far as I know).

I modified Proc to have only one select and suspend.
It has not  a for do loop.

> What happens if you, using IBConsole or ISQL, simply select directly from 
> the stored procedure passing various parameters? Do you always get correct 
> returns?

select distinct  b.abinp, b.ambname, b.fil, b.filname, b.aut_input,
b.aut_celur
from  ABFL_USRSOCINP_UNO (:UTENTE, :csoc, :nreserc, :abinp, :fil) b

passing values that above examples don't work, in this case work fine.

test values :

abinp             fil
''                    ''                         ok!
ABIT            DETO                 ok!
ABIT            MASC                ok!

as I already said   using clause
left join  ABFL_USRSOCINP_UNO (:UTENTE, a.csoc, a.nreserc, a.abinp, a.fil) b
on (1=1)
works fine.

Adalberto Baldini