SQL calculation 1

[evr72]

Hello, I have an SQL statements that uses 3 fiels

(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

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

the above gives me the following

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]

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]

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.

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]

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

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]

Is this working as you want now?

 

[evr72]

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

(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

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

 

[blom0344]

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

Ties Blom

 

[LarrySteele]

I would have written the case statement like this:

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]

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

(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

((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]

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