Home » SQL & PL/SQL » SQL & PL/SQL » SQL query not calculating
SQL query not calculating [message #252099] Tue, 17 July 2007 19:28 Go to next message
vamcs
Messages: 20
Registered: June 2007
Location: somerset
Junior Member

SQL query not calculating
gross_amount is giving the value of extended_amount and the gross_price_rc is giving the value of unit_selling_price

select customer_trx_line_id,
( extended_amount / 1- (decode(decode(attribute6,null,0)+decode(attribute7,null,0)+decode(attribute8,null,0)+decode(attribute9,null,0)/100,null,0,1,0))) GROSS_AMOUNT,
( unit_selling_price /1-(decode(decode(attribute6,null,0)+decode(attribute7,null,0)+decode(attribute8,null,0)+decode(attribute9,null,0)/100,null,0,1,0))) gross_price_rc
from ra_customer_trx_lines_all
where attribute6 is not null or attribute7 is not null or attribute8 is not null or attribute9 is not null

whats the isuue here??

Thanks
Re: SQL query not calculating [message #252100 is a reply to message #252099] Tue, 17 July 2007 19:34 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Please read the sticky on how to format your post.
Re: SQL query not calculating [message #252103 is a reply to message #252099] Tue, 17 July 2007 20:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>whats the isuue here??
You don't post enough details for anyone to help you.

We have NO idea what the input data is or even what the results are.

How/why do you expect any actual help when you withold needed details that are CLEARLY listed in the STICKY post which you refuse to follow.

You're On Your Own (YOYO)!
Re: SQL query not calculating [message #252934 is a reply to message #252103] Fri, 20 July 2007 14:04 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Oh well, it's obviously apps/EBS, so while you're on your own (or not), some tips:

Most of your decodes do the same as NVL, so it might be easier to use that, like:
SELECT customer_trx_line_id
      ,(extended_amount / 1 - (decode(nvl(attribute6
                                      ,0) + nvl(attribute7
                                               ,0) +
                                   nvl(attribute8
                                      ,0) + nvl(attribute9
                                               ,0) / 100
                                  ,null
                                  ,0
                                  ,1
                                  ,0))) gross_amount
      ,(unit_selling_price / 1 - (decode(nvl(attribute6
                                         ,0) + nvl(attribute7
                                                  ,0) +
                                      nvl(attribute8
                                         ,0) + nvl(attribute9
                                                  ,0) / 100
                                     ,null
                                     ,0
                                     ,1
                                     ,0))) gross_price_rc
FROM   ra_customer_trx_lines_all
WHERE  attribute6 IS NOT NULL
OR     attribute7 IS NOT NULL
OR     attribute8 IS NOT NULL
OR     attribute9 IS NOT NULL


Furthermore, please note that attributes always are varchar2 columns, so you're trusting the implicit conversion to work in your code, not a very good habit to pick up, so I suggest to use to_number around them.

Last one (and probably causing your problem): the last two remaining decodes in the code result in:
decode(some_value,NULL
                       ,0
                       ,1
                       ,0)

So in English: if some_value is null then 0, if that some_value is 1 then 0 (and do nothing in all other cases), so no matter what the value is, this always ends up in 0. Probably not what you meant, right?

Look up NVL and decode in the SQL reference.

Hope this helps.
Previous Topic: to insert a record thru view
Next Topic: selecting user defined records into ref cursor
Goto Forum:
  


Current Time: Sun Dec 11 04:17:06 CST 2016

Total time taken to generate the page: 0.09778 seconds