Re: Trying to run a SELECT...INTO SQL Command

Actually I have in the past run into problems using AS to designate column aliases, so as it is not needed I don't use it. However in this case the problem is that Oracle's Select Into selects into variables and not into tables. To create a new table with the data selected the correct syntax is

create table tsp.Level_0 as
(SELECT Comp.ID AS LEVEL0ID, Comp.NAME AS LEVEL0NAME, Comp.ABBREVIATION AS LEVEL0Abbreviation
FROM tsp.TSP_COMPONENTS Comp
WHERE Comp.COMPONENT_LEVEL=0);

Notice the use of a table alias to shorten the column names.

Polaris <rkamene_at_easynet.co.uk> wrote in article <70gea3$n3l$1_at_apple.news.easynet.net>...
> Oh yes ,
> Oracle does use as to designate column aliases. You can for example
> write "select ename as my_name from emp;". If you you want to use one
 table
> to create another table then you can write
> "create table my_table as select col1, col2 , col3 , col4 from
 your_table;"
>
> Good luck.
> RNK.
> Pendragon wrote in message

 <01bdf90c$035b97e0$96b9d5d0_at_yesford.btg.com>...
> >Oracle doesn't use as to designate column aliases.
> >
> >Peter DeSimone <pdesimone_at_sageus.com> wrote in article
> ><6v37lv$peh$1_at_news-1.news.gte.net>...
> >> When I run this SQL Command through the SQL tool in Oracle, I get the
 error
> >> below it:
> >>
> >> SELECT tsp.TSP_COMPONENTS.ID AS LEVEL0ID, tsp.TSP_COMPONENTS.NAME AS
> >> LEVEL0NAME, tsp.TSP_COMPONENTS.ABBREVIATION AS LEVEL0Abbreviation INTO
> >> tsp.Level_0
> >> FROM tsp.TSP_COMPONENTS
> >> WHERE (((tsp.TSP_COMPONENTS.COMPONENT_LEVEL)=0));
> >>
> >>
> >> Error Message.
> >>
> >> ORA-00905: missing keyword
> >>
> >> This syntax works correctly with Ms SQL and Access, why should Oracle
 work
> >> differently...
> >>
> >> I want the INTO command to put INTO a new table called TSP.LEVEL_0.
> >>
> >> Thanks..
> >>
> >> Peter DeSimone
> >> pdesimone_at_sageus.com
> >>
> >>
> >>
> >>
> >>
>
>
>
Received on Wed Oct 21 1998 - 00:00:00 CEST