#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    39
    Rep Power
    11

    Question about the WHERE statement


    Hi all,
    I have a page with 5 drop down menus. These drop downs act like filters for whatever data the user wants to retrieve from my database. Once submitted, it then goes to my asp page which displays the records based on what the user filtered from the previous page.

    Here is the question.

    How do i form my WHERE statement so that it returns the records based on the filters? Like If the user only decides to filter using 1 of the drop downs instead of all 5. If I use the AND statement, it trys to return all records that have a blank in them because one of the drop downs were left blank. Therefore returning no records. If I use the OR statement then it returns all records that meet drop down 1 and all records that meet drop down 2. What i want is someway to tell my SQL statement to only return records that meet drop down 1 and drop down 2 but when drop down 2 is blank, dont search for blank records in the database. Kind of like an And/Or statement. I hope that makes sense.

    Here is my current SQL statement:

    "SELECT YKPC_EXT_Orders.ACCOUNTANT, YKPC_EXT_Orders.FULL_NAME, YKPC_EXT_Orders.CL, YKPC_EXT_Orders.RF, YKPC_EXT_Orders.RFGL, YKPC_EXT_Orders.SITE, YKPC_EXT_Orders.PL, YKPC_EXT_Orders.[ACTUAL AMOUNT], YKPC_EXT_Orders.[TARGET AMOUNT], YKPC_EXT_Orders.[PRICE VAR], YKPC_EXT_Orders.[MATL USAGE], YKPC_EXT_Orders.[OTHER USAGE], YKPC_EXT_Orders.[PROCESS CHG VAR], YKPC_EXT_Orders.[VOL VAR] FROM YKPC_EXT_Orders WHERE (((YKPC_EXT_Orders.ACCOUNTANT)='" & Request.Form("txtAcct") & "')) OR (((YKPC_EXT_Orders.CL)='" & Request.Form("txtClient") & "'));"

    thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    the answer is to start your WHERE clause like this --

    ... WHERE 1=1

    then in your script, you can test each variable for a value, and if a value has been selected, then you generate an AND like this --

    ... AND foo = 'bar'

    that way, you do not have to wory about which value is the first one selected, or even if any were selected, because if none were selected, then WHERE 1=1 will return all the rows in the table

    see also The "any" option in dynamic search SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    39
    Rep Power
    11
    thanks alot.
    however thay article i found to be just a little confusing.

    Does this modified SQL Line look alright??

    strSQL = "SELECT YKPC_EXT_Orders.ACCOUNTANT, YKPC_EXT_Orders.FULL_NAME, YKPC_EXT_Orders.CL, YKPC_EXT_Orders.RF, YKPC_EXT_Orders.RFGL, YKPC_EXT_Orders.SITE, YKPC_EXT_Orders.PL, YKPC_EXT_Orders.[ACTUAL AMOUNT], YKPC_EXT_Orders.[TARGET AMOUNT], YKPC_EXT_Orders.[PRICE VAR], YKPC_EXT_Orders.[MATL USAGE], YKPC_EXT_Orders.[OTHER USAGE], YKPC_EXT_Orders.[PROCESS CHG VAR], YKPC_EXT_Orders.[VOL VAR] FROM YKPC_EXT_Orders" & _
    " WHERE 1=1" & _
    " If strCboAcct <> 'Show All' Then" & _
    " AND (((YKPC_EXT_Orders.ACCOUNTANT)='" & strCboAcct & "'))" & _
    " End If" & _
    " If strCboCl <> 'Show All' Then" & _
    " AND (((YKPC_EXT_Orders.CL)='" & strCboCl & "'))" & _
    " End If"

    I am getting an Unspecified Error whenever i try to load the page.

    thanks
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    you have

    " WHERE 1=1" & _
    " If strCboAcct <> 'Show All' Then" & _

    this makes your If test part of the sql string

    what you have to do is terminate the string after WHERE 1=1, then do your if test, and for the true condition,

    strSQL = strSQL & " AND (((YKPC_EXT_Orders.ACCOUNTANT)='" & strCboAcct & "'))"
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    39
    Rep Power
    11
    r937, thank you for your help.

    This is my new statement:

    strAcct = Request.Form("txtAcct")
    strClient = Request.Form("txtClient")

    strSQL = "SELECT YKPC_EXT_Orders.ACCOUNTANT, YKPC_EXT_Orders.FULL_NAME, YKPC_EXT_Orders.CL, YKPC_EXT_Orders.RF, YKPC_EXT_Orders.RFGL, YKPC_EXT_Orders.SITE, YKPC_EXT_Orders.PL, YKPC_EXT_Orders.[ACTUAL AMOUNT], YKPC_EXT_Orders.[TARGET AMOUNT], YKPC_EXT_Orders.[PRICE VAR], YKPC_EXT_Orders.[MATL USAGE], YKPC_EXT_Orders.[OTHER USAGE], YKPC_EXT_Orders.[PROCESS CHG VAR], YKPC_EXT_Orders.[VOL VAR] FROM YKPC_EXT_Orders WHERE 1=1"

    'Accountant Filter
    If strAcct <> "Show All" Then
    strSQL = strSQL & " AND (((YKPC_EXT_Orders.ACCOUNTANT)='" & strAcct & "'))"
    End If

    'CL Filter
    If strClient <> "Show All" Then
    strSQL = strSQL & " AND (((YKPC_EXT_Orders.CL)='" & strClient & "'))"
    End If



    Do you see anything wrong with this statement? For some reason it will not return any results anymore when searching for an accountant and leaving the client field's value = Show All.

    I double checked using response.write to make sure i was bringing in the right field names and i am.

    thanks
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    leave both fields as "show all" -- it should return all rows

    if not, you have a problem in your ASP, and i don't do ASP, sorry

IMN logo majestic logo threadwatch logo seochat tools logo