Re: newbie sql question

A copy of this was sent to Vic Smyth <vicsmyth_at_megsinet.net> (if that email address didn't require changing) On Sun, 11 Apr 1999 16:43:43 -0700, you wrote:

>Hi,
>
>I'm doing a project for class using Oracle Sqlplus and am stuck on a problem.
>I've created a view and am trying to query the view but get an error message.
>Any help would be appreaciated!
>
>Here's what I have, the error message is below.
>

When you use double quotes to create column names -- you will need to continue to use them in queries and such. Your query should be

SELECT SUM("Inv_Num"),SUM("SumOfLine"),MIN("SumOfLine"), ....

The quoted identifiers preserve case and special characters (allowing your for example to have an identifier "This has a space" as a table name or column name...

>Vic
>
>To reply to email remove the "myth" from the address.
>
>SQL> CREATE VIEW TEMPX AS
> 2 SELECT INVOICE.CUS_CODE "C_Code", COUNT(DISTINCT
>INVOICE.INV_NUMBER)"Inv_Nu
>m",
> 3 SUM(LINE_UNITS*LINE_PRICE)"SumOfLine"
> 4 FROM INVOICE, LINE
> 5 WHERE INVOICE.INV_NUMBER = LINE.INV_NUMBER
> 6 GROUP BY INVOICE.CUS_CODE;
>
>View created.
>
>SQL> select * from tempx;
>
>C_Cod Inv_Num SumOfLine
>----- --------- ---------
>10011 3 444
>10012 1 153.85
>10014 2 422.77
>10015 1 34.97
>10018 1 70.44
>
>SQL> DESCRIBE TEMPX;
> Name Null? Type
> ------------------------------- -------- ----
> C_Code NOT NULL CHAR(5)
> Inv_Num NUMBER
> SumOfLine NUMBER
>
>SQL> SELECT SUM(Inv_Num),SUM(SumOfLine),MIN(SumOfLine),
> 2 MAX(SumOfLine),AVG(SumOfLine)
> 3 FROM TEMPX;
>MAX(SumOfLine),AVG(SumOfLine)
> *
>ERROR at line 2:
>ORA-00904: invalid column name
>
>
>SQL>
  Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation