Re: SQL*PLUS select statment

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 Received on Tue Jun 05 2007 - 16:32:05 CDT