Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    20

    Question Unanswered: Help with a SQL Query

    I have a if condition in a query for a columnA <> 1 but this columnA in the table is null and my IF query fails - How do I need to structure my query:
    If rsX = 1 Or rsX = 2 AND columnA <> 1 Then



    ANy help will be appreciated.
    Thanks!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you really did mean to connect columnA<>1 with rsX=2 then what you want is this --
    Code:
    select rsX
         , columnA
         , otherstuff
      from yourtable
     where rsX = 1
        or (
           rsX = 2 
       and ( columnA is null
          or columnA <> 1 
           )
           )
    if not, then you probably want this --
    Code:
    select rsX
         , columnA
         , otherstuff
      from yourtable
     where ( rsX = 1
          or rsX = 2 
           ) 
       and ( columnA is null
          or columnA <> 1 
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    20
    Thanks for your reply - Now I have rewritten my query as -
    If ((rsX = 1 Or rsX = 2) AND (columnA is NULL OR columnA <> 1)) Then

    I am getting a run time error 424 in VB -object required - Any ideas??

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope, sorry, no idea, i don't know VB

    however, you may want to try SELECT ... WHERE instead of IF ... THEN

    you should let the database engine select just the rows you want


    rudy

  5. #5
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45
    What kind of variable is columnA?

    VB only supports the Null value for certain data types.


    Originally posted by sri2003
    Thanks for your reply - Now I have rewritten my query as -
    If ((rsX = 1 Or rsX = 2) AND (columnA is NULL OR columnA <> 1)) Then

    I am getting a run time error 424 in VB -object required - Any ideas??

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Please post your entire SQL query, not just the IF clause.

    blindman

  7. #7
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    20
    The variable for the column is Char.

  8. #8
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    20
    The SQL query is select all the records from the table it is a simple SQL - Something like Select a,b,c,d,e from tableDummy. The in VB is where I do the IF condition for the recordset that is:
    If rsX = 1 Or rsX = 2 AND columnA <> 1 - do you want me to post the entire RecordSet - please let me know.
    Thanks!

  9. #9
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45
    Ok, the column is of type char, but a variable in VB of type String is not able to hold a Null value.

    Is it a VBScript?

    What are the variables declared as? (if they are explicitly declared)

    Is there some reason you do not directly use the fields from the recordset in the comparison?
    ((rs.rsX = 1 Or rs.rsX = 2) AND (rs.ColumnA Is Null Or rs.ColumnA <> 1))

    Also, as some suggested, it might be a better idea to do the entire filtering in the WHERE clause if possible.

Posting Permissions

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