select * from a supertype ? 
Author Message
 select * from a supertype ?
Hi,

Having just built a test structured type hierarchy, I was expecting
a "select *" of the highest supertype to reveal all subtypes' attributes?

Instead it lists ALL OIDs, but the attributes of only the supertype?

I really want to see all the subtypes' attributes (NULL'd where appropriate),
The way I read the docs. lead me to believe this would be the default behaviour?

Thanks.

Paul.



Mon, 01 Aug 2005 00:55:39 GMT
 select * from a supertype ?

Hi,

SELECT * FROM OUTER(supertype)
will give you want you want.
To do that you need select privileges on all subtables.
In a normal select it would be dangerous to include say the salary
column of employees when you select * from person.

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Visit DB2 Developer Domain at
http://www7b.software.ibm.com/dmdd/



Mon, 01 Aug 2005 05:48:37 GMT
 select * from a supertype ?
Thanks for that, should have looked more closer in the manaual! :-)

One other point is there any syntax in ANSI/SQL standards for this
syntax for i.e outer() only() etc... ?

Thanks.

Paul.



Tue, 02 Aug 2005 17:08:38 GMT
 select * from a supertype ?
AFAIK ONLY() is ANSI. Outer is a DB2 product extention. It's main
purpose is to allow apps to "load" a whole hierarchy in one gulp into
memory without having to perform piecemeal selects from each subtable.
We never got brave enough to allow INSERT INTO OUTER() though (without
ANSI saying it's OK). Time will tell :-)

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Visit DB2 Developer Domain at
http://www7b.software.ibm.com/dmdd/



Tue, 02 Aug 2005 22:36:50 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. recursive and supertype-subtype relationship definition

2. Subtype or supertype problem.

3. Subtype or supertype problem

4. Subtype or supertype problem

5. Supertypes?

6. Question about supertypes and subtypes

7. Supertype/Subtype relationships

8. SuperType/SubType modeling

9. Supertypes/subtypes

10. Supertypes and Subtypes

11. Need help w/ supertypes/subtypes


 
Powered by phpBB® Forum Software