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

ajexpert

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

gogetsome

ASKER

Thank you for your assistance Ajexpert. I tried, but got the same message.

ajexpert

Instead of using ODBC drivers, I guess you should be using ODP.NET drivers when using Oracle with .NET

ajexpert

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

gogetsome

ASKER

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

ajexpert

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

gogetsome

ASKER

Yes, that does work, but as soon as we try to use date in the where condition if fails with the lexical error.

ajexpert

Can you please tell us if you are storing fraction of seconds also in modified_date?

gogetsome

ASKER

The dates contain:

2011-10-04 13:36:02.107

ASKER CERTIFIED SOLUTION

ajexpert

membership