RE: SQL question - why doesn't WHERE clause know about renamed fields?

["Dennis Lovelady" <iseries@xxxxxxxxxxxx>]

That's one way.  Another (mentioned already, not by me) is to use CTE:

WITH T1 as 
 (
  SELECT
    numtodate(polog.poendt) as day_entered,
    numtodate(ech.hedte) as day_ordered
  FROM xyzlib/pologpf AS polog
  LEFT JOIN xyzlib/ech AS ech
      ON polog.pokord = ech.hord
   )
SELECT * from T1
 where day_entered+3 >  day_ordered


Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Genius is one per cent inspiration and ninety-nine per cent perspiration."
        -- Thomas Edison 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> bounces@xxxxxxxxxxxx] On Behalf Of Gqcy
> Sent: Monday, November 08, 2010 11:44 AM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Re: SQL question - why doesn't WHERE clause know about renamed
> fields?
>
> Thanks for the answers...
> So, If I add my UDF to this discussion, I need to call it multiple
> times, as shown:
>
> SELECT
> numtodate(polog.poendt) as day_entered,
> numtodate(ech.hedte) as day_ordered
> FROM xyzlib/pologpf AS polog
> LEFT JOIN xyzlib/ech AS ech
>      ON polog.pokord = ech.hord
> where DAYS(numtodate(polog.poendt))+3 >  DAYS(numtodate(ech.hedte))
>
>
>
> On 11/8/2010 10:31 AM, Dennis Lovelady wrote:
> > Sorry, Lloyd, but that's kind of like answering a "why is the sky
> blue"
> > question by saying that the sky color contains no red or yellow.  The
> answer
> > of WHY is that the SELECT list is evaluated last, so there is no way
> for the
> > WHERE clause to be aware of the SELECT list's rename.
> >
> > Dennis Lovelady
> > http://www.linkedin.com/in/dennislovelady
> > --
> > "Few things are harder to put up with than the annoyance of a good
> example."
> >          -- Mark Twain
> >
> >
> > > Generally, you cannot use aliased names (day_entered) in the WHERE
> > > clause.
> > > You need to use polog.poendt instead.
> > >
> > > --Loyd
> > >
> > > On Mon, Nov 8, 2010 at 10:02 AM, Gqcy<gmufasa01@xxxxxxxxx>  wrote:
> > >
> > > > SELECT
> > > > polog.poendt as day_entered,
> > > > ech.hedte as day_ordered
> > > > FROM xyzlib/pologpf AS polog
> > > > JOIN xyzlib/ech AS ech
> > > >      ON polog.pokord = ech.hord
> > > > where day_entered>  day_ordered
> > > >
> > > > Column DAY_ENTERED not in specified tables.
> > > >
> > > >
> > > > Is this just part of SQL, or am I missing something???
> > > >
> > > > These are dates, and I have created a UDF to convert numeric
> > > > to Date(not shown in above example), but I can't use the renamed
> > > fields...
> > > > thanks
> > > >
> > > > Gerald
> > > --
> > > 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.
>
> --
> 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.