Home » SQL & PL/SQL » SQL & PL/SQL » CTE
| CTE [message #563510] |
Tue, 14 August 2012 09:47  |
 |
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 #563513 is a reply to message #563510] |
Tue, 14 August 2012 10:17  |
 |
Michel Cadot
Messages: 54253
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
From your previous topics:
cookiemonster wrote on Fri, 27 July 2012 14:34That, 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:13With 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 |
|
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 #563546 is a reply to message #563544] |
Tue, 14 August 2012 13:21 |
|
Michel Cadot
Messages: 54253
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Michel Cadot wrote on Tue, 14 August 2012 17:17From your previous topics:
cookiemonster wrote on Fri, 27 July 2012 14:34That, 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:13With 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 #563556 is a reply to message #563552] |
Tue, 14 August 2012 14:03 |
 |
Littlefoot
Messages: 17000
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> |
|
|
|
Goto Forum:
Current Time: Sun May 26 04:28:58 CDT 2013
Total time taken to generate the page: 0.10007 seconds
|
] 
Members
Search
Help
Register
Login
Home
