Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!newsfeed01.sul.t-online.de!newsfeedt0.toon.t-online.de!newsmm00.sul.t-online.de!t-online.de!news.t-online.com!not-for-mail From: Maxim Demenko Newsgroups: comp.databases.oracle.misc Subject: Re: SQL*PLUS select statment Date: Wed, 06 Jun 2007 19:16:54 +0200 Organization: T-Online Lines: 179 Message-ID: <4666EC06.9030001@gmail.com> References: <1180538951.425243.214470@o5g2000hsb.googlegroups.com> <465DD378.6080202@gmail.com> <1181079125.450704.303980@q69g2000hsb.googlegroups.com> <1181128855.896427.28680@p47g2000hsd.googlegroups.com> Reply-To: mdemenko@gmail.com Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Trace: news.t-online.com 1181150213 03 16598 NsDn6c+vazeHm0d 070606 17:16:53 X-Complaints-To: usenet-abuse@t-online.de To: sybrandb X-ID: GFnAzeZ-Zecj5hMq1p+D-z3JXWU3uG-XzC5a9GGqRmZ-BvaZ4HGr8C User-Agent: Thunderbird 2.0.0.0 (Windows/20070326) In-Reply-To: <1181128855.896427.28680@p47g2000hsd.googlegroups.com> Xref: news.f.de.plusline.net comp.databases.oracle.misc:79868 sybrandb schrieb: > On Jun 5, 11:32 pm, sunadmn wrote: >> On May 30, 3:41 pm, Maxim Demenko 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