Re: SQL*PLUS select statment
- From: sunadmn <sunadmn@xxxxxxxxx>
- Date: Tue, 05 Jun 2007 14:32:05 -0700
On May 30, 3:41 pm, Maxim Demenko <mdeme...@xxxxxxxxx> 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
.
- Follow-Ups:
- Re: SQL*PLUS select statment
- From: sybrandb
- Re: SQL*PLUS select statment
- Prev by Date: Re: database
- Next by Date: tree: start with parent but don't include parent in results
- Previous by thread: Pre approved labor available with us
- Next by thread: Re: SQL*PLUS select statment
- Index(es):
Relevant Pages
|
