Re: A bit of a conundrum with using a CTE

[Pete Helgren <pete@xxxxxxxxxx>]

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.