Link to home
Start Free TrialLog in
Avatar of JDCam
JDCam

asked on

Oracle 9i - divide result from subselect

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

Avatar of Devinder Singh Virdi
Devinder Singh Virdi
Flag of United States of America image

Rewriting my comment
What is the question. Are you asking to rewrite case statement with below?
DECODE(:UOM , 'BX' , :QTY / CA_QTY, :QTY )
Avatar of JDCam
JDCam

ASKER

Sorry... my attempt does not work and causes error. I am looking for help to repair my syntax so it works correctly
Change (Select :QTY / CA_QTY)
TO
(:QTY / CA_QTY)
Also you didn't close CASE. Use END

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

ASKER

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
Avatar of JDCam

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Devinder Singh Virdi
Devinder Singh Virdi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JDCam

ASKER

Working Perfect !! thanks