Re: SQL*PLUS select statment

From: sybrandb <sybrandb_at_gmail.com>
Date: Wed, 06 Jun 2007 04:20:55 -0700
Message-ID: <1181128855.896427.28680@p47g2000hsd.googlegroups.com>

On Jun 5, 11:32 pm, sunadmn <suna..._at_gmail.com> wrote:
> On May 30, 3:41 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>
>
>
>
>
> > sunadmnschrieb:
>
> > > Afternoon all I have a simple query that I am running to get a dataset
> > > for BIRT. The query runs fine but I need to manipulate the data into a
> > > single column; Here is an example:
>
> > > SQL> select
> > > ONLINEFX.EVERYDAY_SUMMARY.COUNT,ONLINEFX.EVERYDAY_SUMMARY.GOOD_COUNT,ONLINEFX.EVERYDAY_
> > > SUMMARY.FAIR_COUNT,ONLINEFX.EVERYDAY_SUMMARY.UNACCEPTABLE_COUNT
> > > 2 from ONLINEFX.EVERYDAY_SUMMARY
> > > 3 where ONLINEFX.EVERYDAY_SUMMARY.PERIOD = to_char(sysdate-1, 'YYYY-
> > > MM-DD');
>
> > > COUNT GOOD_COUNT FAIR_COUNT UNACCEPTABLE_COUNT
> > > ---------- ---------- ----------
> > > ------------------
> > > 6577 6146 91 340
>
> > > I want to have the data returned in the format of multiple rows and a
> > > single column like this:
>
> > > COUNT 6577
> > > GOOD_COUNT 6146
> > > FAIR_COUNT 91
> > > UNACCEPTABLE_COUNT 340
>
> > > I know this has to be possible, but I lack the SQL skill to get to
> > > where I need to be if anyone could give me some pointers or point me
> > > to a good ref doc that would be great.
>
> > > Cheers,
> > > -Steve
>
> > SQL> WITH EVERYDAY_SUMMARY AS(
> > 2 SELECT 6577 "COUNT",
> > 3 6146 GOOD_COUNT,
> > 4 91 FAIR_COUNT,
> > 5 340 UNACCEPTABLE_COUNT,
> > 6 TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') PERIOD
> > 7 FROM DUAL)
> > 8 SELECT DECODE(RN,
> > 9 1,
> > 10 'COUNT',
> > 11 2,
> > 12 'GOOD_COUNT',
> > 13 3,
> > 14 'FAIR_COUNT',
> > 15 4,
> > 16 'UNACCEPTABLE_COUNT') count_type,
> > 17 DECODE(RN,
> > 18 1,
> > 19 "COUNT",
> > 20 2,
> > 21 GOOD_COUNT,
> > 22 3,
> > 23 FAIR_COUNT,
> > 24 4,
> > 25 UNACCEPTABLE_COUNT) count_value
> > 26 FROM EVERYDAY_SUMMARY, (SELECT ROWNUM RN FROM DUAL CONNECT BY
> > LEVEL <= 4)
> > 27 WHERE PERIOD = TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD')
> > 28 /
>
> > COUNT_TYPE COUNT_VALUE
> > ------------------ -----------
> > COUNT 6577
> > GOOD_COUNT 6146
> > FAIR_COUNT 91
> > UNACCEPTABLE_COUNT 340
>
> > As you are new to sql, you may be unaware, that COUNT is the widely used
> > aggregation/analytical function in oracle, so it is highly unadvisable
> > (despite my bad example) to use it as identifier or column name. This
> > relies as well to all other reserved words, which can be looked up in
> > oracle documentation or in v$reserved_words.
> > Another bad habit is to store dates as character strings ( rely as well
> > to numbers as stored as character strings).
>
> > Best regards
>
> > Maxim
>
> I guess I am really missing the idea here. Would anyone mind taking
> the time to walk me through what each step is doing here. I know this
> is asking a lot but I am really lost in all the statements and a
> overview of this would help me learn greatly.
>
> Thank you greatly,
> -Steve- Hide quoted text -
>
> - Show quoted text -

Ok, for once, just because this is new in 9i and higher, and I also never had to use it.

You basically have two selects

WITH EVERYDAY_SUMMARY AS( SELECT 6577 "COUNT", 6146 GOOD_COUNT, 91 FAIR_COUNT, 340 UNACCEPTABLE_COUNT, TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') PERIOD FROM DUAL)

and
b)
select rownum rn from dual connect by level <= 4

results in one record. The WITH construct is new in 9i, to prevent users to execute subqueries more than once. (You know Michelle from 'Allo 'Allo? She would have loved this)
results in four records. Why, I would have to verify in the documentation.

This is the next step
c)
WITH EVERYDAY_SUMMARY AS(
SELECT 6577 "COUNT",

       6146 GOOD_COUNT,
       91 FAIR_COUNT,
     340 UNACCEPTABLE_COUNT,
      TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') PERIOD

FROM DUAL)
select * from everyday_summary,
( select rownum rn from dual connect by level <= 4 )

Actually this in an ordinary cartesian product between the two queries!

d) The two decodes 'join' the RN from query b) to query a), so everything is printed only once.

It is even more dirty than putting a copy of the 'Fallen Madonna with the Big Boobies' in a knockwurst in your trousers!!!

Hth

--
Sybrand Bakker
Senior Oracle DBA

Received on Wed Jun 06 2007 - 06:20:55 CDT