Return Boolean Value on SQL Select Statement

Return Boolean Value on SQL Select Statement

What you have there will return no row at all if the user doesn't exist. Here's what you need:

SELECT CASE WHEN EXISTS (
    SELECT *
    FROM [User]
    WHERE UserID = 20070022
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END

SQL SELECT - Return boolean based on condition

You don't need a CASE expression

Just leverage how bit works: all non-zero values give 1 when cast to bit

SELECT
    u.Hostname,
    u.IsCustom,
    ~ CAST((u.Status - 5) AS bit) AS IsActive
from    SiteUrlMappings u

SQL - How to return Boolean value if values exists in table

I would recommend exists with a table of values. This will prevent duplicates in the result set:

SELECT v.parcel,
       (CASE WHEN EXISTS (SELECT 1
                          FROM Property p
                          WHERE p.taxyear = YEAR(GETDATE()) - 1 AND
                                p.parcel = v.parcel
                         ) 
             THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
        END) AS ExistsStatus
FROM (VALUES ('1719309002000'),
             ('1024247013000'),
             ('1024247008000'),
             ('1024247001000')
     ) v(parcel)

Return Boolean Value as TRUE or FALSE in Select (PostgreSQL/pgAdmin)

If all you want to show is the literal TRUE or FALSE, you can use the case statements like you had proposed. Since PostgreSQL treats TRUE, true, yes, on, y, t and 1 as true, I'd control how I'd want the output to look like.

Where clause can be written like:

select * from tablename where active
--or--
select * from tablename where active = true

(My recommendation is the same as PostgreSQL - use true)

When selecting, although there may be hesitation to use the case statements, I'd still recommend doing that to have control over your output string literal.

Your query would look like this:

select 
  case when active = TRUE then 'TRUE' else 'FALSE' end as active_status,
  ...other columns...
from tablename
where active = TRUE;

SQLFiddle example: http://sqlfiddle.com/#!15/4764d/1

create table test (id int, fullname varchar(100), active boolean);
insert into test values (1, 'test1', FALSE), (2, 'test2', TRUE), (3, 'test3', TRUE);

select
  id,
  fullname,
  case when active = TRUE then 'TRUE' else 'FALSE' end as active_status
from test;

| id | fullname | active_status |
|----|----------|---------------|
|  1 |    test1 |         FALSE |
|  2 |    test2 |          TRUE |
|  3 |    test3 |          TRUE |

SQL: Return true if list of records exists?

Given your updated question, these are the simplest forms:

If ProductID is unique you want

SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100)

and then check that result against 3, the number of products you're querying (this last part can be done in SQL, but it may be easier to do it in C# unless you're doing even more in SQL).

If ProductID is not unique it is

SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100)

---

When the question was thought to require returning rows when all ProductIds are present and none otherwise:

SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100))=3)

or

SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100))=3)

if you actually intend to do something with the results. Otherwise the simple SELECT 1 WHERE (SELECT ...)=3 will do as other answers have stated or implied.

How do I return a boolean value from a query during condition evaluation?

Try this.

SELECT (CASE WHEN LEN(SomeLongTextColumn) = 0 THEN 1 ELSE 0 END) AS IsEmtpy

@gbn has good explanation about how to return boolean.

SQL select subquery that returns boolean value based on case statement

I think this is what you want. But not really sure without sample expected results.

SELECT COALESCE(
         ( SELECT 'URBAN'
             FROM T2
            WHERE T1.city = T2.city
         ), 'RURAL' ) AS CITY_TYPE,
       T1.*
  FROM T1

How to create a function which return a boolean depending on a select statement result

COUNT(...) > 0 WHERE ... is equivalent to EXISTS(select ... where ...) , and
EXISTS(table expression) already yields a boolean:

EXISTS (
 SELECT *
 FROM table t
 WHERE t.FK = FK AND t.Date2 IS NULL
 )

---

Putting that into your query (Note the Date1 and Date2 arguments appear to be unused...)

CREATE FUNCTION isAlreadyTaken1(FK INT4, Date1 DATE, Date2 DATE)
  RETURNS BOOLEAN
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM table t
    WHERE t.FK = FK 
         AND t.Date2 IS NULL    -- <<-- this looks nonsensical
        );
END
$$ LANGUAGE plpgsql;

---

And, as @a_horse_with_no_name commented: you don't even need plpgsql, just plain SQL will do:

CREATE FUNCTION isAlreadyTaken1a(FK INT4, Date1 DATE, Date2 DATE)
  RETURNS BOOLEAN
AS $func$
  SELECT EXISTS (
    SELECT 1
    FROM table t
    WHERE t.FK = FK
         AND t.Date2 IS NULL    -- <<-- this looks nonsensical
        );
$func$ LANGUAGE sql;

---

BTW: I inverted the logic. IMO allreadyTaken means count() > 0.

Boolean return value of some function inside SELECT

Write a simple wrapper function to convert the PL/SQL BOOLEAN to a NUMBER data type that is valid in SQL and expected by your record type and you can move the function call outside the SQL.

CREATE FUNCTION MAP_BOOLEAN( truthy IN BOOLEAN ) RETURN NUMBER DETERMINISTIC
IS
BEGIN
  RETURN CASE truthy
         WHEN TRUE  THEN 1
         WHEN FALSE THEN 0
         ELSE NULL
         END;
END map_boolean;
/

So your specification would be:

CREATE PACKAGE package_name IS
  TYPE outrec IS RECORD(
    tw_m_id        NUMBER,
    tw_m_dealer_id NUMBER,
    tw_number      NUMBER,
    check_uid      NUMBER
  );

  TYPE outrecset IS TABLE OF outrec;

  -- Note: This may be in another package but is here for convenience.
  FUNCTION somefunction(value IN NUMBER) RETURN BOOLEAN;

  FUNCTION report
  (
    p_watermark IN NUMBER,
    p_param     IN NUMBER,
    p_index     IN NUMBER
  ) RETURN outrecset PIPELINED;
END;
/

and the corresponding body would be:

CREATE PACKAGE BODY package_name IS
  FUNCTION somefunction(value IN NUMBER) RETURN BOOLEAN
  IS
  BEGIN
    RETURN TRUE;
  END;

  PROCEDURE before_report(
    p_watermark IN NUMBER,
    p_param     IN NUMBER,
    p_index     IN NUMBER
  )
  IS
  BEGIN
    NULL;
  END;

  FUNCTION report(
    p_watermark IN NUMBER,
    p_param     IN NUMBER,
    p_index     IN NUMBER
  ) RETURN outrecset PIPELINED
  IS
    temp outrec;
  BEGIN
    before_report(
      p_watermark => p_watermark,
      p_param     => p_param,
      p_index     => p_index
    );

    FOR c_rec IN (
      SELECT tw_m_id,
             tw_m_dealer_id,
             tw_number
      FROM   table1
             JOIN table2 rk ON id1 = rk.id2
      WHERE  id1 = p_param
    )
    LOOP
      temp.tw_m_id        := c_rec.tw_m_id;
      temp.tw_m_dealer_id := c_rec.tw_m_dealer_id;
      temp.check_uid      := MAP_BOOLEAN(
                               PACKAGE_NAME.SOMEFUNCTION( c_rec.tw_number )
                             );
      PIPE ROW(temp);
    END LOOP;
  END;
END;
/

(Note: you also need to update the cursor loop as the values you were selecting did not match the fields of the record.)

db<>fiddle here

---

Related Topics