Re: How to arrange this query ???

etantonio_at_libero.it wrote:
> > 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.

> 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:
>

You are close. Change the == to = then move this requirement into a HAVING clause, which is applied after the grouping takes place.

Back to my original example - the full, modified version with HAVING: 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
HAVING
  SIGN(COUNT(N.PART_ID))=1; If you decide to use this as criteria, I suggest making a change to the SQL statement. With this HAVING clause restriction, you might as well remove the outer join. The SQL statement then becomes: SELECT
  P.ID,
  P.DESCRIPTION
FROM
  PART P,
  NCMR N
WHERE
  P.ID=N.PART_ID
GROUP BY
   P.ID,
   P.DESCRIPTION; This can be further shortened as follows: SELECT DISTINCT
  P.ID,
  P.DESCRIPTION
FROM
  PART P,
  NCMR N
WHERE
  P.ID=N.PART_ID; Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Aug 01 2006 - 18:18:09 CDT