weird sql problem.

  • mig28mx

    Ten Centuries

    Points: 1088

    Hi all,

    I´m not sure what I´m doing bad with this query:

    Situation: I need to select all the purchase order lines(poitem_all) that not exists in the table of requisition lines (preqlines).

    select po_num from poitem_all

    results with all the  purchase orders (po_num) for the table poitem_all. Ok. Then for select only that not exists in preqitem table:

    select po_num from poitem_all
    where poitem_all.ponum not in (select po_num from preqitem)

    results with no records! This is weird due to, I have purchase orders that not exists in preqitem table.

    But this query:

    select po_num  from poitem_all
    except
    (select preqitem.po_num from preqitem)

    results with the correct information.

    Anyone knows what is the trick here?

    Thank you in advance.

     

     

     

     

     

     

  • Jonathan AC Roberts

    SSCoach

    Points: 17099

    Is it because you are looking up on a different column: ponum instead of po_num 

    select po_num from poitem_all
    where poitem_all.ponum not in (select po_num from preqitem)
  • ScottPletcher

    SSC Guru

    Points: 98293

    You also might have a NULL value for po_num in preqitem.  That would prevent the NOT IN from yielding any results, so add a NOT NULL check.  Or, perhaps even better, use NOT EXISTS instead of NOT IN:

    select po_num from dbo.poitem_all 
    where poitem_all.po_num /*ponum?*/ not in (select po_num from dbo.preqitem where po_num is not null)

    --or, more cleanly, write it like this:
    select po_num from dbo.poitem_all po
    where not exists(select 1 from dbo.preqitem pr where pr.po_num = po.po_num)

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88222

    Also - be careful with referencing columns without using the table alias.

     Select po_num
    From poitem_all
    Where poitem_all.po_num Not In (Select po_num From preqitem)

    In this query - let's assume the column name in preqitem is actually 'ponum' and not po_num.  If that is the case - the query will execute with no errors but will give you results you are not expecting (actually - most or all rows will match).

     Select po_num
    From poitem_all
    Where poitem_all.po_num Not In (Select i.po_num From preqitem i)

    This simple change - using the table alias i - would cause the query to fail if the table preqitem does not have a column named po_num.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • mig28mx

    Ten Centuries

    Points: 1088

    Hi Jonathan,

    Yes. My mistake. I wrote  ponum instead of po_num.

    But the problem persists.

    Thank you.

  • mig28mx

    Ten Centuries

    Points: 1088

    Hi Scott

    Your solution worked like a charm!

    Thank you.

  • mig28mx

    Ten Centuries

    Points: 1088

    Hi Jeffrey,

    It was my mistake. The column name is po_num and not ponum.

     

    thank you.

  • ScottPletcher

    SSC Guru

    Points: 98293

    I think it's worth a second here to go through why a SQL NOT IN with a NULL value in it causes no values to match.

    SQL Rule 1) A WHERE condition must be true to allow a row to be selected.  Not just "not proven false", but absolutely proven true.

    SQL Rule 2) A comparison against a NULL does not yield TRUE or FALSE, only NULL.

    Ok, that said, run these statements:

    SELECT value FROM ( VALUES(1),(2),(3) ) AS data(value) WHERE value NOT IN (5, 6, 7) --all rows

    SELECT value FROM ( VALUES(1),(2),(3) ) AS data(value) WHERE value NOT IN (5, 6, 7, NULL) --no rows!

    Probably easiest is to think of NULL as "unknown".  When SQL hits the NULL in the value list, is 1 not = NULL?  Since NULL is unknown, SQL can't know.  An unknown value could be 1, after all.  So SQL cannot absolutely say that the NOT IN is true.  But if it's not known to be absolutely true, then the WHERE clause has failed, and the row can't be included in the result from the SELECT.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • mig28mx

    Ten Centuries

    Points: 1088

    Hi Scott,

    Thank for share this interesting rules. I was not knowing that!

    I came from Oracle SQL, and these NULL things, works different.

    Thank you again.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply