× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. July 2012

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?


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.