| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL*PLUS select statment
On Jun 7, 10:39 am, sybrandb <sybra..._at_gmail.com> wrote:
> On Jun 7, 4:22 pm,sunadmn<suna..._at_gmail.com> wrote:
>
>
>
> > 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,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
>
> > > > 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
Thanks for the confidence here I will get this I know, just being a NB sometimes the concept is tough to grasp at first but I am starting to see light at the end of the tunnel now. But I am getting a strange error from my application now in the form of:
ORA-00911: invalid character Cause: identifiers may not start with any ASCII character other thanletters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual. Action: none
But this query runs fine in SQL*Plus would this again be a case of using something that is native to SQL*Plus?
Thanks,
-Steve
Received on Thu Jun 07 2007 - 09:47:30 CDT
 |
 |