Re: SQL*PLUS select statment

On Jun 7, 4:22 pm, sunadmn <suna...@xxxxxxxxx> wrote:
On Jun 6, 7:20 am, sybrandb <sybra...@xxxxxxxxx> wrote:





On Jun 5, 11:32 pm,sunadmn<suna...@xxxxxxxxx> wrote:

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,ONLINE­­FX.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

a) 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

a) 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)
b) 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

Ok here is the issue I have right now.

In the above select (first one) it appears to me that this is looking
at particulars with regards to the return data (numeric data) i.e.:

SELECT 6577 "COUNT",
6146 GOOD_COUNT,
91 FAIR_COUNT,
340 UNACCEPTABLE_COUNT,

To me means select 6577,6146,91,340 from the corresponding. Because
this is dynamic data that changes every day/hour/minute these values
can't be hard coded. I did try to fool around (and I do mean "Fool")
with the query and modify it to the way I need it and I could not get
dynamic data back with that said how could I turn this into a dynamic
query that just pulls whatever data is in the data source to get a
dynamic set? Am I making any sense here? Am I just overlooking the
obvious?

Thanks,
-Steve- Hide quoted text -

- Show quoted text -

You could apply this approach to *any* (and I really mean *any*)
resultset.
The only thing you need to know upfront is the number of columns, as
that will determine your (second) select from dual and your two decode
statements. Just take 1 record from dept and show it to yourself:
your select deptno, dept, location from dept where dept=10 goes in the
WITH part
your dual query would look like
select rownum rn
from dual
connect by level <= 3
and your decodes would look like ...

You can do it, just try!!!

--
Sybrand Bakker
Senior Oracle DBA

.

Relevant Pages

  • Re: SQL*PLUS select statment
    ... single column; Here is an example: ... Another bad habit is to store dates as character strings (rely as well ... with the query and modify it to the way I need it and I could not get ... dynamic data back with that said how could I turn this into a dynamic ...
    (comp.databases.oracle.misc)
  • Re: SQL*PLUS select statment
    ... single column; Here is an example: ... Another bad habit is to store dates as character strings (rely as well ... with the query and modify it to the way I need it and I could not get ... dynamic data back with that said how could I turn this into a dynamic ...
    (comp.databases.oracle.misc)
  • Re: SQL*PLUS select statment
    ... single column; Here is an example: ... oracle documentation or in v$reserved_words. ... The two decodes 'join' the RN from query b) to query a), ... dynamic data back with that said how could I turn this into a dynamic ...
    (comp.databases.oracle.misc)
  • Re: SQL*PLUS select statment
    ... single column; Here is an example: ... (SELECT ROWNUM RN FROM DUAL CONNECT BY ... oracle documentation or in v$reserved_words. ... The two decodes 'join' the RN from query b) to query a), ...
    (comp.databases.oracle.misc)
  • Re: EDI export
    ... To put each column in one line you'll have to use VBA & a recordset. ... > export a text file from a query or table. ... > It has to all be in a single column. ...
    (microsoft.public.access.queries)