Re: How to arrange this query ???

Ciao,
the query that works for me is the following :

SELECT
   SIGN(COUNT(MTA.key1)) as "HiddenDestinoAssociato" ,    MDA.COD_ABACUS "Cod. <br> Destino" ,    MDA.Servizio "Servizio"

from

   MAPLVANAG_DESTINI_ALL MDA , mapltelem_anag MTA

where

    MDA.COD_abacus = MTA.key1(+)
and MDA.SERVIZIO = MTA.key2(+)

GROUP BY MDA.COD_abacus, MDA.SERVIZIO

now I need to add another restriction regarding HiddenDestinoAssociato that have to be only 1 so I insert the following line in the where

                 and SIGN(COUNT(MTA.key1)) == 1

but Oracle signals to me an error, what happens ???

Thanks

Antonio D'Ottavio
www.etantonio.it/en

etantonio_at_libero.it ha scritto:

> Many thanks,
> I'll try and make you know as soon as possible.
> Thanks
>
> Antonio D'Ottavio
> www.etantonio.it/en
>
>
>
> Charles Hooper ha scritto:
>
> > etantonio_at_libero.it wrote:
> > > The tables in the database are the following :
> > >
> > > T1
> > > cod_dest
> > > descr_dest
> > >
> > >
> > > T2
> > > cod_dest
> > >
> > >
> > > in my select I need 3 columns
> > > T1.COD-dest , T1.DESCR-dest , 0 or 1 depending if T1.cod_dest is also
> > > present in T2.cod_dest.
> > >
> > > Can you help me to arrange such query ???
> > > many thanks
> > >
> > >
> > > Antonio D'Ottavio
> > > www.etantonio.it/en
> >
> > Sybrand Bakker's outer join suggestion is just what is needed.
> >
> > Here is an example. Assume that you have two tables PART (your table
> > T1) and NCMR (your table T2) - which could have 0, 1, 2, or more than
> > 100 entries for each row in the PART table. PART.ID (your T1.COD_DEST
> > column) is set up in an outer join with NCMR.PART_ID (your T2.COD_DEST
> > column), and PART.DESCRIPTION (your T1.DESCR_DEST) is also included.
> > Because there could be 0 or more than 100 matches in the NCMR (your T2)
> > table, it is necessary to group by the other columns being retrieved
> > and to retrieve the COUNT of the matching rows from the NCMR (your T2)
> > table. The COUNT is transformed into 0 or 1 with the SIGN function (-1
> > if negative, 0 if 0, 1 if positive).
> >
> > SELECT
> > P.ID,
> > P.DESCRIPTION,
> > SIGN(COUNT(N.PART_ID)) IS_PRESENT
> > FROM
> > PART P,
> > NCMR N
> > WHERE
> > P.ID=N.PART_ID(+)
> > GROUP BY
> > P.ID,
> > P.DESCRIPTION;
> >
> > Use the above example to work out your SELECT statement.
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
Received on Tue Aug 01 2006 - 09:49:59 CDT