Re: SQL*PLUS select statment

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 30 May 2007 21:41:44 +0200
Message-ID: <465DD378.6080202@gmail.com>

sunadmn schrieb:
> 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 Received on Wed May 30 2007 - 14:41:44 CDT