Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2013
    Posts
    7

    Help with oracle query using null values

    Hi all,

    I have the following query which is running fine:

    select *
    from MyTable
    where Column1 = 'A' and
    Column2 = 'B' and
    Column3 = 'C' and
    Column4 is null and
    Column5 is null and
    Column6 = 3;

    It returns one row. But when I convert the above into the following:

    select *
    from MyTable
    where Column1 = 'A' and
    Column2 = 'B' and
    Column3 = 'C' and
    Column4 = null and
    Column5 = null and
    Column6 = 3;

    Its returning nothing. How can I make it work The second way? I actually need to pass the values for the colums through variables and place the same query into a stored procedure. How can I make this work.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,627
    ANYTHING = NULL is always FALSE
    even NULL = NULL is FALSE.

    do not use equal sign & NULL together.

    AND (COLUMN4 = VAL1 OR VAL1 IS NULL)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Feb 2013
    Posts
    7
    Quote Originally Posted by anacedent View Post
    ANYTHING = NULL is always FALSE
    even NULL = NULL is FALSE.

    do not use equal sign & NULL together.

    AND (COLUMN4 = VAL1 OR VAL1 IS NULL)
    I can see what you mean. But, if I use OR condition, it returns the rows with both VAL1 or NULL in the column. Let me make my self more clear. Below is the procedure I am trying to achieve:

    create or replace
    procedure MyProcedure
    (val1 in varchar2 default null,
    val2 in varchar2 default null,
    val3 in varchar2 default null,
    val4 in varchar2 default null,
    val5 in varchar2 default null,
    val6 in varchar2 default null,
    val7 in varchar2 default null,
    val8 in varchar2 default null,
    cCount in number,
    DataSet OUT Types.cursor_type)
    as
    begin
    OPEN DataSet FOR
    select CID
    from MyTable
    where Column_1 = val1 and
    Column_2 = val2 and
    Column_3 = val3 and
    Column_4 = val4 and
    Column_5 = val5 and
    Column_6 = val6 and
    Column_7 = val7 and
    Column_8 = val8 and
    Column_9 = Count;
    end;

    The above procedure should always return one row. All parameters will not have values all the time. They can have nulls as well. I want to make this work with nulls.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,627
    we don't have your tables
    we don't have your data
    we don't have your requirements

    what EXACTLY determines which single row is returned?
    SELECT can return 0, 1, or more than 1 row; depending upon WHERE clause & data in table
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  5. #5
    Join Date
    Feb 2013
    Posts
    7
    Quote Originally Posted by anacedent View Post
    we don't have your tables
    we don't have your data
    we don't have your requirements

    what EXACTLY determines which single row is returned?
    SELECT can return 0, 1, or more than 1 row; depending upon WHERE clause & data in table
    Here you go. I created dummy data for you:

    My Table:

    COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 COLUMN7 COLUMN8 ID
    A B C 3 1
    A B C D E F G 7 2

    Below is my procedure:

    create or replace
    procedure GetData
    (POS1 in varchar2 default null,
    POS2 in varchar2 default null,
    POS3 in varchar2 default null,
    POS4 in varchar2 default null,
    POS5 in varchar2 default null,
    POS6 in varchar2 default null,
    POS7 in varchar2 default null,
    POS8 in number)
    as
    cid number(9,0);
    begin
    select MYTESTTABLE.id into cid
    from MYTESTTABLE
    where COLUMN1 = POS1 and
    COLUMN2 = POS2 and
    COLUMN3 = POS3 and
    COLUMN4 = POS4 and
    COLUMN5 = POS5 and
    COLUMN6 = POS6 and
    COLUMN7 = POS7 and
    COLUMN8 = POS8;
    DBMS_OUTPUT.PUT_LINE(CID);
    end;


    When I run the above procedure as follows:

    DECLARE
    POS1 VARCHAR2(200);
    POS2 VARCHAR2(200);
    POS3 VARCHAR2(200);
    POS4 VARCHAR2(200);
    POS5 VARCHAR2(200);
    POS6 VARCHAR2(200);
    POS7 VARCHAR2(200);
    POS8 NUMBER;
    begin
    POS1 := 'A';
    POS2 := 'B';
    POS3 := 'C';
    POS4 := null;
    POS5 := null;
    POS6 := null;
    POS7 := null;
    POS8 := 3;

    GETDATA(
    POS1 => POS1,
    POS2 => POS2,
    POS3 => POS3,
    POS4 => POS4,
    POS5 => POS5,
    POS6 => POS6,
    POS7 => POS7,
    POS8 => POS8
    );
    END;

    it is returning an exception saying no data found. I am expecting CID to be 1 printed out. How can I do that?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    Unformatted code, as well as sample data you provided, is awfully difficult to read. Please, have a look at CODE tags and try to provide another sample data; because, I have no idea which columns are NULL.

    Anyway: would a WHERE clause that uses NVL function - as follows - do any good?
    Code:
    select MYTESTTABLE.id 
    into cid
    from MYTESTTABLE
    where COLUMN1 = nvl(POS1, column1) and
      COLUMN2 = nvl(POS2, column2) and
      COLUMN3 = nvl(POS3, column3) and
      etc.;

  7. #7
    Join Date
    Feb 2013
    Posts
    7
    Quote Originally Posted by Littlefoot View Post
    Unformatted code, as well as sample data you provided, is awfully difficult to read. Please, have a look at CODE tags and try to provide another sample data; because, I have no idea which columns are NULL.

    Anyway: would a WHERE clause that uses NVL function - as follows - do any good?
    Code:
    select MYTESTTABLE.id 
    into cid
    from MYTESTTABLE
    where COLUMN1 = nvl(POS1, column1) and
      COLUMN2 = nvl(POS2, column2) and
      COLUMN3 = nvl(POS3, column3) and
      etc.;
    Pardon my ignorance. I am new to this forum and so I have no idea how to format my text. Here you go again:

    No code has to be inserted here.
    A B C 3 1
    A B C D E F G 7 2

    I am sorry but I am not able to format table, but here are the details:

    Row1:
    Column1 = A
    Column2 = B
    Column3 = C
    Column4 = null
    Column5 = null
    Column6 = null
    Column7 = null
    Column8 = 3
    ID = 1

    Row2
    Column1 = A
    Column2 = B
    Column3 = C
    Column4 = D
    Column5 = E
    Column6 = F
    Column7 = G
    Column8 = 7
    ID = 2

    My Procedure:

    Code:
    create or replace procedure GetData
    (POS1 in varchar2 default null,
    POS2 in varchar2 default null,
    POS3 in varchar2 default null,
    POS4 in varchar2 default null,
    POS5 in varchar2 default null,
    POS6 in varchar2 default null,
    POS7 in varchar2 default null,
    POS8 in number)
    as
    cid number(9,0);
    begin
    	select MYTESTTABLE.id into cid
    	from MYTESTTABLE
    	where COLUMN1 = POS1 and
    	COLUMN2 = POS2 and
    	COLUMN3 = POS3 and
    	COLUMN4 = POS4 and
    	COLUMN5 = POS5 and
    	COLUMN6 = POS6 and
    	COLUMN7 = POS7 and
    	COLUMN8 = POS8;
    	DBMS_OUTPUT.PUT_LINE(CID); 
    end;

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    Just as you used [code] tags for the procedure, you should have done the same with sample data. [code] tags would preserve formatting.

    Did you try to apply my suggestion to your procedure?

  9. #9
    Join Date
    Feb 2013
    Posts
    7
    Quote Originally Posted by Littlefoot View Post
    Just as you used [code] tags for the procedure, you should have done the same with sample data. [code] tags would preserve formatting.

    Did you try to apply my suggestion to your procedure?
    I tried as below:

    Code:
    create or replace
    procedure GetData
    (POS1 in varchar2 default null,
     POS2 in varchar2 default null,
     POS3 in varchar2 default null,
     POS4 in varchar2 default null,
     POS5 in varchar2 default null,
     POS6 in varchar2 default null,
     POS7 in varchar2 default null,
     POS8 in number)
    as
    cid number(9,0);
    begin
    select MYTESTTABLE.id into cid
    from MYTESTTABLE
    where COLUMN1 = nvl(POS1,COLUMN1) and
          COLUMN2 = nvl(POS2,COLUMN2) and
          COLUMN3 = NVL(POS3,COLUMN3) and
          COLUMN4 = nvl(POS4,COLUMN4) and
          COLUMN5 = nvl(POS5,COLUMN5) and
          COLUMN6 = NVL(POS6,COLUMN6) and
          COLUMN7 = nvl(POS7,COLUMN7) and
          COLUMN8 = POS8;
          DBMS_OUTPUT.PUT_LINE(CID); 
    end;
    It is giving me no data found exception

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    Your sample data is still useless. Without it, nobody is capable of testing your code. Besides, you "forgot" NVL with the COLUMN8.

    I believe that you should take some time, create a proper test case (that involves CREATE TABLE and INSERT INTO sample data), procedure you wrote, as well as its execution (which shows clearly which parameters you used) and the way Oracle responded.

  11. #11
    Join Date
    Feb 2013
    Posts
    7
    Quote Originally Posted by Littlefoot View Post
    Your sample data is still useless. Without it, nobody is capable of testing your code. Besides, you "forgot" NVL with the COLUMN8.

    I believe that you should take some time, create a proper test case (that involves CREATE TABLE and INSERT INTO sample data), procedure you wrote, as well as its execution (which shows clearly which parameters you used) and the way Oracle responded.
    Sure. Here is my table script:

    Code:
      CREATE TABLE "SYSTEM"."MYTESTTABLE" 
       (	"COLUMN1" VARCHAR2(1 CHAR), 
    	"COLUMN2" VARCHAR2(1 CHAR), 
    	"COLUMN3" VARCHAR2(1 CHAR), 
    	"COLUMN4" VARCHAR2(1 CHAR), 
    	"COLUMN5" VARCHAR2(1 CHAR), 
    	"COLUMN6" VARCHAR2(1 CHAR), 
    	"COLUMN7" VARCHAR2(1 CHAR), 
    	"COLUMN8" NUMBER(9,0), 
    	"ID" NUMBER(9,0)
       );
    
    REM INSERTING into SYSTEM.MYTESTTABLE
    Insert into SYSTEM.MYTESTTABLE (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6,COLUMN7,COLUMN8,ID) values ('A','B','C',null,null,null,null,3,1);
    Insert into SYSTEM.MYTESTTABLE (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6,COLUMN7,COLUMN8,ID) values ('A','B','C','D','E','F','G',7,2);
    Here is my procedure wrote:

    Code:
    create or replace
    procedure GetData
    (POS1 in varchar2 default null,
     POS2 in varchar2 default null,
     POS3 in varchar2 default null,
     POS4 in varchar2 default null,
     POS5 in varchar2 default null,
     POS6 in varchar2 default null,
     POS7 in varchar2 default null,
     POS8 in number)
    as
    cid number(9,0);
    begin
    select MYTESTTABLE.id into cid
    from MYTESTTABLE
    where COLUMN1 = POS1 and
          COLUMN2 = POS2 and
          COLUMN3 = POS3 and
          COLUMN4 = POS4 and
          COLUMN5 = POS5 and
          COLUMN6 = POS6 and
          COLUMN7 = POS7 and
          COLUMN8 = POS8;
          DBMS_OUTPUT.PUT_LINE(CID); 
    end;
    Here is the procedure execution script

    Code:
    DECLARE
      POS1 VARCHAR2(200);
      POS2 VARCHAR2(200);
      POS3 VARCHAR2(200);
      POS4 VARCHAR2(200);
      POS5 VARCHAR2(200);
      POS6 VARCHAR2(200);
      POS7 VARCHAR2(200);
      POS8 NUMBER;
    begin
      POS1 := 'A';
      POS2 := 'B';
      POS3 := 'C';
      POS4 := null;
      POS5 := null;
      POS6 := null;
      POS7 := null;
      POS8 := 3;
    
      GETDATA(
        POS1 => POS1,
        POS2 => POS2,
        POS3 => POS3,
        POS4 => POS4,
        POS5 => POS5,
        POS6 => POS6,
        POS7 => POS7,
        POS8 => POS8
      );
    END;

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    Well, back to what BlackSwan said ages ago.

    Your table and a procedure:
    Code:
    SQL> select * from mytesttable;
    
    C C C C C C C    COLUMN8         ID
    - - - - - - - ---------- ----------
    A B C                  3          1
    A B C D E F G          7          2
    
    SQL> create or replace procedure GetData
      2   (POS1 in varchar2 default null,
      3    POS2 in varchar2 default null,
      4    POS3 in varchar2 default null,
      5    POS4 in varchar2 default null,
      6    POS5 in varchar2 default null,
      7    POS6 in varchar2 default null,
      8    POS7 in varchar2 default null,
      9    POS8 in number
     10   )
     11  as
     12    cid number(9,0);
     13  begin
     14    select id
     15    into cid
     16    from mytesttable
     17    where (COLUMN1 = POS1 OR COLUMN1 IS NULL) AND
     18          (COLUMN2 = POS2 OR COLUMN2 IS NULL) AND
     19          (COLUMN3 = POS3 OR COLUMN3 IS NULL) AND
     20          (COLUMN4 = POS4 OR COLUMN4 IS NULL) AND
     21          (COLUMN5 = POS5 OR COLUMN5 IS NULL) AND
     22          (COLUMN6 = POS6 OR COLUMN6 IS NULL) AND
     23          (COLUMN7 = POS7 OR COLUMN7 IS NULL) AND
     24          (COLUMN8 = POS8 OR COLUMN8 IS NULL);
     25
     26    DBMS_OUTPUT.PUT_LINE(CID);
     27  end;
     28  /
    
    Procedure created.
    Now, testing:
    Code:
    SQL> DECLARE
      2    POS1 VARCHAR2(200);
      3    POS2 VARCHAR2(200);
      4    POS3 VARCHAR2(200);
      5    POS4 VARCHAR2(200);
      6    POS5 VARCHAR2(200);
      7    POS6 VARCHAR2(200);
      8    POS7 VARCHAR2(200);
      9    POS8 NUMBER;
     10  begin
     11    POS1 := 'A';
     12    POS2 := 'B';
     13    POS3 := 'C';
     14    POS4 := null;
     15    POS5 := null;
     16    POS6 := null;
     17    POS7 := null;
     18    POS8 := 3;
     19
     20    GETDATA(
     21      POS1 => POS1,
     22      POS2 => POS2,
     23      POS3 => POS3,
     24      POS4 => POS4,
     25      POS5 => POS5,
     26      POS6 => POS6,
     27      POS7 => POS7,
     28      POS8 => POS8
     29    );
     30  END;
     31  /
    1
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Obviously,
    Code:
     where (COLUMN1 = POS1 OR COLUMN1 IS NULL) AND
           (COLUMN2 = POS2 OR COLUMN2 IS NULL) AND etc.
    did the trick. NVL I suggested doesn't do anything smart, so - forget it.

  13. #13
    Join Date
    Feb 2013
    Posts
    7
    Thanks. That worked. Thanks for your time and support once again.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •