locked
WHERE DOES NOT CONTAIN RRS feed

  • Question

  • Can someone tell me why this doesn't work?

    I need to return everything that does not contain the word "Finaled".  I researched and this was supposed to be the solution but it doesn't work.

    SELECT CaseNum,
    ItemNumber,
    ToxTrackingDate,
    ToxTracking
    ToxTrackingInits
    FROM tblToxTracking
    WHERE ToxTrackingInits IS NULL OR ToxTrackingInits<> 'FINALED'

    Order by CaseNum


    Gee

    Friday, October 12, 2012 7:48 PM

Answers

  • try 

    SELECT CaseNum,
    ItemNumber,
    ToxTrackingDate,
    ToxTracking
    ToxTrackingInits
    FROM tblToxTracking
    WHERE ToxTrackingInits IS NULL OR ToxTrackingInits not like  '%FINALED%'

    Order by CaseNum

    • Proposed as answer by webJose Friday, October 12, 2012 7:54 PM
    • Marked as answer by GretaF Saturday, October 13, 2012 1:00 AM
    Friday, October 12, 2012 7:52 PM

All replies

  • try 

    SELECT CaseNum,
    ItemNumber,
    ToxTrackingDate,
    ToxTracking
    ToxTrackingInits
    FROM tblToxTracking
    WHERE ToxTrackingInits IS NULL OR ToxTrackingInits not like  '%FINALED%'

    Order by CaseNum

    • Proposed as answer by webJose Friday, October 12, 2012 7:54 PM
    • Marked as answer by GretaF Saturday, October 13, 2012 1:00 AM
    Friday, October 12, 2012 7:52 PM
  • That didn't work...I'm still getting the FINALED in there.

    Hmmmm...


    Gee

    Friday, October 12, 2012 7:57 PM
  • it should !

    declare @test table (name varchar(100))

    insert into @test values ('FINALED1')
    insert into @test values ('AbcFINALED2')
    insert into @test values ('abc')
    insert into @test values (NULL)

    select * from @test where name Is NULL OR NAME not like '%FINALED%'

     output :

    name
    abc
    NULL

    • Edited by JR1811 Friday, October 12, 2012 8:05 PM
    Friday, October 12, 2012 8:02 PM
  • OH, but it doesn't work.

    Any other ideas?


    Gee

    Friday, October 12, 2012 8:14 PM
  • You don't need another idea.  You need to troubleshoot your current scenario to determine why the proposed answer doesn't work in your case.  The answer given by JR1811 is correct.

    For example, make sure you are aware of the collation type in use by your database/text field.  Is it case sensitive or case insensitive?  Etc.  Find out the reason why it doesn't work for you.


    Jose R. MCP
    Code Samples

    Friday, October 12, 2012 8:16 PM
  • Right !! 

    May be check your db collation 

     WHERE ToxTrackingInits IS NULL OR ToxTrackingInits COLLATE ..........  not like  '%FINALED%' 

    http://msdn.microsoft.com/en-us/library/ms179886.aspx

    • Edited by JR1811 Friday, October 12, 2012 9:08 PM
    Friday, October 12, 2012 8:24 PM
  • I've shaved it down a bit, changed some requirements...now it works!

    Thank you so much for your help...I changed it up a bit, using your answers and this works!

    I appreciate your patience and time.

    SELECT tblCase.CaseNum,
    tblCase.PrnByDate,
    tblCurrentReportStatus.ToxStatus
    FROM tblCase INNER JOIN tblCurrentReportStatus ON tblCase.CaseNum = tblCurrentReportStatus.CaseNum
    WHERE tblCurrentReportStatus.ToxStatus Not Like '%final%'
    And tblCurrentReportStatus.ToxStatus  Not Like '%No Tox Requested%'
    AND tblCase.PrnByDate Between @StartDate AND DATEADD(d,1,@EndDate)

    order by tblCase.CaseNum


    Gee

    Friday, October 12, 2012 9:05 PM