MVSFORUMS.com

Sarangadhar

I have a requirement like below:

After querying the database, I'll manipulate the values, and i would like to display the new values without using a programming language. It would be good if I can display using QMF or SPUFI.

here is an example:
SELECT AMOUNT,EMP_ID
FROM EMP;
IF AMOUNT > 100
THEN
SET NEW_AMOUNT = AMOUNT * 1;
ELSE
SET NEW_AMOUNT = AMOUNT *10;
END IF

Here I would want to display NEW_AMOUNT and EMP_ID in each row instead of AMOUNT. any way to acheive this?
_________________
Thanks

kolusu · Posted: Mon Sep 17, 2007 1:35 pm Post subject:

Sarangadhar,

Define a workstorage variable and perform the calculations on that and display it

Sarangadhar · Posted: Mon Sep 17, 2007 1:48 pm Post subject:

kOLUSU,
i would want to perform this in QMF or SPUFI, is it possible to use working storage variables and us COMPURE, DISPLAY statements?

Another approache I remember sometime back on thsi forum is using CASE WHEN in the select statement to modify the column value before display.
_________________
Thanks

Sarangadhar · Posted: Mon Sep 17, 2007 1:53 pm Post subject:

Kolusu,
this works. thanks.

SELECT EMP_ID,AMOUNT,
CASE WHEN AMOUNT > 100
THEN CHAR(AMOUNT * 1)
ELSE CHAR(AMOUNT * 10)
END AS NEW_AMOUNT
FROM EMP
_________________
Thanks

jsharon1248 · Posted: Mon Sep 17, 2007 2:01 pm Post subject:

The CASE is your best bet. If you're still interested in QMF, there are a couple manuals at IBM's website that would get you going. QMF is not exactly intuitive, but it can create nice reports. There are way better tools available, but if you don't have access to anything else, any port in a storm will do...

http://www-306.ibm.com/software/data/db2/zos/v8books.html

Sarangadhar · Posted: Mon Sep 17, 2007 4:06 pm Post subject:

jsharon1248 -
My question is not related to usage of QMF. I would like to get the required output without using application programming constructs so that I can run it on QMF or SPUFI.

Hope you got mine.
_________________
Thanks