query without records

Hi,
I run this query

SELECT A.COD, A.FLAG, B.AREA, B.NUM_ID
FROM TAB A, TAB1 B
WHERE A.ID=B.ID
AND ((FLAG=1 AND COD <> 'M') OR (FLAG=2 AND COD = 'N'))

With this query I get:

no row select ( there are rows with flag=1 and cod<>'M')

but if I run this query:

SELECT A.COD, A.FLAG, B.AREA, B.NUM_ID
FROM TAB A, TAB1 B
WHERE A.ID=B.ID
AND (FLAG=1 AND OR (FLAG=2 AND COD = 'N'))

I get rows


What I wrong??

Thanks
Raf

Uh, because they're different queries.

You obviously have a record where flag = 1 and cod = M, which the second query will find but the first will not.

And I'm assuming you meant:

Code:

---

SELECT A.COD, A.FLAG, B.AREA, B.NUM_ID
FROM TAB A, TAB1 B
WHERE A.ID=B.ID
AND (FLAG=1 OR (FLAG=2 AND COD = 'N'))

---

- Chris

If either of the two predicates in ORed condition returns rows, you should get atleast that many rows in result sets.

Paste the exact sql you are running in sqlprompt here. The one you pasted above seems not correct.

sorry,

SELECT A.COD, A.FLAG, B.AREA, B.NUM_ID
FROM TAB A, TAB1 B
WHERE A.ID=B.ID
AND (FLAG=1 OR (FLAG=2 AND COD = 'N'))

Uh, yeah, just like I assumed, and, uh, it's still a different query that will give different results for the reasons I already explained.

- Chris

Quote:

---

Originally posted by raf
no row select ( there are rows with flag=1 and cod<>'M')

---

No, there obviously aren't any such rows! I bet the only combinaton you have there are:

- flag=1, cod='M'
and/or
- flag=1, cod IS NULL !!!!

If something is NULL, then you can't say it's <>'M'.

Quote:

---

Originally posted by jmodic
No, there obviously aren't any such rows! I bet the only combinaton you have there are:

- flag=1, cod='M'
and/or
- flag=1, cod IS NULL !!!!

If something is NULL, then you can't say it's <>'M'.

---

ok jmodic

is correct this?

SELECT A.COD, A.FLAG, B.AREA, B.NUM_ID
FROM TAB A, TAB1 B
WHERE A.ID=B.ID
AND ((FLAG=1 AND nvl(COD, 'S') <> 'M') OR (FLAG=2 AND COD = 'N'))

Quote:

---

Originally posted by raf
[B]ok jmodic

is correct this?

---

Hm, you tell me.... You know your data, you know what you expect from your query - I don't know that. I'm only guessing, becuse you haven't explained neither of those two things sufficiently.