Help with oracle query/procedure using null values
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Help with oracle query/procedure using null values

Hybrid View

  1. #1
    Join Date
    Feb 2013
    Posts
    4

    Help with oracle query/procedure 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
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,460

    Cool

    Try:
    Code:
    ...
    Column4 IS null and
    ...
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Feb 2013
    Posts
    4
    Quote Originally Posted by LKBrwn_DBA View Post
    Try:
    Code:
    ...
    Column4 IS null and
    ...
    Thanks for the reply. I want to use the above query in a stored procedure which have null values for the parameters as below:

    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;


    My paramters will sometimes have values and some times null. I can not use IS NULL all the time. Please advise.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,460

    Cool

    Quote Originally Posted by harish31 View Post
    Thanks for the reply. I want to use the above query in a stored procedure which have null values for the parameters as below:

    . . . E t c . . .

    My paramters will sometimes have values and some times null. I can not use IS NULL all the time. Please advise.
    Two ways:
    Code:
     . . . 
    (val7 IS NULL OR Column_7 = val7) and
    ...
    Or:
    Code:
     . . . 
    Column_7 = NVL(val7,Column_7) and
    ...
    Last edited by LKBrwn_DBA; 02-18-2013 at 04:47 PM.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Feb 2013
    Posts
    4
    Let me make myself clear.

    Here is my sample table and data:

    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);
    Below is 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;
    Below is the execution script I am using:

    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;
    I am expecting the procedure to print out 1. But it is throwing a no data found exception.

  6. #6
    Join Date
    Feb 2013
    Posts
    4
    I found the solution here: http://www.dbforums.com/oracle/16900...ml#post6582987

    Thanks all for your time and support

  7. #7
    Join Date
    Feb 2013
    Posts
    8
    Whenever you check column with equal to (=) NULL it doesnt return any value.
    NULL columns should always be checked with IS NULL clause.

    So the modified query will be
    select *
    from MyTable
    where Column1 = 'A' and
    Column2 = 'B' and
    Column3 = 'C' and
    Column4 IS null and
    Column5 IS null and
    Column6 = 3;

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
  •  



Click Here to Expand Forum to Full Width