Re: SQL*PLUS select statment

On Jun 7, 10:47 am, sunadmn <suna..._at_gmail.com> wrote:
> 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,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- 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 than
> letters 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

Never mind I had a stupid mistake I spotted thank you all for the help you have given and please allow me to apologize for being a sort of pain. I do respect all your advice and greatly appreciate your time in helping me with this matter.

Thanks a million,
-Steve Received on Thu Jun 07 2007 - 09:49:44 CDT