Results 1 to 6 of 6

Thread: SQL Query Help

  1. #1
    Join Date
    Mar 2011
    Posts
    13

    Unanswered: SQL Query Help

    I have an ASP Sql Query page with 3 text fields to query a database with some other fields being displayed. 2 of the fields are date fields to search Begin Date and End Date and the 3rd field is Unit. I want to be able to search the database by Dates Only and by Dates and Unit. The SQL Statement I have setup currently will work with Dates and Unit but when I leave the Unit Field Blank on the search no data is returned and I want all records between the date ranges to return even if there is nothing entered in the Unit field. ANY help would be greatly appreciated. Thanks

    Chris

    Here's the statement:

    SELECT Active_Calls.Jurisdiction, Active_Calls.Call_Taker, Active_Calls.Call_Number, Active_Calls.Date_Received, Active_Calls.Call_Class, Active_Calls.Actual_Incid_City, Active_Calls.Actual_Incid_Location, Active_Calls.First_Unit, Active_Calls.Complaint, Active_Calls.Landmark, Callunitdata.Unit FROM dbo.Active_Calls AS Active_Calls INNER JOIN dbo.CALLUNITDATA AS Callunitdata ON Active_Calls.Call_Number = Callunitdata.Call_Number WHERE Active_Calls.Date_Received BETWEEN MMColParam1 AND Date2 AND Active_Calls.Jurisdiction = 'surry_co' AND Active_Calls.Call_Taker <> 'MOB' AND Active_Calls.Call_Class IN ('FIR','EMS','F','M') AND Callunitdata.Unit LIKE %Unit1%

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    If this is the query you are passing, then it is ALWAYS selecting only those records for which Callunitdata.Unit contains the string UNIT1.

    Is that what you intended?

    Or, is it that UNIT1 is a field name, or variable, that you are passing to this query?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Mar 2011
    Posts
    13
    Unit1 is coming over as a value in the URL ?Unit1=....based on what the user entered into the field.

    Chris

  4. #4
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    My guess is that if you don't enter a value into the units field the parameter value being passed to the query is null.

    Nothing is like null or equal to null, therefore any comparison on that would fail.

    Simplest solution would be to modify the query to test for nulls.

    Eg

    AND (Callunitdata.Unit LIKE %Unit1% OR Unit1 is null)

    You could also coalesce the parameter to a non-null value before doing your like comparison.

  5. #5
    Join Date
    Mar 2011
    Posts
    13
    That done it....THANK YOU SO MUCH!!!!

    CS

  6. #6
    Join Date
    Mar 2011
    Posts
    13
    Now that I have the query correct when I search based on date only without the unit in there it gives back data but there are some duplicates in there. I think the whole record isn't a duplicate as there are some fields that are different but the call#, location fields are the same. Is there any way to suppress duplicates based on like the call# field only???

    Thanks

    Chris

Posting Permissions

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