| [SOLVED] Aliases, a lesson learnt [message #599521] |
Fri, 25 October 2013 01:51 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Last week I came across something I didn't know. I wanted to share it with you.
A colleague asked to help him out with a problem. He had created an object type and wanted to populate it in a query. So far so good. But then he wanted to select some attributes from that object in the same query. Basically, he wanted to do something like this:
With his_view
As
( select object_type( attr1, attr2) theobj
From his_table
Where ...
)
Select theobj.attr1
From his_view
But somehow he was hitting ORA-00904: "THEOBJ"."ATTR1": invalid identifier over and over again.
Here's a test script:
Create type mhe_type As Object( col1 Number
, col2 Varchar2(30)
)
/
-- Basic select
With mhe_view
As
( Select mhe_type( level, To_Char(To_Date(level, 'J'),'Jsp')) mhe_obj
From dual
Connect By level <= 5
)
Select mhe_obj
From mhe_view
/
-- Now, I want just one element, col2
With mhe_view
As
( Select mhe_type( level, To_Char(To_Date(level, 'J'),'Jsp')) mhe_obj
From dual
Connect By level <= 5
)
Select mhe_obj.col2
From mhe_view
/
-- Failed? But How? Let's try it differently:
Select mhe_obj.col2
From ( Select mhe_type( level, To_Char(To_Date(level, 'J'),'Jsp')) mhe_obj
From dual
Connect By level <= 5
) mhe_view
/
-- Let's just fully qualify the selected object attribute.
Select mhe_view.mhe_obj.col2
From ( Select mhe_type( level, To_Char(To_Date(level, 'J'),'Jsp')) mhe_obj
From dual
Connect By level <= 5
) mhe_view
/
-- EUREKA! Now with my initial select and full qualification
With mhe_view
As
( Select mhe_type( level, To_Char(To_Date(level, 'J'),'Jsp')) mhe_obj
From dual
Connect By level <= 5
)
Select mhe_view.mhe_obj.col2
From mhe_view
/
-- How did that happen?
The select with the subquery worked, while the correlated subquery using the WITH clause wasn't. Then I had the idea of using an alias:
With mhe_view
As
( Select mhe_type( level, To_Char(To_Date(level, 'J'),'Jsp')) mhe_obj
From dual
Connect By level <= 5
)
Select mv.mhe_obj.col2
From mhe_view mv
/
 So all we had to do was add an alias. And, of course, only after finding the solution I browsed through the Oracle documentation and came across this part in the SQL Reference.
"Oracle says"t_alias
Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods.
link
I never had any problems with this myself, since I use aliases all the time but I thought it was worth sharing with you.
MHE
Edit: if any moderator thinks it's in the wrong place, just move it or let me know and I'll move it myself.
[Updated on: Fri, 25 October 2013 01:53] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
