Re: using kind of alias

From: Dan Blum <tool_at_panix.com>
Date: Thu, 12 Jun 2008 21:53:47 +0000 (UTC)
Message-ID: <g2s5ta$jo8$1_at_reader2.panix.com>


Shakespeare <whatsin_at_xs4all.nl> wrote:

> "Ronald" <rmoleveld_at_gmail.com> schreef in bericht
> news:22044960-3e83-4a47-8a40-3358c763cc5e_at_t54g2000hsg.googlegroups.com...
> > Can someone please help me on the following.

<snip>

> > select decode((select count(*)
> > from table1 t
> > where t.item = u.item
> > and t.fam1 <> u.fam1),0,t.fam1,1111) F1,
> > decode((select count(*)
> > from table1 t
> > where t.item = u.item
> > and t.fam1 <> u.fam1),0,t.fam2,2222) F2,
> > decode((select count(*)
> > from table1 t
> > where t.item = u.item
> > and t.fam1 <> u.fam1),0,t.fam3,3333) F3,
> > decode((select count(*)
> > from table1 t
> > where t.item = u.item
> > and t.fam1 <> u.fam1),0,t.fam4,4444) F4
> > from table1 u

> By redesign. This is horrible.

> Shakespeare

I'm inclined to agree with Shakespeare. However, if you MUST use this bizarre data model, there are a number of ways to rewrite your query. Here is a simple one:

select decode(cnt, 0, fam1, 1111) f1, ...   from (select u.item, u.fam1, u.fam2, u.fam3, u.fam4,

               (select count(*)
                  from table1 t
                 where t.item = u.item
                   and t.fam1 != u.fam1) cnt
          from table1 u);

There is probably a more efficient way to do it, but I am disinclined to spend more time thinking about it.

-- 
_______________________________________________________________________
Dan Blum					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."
Received on Thu Jun 12 2008 - 23:53:47 CEST

Original text of this message