Posted by Serge Rielau on 10/03/07 22:06

steve wrote:
> On Oct 3, 6:58 am, Serge Rielau <srie...@ca.ibm.com> wrote:
>> The intent of a CTE is that it is referenced. The same isn't necessarily
>> true for columns in the select list (positional bind-out of a cursor)
>
> Hi,
>
> So your explaining another aspect of 'referential' integrity. :-)
> I reference it (CTE,Order By etc) the integrity is checked and
> enforced.
> And if I don't reference it it's my tough luck or my oversight and I
> could wind up with a big mess?
Not at all.
I presume you are aware that columns in the select list can actually be
unnamed:
SELECT c1 * c2, foo(c3) FROM T
You now have two unnamed columns returned from the select list.
That's all right, chances are your .NET variables that you're going to
bind them out to have different names anyway and your bind-out goes by
position anyway.
Obviously when you create a view:
CREATE VIEW v AS SELECT c1 * c2, foo(c3) FROM T
that view would be quite useless because you can reference the columns
So you give them names either in the view signature or by naming each
expression.

> Interestingly, it does resemble the logic of a FK reference in a
> twisted way. But here chance seems to play a major part :-)
> These queries go happly on their merry way:
>
> SELECT a.orderid,a.customerid as Cust,a.shipcountry as
> Cust,b.productid
> FROM orders as a join [order details] as b
> on a.orderid=b.orderid
>
> SELECT a.orderid,a.orderid,a.customerid as Cust,a.shipcountry as
> Cust,b.productid
> FROM orders as a join [order details] as b
> on a.orderid=b.orderid
And why shouldn't they?

This has nothing to do with luck. It's how the SQL Standard was
consciously designed. Now if you want to enforce that all expressions in
the select list must be named and be unambiguously feel free to write
your own standard. I sure am too lazy to label stuff I don't need :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Back to original message]