Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Crosstab with Oracle SqlPlus?

Re: Crosstab with Oracle SqlPlus?

From: <antonyliu2002_at_yahoo.com>
Date: 27 Aug 2006 10:11:53 -0700
Message-ID: <1156698713.782213.7700@m73g2000cwd.googlegroups.com>


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. Received on Sun Aug 27 2006 - 12:11:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US