Home » SQL & PL/SQL » SQL & PL/SQL » How to print Boolean or True/False value in Sql (Oracle 12c, Windows)
How to print Boolean or True/False value in Sql [message #668653] Wed, 07 March 2018 21:58 Go to next message
akssre
Messages: 26
Registered: March 2018
Junior Member
Dear All,

Good Morning!

Just started the journey... I hope I will learn more and more with your help and assistance.

Is there any way to find out the availability of employee present in a table with Status 'Yes' or 'No' with a sql statement. Following is the workflow I tried.
SQL> create table employee (name varchar2 (20));
Table created
SQL>  insert into employee values ('JOHN');
1 row inserted
SQL>  insert into employee values ('SCOTT');
1 row inserted
SQL>  insert into employee values ('CHARLES');
1 row inserted

SQL> select * from employee;

NAME
--------------------
JOHN
SCOTT
CHARLES

SQL> SELECT emp.NAME, estat.status
      from (SELECT NAME,
                   (CASE
                     WHEN NAME IN ('JOHN', 'MICHAEL') THEN
                      'Yes'
                     ELSE
                      'No'
                   END) status
              from employee) estat,
          employee emp
    where emp.name = estat.name
      and status = 'Yes';
	  
NAME                 STATUS
-------------------- ------
JOHN                 Yes
But I want result like following:-
NAME                 STATUS
-------------------- ------
JOHN                 Yes
Michael               No
Best Regards,
AKS



[Edit MC: add code tags]

[Updated on: Wed, 07 March 2018 23:36] by Moderator

Report message to a moderator

Re: How to print Boolean or True/False value in Sql [message #668655 is a reply to message #668653] Wed, 07 March 2018 23:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
See how your post is now I formatted it.
Indent the code, use code tags and align the columns in result.
Format your SQL, if you don't know how to do it, learn it using SQL Formatter.

Quote:
But I want result like following:-

Just move the CASE expression in the SELECT part:
SELECT emp.name, CASE ...
FROM employee emp
Re: How to print Boolean or True/False value in Sql [message #668660 is a reply to message #668655] Thu, 08 March 2018 01:35 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure you can get MICHAEL in the output - at least, not this way. There's no MICHAEL in the table, and having int only as an element in the IN list isn't enough. You'd need another source so that you could compare it (hint: outer join) with current EMPLOYEE table contents. Something like this:

SQL> with t_source (name) as
  2    (select 'JOHN'    from dual union
  3     select 'MICHAEL' from dual
  4    )
  5  select t.name,
  6         decode(e.rowid, null, 'No', 'Yes') status
  7  from t_source t left join employee e on t.name = e.name;

NAME    STATUS
------- ------
JOHN    Yes
MICHAEL No

SQL>
Re: How to print Boolean or True/False value in Sql [message #668670 is a reply to message #668660] Thu, 08 March 2018 09:00 Go to previous messageGo to next message
akssre
Messages: 26
Registered: March 2018
Junior Member


Dear Littlefoot,

Thank you for your response.

That is exactly the problem, i do not have a source to compare. I tried before reaching out to community.

And yes with your response problem is partially resolved but it is not feasible to add and union for all the employees. It might be 10 or 100.

I am not sure how to resolve this. Though many thanks to all experts response.

Best Regards,
AKS

[Updated on: Thu, 08 March 2018 09:01]

Report message to a moderator

Re: How to print Boolean or True/False value in Sql [message #668671 is a reply to message #668670] Thu, 08 March 2018 10:38 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Even without any "source to compare", you have to put or state those values somewhere. They were present in the IN clause of the initial code anyway.
The best option would be creating a real table and fill it with the required values.
For more concise notation, you may use TABLE collection expression, something like
with t_source (name) as ( select column_value
  from table( sys.odcivarchar2list( 'JOHN', 'MICHAEL' ) )
  )
select ...
Re: How to print Boolean or True/False value in Sql [message #668673 is a reply to message #668670] Thu, 08 March 2018 13:08 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
akssre wrote on Thu, 08 March 2018 10:00

And yes with your response problem is partially resolved but it is not feasible to add and union for all the employees. It might be 10 or 100.
The union in Littlefoot's answer is just to simulate a table of data. You can just (outer) join to the distinct names from your real table.

I am still confused by your post though because it looks like if the name is John or Michael, then you want a YES. Further, Michael is not the same as MICHAEL.
Re: How to print Boolean or True/False value in Sql [message #668676 is a reply to message #668673] Fri, 09 March 2018 02:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The OP is saying they don't have a real table.
Re: How to print Boolean or True/False value in Sql [message #668677 is a reply to message #668676] Fri, 09 March 2018 03:26 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OP

I do not have a source to compare (...) it is not feasible to add union for all the employees. It might be 10 or 100.
Is it a real situation? I mean, what kind of a company is it, that you don't know who are the employees? Whether 10 or 100 people work there, they must be known.
Re: How to print Boolean or True/False value in Sql [message #668690 is a reply to message #668676] Fri, 09 March 2018 08:04 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Fri, 09 March 2018 03:56
The OP is saying they don't have a real table.
Ah, I missed that one.
Re: How to print Boolean or True/False value in Sql [message #668696 is a reply to message #668677] Fri, 09 March 2018 23:40 Go to previous messageGo to next message
akssre
Messages: 26
Registered: March 2018
Junior Member


Dear Littlefoot,

This is a real situation. I agree, it does not fit in HR data model perspective.

I have just given example from HR to make things simple.

Best Regards,
AKS

Re: How to print Boolean or True/False value in Sql [message #672918 is a reply to message #668696] Wed, 31 October 2018 23:20 Go to previous messageGo to next message
Nirali
Messages: 1
Registered: October 2018
Junior Member
By following way you can print boolean or True/false value in SQL-DBA:

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 l_test BOOLEAN:=TRUE;
3 i boolean;
4 BEGIN
5 dbms_output.put_line(l_test);
6* END;
SQL> /
dbms_output.put_line(l_test);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 5, column 1:
PL/SQL:
Re: How to print Boolean or True/False value in Sql [message #672921 is a reply to message #672918] Thu, 01 November 2018 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

SQL does not know BOOLEAN datatype.
The simplest way is to use:
SQL> declare
  2    l_test boolean;
  3  begin
  4    l_test := true;
  5    dbms_output.put_line(case when l_test then 'TRUE' else 'FALSE' end);
  6    l_test := false;
  7    dbms_output.put_line(case when l_test then 'TRUE' else 'FALSE' end);
  8  end;
  9  /
TRUE
FALSE

PL/SQL procedure successfully completed.
You can also use a function to convert your boolean:
SQL> declare
  2    l_test boolean;
  3    function tochar(p in boolean) return varchar2 is
  4    begin
  5      return case when l_test then 'TRUE' else 'FALSE' end;
  6    end;
  7  begin
  8    l_test := true;
  9    dbms_output.put_line(tochar(l_test));
 10    l_test := false;
 11    dbms_output.put_line(tochar(l_test));
 12  end;
 13  /
TRUE
FALSE

PL/SQL procedure successfully completed.
You can also use a predefined function:
SQL> begin
  2  dbms_output.put_line(sys.diutil.bool_to_int(true));
  3  dbms_output.put_line(sys.diutil.bool_to_int(false));
  4  end;
  5  /
1
0

PL/SQL procedure successfully completed.

[Updated on: Thu, 01 November 2018 02:11]

Report message to a moderator

Re: How to print Boolean or True/False value in Sql [message #672923 is a reply to message #672921] Thu, 01 November 2018 02:49 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
The ANSI SQL standard says that BOOLEAN may be true, false, or null. I just checked the diutil function (I didn't know about it, thank you) and it does conform to that standard:
pdby1> set serverout on
pdby1> exec dbms_output.put_line(sys.diutil.bool_to_int(true))
1

PL/SQL procedure successfully completed.

pdby1> exec dbms_output.put_line(sys.diutil.bool_to_int(false))
0

PL/SQL procedure successfully completed.

pdby1> exec dbms_output.put_line(sys.diutil.bool_to_int(null))

PL/SQL procedure successfully completed.

pdby1>
pdby1>
pdby1> select * from dual where sys.diutil.bool_to_int(null) is null;

D
-
X

pdby1>
but your first examples do not, they do what any sane developer would do: equate NULL with FALSE. Perhaps the fact that 99 out of a 100 people disagree with ANSI and think that a NULL should be FALSE is why most database publishers have not implemented the BOOLEAN data type. ANSI does say that it is optional.
Re: How to print Boolean or True/False value in Sql [message #672924 is a reply to message #672923] Thu, 01 November 2018 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I agree, no problem to extend my example:
SQL> declare
  2    l_test boolean;
  3    function tochar(p in boolean) return varchar2 is
  4    begin
  5      return case when l_test then 'TRUE' when not l_test then 'FALSE' else null end;
  6    end;
  7  begin
  8    l_test := true;
  9    dbms_output.put_line('l_test is '||tochar(l_test));
 10    l_test := false;
 11    dbms_output.put_line('l_test is '||tochar(l_test));
 12    l_test := null;
 13    dbms_output.put_line('l_test is '||tochar(l_test));
 14  end;
 15  /
l_test is TRUE
l_test is FALSE
l_test is

PL/SQL procedure successfully completed.
Re: How to print Boolean or True/False value in Sql [message #672929 is a reply to message #672918] Thu, 01 November 2018 04:34 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Nirali wrote on Thu, 01 November 2018 04:20
By following way you can print boolean or True/false value in SQL-DBA:

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 l_test BOOLEAN:=TRUE;
3 i boolean;
4 BEGIN
5 dbms_output.put_line(l_test);
6* END;
SQL> /
dbms_output.put_line(l_test);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 5, column 1:
PL/SQL:
You say this is how to do it, then print code that errors out.
That's just weird.
Re: How to print Boolean or True/False value in Sql [message #672959 is a reply to message #672929] Thu, 01 November 2018 08:29 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Thu, 01 November 2018 05:34

You say this is how to do it, then print code that errors out.
That's just weird.
My thoughts exactly. You beat me to it due to your timezone.

[Updated on: Thu, 01 November 2018 08:30]

Report message to a moderator

Previous Topic: How to allow multi-users in Procedure
Next Topic: DBMS_LOB.SUBSTR
Goto Forum:
  


Current Time: Thu Mar 28 17:21:33 CDT 2024