?
Solved

Oracle 9i - divide result from subselect

Posted on 2014-04-10
9
Medium Priority
?
365 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
 
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

809 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