Select vs Choose

[Darren Reely]

Matt, thanks for introducing cursors into this thread.

Recently I had a situation where I wanted records returned where a column
did not equal two particular values.  The only way I know of to code this
in straight PH is to use SELECT IF COLUMN <> 'A' AND COLUMN <> 'B'.  The
problem was that the screen took a long time to return anything because the
SQL query was returning every row.  SELECT if never parsed to add to the
SQL query.

So I created this cursor and now the screen is very quick.

        sql in mfg declare pick_data cursor for &
        select * from pick_data &
                where shipment_status NOT IN ('CANCEL','SHIPPED') &
                order by ship_date, carrier, custno

I try to avoid the use of cursors, but in this case it made a huge
difference.  I'll have to look at some other pesky screens to see if I can
push more decision making to the database.

At a past job I inherited a report that took 1, then 1.5, then 2 hours to
run.  When I got around to looking into the problem, it turned out that
there was no CHOOSE when there could have been one.  When I placed an
appropriate CHOOSE statement in the code, I cut the run time down to 10
minutes.

If you don't let your users know how easy these fixes are, they just keep
thinking of you as a god! ;)

Darren


"Ohmes, Matt" wrote:
> 
> Oh good! Something I know something about!
> You are correct Mark.  And so is Michael Lee and Bob Deskin (of course!). .