Home » SQL & PL/SQL » SQL & PL/SQL » CTE
CTE [message #563510] Tue, 14 August 2012 09:47 Go to next message
musclebreast
Messages: 24
Registered: July 2012
Junior Member
Hello everybody,

as you know I work normally with SQL Server. Now I struggle with With Clauses in Orcale...my Statement works fine in SQL server but I can't run it in orcacle.

With CTE
AS
(
SELECT
ID
FROM  dee A1
inner join data A2 
on A1.DataID = A2.ID
AND A1.Subtype = '999' 
inner join ancestors an
ON an.DataID = A1.DataID
and an.AncestorID ='7000'
inner join dee A11
ON A11.DataID = A1.ParentID

where A11.Name <>'Retired' 



Group BY ID
)

Select * from cte


I checked what the syntax in oracle and for me it looks alright. I get the following error:

ORA-32033: unsupported column aliasing - With CTEAS(SELECTIDFROM dtree A1inner join


I did column aliasing but removed it...so I don't really see what causes the error...can you give me a hint?

Kind regards,

Lara
Re: CTE [message #563511 is a reply to message #563510] Tue, 14 August 2012 09:50 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Go to this site.

http://psoug.org/reference/with.html
Re: CTE [message #563513 is a reply to message #563510] Tue, 14 August 2012 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topics:

cookiemonster wrote on Fri, 27 July 2012 14:34
That, I'm afraid, is completely unreadable
Please read How to use [code] tags and make your code easier to read?
and repost the code, formatted, in code tags, as described in that link.
...
Really you need to post a Test case - create table statements and insert statements for data, then we'll be able to work with your tables and data to recreate the problem.


Michel Cadot wrote on Fri, 27 July 2012 16:13
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...


BlackSwan wrote on Mon, 13 August 2012 16:35
...
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
...


Michel Cadot wrote on Mon, 13 August 2012 17:50
...All Oracle documentation is online, for instance: Database SQL Reference.
...


Regards
Michel
Re: CTE [message #563544 is a reply to message #563513] Tue, 14 August 2012 13:10 Go to previous messageGo to next message
musclebreast
Messages: 24
Registered: July 2012
Junior Member
hello,

thanks for the link mulakri, I've been to this site before...anyway my CTE works now...the syntax is correct...just a little problem with a line break..it seems oracle is very sensitive in that point.

Yes, I understand...you need complete examples to help me. It's not that easy to export everthing, because often they are in a complex system environment.

Mostly it's a logical problem..you don't really need a table I think, because I do just simple oracle mistakes..it's tough the switch from sql server...

anyway this is my problem:

With CTE
AS
(
SELECT
ID,
Max(A1.Name) as "Name", 
MAX(ParentID) as "ParentID"
FROM  dee A1
inner join data A2 
on A1.DataID = A2.ID
AND A1.Subtype = '999' 
inner join ancestors an
ON an.DataID = A1.DataID
and an.AncestorID ='7000'
inner join dee A11
ON A11.DataID = A1.ParentID

where A11.Name <>'Retired' 



Group BY ID
)

Select * from cte


As result I get a table with the follwoing columns:

ID.....Name.......ParentID


And now my problem, it's just a basic sql query:

select

 A1.Name

 from CTE A1, dee A2


I get the follwing error:

[ORA-00904: "A1"."Name": invalid identifier


Checked in Sql Server and it works...what is the problem here...the table exists an it's just a query for the name...

I hope you can give me a hint...

Kind regards,

Lara
Re: CTE [message #563545 is a reply to message #563544] Tue, 14 August 2012 13:15 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


How can we reproduce what you report?
You have syntax error.

It is a Bad Thing to use double quote marks with Oracle!
Re: CTE [message #563546 is a reply to message #563544] Tue, 14 August 2012 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 14 August 2012 17:17
From your previous topics:

cookiemonster wrote on Fri, 27 July 2012 14:34
That, I'm afraid, is completely unreadable
Please read How to use [code] tags and make your code easier to read?
and repost the code, formatted, in code tags, as described in that link.
...
Really you need to post a Test case - create table statements and insert statements for data, then we'll be able to work with your tables and data to recreate the problem.


Michel Cadot wrote on Fri, 27 July 2012 16:13
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...


BlackSwan wrote on Mon, 13 August 2012 16:35
...
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
...


Michel Cadot wrote on Mon, 13 August 2012 17:50
...All Oracle documentation is online, for instance: Database SQL Reference.
...


Regards
Michel

Re: CTE [message #563549 is a reply to message #563546] Tue, 14 August 2012 13:29 Go to previous messageGo to next message
musclebreast
Messages: 24
Registered: July 2012
Junior Member
Hi guys,

please don'T blame me...I read everthing...the problem I can't give you more..I don't work directly on Oracle...it's a software web based software and oracle is behind...I haven't got accecc...I just can send queries...for that reason it's difficult to track everything...is there not another option to help me? I just broke down the problem so it's simple to follow me (hopefully:))
can you check just the

select

 A1.Name

 from CTE A1, dee A2


I don't understand why A1.Name is wrong in oracle...

Thanks for the hint with the double quote marks.. I found an example on another website...in sql server i don't need quotes...

..kind regards,

lara
Re: CTE [message #563551 is a reply to message #563549] Tue, 14 August 2012 13:38 Go to previous messageGo to next message
musclebreast
Messages: 24
Registered: July 2012
Junior Member
I think I've got it...the double quote marks are the real problem..if I use A1."Name" it works..

Ok, but how can i define an alias column? Are any quotes neccessary? but without i got errors...
Kind regards,

lara
Re: CTE [message #563552 is a reply to message #563549] Tue, 14 August 2012 13:38 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
select * from V_$RESERVED_WORDS ;
Re: CTE [message #563556 is a reply to message #563552] Tue, 14 August 2012 14:03 Go to previous message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As of quotes you mentioned: in Oracle, it is usually a bad idea to name objects using mixed case. By default, names are all UPPERCASE. However, if you create objects by enclosing names into double quotes, you MUST use double quotes every time you reference those objects, writing their names exactly the same as at the time you created them.

For example:
SQL> create table test (col1 number, "CoL2" number);

Table created.

SQL> insert into test (col1, col2) values (1, 2);
insert into test (col1, col2) values (1, 2)
                        *
ERROR at line 1:
ORA-00904: "COL2": invalid identifier


SQL> insert into test (col1, COL2) values (1, 2);
insert into test (col1, COL2) values (1, 2)
                        *
ERROR at line 1:
ORA-00904: "COL2": invalid identifier


SQL> insert into test (col1, "COL2") values (1, 2);
insert into test (col1, "COL2") values (1, 2)
                        *
ERROR at line 1:
ORA-00904: "COL2": invalid identifier


SQL> insert into test (col1, "CoL2") values (1, 2);

1 row created.

SQL>
Previous Topic: Exception at Update Trigger
Next Topic: updating Nth row and adding a column (merged 2)
Goto Forum:
  


Current Time: Sat Oct 25 01:31:08 CDT 2014

Total time taken to generate the page: 0.09170 seconds