1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Trying to set a boolean value

Discussion in 'SQL PL/SQL' started by dsimpson, May 27, 2016.

  1. dsimpson

    dsimpson Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Tucson, AZ
    Can anyone tell me why the first select works but the second one fails? I'm new to PL/SQL.

    Code (SQL):

    DECLARE
        p_group varchar2(200) := 'TEST1';
        p_value varchar2(200) := 'val';
        valueExists INT := 0;
        rtn BOOLEAN := FALSE;
     
    BEGIN

        SELECT
            CASE WHEN (COUNT(*) > 0) THEN 1 ELSE 0 END CASE
        INTO valueExists
        FROM masterv72.CODE_VALUE_LIST
            WHERE group_name = p_group
            AND VALUE = p_value;

        SELECT
            CASE WHEN (COUNT(*) > 0) THEN TRUE ELSE FALSE END CASE
        INTO rtn
        FROM masterv72.CODE_VALUE_LIST
            WHERE group_name = p_group
            AND VALUE = p_value;
     
    END;
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Run the second select outside of PL/SQL (remove the INTO line) and see what is returned. It won't be what you expect. You can SET Boolean values to TRUE or FALSE but you can't return Boolean values from a select.
     
  3. dsimpson

    dsimpson Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Tucson, AZ
    Thank you for the reply. So in order to return a boolean from a function, am I required to do something like:

    Code (SQL):

    DECLARE
        p_group varchar2(200) := 'TEST1';
        p_value varchar2(200) := 'val';
        valueExists INT := 0;
        rtn BOOLEAN := FALSE;
    BEGIN

        SELECT COUNT(*) INTO valueExists
        FROM masterv72.CODE_VALUE_LIST
            WHERE group_name = p_group
            AND VALUE = p_value;

        IF valueExists > 0 THEN
             rtn :+ TRUE;
        END IF;
    END;
     
     
  4. dsimpson

    dsimpson Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Tucson, AZ
    Thank you for the reply. So in order to return a boolean from a function, am I required to do something like:

    Code (SQL):

    DECLARE
        p_group varchar2(200) := 'TEST1';
        p_value varchar2(200) := 'val';
        valueExists INT := 0;
        rtn BOOLEAN := FALSE;
    BEGIN

        SELECT COUNT(*) INTO valueExists
        FROM masterv72.CODE_VALUE_LIST
            WHERE group_name = p_group
            AND VALUE = p_value;

        IF valueExists > 0 THEN
             rtn := TRUE;
        END IF;
    END;
     
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Constant/variable of type "BOOLEAN" only in PL/Sql

    for example...
    Code (SQL):
    DECLARE
       FUNCTION is_row_present (p_tab_name all_tables.table_name%TYPE)
       RETURN BOOLEAN
       IS
           l_cnt NUMBER;
       BEGIN
            SELECT COUNT(*) INTO l_cnt FROM  all_tables WHERE TABLE_NAME = p_tab_name;
            RETURN CASE l_cnt WHEN 0 THEN FALSE ELSE TRUE END ;
       END;
    BEGIN
        dbms_output.put_line('Record '||CASE WHEN NOT is_row_present('DUAL') THEN ' not ' END || 'found');
    END;
    /
     
  6. dsimpson

    dsimpson Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Tucson, AZ
    That is crystal clear, thank you so much.