Solved

Oracle 9i - divide result from subselect

Posted on 2014-04-10
9
349 Views
Last Modified: 2014-04-10
Experts,
Hopefully my poor attempt at solving this does not confuse things more

1. I am using a select to return a value 'CA_QTY'
2. If input1 (UOM) = 'BX', then divide input2 (QTY) by CA_QTY
3 If input1 (UOM) <> 'BX' then output input2 (Qty)

SELECT CASE
 WHEN :UOM = 'BX' 
 THEN
  (Select :QTY / CA_QTY)
 ELSE :QTY
FROM
(select Item_qty_Bkd_qty CA_QTY
from M_item_D1 where comp_code = 'A1' and cust_code = 'CLIFBAR'
and item_code = '120004'
and ITEM_QTY_BKD_LEV_NUM = 2)

Open in new window

0
Comment
Question by:JDCam
  • 5
  • 4
9 Comments
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 39992125
Rewriting my comment
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 39992136
What is the question. Are you asking to rewrite case statement with below?
DECODE(:UOM , 'BX' , :QTY / CA_QTY, :QTY )
0
 

Author Comment

by:JDCam
ID: 39992392
Sorry... my attempt does not work and causes error. I am looking for help to repair my syntax so it works correctly
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 39992429
Change (Select :QTY / CA_QTY)
TO
(:QTY / CA_QTY)
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 39992445
Also you didn't close CASE. Use END

CASE
 WHEN :UOM = 'BX'
 THEN
  (Select :QTY / CA_QTY)
 ELSE :QTY
END as QTY
0
 

Author Comment

by:JDCam
ID: 39992473
edit made
SELECT CASE
 WHEN :UOM = 'BX' 
 THEN
  (:QTY / CA_QTY)
 ELSE :QTY
FROM
(select Item_qty_Bkd_qty CA_QTY
from M_item_D1 where comp_code = 'A1' and cust_code = 'CLIFBAR'
and item_code = '120004'
and ITEM_QTY_BKD_LEV_NUM = 2)

Open in new window


Now returns error:
ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:
Error at Line: 5 Column: 10
0
 

Author Comment

by:JDCam
ID: 39992503
END has been added.  New error

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

I added TO_NUMBER on the bottom select, and confirmed that it returns a value of 16
Same error continues

SELECT CASE
 WHEN :UOM = 'BX' 
 THEN
  (:QTY/CA_QTY)
 ELSE :QTY
 END as QTY
FROM
(select TO_NUMBER(Item_qty_Bkd_qty) CA_QTY
from M_item_D1 where comp_code = 'A1' and cust_code = 'CLIFBAR'
and item_code = '120004'
and ITEM_QTY_BKD_LEV_NUM = 2)

Open in new window

0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 500 total points
ID: 39992529
I belive problem could be in line 4. Did you see line number if you have run from Sqlplus.
Please use

CASE
 WHEN :UOM = 'BX'
 THEN
  TO_CHAR( TO_NUMBER(:QTY)/TO_NUMBER(CA_QTY))
 ELSE TO_CHAR(:QTY)
 END as QTY
0
 

Author Closing Comment

by:JDCam
ID: 39992547
Working Perfect !! thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now