Dynamic SQL View and FlexODBC

[Andy Roden]

Does anyone know of any reason why I would be getting the following error message:

[FlexODBC] Expected lexical element not found: (-2147217900)

when I try to browse the view once it has been created. When I create the view and hit the Execute button, I do get a sample query as expected.

The error log message is as follows:

[FlexODBC] Expected lexical element not found: 10004400-2147217900Microsoft OLE DB Provider for ODBC DriversDAI 0001950D_=434C465F5F5931303031264B5F3D616956616944594E56494557616931303131616956464E61695461736B5F56696577SQLEXEC_GETCOLLECTION0Select DAI_.* FROM (Select Tickets.ticket_no, Tickets.Short_Desc, Tickets.Status, Tickets.Priority, Tickets.Job_Type, JMEvents.Event_Code, JMEvents.Staff_ID, JMEvents.Task_Time_Dc, JMEvents.Divert_Time_Dc, JMEvents.Booked_Time_Dc From Tickets, JMEvents Where Tickets.Ticket_No = JMEvents.Ticket_No and Tickets.Status = 8 and JMEvents.Event_Code = 500) DAI_

Is this an error due to the FlexODBC driver on the machine or Dynamic itself? The FlexODBC driver is FlexODBC v4.0.24 and Dynamic AI Server Edition, AI6Beta Q4 version 6.0.70

[Carsten Sørensen]

The reason is that FlexODBC doesn't support SUB-Selects. Dynamic AI uses sub-selects when building reports on top of Dynamic AI SQL Views.

As I see your SELECT you might be able to work-around that limitation by using a JOINBUILDER view instead.

If you JOINBuild on Tickets and JMEvents tables (in your case as INNER JOINS) and pair the Ticket_No, then on the report itself you can set the Conditions that Ticket.Status = 8 and JMEvents.Event_Code = 500.
Or you can make the Status and Event_Code normal filters, that would make it possible to use the report / join also for other purposes.

Hope that helps you!

[Andy Roden]

Thank you for the prompt reply Carsten. I thought it might be something obvious.