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)
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)
Rewriting my comment
What is the question. Are you asking to rewrite case statement with below?
DECODE(:UOM , 'BX' , :QTY / CA_QTY, :QTY )
DECODE(:UOM , 'BX' , :QTY / CA_QTY, :QTY )
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)
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
CASE
WHEN :UOM = 'BX'
THEN
(Select :QTY / CA_QTY)
ELSE :QTY
END as QTY
ASKER
edit made
Now returns error:
ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
Error at Line: 5 Column: 10
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)
Now returns error:
ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
Error at Line: 5 Column: 10
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
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Working Perfect !! thanks