Re: Complex SQL Qry?

Thanks,

I got it working. I missed an outer join.

Prince

In article <391BB31F.D4A5AEC2_at_eragsm.com.pl>,   Maciej Zoltowski <mzoltowski_at_eragsm.com.pl> wrote:
> prince_kumar_at_my-deja.com wrote:
> >
> > I have a table CompanyTab like the following one:
> >
> > Comp_name Prod Desc Int_code
> > --------- ----- ----- --------
> > CompanyA prod1 desc1 CAP1D1 -- 1
> > CompanyA prod1 NULL CAP1Z1 -- 2 block A
> > CompanyA prod1 desc2 CAP1D2 -- 3
> > CompanyA prod2 desc1 CAP2D1 -- 4
> >
> > CompanyB prod1 desc2 CBP1D2 -- 5
> > CompanyB prod1 desc3 CBP1D3 -- 6 block B
> > CompanyB prod1 NULL CBP1ZZ -- 7
> >
> > How do I list all the Internal codes (Int_code which is the
 combination
> > of Prod and Desc field for each comp_name) corresponding to
 CompanyB,
> > if there is a matching Prod, Desc in the CompanyA?
> > If there is no matching, I need to diplay 'n/a' .
> >
> > The result should be
> >
> > n/a - record 1 of companyA matches with nothing on companyB
> > CBP1ZZ - record 2 of companyA matches with 7 of companyB
> > CBP1D2 - record 3 of companyA matches with 5 of companyB
> > n/a - record 4 of companyA matches with nothing on companyB
> >
>
> SQL> desc tmp
> Name Null? Type
> ------------------------------- -------- ----
> COMP_NAME VARCHAR2(100)
> PROD VARCHAR2(100)
> PROD_DESC VARCHAR2(100)
> INT_CODE VARCHAR2(100)
>
> SQL> select * from tmp;
>
> COMP_NAME
> ----------------------------------------------------------------------
 --
> PROD
> ----------------------------------------------------------------------
 --
> PROD_DESC
> ----------------------------------------------------------------------
 --
> INT_CODE
> ----------------------------------------------------------------------
 --
> CompanyA
> prod1
> desc1
> CAP1D1
>
> CompanyA
> prod1
>
> CAP1Z1
>
> CompanyA
> prod1
> desc2
> CAP1D2
>
> CompanyA
> prod2
> desc1
> CAP2D1
>
> CompanyB
> prod1
> desc2
> CBP1D2
>
> CompanyB
> prod1
> desc3
> CBP1D3
>
> CompanyB
> prod1
>
> CBP1ZZ
>
> 7 rows selected.
>
> SQL> select decode(t2.int_code, null, 'n/a', t2.int_code)
> 2 from tmp t1, tmp t2
> 3 where t1.comp_name = 'CompanyA'
> 4 and t2.comp_name (+) = 'CompanyB'
> 5 and t1.prod = t2.prod (+)
> 6 and nvl(t1.prod_desc, ' ') = nvl(t2.prod_desc (+), ' ');
>
> DECODE(T2.INT_CODE,NULL,'N/A',T2.INT_CODE)
> ----------------------------------------------------------------------
 --
> CBP1ZZ
> n/a
> CBP1D2
> n/a
>
> Probably you should handle nulls in prod_desc with more care.
>
> Regards
> Maciek
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun May 14 2000 - 00:00:00 CDT