Re: A bit of a conundrum with using a CTE

[Charles Wilt <charles.wilt@xxxxxxxxx>]

You might try
with tbl as (select v.*,i.* from vwPunches v,tbltclocs l
where v.clockname = l.clockname and location = 'GARAGE'
and srtDate between '2012-06-06' and '2012-06-21') ,

Not sure if it will work...

Also you can rename the results
with tbl (fld1, fld2, fld3, <...>)
 as (select <...> )

HTH,
Charles

On Thu, Jul 19, 2012 at 1:56 PM, Pete Helgren <pete@xxxxxxxxxx> wrote:
> 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.