query without records
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: query without records

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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.
    -- Dilip

  4. #4
    Join Date
    Jul 2002
    Posts
    228
    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'))

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: query without records

    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'.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jul 2002
    Posts
    228

    Re: Re: query without records

    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'))

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Re: Re: query without records

    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width