Professional Web Applications Themes

WHERE clause error - Microsoft SQL / MS SQL Server

Below, I'm trying to restrict my results in the USERNOTES field if there is a numeric value in the first 2 characters. The ISNUMERIC part works within the main query because I'm using the same syntax with a CASE statement. I only get an error with my query if I add the ISNUMERIC part. Shouldn't the ISNUMERIC part below return the record if a numerical value is in the first 2 digits of USERNOTES field? Error simply says "Error near ORDER BY" WHERE (tblDowntime.DTID = 1) AND ISNUMERIC(SUBSTRING(tblDowntime.UserNotes, 1, 2)) ORDER BY tblDowntime.StartDateTime Any ideas?...

  1. #1

    Default WHERE clause error

    Below, I'm trying to restrict my results in the USERNOTES field if there is
    a numeric value in the first 2 characters. The ISNUMERIC part works within
    the main query because I'm using the same syntax with a CASE statement. I
    only get an error with my query if I add the ISNUMERIC part.

    Shouldn't the ISNUMERIC part below return the record if a numerical value is
    in the first 2 digits of USERNOTES field?

    Error simply says "Error near ORDER BY"


    WHERE (tblDowntime.DTID = 1) AND
    ISNUMERIC(SUBSTRING(tblDowntime.UserNotes, 1, 2))
    ORDER BY tblDowntime.StartDateTime

    Any ideas?


    Scott Guest

  2. #2

    Default Re: WHERE clause error

    Your need to check for the desired ISNUMERIC value. Try:

    WHERE (tblDowntime.DTID = 1) AND
    ISNUMERIC(SUBSTRING(tblDowntime.UserNotes, 1, 2)) = 1
    ORDER BY tblDowntime.StartDateTime

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Scott" <com> wrote in message
    news:%phx.gbl... 
    there is 
    within 
    statement. I 
    value is 


    Dan Guest

  3. #3

    Default Re: WHERE clause error

    ISNUMERIC(SUBSTRING(tblDowntime.UserNotes, 1, 2))=1

    --
    -oj
    Rac v2.2 & QALite!
    http://www.rac4sql.net


    "Scott" <com> wrote in message
    news:%phx.gbl... 
    is 
    is 


    oj Guest

Similar Threads

  1. I'm getting an Error in my WHERE clause - seems sosimple.
    By CarlMarket in forum Coldfusion Database Access
    Replies: 7
    Last Post: April 7th, 12:32 AM
  2. Simple WHERE clause in CFQUERY Has error executingdatabase query
    By Johnny Jevidetti in forum Coldfusion Database Access
    Replies: 8
    Last Post: November 7th, 06:00 AM
  3. Syntax error in FROM clause
    By jdeline in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 10th, 02:30 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139