Help with oracle query/procedure using null values

[harish31]

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

[LKBrwn_DBA]

Try:

...
Column4 IS null and
...

[harish31]

Try:

...
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.

[LKBrwn_DBA]

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:

 . . . 
(val7 IS NULL OR Column_7 = val7) and
...

Or:

 . . . 
Column_7 = NVL(val7,Column_7) and
...

[harish31]

Let me make myself clear.

Here is my sample table and data:

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:

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:

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.

[harish31]

I found the solution here: http://www.dbforums.com/oracle/16900...ml#post6582987

Thanks all for your time and support

[Paresh Jagnade]

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;