Cursor: Can where clause have NULL values?

[goyalpdm]

Hi,

I have a cursor on table:

CURSOR curMyTable IS SELECT col1 FROM MyTable
WHERE col2 = v_programVariable1
AND col3 = v_programVariable1;

As per business condition, either one of v_programVariable1 and v_programVariable2 has to be NULL.

Wonder If my curosr is OK? What I see is, cursor fails and doesn't select col1 at all.

Can suggest some approach to handle this? May be, I should write two cursors? OR use NVL for v_programVariable1 etc..?

Thanks,
Sachin

 

[goyalpdm]

Please read original query as:
CURSOR curMyTable IS SELECT col1 FROM MyTable
WHERE col2 = v_programVariable1
AND col3 = v_programVariable2;

I verified. Two cursors resolve the problem:

CURSOR curMyTable1 IS SELECT col1 FROM MyTable
WHERE col2 = v_programVariable1;

CURSOR curMyTable2 IS SELECT col1 FROM MyTable
WHERE col3 = v_programVariable2;

I do a IF (programVariable1 IS NOT NULL) THEN..ELSE..ENDIF and open cursor accordingly.

What I am looking for is: If can use NVL etc and do everything by one cursor...?

 

[SantaMufasa]

Sachin,

Something seems to be wrong in your explanation: Your cursor code does not reference "v_programVariable2". I presume that you wish, instead to compare "v_programVariable2" to "col3". And if your business rule says that either "v_programVariable1 or v_programVariable2 must be NULL, but not both", then you can add that situation into your WHERE clause as follows. Here is my interpretation of your conditions:

...WHERE (col2 = v_programVariable1 or col2 is null) and
          col3 = v_programVariable2 or col3 is null) and
         ((v_programVariable1 is NULL and
           v_programVariable2 is NOT NULL) or
          (v_programVariable2 is NULL and
           v_programVariable1 is NOT NULL))

Let me know if this is closer to what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 03:34 (12Jan05) UTC (aka "GMT" and "Zulu"),
@ 20:34 (11Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[goyalpdm]

BINGO !!!

...WHERE (col2 = v_programVariable1 or col2 is null) and
col3 = v_programVariable2 or col3 is null)

Was enough to do the trick. Thanks Man. You are great !!!

Cheers,
Sachin