-1

I had a next query, which doesn't work:

SELECT case BasePak.SomeFunction(333,4,5555) when true then 1 else 0 end 
               from dual

BasePak.SomeFunction(333,4,5555) returns boolean value. How to convert result(true, false) to number or string?

I tried cast function but not successful.

Is there any way to convert boolean to int or string?

Horman Lewis
  • 346
  • 2
  • 5
  • 13
  • What string do you want to convert to? – A Nice Guy Apr 25 '14 at 10:10
  • something like SELECT case BasePak.SomeFunction(333,4,5555) when 1 then "1" else "0" end from dual – A Nice Guy Apr 25 '14 at 10:11
  • If you use double quotes then it's an identifier @Arnab, i.e. a table/column etc. Use single quotes for strings, though these are numbers so there's no point having quotes at all. – Ben Apr 25 '14 at 10:55

2 Answers2

0

SQL does not have boolean datatype, but PL/SQL does. In PL/SQL you have more options. You can:

  • write functions so they don't return boolean, but the type you need.
  • if you can't edit the functions, you can write a wrapper function, that does the conversion

  • or you can directly use code like this:

    string_var := CASE
WHEN  boolean_var
THEN  'True'
WHEN  NOT boolean_var  -- or ELSE, depending on what you want
THEN  'False'
END;

In your case, I would either change the original function or write a wrapper function and use it directly from SQL code.

Michal Krasny
  • 5,434
  • 7
  • 36
  • 64
-1

As far as i know SQL doesn't contain a Boolean datatype. A Bit is generally used to represent true (1) or false (0). Are you sure your function is not returning a NULL value as this needs to be handled differently

Try this instead:

SELECT case when BasePak.SomeFunction(333,4,5555) = 1 then 'true' else 'False' end 
from dual
DLR
  • 796
  • 1
  • 6
  • 12
  • If the function already returns a number then there's no need for the CASE statement... – Ben Apr 25 '14 at 10:52
  • Your suggestion isn't significantly different than the original sample query posted, which the author said did NOT work. – StewS2 Apr 08 '16 at 20:06