Re: Complex SQL Qry?

From: Maciej Zoltowski <mzoltowski_at_eragsm.com.pl>
Date: 2000/05/12
Message-ID: <391BB31F.D4A5AEC2@eragsm.com.pl>#1/1

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 Received on Fri May 12 2000 - 00:00:00 CDT