?
Solved

Oracle 9i - divide result from subselect

Posted on 2014-04-10
9
Medium Priority
?
360 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 recover a database from a user managed backup
Suggested Courses

764 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