Link to home
Start Free TrialLog in
Avatar of gogetsome
gogetsomeFlag for United States of America

asked on

Lexical error when selecting data with date value in where clause


Hello, I'm attempting to execute the attached statement but keep getting the following error:

ERROR [3700] [AR System ODBC Driver]Expected lexical element not found: )

If I remove the date parameter the query executes propely. What do I need to do in order to refine my select statement to include date?
Dim RemedyLast As String = "2011-10-04 13:36:02.107"
        Dim tsdate = "{ ts '" + Format$(RemedyLast, "yyyy-MM-dd HH:mm:ss.ff") + "' }"

        ' Set up query string
        Dim CmdString As String = "SELECT ""Contact Name"", ""Contact Phone"", ""Create-date"", ""Div."", ""ETA Timestamp"", """ & _
        "Last-modified-by"", ""Modified-date"", ""Onsite Timestamp"", Priority, ""Problem Descr" & _
        "iption"", ""Record-Id"", Resolution, ""Service Status"", ""Site ID"", Status, ""Symptom " & _
        "Type"", ""Vendor Name"", ""Vendor Notify Timestamp"", ""Budget Line Item #"", ""Bill to " & _
        "Account #"", Log, ""Critical Notes"" FROM ISD ISD WHERE ((""Vendor Name"" = 'me') or (""Vendor Name"" = 'me-l') or (""Vendor Name"" = 'me-mn') or (""Vendor Name"" = 'me-tn')) AND (""Modif" & _
        "ied-date"" >= " + tsdate + " )"

Open in new window

Avatar of ajexpert
ajexpert
Flag of United States of America image

Can you try
Dim RemedyLast As String = "2011-10-04 13:36:02.107"
        Dim tsdate = "{ ts '" + Format$(RemedyLast, "yyyy-MM-dd HH24:mm:ss.ff") + "' }"

        ' Set up query string
        Dim CmdString As String = "SELECT ""Contact Name"", ""Contact Phone"", ""Create-date"", ""Div."", ""ETA Timestamp"", """ & _
        "Last-modified-by"", ""Modified-date"", ""Onsite Timestamp"", Priority, ""Problem Descr" & _
        "iption"", ""Record-Id"", Resolution, ""Service Status"", ""Site ID"", Status, ""Symptom " & _
        "Type"", ""Vendor Name"", ""Vendor Notify Timestamp"", ""Budget Line Item #"", ""Bill to " & _
        "Account #"", Log, ""Critical Notes"" FROM ISD ISD WHERE ((""Vendor Name"" = 'me') or (""Vendor Name"" = 'me-l') or (""Vendor Name"" = 'me-mn') or (""Vendor Name"" = 'me-tn')) AND (""Modif" & _
        "ied-date"" >= " + tsdate + " )"

Open in new window

Avatar of gogetsome

ASKER

Thank you for your assistance Ajexpert. I tried, but got the same message.
Instead of using ODBC drivers, I guess you should be using ODP.NET drivers when using Oracle with .NET
Sorry for above 2 posts

Plz try this
Dim RemedyLast As String = "2011-10-04 13:36:02.107"
        Dim tsdate = "{ ts '" + Format$(RemedyLast, "yyyy-MM-dd HH:mm:ss.fff") + "' }"

        ' Set up query string
        Dim CmdString As String = "SELECT ""Contact Name"", ""Contact Phone"", ""Create-date"", ""Div."", ""ETA Timestamp"", """ & _
        "Last-modified-by"", ""Modified-date"", ""Onsite Timestamp"", Priority, ""Problem Descr" & _
        "iption"", ""Record-Id"", Resolution, ""Service Status"", ""Site ID"", Status, ""Symptom " & _
        "Type"", ""Vendor Name"", ""Vendor Notify Timestamp"", ""Budget Line Item #"", ""Bill to " & _
        "Account #"", Log, ""Critical Notes"" FROM ISD ISD WHERE ((""Vendor Name"" = 'me') or (""Vendor Name"" = 'me-l') or (""Vendor Name"" = 'me-mn') or (""Vendor Name"" = 'me-tn')) AND (""Modif" & _
        "ied-date"" >= " + tsdate + " )"

Open in new window

Awesome, that did get me past that bug but now it is throwing this:
ERROR [S000] [AR System ODBC Driver]Data types are not appropriate for relational operation
Can you please remove date condition and check if your query is working fine...

May be this?
Dim RemedyLast As String = "2011-10-04 13:36:02.107"
        Dim tsdate = "{ ts '" + Format$(RemedyLast, "yyyy-MM-dd HH:mm:ss.fff") + "' }"

        ' Set up query string
        Dim CmdString As String = "SELECT ""Contact Name"", ""Contact Phone"", ""Create-date"", ""Div."", ""ETA Timestamp"", """ & _
        "Last-modified-by"", ""Modified-date"", ""Onsite Timestamp"", Priority, ""Problem Descr" & _
        "iption"", ""Record-Id"", Resolution, ""Service Status"", ""Site ID"", Status, ""Symptom " & _
        "Type"", ""Vendor Name"", ""Vendor Notify Timestamp"", ""Budget Line Item #"", ""Bill to " & _
        "Account #"", Log, ""Critical Notes"" FROM ISD ISD WHERE ((""Vendor Name"" = 'me') or (""Vendor Name"" = 'me-l') or (""Vendor Name"" = 'me-mn') or (""Vendor Name"" = 'me-tn'))

Open in new window

Yes, that does work, but as soon as we try to use date in the where condition if fails with the lexical error.
Can you please tell us if you are storing fraction of seconds also in modified_date?
The dates contain:

2011-10-04 13:36:02.107
ASKER CERTIFIED SOLUTION
Avatar of ajexpert
ajexpert
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, the date must be converted! Thank you for your assistance.