RE: SQL/ordering data based on a computed result/stored procedure?

This SQL works on our box...

select partno as item_number,
descr00001 as item_description,
cast((isu_qty * item_cost) as decimal (11,2)) as issue_price
from msdb1/stxns
where xn_code='MT25' /* item issues */
and xn_date=current_date /* transaction date */
order by issue_price

(current_date is a built-in SQL variable, not a field name)
Sometimes "derived columns" such as issue_price don't work (possibly a
result of a function call?). If I had this fictitious SQL:

select partno as item_number,
descr00001 as item_description,
get_current_price(item_number, current_date) as issue_price
from msdb1/stxns
where xn_code='MT25' /* item issues */
and xn_date=current_date /* transaction date */

I might not be able to order by the issue price [ order by issue_price ]. In
those cases, I would try ordering by the function call. [ order by
get_current_price(item_number, current_date) ].

HTH,
Loyd


-----Original Message-----
From: Reeve Fritchman [mailto:reeve@ltl400.com]
Sent: Tuesday, November 13, 2001 7:12 AM
To: Midrange-L@Midrange. Com
Subject: SQL/ordering data based on a computed result/stored procedure?


This is a multi-part message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
I need to display data in ascending order where the key field (price) is
dynamically computed.  I can do this with the QLGSORT API's and I can do it
with funky RPG; I'm wondering if I can do it with SQL and stored procedures,
or something of that ilk.

"Price" is a function of the item's base price and the current incentives
(discounts) in place for that customer/product line combination; the actual
incentive is determined from a complex set of business rules.  Therefore, a
$100 item with a 20% discount (net $80) becomes less expensive than a $90
item with a 10% discount (net price $81), and I'd like the $80 item to show
up before the $81 item.

So, SQL reads a group of items, applies a function to the item base price,
gets a "net" price, and presents ordered-by-net-price data into my
application program.  It seems simple enough...

No green-screen, client/server, or CFINT opinions are required!

Thanks,
rf


--

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.