• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

Case statement from numeric to character

I am looking for a way to implement

select
case when (ord - (select sum(shipping) from shipping where item = 'SOME ITEM')) >= (0) 
THEN (select sum(shipping) from shipping where item = 'SOME ITEM') ELSE 'ERROR' END AS "QTY"
from recinc
where IN_ITEM = 'SOME ITEM';//

Open in new window


if I replace ERROR with a number, i the query works, right now im getting error


ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 3 Column: 97
0
futureDBA
Asked:
futureDBA
  • 4
  • 3
  • 2
  • +2
1 Solution
 
slightwv (䄆 Netminder) Commented:
explicit data conversions:

select to_char(sum(shipping)) ... else 'ERROR'
0
 
HainKurtSr. System AnalystCommented:
or try:

ELSE 'ERROR'
>>>
ELSE -1

(assuming it cannot be negative) and handle -1 in your app/report
0
 
awking00Commented:
select
case when (ord - (select sum(shipping) from shipping where item = 'SOME ITEM')) >= 0
THEN (select to_char(sum(shipping)) from shipping where item = 'SOME ITEM') ELSE 'ERROR' END AS "QTY"
from recinc
where IN_ITEM = 'SOME ITEM';//
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
awking00,

Isn't that what my post says?
0
 
awking00Commented:
slightwv,
Sorry, I didn't even see your post.
0
 
futureDBAAuthor Commented:
still not working for me, right now I am at


select
  CASE WHEN ORD - (select to_char(sum(shipping))from shipping where item = 'SOME ITEM') >= (0) 
  THEN  ORD - (select to_char(sum(shipping))from shipping where item = 'SOME ITEM')
  ELSE 'ERROR' END AS "QTY"
from 
recinc
where in_item = 'SOME ITEM';//

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
My mistake...

You need the result of the CASE to be a char:

select
  CASE WHEN ORD - (select to_char(sum(shipping))from shipping where item = 'SOME ITEM') >= (0) 
  THEN  to_char(ORD - (select sum(shipping)from shipping where item = 'SOME ITEM'))
  ELSE 'ERROR' END AS "QTY"
from 
recinc
where in_item = 'SOME ITEM';// 

Open in new window

0
 
futureDBAAuthor Commented:
after i added the "ord -"

it stopped working, if i take it away, it works. but i need the THEN portion to do

ORD - (select to_char(sum(shipping))from shipping where item = 'SOME ITEM')
0
 
futureDBAAuthor Commented:
worked, thank you
0
 
sdstuberCommented:
I know the question is closed, but just to provide a little explanation


>>> after i added the "ord -"


that was the problem.  When you introduced arithmetic you forced the string to be converted back into a number.
0
 
futureDBAAuthor Commented:
Thanks for clarifying, has come in handy this morning
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now