Hi Helen,

> Move the second WHERE clause up to the sub-select where it belongs:

it does not belong to the subselect. The subselect is like a lookup, and I'=
ve to leave out those rows of the main query that have this "lookup field" w=
ith a certain value.
The way you suggested brings me back all the main dataset, with some 'RIP' =
column to null if no match is found.
Daniel Rail suggested a working solution, but I could do a join too. My pro=
blem stills: why can't I filter the main query upon such a field (RIP)? Is i=
t a firebird/IB bug?
Thanks
Marco Menardi

--- In ib-support@y..., Helen Borrie <helebor@t...> wrote:
> At 02:04 PM 16-04-02 +0000, you wrote:
> >Hi, I've a query like this (FB 1.0):
> >SELECT PIANO_CONTI.ESERCIZIO_ID,
> > PIANO_CONTI.CONDOMINIO_ID,
> > PIANO_CONTI.CONTO_ID,
> > PIANO_CONTI.DESCRIZIONEDISP,
> > (SELECT RIPARTITO FROM PIANO_CONTI SSPC WHERE
> > (SSPC.ESERCIZIO_ID=OPC.ESERCIZIO_ID) ) RIP
> >FROM PIANO_CONTI OPC
> >WHERE RIP='S'
> >
> >and I get the error "Column does not belong to referenced table" referr=
ed
> >to the "RIP='S'" part.
> >How can I refer to that field? It shown as "RIP" if I run the query, but=

> >adding the Where clause I get that error.
>
> Move the second WHERE clause up to the sub-select where it belongs:
>
> SELECT PIANO_CONTI.ESERCIZIO_ID,
> PIANO_CONTI.CONDOMINIO_ID,
> PIANO_CONTI.CONTO_ID,
> PIANO_CONTI.DESCRIZIONEDISP,
> (SELECT RIPARTITO FROM PIANO_CONTI SSPC WHERE
> SSPC.ESERCIZIO_ID=OPC.ESERCIZIO_ID
> and SSPC.RIPARTITO = 'S' ) as RIP
> FROM PIANO_CONTI OPC
>
> cheers,
> H.
>
> All for Open and Open for All
> Firebird Open SQL Database · http://firebirdsql.org ·
> http://users.tpg.com.au/helebor/
> _______________________________________________________