Re: SQL*PLUS select statment

On Jun 6, 7:20 am, sybrandb <sybra..._at_gmail.com> wrote:
> 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

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 Received on Thu Jun 07 2007 - 09:22:22 CDT