Re: How to write this sql urgent...

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 25 Jan 2010 12:10:12 -0800 (PST)
Message-ID: <464f17b7-c064-4182-aca8-3be6f4f04f5c_at_k35g2000yqb.googlegroups.com>

On Jan 25, 2:59 pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -

I forgot to add v1.code to the order by, but the query should give you an idea. If the second data table does not always have data for a code then you will need to use an outer join. Proper test data is necessary to get the correct solution which is what some of the other posters were driving at.

• Mark --

Received on Mon Jan 25 2010 - 14:10:12 CST