error at run time? wrong results? no results? keyboard catches fire?

do you have any NULL values?

can you post sample data and expected results for that data?

Solved

Posted on 2011-04-21

Hi Folks:

I have an sql statement in PL/SQL that is not working as expected.

select columns

from table

where key = value

and column_q = variable_w

and column_t = variable_p

and column_a in (a,b,c,d,x)

and NOT (column_a = x and column_b = y);

The statement compiles but the "NOT (column_a = x and column_b = y)" does not work.

Can you help me with the proper syntax for this condition?

I have an sql statement in PL/SQL that is not working as expected.

select columns

from table

where key = value

and column_q = variable_w

and column_t = variable_p

and column_a in (a,b,c,d,x)

and NOT (column_a = x and column_b = y);

The statement compiles but the "NOT (column_a = x and column_b = y)" does not work.

Can you help me with the proper syntax for this condition?

20 Comments

error at run time? wrong results? no results? keyboard catches fire?

do you have any NULL values?

can you post sample data and expected results for that data?

```
select columns
from table a
where key = value
and column_q = variable_w
and column_t = variable_p
and column_a in (a,b,c,d,x)
and NOT EXISTS
(SELECT 1 FROM TABLE b
WHERE
A.KEY = B.KEY
B.column_a = x and B.column_b = y);
```

and more easily resolved with

and LNNVL(column_a = x and column_b = y);

a=b is FALSE if a or b or both is NULL

a!= b is FALSE if a or b or both is NULL

and, my earlier LNNVL suggestion isn't correct either

lnnvl can only evaluate a single expression

so, it would have to be...

and LNNVL(column_a = x) and LNNVL(column_b = y);

```
with t as (
select 1 id, 'xxx' a, null b from dual
union
select 2, 'hainkurt', 'pwd123' from dual
union
select 3, 'EE', 'pwdee' from dual
)
--select * from t where not (a='EE' and b='pwd')
select * from t where (a!='EE' or b!='pwd')
ID A B
1 xxx
2 hainkurt pwd123
3 EE pwdee
```

oops!, you're right, I was typing faster than I was thinking

since we don't have any sample data or expected results or a clear description of what is going wrong, I was just guessing about nulls and then, lacking a sample didn't test.

so, I agree and retract my "correction" for lnnvl as well

but, that doesn't mean any of the above is actually helpful to the asker. :)

noreenburke,

as you can see, insufficient detail in the question forces us to guess at what you are looking for

It works without doubt.

the problem is in the expression (column_a = x and column_b = y)

You have to figure out why you doesn't get the anticipated result.

For instance if you work with dates try to check if the comparisons work.

what does "does not work" mean to the asker

we can only guess because the syntax itself is fine.

Check the comparisons and check if they are correct, if they work.

We both (with Mr. Sruber) think that there is the problem.

I apologize, I was in a hurry to get this posted and did not complete my thoughts.

The statement ---

select columns

from table

where key = value

and column_q = variable_w

and column_t = variable_p

and column_a in (a,b,c,d,x)

and NOT (column_a = x and column_b = y);

does not work in that it removes all occurences of rows that have "column_a = x" regardless of the contents of "column_b "

When I get my test environment back, I will explore sdstuber's null hypothesis.

Thanks All.

that's a repeat of http:#35442770

I want to eliminate any rows from the cursor that have an x in column_a AND a y in collumn_b.

I have tried "and LNNVL(column_a = x) and LNNVL(column_b = y);" to no avail

Also did the select where not exists -- to no avail

Thanks

Title | # Comments | Views | Activity |
---|---|---|---|

Parse data from XMLtype data in Oracle SQL | 5 | 59 | |

Oracle SQL Update Statement Query Syntax | 3 | 46 | |

Left Justify field in Oracle | 6 | 53 | |

Adding a blank row when using union all | 4 | 37 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**21** Experts available now in Live!