Re: Bad order by syntax

[Arun Kumar <arunkumar13@YAHOO.COM>]

try running the sqr with -s option ( commandlineflag
)which would show you the exact sql statements in the
log file.

--- David Thompson-Hall
<david.thompson-hall@DOIT.WISC.EDU> wrote:
> (We're in PS 7.6, using Oracle 8.x...)
>
> So a fellow programmer and I were chasing down a bug
> when we discovered this...
>
> We were trying to pull rows from PERS_DATA_EFFDT in
> reverse order, so we
> could grab the most recent row.  For some reason my
> colleague didn't use
> max(effdt), but the way she wrote it still makes
> sense.  She selected only
> rows before the current data, and then pulled the
> rows in reverse order
> (order by effdt desc), and used a LOOPS=1 to just
> grab the first row.
>
> However, it didn't actually work right, and we
> couldn't understand why not
> until we noticed a strange typo that somehow got
> past the SQR compiler...
>
> Here is the code
>
> begin-select  loops =1                  <--  so we
> only look at the first row
>   (...list of fields...)
> from PS_PERS_DATA_EFFDT
> where EffDt <= $curr_dt   ! get current rows
> order by $effdt desc    ! i.e. get  most recent
> first, and since we only look
> at the
>                         ! one row (thanks to LOOPS,
> above), we get the row we want
>
> What slipped by us was the use of "order by $effdt"
> instead of "order by
> effdt".
>
> What amazed me is that apparently SQR will let this
> compile.  How does SQR
> accept using a variable as an ORDER BY argument,
> without any Dynamic SQR
> brackets?  What is it trying to do, order by the
> string value of whatever
> $effdt is?  Or does SQL (or SQR) just ignore an
> order by if the column name
> doesn't make any sense?
>
> I can't even reconstruct this using Oracle (trying a
> similar query thru
> TOAD), it won't execute the SQL.  So how does SQR
> treat this code?
> (Frankly, the fact SQR will let this compile is a
> bit disturbing to me...)
>
> We're busy chasing down problems here, so we don't
> have any time to see
> what works and what doesn't, but I thought someone
> here might have some
> related thoughts or previous experience.
> Anyone else seen similar issues from SQR?
>
> DTH
>
> David Thompson-Hall
>    1210 W. Dayton St.
> Programmer/Analyst
>    Madison, WI 53706
> Apps Tech/Business Operations Apps
>    608/265-9571
> Div of Info Technology (DoIT)
> University of Wisconsin-Madison
david.thompson-hall@doit.wisc.edu


__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/