INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL calculation

SQL calculation

(OP)
Hello, I have an SQL statements that uses 3 fiels

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

RE: SQL calculation

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 . . . .

RE: SQL calculation

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

RE: SQL calculation

(OP)
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) 

RE: SQL calculation

Is this working as you want now?

RE: SQL calculation

(OP)
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, 

RE: SQL calculation

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

Ties Blom

RE: SQL calculation

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

RE: SQL calculation

(OP)
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


RE: SQL calculation

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

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!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close