Re: A bit of a conundrum with using a CTE

Cool! The first approach didn't work because it included the id column again but all I did was qualify the 'l' table to select one column that *wasn't* the id column (which I didn't need anyway) and that took care of the problem.

I didn't realize you could do something like

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

or even a

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

tbltclocs was only there for selection purposes (cross reference). I didn't need any of the columns...

Nice!

Thanks

Pete Helgren
Value Added Software, Inc
www.petesworkshop.com
GIAC Secure Software Programmer-Java

On 7/19/2012 12:01 PM, Charles Wilt wrote:

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.