[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Oracle 9i - divide result from subselect

Posted on 2014-04-10
9
Medium Priority
?
362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 
LVL 1

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 39992429
Change (Select :QTY / CA_QTY)
TO
(:QTY / CA_QTY)
0
 
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
 
LVL 1

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
 
LVL 1

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 2000 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
 
LVL 1

Author Closing Comment

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

650 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