Re: How to write this sql urgent...

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 25 Jan 2010 11:59:25 -0800 (PST)
Message-ID: <1ae39dba-ce7c-4449-bcc3-d79abb751870_at_p24g2000yqm.googlegroups.com>

On Jan 25, 12:34 pm, JIACHENG SUN <sun..._at_googlemail.com> wrote:

> right..
>
> create table AA
> (
>   C_1 VARCHAR2(50) not null,
>   C_2 VARCHAR2(4000) not null,
>   C_3 VARCHAR2(4000),
>   C_4 VARCHAR2(4000)
> );
>
> insert into table aa values (1, code1, null, null);
> insert into table aa values (1, code2, null, null);
> insert into table aa values (2, code3, null, null);
> insert into table aa values (2, code5, null, null);
>
> create table AAA
> (
>   C_1 VARCHAR2(50) not null,
>   C_2 VARCHAR2(4000) not null,
>   C_3 VARCHAR2(4000),
>   C_4 VARCHAR2(4000)
> );
>
> insert into table aa values (code1, 5.5, null, null);
> insert into table aa values (code2, 3.5, null, null);
> insert into table aa values (code3, 0.00, null, null);
> insert into table aa values (code4, 0.00, null, null);
> insert into table aa values (code5, 0.00, null, null);

I had already created some test data this mroning but did not have time to work on a solution. How about this (it is not real pretty but it seems to work)

> _at_t19
> set echo on
>
> drop table t1;

Table dropped.

> drop table t2;

Table dropped.

>
> create table t1 ( ref number, code varchar2(6) );

Table created.

>
> insert into t1 values (1,'code_1');

1 row created.

> insert into t1 values (1,'code_2');

1 row created.

> insert into t1 values (2,'code_3');

1 row created.

> insert into t1 values (2,'code_5');

1 row created.

>
> create table t2 ( code varchar2(6), descr varchar2(12), value
number);

Table created.

>
> insert into t2 values ('code_1','description1',3.4);

1 row created.

> insert into t2 values ('code_2','description1',3.5);

1 row created.

> insert into t2 values ('code_3','description1',0);

1 row created.

> insert into t2 values ('code_4','description1',0);

1 row created.

> insert into t2 values ('code_5','description1',0);

1 row created.

>
> select *

  2 from (select r1.ref, c1.code

  3         from   (select distinct ref from t1)  r1,
  4                (select distinct code from t2) c1
  5        ) v1
  6        ,t2

  7 where v1.code = t2.code
  8 order by v1.ref
  9 /

       REF CODE CODE DESCR VALUE ---------- ------ ------ ------------ ----------

         1 code_1 code_1 description1        3.4
         1 code_2 code_2 description1        3.5
         1 code_4 code_4 description1          0
         1 code_5 code_5 description1          0
         1 code_3 code_3 description1          0
         2 code_1 code_1 description1        3.4
         2 code_2 code_2 description1        3.5
         2 code_4 code_4 description1          0
         2 code_5 code_5 description1          0
         2 code_3 code_3 description1          0

10 rows selected.

> spool off

HTH -- Mark D Powell -- Received on Mon Jan 25 2010 - 13:59:25 CST