Re: SQL*PLUS select statment

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 06 Jun 2007 19:16:54 +0200
Message-ID: <4666EC06.9030001@gmail.com>

sybrandb schrieb:
> 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,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
>
>

Sybrand gave you already very nice explanation, just some remarks: WITH part has nothing to do with your requirement, it is just the handy way to represent the small set of test data without to create a table for it. One could equally well create a table EVERYDAY_SUMMARY, insert a sample row into it and build the query based on this table. But while you can reference a subquery in a WITH clause as a regular table, there is in most cases no difference between real rows and faked rows.

To unpivot a row ( i.e. make columns to rows ) the natural way is to join a source rowset to another table via cartesian join. So , if you need a row multiplied 4 times, you just join the original table with another one having 4 rows - this will produce all combinations of rows from both tables. These rows are faked as well - they don't exist in the database.

To get such auxiliary table ( which consists exactly of 4 rows) one may take any table/view , which he knows for sure, it exists in the database and has at least N rows - for example all_objects, or generate rows in some way. One popular approach to later is a connect by query from dual, which is though not very clean ( because this way to use CONNECT BY without PRIOR is not documented) - but it works.

The decode part is only to finalize the unpivot - the resulting rowset will have 4 rows with 4 columns, you need only one of them for value and one for description of this value.

I tend to agree with Sybrand ( if i understood him correctly, please, correct me if i am wrong) - the produced resultset is triple faked ( first time by generating source row by means of WITH clause, the second - virtual rows were created by means of cartesian join to another pseudo table, which is - third - virtual as well).

Not faked however is the approach, how rows can be generated, if source table don't have them.

Best regards

Maxim Received on Wed Jun 06 2007 - 12:16:54 CDT