Hi Pete,
That has nothing to do with the CTE, column names within an view must be
unique, i.e. you need to add column list when creating your view:
Create View MySchema/MyView (Col1, Col2, Col3, ... ColN)
As With x ....;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Pete Helgren
Gesendet: Thursday, 19.7 2012 19:57
An: Midrange Systems Technical Discussion
Betreff: A bit of a conundrum with using a CTE
I have an sql statement like so:
with tbl as (select * from vwPunches v,tbltclocs l where v.clockname =
l.clockname and location = 'GARAGE'
and srtDate between '2012-06-06' and '2012-06-21') , ins as (select * from
tbl where strinout='IN'), outs as (select * from tbl where strinout='OUT')
select .....
The vwPunches view and the tbltcdocs table both have an ID column. In the
SQL select statement that uses the ins and outs table I select the ID
columns like so:
ins.id, outs.id
but the statement issues an SQL0203 error: "Name ID is ambiguous." At first
I thought: It CAN'T be. I qualified the column name. But as I thought about
it further I realized that it was the CTE that was causing the problem.
Is the only fix to name the columns in the CTE rather than using * or is
there some clever way to refer to the column within the CTE that is
qualified?
--
Pete Helgren
Value Added Software, Inc
www.petesworkshop.com
GIAC Secure Software Programmer-Java
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
