If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Help with oracle query using null values

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,571
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.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,571
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.
Don't say, show. Don't promise, prove.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #6 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,985
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.;
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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:

COLUMN1COLUMN2COLUMN3COLUMN4COLUMN5COLUMN6COLUMN7COLUMN8ID
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;
Reply With Quote
  #8 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,985
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?
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #10 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,985
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.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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;
Reply With Quote
  #12 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,985
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.
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 7
Thanks. That worked. Thanks for your time and support once again.
Reply With Quote
Reply

Tags
oracle query, psql

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On