Great. Thank you very much. That makes sense. Never realized that
Oracle makes such a distinction about these 2 types of quotation marks.
(How did we end up getting two types of quotation marks anyway?)
Vladimir M. Zakharychev wrote:
> antonyliu2002_at_yahoo.com wrote:
> > Rene Nyffenegger wrote:
> > > On 2006-08-26, antonyliu2002_at_yahoo.com <antonyliu2002_at_yahoo.com> wrote:
> > > > antonyliu2002_at_yahoo.com wrote:
> > > >> DA Morgan wrote:
> > > >> > antonyliu2002_at_yahoo.com wrote:
> > > >> > > OK, I know this has been asked many times. I've done a google search,
> > > >> > > but but not figure out.
> > > >> > >
> > > >> > > I have a very simple table of student information in a Oracle database
> > > >> > > like below, where STU_ID is student id and COLL_CD is college code and
> > > >> > > CLS_LEVEL is class level.
> > > >> > >
> > > >> > > STU_ID COLL_CD CLS_LEVEL
> > > >> > > 123 KV 1U
> > > >> > > 234 KV 1U
> > > >> > > 345 KY 1U
> > > >> > > 456 KN 1G
> > > >> > > 567 KP 1L
> > > >> > > 678 KV 1U
> > > >> > > 789 KY 1G
> > > >> > > 890 KY 1G
> > > >> > > 901 KY 1U
> > > >> > > 911 KP 1V
> > > >> > > 912 KV 1L
> > > >> > > 913 KN 1G
> > > >> > > 915 KP 1U
> > > >> > >
> > > >> > > I would like to count and list the number of students of each class
> > > >> > > level in each college like so:
> > > >> > >
> > > >> > > COLL_CD 1U 1G 1L 1V
> > > >> > > KV 23 2 0 0
> > > >> > > KY 9 16 88 1
> > > >> > > KN 13 45 55 0
> > > >> > > KP 0 0 61 18
> > > >> > >
> > > >> > > I know I can do
> > > >> > >
> > > >> > > Select count(*) from T_STUDENT where COLL_CD='KV' and CLS_LEVEL='1U'
> > > >> > >
> > > >> > > one after another.
> > > >> > >
> > > >> > > But can I get this crosstab in a single SQL query with SqlPlus?
> > > >> > >
> > > >> > > Thanks.
> > > >> >
> > > >> > Morgan's library at www.psoug.org
> > > >> > click on DECODE.
> > > >> > --
> > > >> > Daniel A. Morgan
> > > >> > University of Washington
> > > >> > damorgan_at_x.washington.edu
> > > >> > (replace x with u to respond)
> > > >> > Puget Sound Oracle Users Group
> > > >> > www.psoug.org
> > > >>
> > > >> Hi, Morgan,
> > > >>
> > > >> I found your notes at http://www.psoug.org/reference/decode_case.html
> > > >> and took your example SQL as below:
> > > >>
> > > >> SELECT program_id,
> > > >> COUNT (AMERICAN) AAL,
> > > >> COUNT (DELTA) DAL,
> > > >> COUNT (NORTHWEST) NWO,
> > > >> COUNT(INTL_LEASING) ILC
> > > >> FROM (
> > > >> SELECT program_id,
> > > >> DECODE(customer_id, 'AAL', 'AAL') AMERICAN,
> > > >> DECODE(customer_id, 'DAL', 'DAL') DELTA,
> > > >> DECODE(customer_id, 'NWO', 'NWO') NORTHWEST,
> > > >> DECODE(customer_id, 'ILC', 'ILC') INTL_LEASING
> > > >> FROM airplanes)
> > > >> GROUP BY program_id;
> > > >>
> > > >> I created the SQL query by following this example as shown below:
> > > >>
> > > >> SELECT
> > > >> COLL_CD,
> > > >> COUNT(UNDER) 1U,
> > > >> COUNT(GRAD) 1G,
> > > >> COUNT(VET) 1V,
> > > >> COUNT(LAW) 1L
> > > >> FROM (
> > > >> SELECT COLL_CD,
> > > >> DECODE(CLS_LEVEL, '1U', '1U') UNDER,
> > > >> DECODE(CLS_LEVEL, '1G', '1G') GRAD,
> > > >> DECODE(CLS_LEVEL, '1V', '1V') VET,
> > > >> DECODE(CLS_LEVEL, '1L', '1L') LAW
> > > >> FROM student)
> > > >> GROUP BY COLL_CD;
> > > >>
> > > >> But I get the following error:
> > > >>
> > > >> COUNT(UNDER) 1U,
> > > >> *
> > > >> ERROR at line 3:
> > > >> ORA-00923: FROM keyword not found where expected
> > > >>
> > > >> I am using Oracle 10i with SQLPLUS.
> > > >
> > > > It turns out that my sql*plus does not accept alias for the count
> > > > function. In other words, if I replace the following lines
> > > >
> > > > COLL_CD,
> > > > COUNT(UNDER) 1U,
> > > > COUNT(GRAD) 1G,
> > > > COUNT(VET) 1V,
> > > > COUNT(LAW) 1L
> > > >
> > > > with
> > > >
> > > > COLL_CD,
> > > > COUNT(UNDER),
> > > > COUNT(GRAD),
> > > > COUNT(VET),
> > > > COUNT(LAW)
> > > >
> > > > It works perfect.
> > > >
> > > > I am not sure how to assign an alias to the count function.
> > >
> > > The problem is that your alias starts with a digit instead
> > > of a non-digit character:
> > >
> > >
> > > Alias starts with digit:
> > >
> > >
> > > RENE> select count(*) 1u from dual;
> > > select count(*) 1u from dual
> > > *
> > > ERROR at line 1:
> > > ORA-00923: FROM keyword not found where expected
> > >
> > >
> > > Alias starts with non digit:
> > >
> > >
> > > RENE> select count(*) u1 from dual;
> > >
> > > U1
> > > ----------
> > > 1
> > >
> > >
> > > Alias in "quoted":
> > >
> > >
> > > RENE> select count(*) "1u" from dual;
> > >
> > > 1u
> > > ----------
> > > 1
> > >
> > >
> > > hth,
> > > Rene
> > >
> > > --
> > > Rene Nyffenegger
> > > http://www.adp-gmbh.ch/
> >
> > Thanks, exactly that is the case.
> >
> > You know, I did suspect that the problem was that my aliases started
> > with a numeric number, so I did try quoting them with SINGLE quotation,
> > which, as it turned out, does not work, and DOUBLE quotation worked!
> >
> > So, why is that we use single quotation in the DECODE function as
> > below?
> >
> > DECODE(CLS_LEVEL, '1U', '1U') UNDER,
> >
> > And it looks like double quotation in such functions won't work.
> >
> > I guess I am asking about the differences of single quotation and
> > double quotation in sql*plus.
>
> The reason is that within DECODE call '1U' is a *literal value* (a
> string,) whereas in the query column list it's an *identifier*. Strings
> are enclosed with single quotes. In Oracle, identifiers are
> conventionally all uppercase and can't start with digits (if you use
> mixed or lower case, it will be automatically converted to upper case.)
> However, if you enclose arbitrary string with double quotes, it is
> accepted as an identifier as is, with case preserved and any characters
> forming the name. So Oracle will not accept 1U, but will accept "1U" as
> an alias.
>
> Regards,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com
Received on Mon Aug 28 2006 - 21:12:14 CDT
