Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Difficult Query: is this possible in SQL?

Re: Difficult Query: is this possible in SQL?

From: Aaron W. West <tallpeak_at_hotmail.NO.SPAM>
Date: Mon, 5 Jul 2004 15:59:30 -0700
Message-ID: <TOGdnVYREfTFQHTdRVn-tw@speakeasy.net>


There's a technique of taking a max of two values concatenated then taking a substring, which avoids the join needed in the below techniques.

Try this:

select color = substring(max(cast(level as char(1))+color),2,10) ,length = cast(substring(max(cast(level as char(1))+cast(length as char(9))),2,9) as integer)
,width = cast(substring(max(cast(level as char(1))+cast(width as char(9))),2,9) as integer)
,hgt = cast(substring(max(cast(level as char(1))+cast(hgt as char(9))),2,9) as integer)
from Foobar

-aaron

"Richard" <rromley_at_optonline.net> wrote in message news:bfbb57f7.0407050932.4cc43ea7_at_posting.google.com... Hi Robert,

Here are 2 more solutions...

create table Foobar
(

  level   int          NOT NULL , -- pk
  color   varchar(10)      NULL ,
  length  int              NULL ,
  width   int              NULL ,
  hgt     int              NULL ,

  primary key clustered
  ( level )
)

insert Foobar

     ( level, color, length, width, hgt )

select 1,'RED',8,10,12 UNION ALL
select 2,NULL,NULL,NULL,20 UNION ALL
select 3,NULL,9,82,25 UNION ALL
select 4,'BLUE',NULL,67,NULL UNION ALL
select 5,'GRAY',NULL,NULL,NULL



select
(select color from Foobar where level =

       (select max(level) from Foobar where color is not null)) as color ,
(select length from Foobar where level =

       (select max(level) from Foobar where length is not null)) as length ,
(select width from Foobar where level =

       (select max(level) from Foobar where width is not null)) as width ,
(select hgt from Foobar where level =

       (select max(level) from Foobar where hgt is not null)) as hgt

select max(case when f.level = t.col then f.color  end) as color  ,
       max(case when f.level = t.lth then f.length end) as length ,
       max(case when f.level = t.wth then f.width  end) as width  ,
       max(case when f.level = t.hgt then f.hgt    end) as hgt
  from (select max(case when color  IS NOT NULL then level end),
               max(case when length IS NOT NULL then level end),
               max(case when width  IS NOT NULL then level end),
               max(case when hgt    IS NOT NULL then level end)
          from Foobar) as t(col,lth,wth,hgt), Foobar as f


Richard

robertbrown1971_at_yahoo.com (Robert Brown) wrote in message news:<240a4d09.0407021517.1ee87cdb_at_posting.google.com>...

> suppose I have the following table:
>
> CREATE TABLE (int level, color varchar, length int, width int, height
> int)
>
> It has the following rows
>
> 1, "RED",  8,    10,   12
> 2, NULL,   NULL, NULL, 20
> 3, NULL,   9,    82,   25
> 4, "BLUE", NULL, 67,   NULL
> 5, "GRAY", NULL  NULL, NULL
>
> I want to write a query that will return me a view collapsed from
> "bottom-to-top" in order of level (level 1 is top, level 5 is bottom)
>
> So I want a query that will return
>
> GRAY, 9, 67, 25
>
> The principle is that looking from the bottom level up in each column
> we first see GRAY for color, 9 for length, 67 for width, 25 for
> height. In other words, any non-NULL row in a lower level overrides
> the value set at a higher level.
>
> Is this possible in SQL without using stored procedures?
>
>
>
> Thanks!
>
>
> - Robert


robertbrown1971_at_yahoo.com (Robert Brown) wrote in message news:<240a4d09.0407021517.1ee87cdb_at_posting.google.com>...

> suppose I have the following table:
>
> CREATE TABLE (int level, color varchar, length int, width int, height
> int)
>
> It has the following rows
>
> 1, "RED",  8,    10,   12
> 2, NULL,   NULL, NULL, 20
> 3, NULL,   9,    82,   25
> 4, "BLUE", NULL, 67,   NULL
> 5, "GRAY", NULL  NULL, NULL
>
> I want to write a query that will return me a view collapsed from
> "bottom-to-top" in order of level (level 1 is top, level 5 is bottom)
>
> So I want a query that will return
>
> GRAY, 9, 67, 25
>
> The principle is that looking from the bottom level up in each column
> we first see GRAY for color, 9 for length, 67 for width, 25 for
> height. In other words, any non-NULL row in a lower level overrides
> the value set at a higher level.
>
> Is this possible in SQL without using stored procedures?
>
>
>
> Thanks!
>
>
> - Robert
Received on Mon Jul 05 2004 - 17:59:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US