OraFAQ Forum: SQL & PL/SQL » help to correct syntax error

Home » SQL & PL/SQL » SQL & PL/SQL » help to correct syntax error (oracle 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

help to correct syntax error [message #646189]

Mon, 21 December 2015 21:39

vs140580
Messages: 70
Registered: December 2015

Member

create table first as (SELECT     * 
FROM       edge1 h1 
inner join edge1 h2 
ON        ( 
             h1.e1=h2.e1
             AND h2.e1 != h1.e2
AND h2.e1 != h1.e3
AND h2.e1 != h1.e4
AND h2.e1 != h1.e5
AND h2.e1 != h1.e6
AND h2.e1 != h1.e7
AND h2.e1 != h1.e8
AND h2.e1 != h1.e9
AND h2.e1 != h1.e10
AND h2.e1 != h1.e11
AND h2.e1 != h1.e12
AND h2.e2 != h1.e1
AND h2.e2 != h1.e2
AND h2.e2 != h1.e3
AND h2.e2 != h1.e4
AND h2.e2 != h1.e5
AND h2.e2 != h1.e6
AND h2.e2 != h1.e7
AND h2.e2 != h1.e8
AND h2.e2 != h1.e9
AND h2.e2 != h1.e10
AND h2.e2 != h1.e11
AND h2.e2 != h1.e12
AND h2.e3 != h1.e1
AND h2.e3 != h1.e2
AND h2.e3 != h1.e3
AND h2.e3 != h1.e4
AND h2.e3 != h1.e5
AND h2.e3 != h1.e6
AND h2.e3 != h1.e7
AND h2.e3 != h1.e8
AND h2.e3 != h1.e9
AND h2.e3 != h1.e10
AND h2.e3 != h1.e11
AND h2.e3 != h1.e12
AND h2.e4 != h1.e1
AND h2.e4 != h1.e2
AND h2.e4 != h1.e3
AND h2.e4 != h1.e4
AND h2.e4 != h1.e5
AND h2.e4 != h1.e6
AND h2.e4 != h1.e7
AND h2.e4 != h1.e8
AND h2.e4 != h1.e9
AND h2.e4 != h1.e10
AND h2.e4 != h1.e11
AND h2.e4 != h1.e12
AND h2.e5 != h1.e1
AND h2.e5 != h1.e2
AND h2.e5 != h1.e3
AND h2.e5 != h1.e4
AND h2.e5 != h1.e5
AND h2.e5 != h1.e6
AND h2.e5 != h1.e7
AND h2.e5 != h1.e8
AND h2.e5 != h1.e9
AND h2.e5 != h1.e10
AND h2.e5 != h1.e11
AND h2.e5 != h1.e12
AND h2.e6 != h1.e1
AND h2.e6 != h1.e2
AND h2.e6 != h1.e3
AND h2.e6 != h1.e4
AND h2.e6 != h1.e5
AND h2.e6 != h1.e6
AND h2.e6 != h1.e7
AND h2.e6 != h1.e8
AND h2.e6 != h1.e9
AND h2.e6 != h1.e10
AND h2.e6 != h1.e11
AND h2.e6 != h1.e12
AND h2.e7 != h1.e1
AND h2.e7 != h1.e2
AND h2.e7 != h1.e3
AND h2.e7 != h1.e4
AND h2.e7 != h1.e5
AND h2.e7 != h1.e6
AND h2.e7 != h1.e7
AND h2.e7 != h1.e8
AND h2.e7 != h1.e9
AND h2.e7 != h1.e10
AND h2.e7 != h1.e11
AND h2.e7 != h1.e12
AND h2.e8 != h1.e1
AND h2.e8 != h1.e2
AND h2.e8 != h1.e3
AND h2.e8 != h1.e4
AND h2.e8 != h1.e5
AND h2.e8 != h1.e6
AND h2.e8 != h1.e7
AND h2.e8 != h1.e8
AND h2.e8 != h1.e9
AND h2.e8 != h1.e10
AND h2.e8 != h1.e11
AND h2.e8 != h1.e12
AND h2.e9 != h1.e1
AND h2.e9 != h1.e2
AND h2.e9 != h1.e3
AND h2.e9 != h1.e4
AND h2.e9 != h1.e5
AND h2.e9 != h1.e6
AND h2.e9 != h1.e7
AND h2.e9 != h1.e8
AND h2.e9 != h1.e9
AND h2.e9 != h1.e10
AND h2.e9 != h1.e11
AND h2.e9 != h1.e12
AND h2.e10 != h1.e1
AND h2.e10 != h1.e2
AND h2.e10 != h1.e3
AND h2.e10 != h1.e4
AND h2.e10 != h1.e5
AND h2.e10 != h1.e6
AND h2.e10 != h1.e7
AND h2.e10 != h1.e8
AND h2.e10 != h1.e9
AND h2.e10 != h1.e10
AND h2.e10 != h1.e11
AND h2.e10 != h1.e12
AND h2.e11 != h1.e1
AND h2.e11 != h1.e2
AND h2.e11 != h1.e3
AND h2.e11 != h1.e4
AND h2.e11 != h1.e5
AND h2.e11 != h1.e6
AND h2.e11 != h1.e7
AND h2.e11 != h1.e8
AND h2.e11 != h1.e9
AND h2.e11 != h1.e10
AND h2.e11 != h1.e11
AND h2.e11 != h1.e12
AND h2.e12 != h1.e1
AND h2.e12 != h1.e2
AND h2.e12 != h1.e3
AND h2.e12 != h1.e4
AND h2.e12 != h1.e5
AND h2.e12 != h1.e6
AND h2.e12 != h1.e7
AND h2.e12 != h1.e8
AND h2.e12 != h1.e9
AND h2.e12 != h1.e10
AND h2.e12 != h1.e11
AND h2.e12 != h1.e12
             
))

Error at Command Line:1 Column:35
Error report:
SQL Error: ORA-00957: duplicate column name
00957. 00000 - "duplicate column name"
*Cause:
*Action:

Report message to a moderator

Re: help to correct syntax error [message #646190 is a reply to message #646189]

Mon, 21 December 2015 21:56

vs140580
Messages: 70
Registered: December 2015

Member

the query runs when i give without create table but i want the output into a table help with the error

Report message to a moderator

Re: help to correct syntax error [message #646191 is a reply to message #646190]

Mon, 21 December 2015 22:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>create table first as (SELECT *
do not use wildcard ("*")
Explicitly specify specific column names.

Report message to a moderator

Re: help to correct syntax error [message #646193 is a reply to message #646191]

Mon, 21 December 2015 22:20

vs140580
Messages: 70
Registered: December 2015

Member

I understand i did it now in this case as there are only 12 columns how to do it if the columns are more E1 to En n is a large number kind help i dont know plsql if possible.

Report message to a moderator

Re: help to correct syntax error [message #646194 is a reply to message #646193]

Mon, 21 December 2015 22:24

vs140580
Messages: 70
Registered: December 2015

Member

one more is if i have several inner joins it becomes more difficult

Report message to a moderator

Re: help to correct syntax error [message #646195 is a reply to message #646194]

Mon, 21 December 2015 22:27

vs140580
Messages: 70
Registered: December 2015

Member

If i run without create table it assumes its own names for columns is there any way in sql developer to export the output with the coulmn names it generates to some csv or to a table create with those names as it is tedious to type each one manually when i want all.

Report message to a moderator

Re: help to correct syntax error [message #646196 is a reply to message #646195]

Mon, 21 December 2015 22:28

vs140580
Messages: 70
Registered: December 2015

Member

inner joins assumed on the same table alias itself

Report message to a moderator

Re: help to correct syntax error [message #646197 is a reply to message #646193]

Mon, 21 December 2015 22:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

vs140580 wrote on Mon, 21 December 2015 20:20

I understand i did it now in this case as there are only 12 columns how to do it if the columns are more E1 to En n is a large number kind help i dont know plsql if possible.

Large is relative.
Oracle table can support up to 1000 columns.
DDL must be written at least once since Oracle can not guess the value of N & must be told about datatype for every column.

Without exception, any table with over 100 columns that I have seen needed to Normalized & remove application data from column name.

Realize that SQL & PL/SQL are TWO entirely different languages.

Report message to a moderator

Re: help to correct syntax error [message #646198 is a reply to message #646197]

Mon, 21 December 2015 22:34

vs140580
Messages: 70
Registered: December 2015

Member

k so i was saying suppose the create table has some 50 columns one is it possible to with each the join table conditions data into different table means E1 to E12 of those in the inner join in one and E1_1 to E12_1 in other similiarly. but typing each when there are more columns is difficult kind help to avoid that i as i am not that good.

Report message to a moderator

Re: help to correct syntax error [message #646199 is a reply to message #646198]

Mon, 21 December 2015 22:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=typing+tutor+online

Report message to a moderator

Re: help to correct syntax error [message #646200 is a reply to message #646199]

Mon, 21 December 2015 22:40

vs140580
Messages: 70
Registered: December 2015

Member

so that is the only option learning to type fast for large columns k

Report message to a moderator

Re: help to correct syntax error [message #646201 is a reply to message #646200]

Mon, 21 December 2015 22:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

compose 10 lines
COPY & PASTE 10 more lines
COPY & PASTE 20 more lines
COPY & PASTE 40 more lines
COPY & PASTE 80 more lines
etc
then minor change on each line

Report message to a moderator

Previous Topic:	how to checkout tablespace
Next Topic:	Help with oracle query to manipulate dates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:59:26 CDT 2024