INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...Many thanks to you for putting it together and to the forum members for taking the time to post their replies and give their time to help others. Their isn't another site that can touch it..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > IBM: DB2 Forum

SQL calculation

CODE

(case when TBC='P' then 1/SRLHU end)as pcsper

This works well but if I want to multiply the pcsper times the quantity it sometimes does the calculations and it sometimes does not. here is the code I am using

CODE

((case when TBC='P' then 1/SRLHU end)*TQCTD)as STDHrs

the above gives me the following

CODE

TBC    pcsper     SRLHU      TQCTD     STDHrs
P      .00200      500.00      2610     
P      .00238      419.99      98      .2333
P      .00294      339.99      1936

Not sure why the sql statement won't work with all the rows. kind of stomp on tis one.

any help is much appreciated

papadba (MIS)

24 Jul 12 21:23

Is there some reason you have posted only a bit of the code? And possibly not the bit of code that is not performing as expected?

How did pcsper get onto the line with the other values? The posted code would not do this . . . .

MarcLodge (Programmer)

26 Jul 12 5:08

Hi EVR72,
I think, like papadba suggests, we'd have to see the full code. I wondered whether you were exceeding the results column's capacity by the arithmetic but as the TQCTD column has no decimal place differences between the values, I'm not sure that's the answer. Also, based on your data, I knocked up the following SQL which mimics what you are doing, and this produces 3 values quite happily.

CODE

SELECT
(CASE WHEN 'P' = 'P' THEN 1/500.00 END)* 2610
FROM SYSIBM.SYSDUMMY1
UNION
SELECT
(CASE WHEN 'P' = 'P' THEN 1/419.99 END)* 98
FROM SYSIBM.SYSDUMMY1
UNION
SELECT
(CASE WHEN 'P' = 'P' THEN 1/339.99 END)* 1936
FROM SYSIBM.SYSDUMMY1

Let us have a look at the actual code that is producing these reuslts.
Marc

evr72 (MIS)

9 Aug 12 9:39

Sorry about that I have been a bit busy and did not get a chance to get back.

here is the code I am using

CODE

SELECT
ORDNO,
OPSEQ,
TBCDE,
SRLHU,
(CASE WHEN TBCDE = 'P' THEN 1/SRLHU WHEN TBCDE = '3' THEN SRLHU/1000 WHEN TBCDE = '4' THEN SRLHU/10000 END)as pcsper,
(CASE WHEN TBCDE = 'P' THEN 1/SRLHU WHEN TBCDE = '3' THEN SRLHU/1000 WHEN TBCDE = '4' THEN SRLHU/10000 END)*TQCTD AS StdHrs,
TQCTD,
SCRAP,
RLHTD,
OPDSC,
RLCTD,
OVCTD,
DPTNO,
SRLAB,
SOVER,
CLDT
FROM DAVLOGIC.XAHRTG01
WHERE (CLDT >1120101)

papadba (MIS)

9 Aug 12 11:50

Is this working as you want now?

evr72 (MIS)

9 Aug 12 16:41

no, it is still not working, if I do my case statements separate it gives me the right information, so I thought I would do

CODE

(CASE WHEN TBCDE = 'P' THEN 1/SRLHU end)((CASE WHEN TBCDE = '3' THEN SRLHU/1000 end)(CASE WHEN TBCDE = '4' THEN SRLHU/10000 end)as pcsper

but that gives me the same result as

CODE

(CASE WHEN TBCDE = 'P' THEN 1/SRLHU WHEN TBCDE = '3' THEN SRLHU/1000 WHEN TBCDE = '4' THEN SRLHU/10000 END)as pcsper,

blom0344 (TechnicalUser)

10 Aug 12 9:51

What happens when you use TQCTD within the case instead of as a factor outside of it?

Ties Blom

LarrySteele (Programmer)

10 Aug 12 10:52

I would have written the case statement like this:

CODE

CASE TBCDE
     WHEN 'P' THEN (1/SRLHU) * TQCTD 
     WHEN '3' THEN (SRLHU/1000) * TQCTD 
     WHEN '4' THEN (SRLHU/10000) * TQCTD
     END AS StdHrs,

HTH,
Larry

evr72 (MIS)

14 Aug 12 10:08

LarrySteele,

I did try that and get the same results as in the original Case statement.
I thought that the TBCDE field might be an alpha field but when I multiply, divide, subtract or add to the field it works fine, if I do the case statements by themselves it also works but then when I try to add them it does not. see this example

CODE

(CASE TBCDE WHEN 'P' THEN (1/SRLHU) * TQCTD end)as pcsp,
(CASE TBCDE WHEN '3' THEN (SRLHU/1000) * TQCTD end)as pcs3,
(CASE TBCDE WHEN 'P' THEN (SRLHU/10000) * TQCTD end)as pcs4

this works well
but if I try to do this

CODE

((CASE TBCDE WHEN 'P' THEN (1/SRLHU) * TQCTD end)+
(CASE TBCDE WHEN '3' THEN (SRLHU/1000) * TQCTD end)+
(CASE TBCDE WHEN 'P' THEN (SRLHU/10000) * TQCTD end))as pcsper

this does not work

blom0344 (TechnicalUser)

14 Aug 12 11:17

You can only make additions with true values. The case may evaluate to null, so my bet would be to wrap each part in a coalesce to make sure the returned value is numerical

Ties Blom

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To IBM: DB2

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

SQL calculation

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!