Strange SQL error on report preview because of where clause
I am seeing a very strange problem. I have the following Elevate DB SQL in a report:
Select S.ClientID,Description,CAST((EndTime-StartTime) SECOND AS
INTEGER)/3600 As ElapsedTime From Schedule S JOIN Clients C
ON(S.ClientID=C.ClientID) WHERE S.ClientID = 'NMI'
This runs fine in the EDB Manager but when I use it in a report I get this error:
ElevateDB Error #700: (Expected column name expression but instead
found "S"."ClientD")'.
If the WHERE clause is removed there is no error when running the
report. It also works if I remove the S. part from S.ClientID but I need this for compatability with other DB engines.
In MS SQL 2008 by ADO (using ZEOS 5) I have:
Select S.ClientID,Description,datediff(hour,StartTime,EndTime) As ElapsedTime From Schedule S JOIN Clients C
ON(S.ClientID=C.ClientID) WHERE S.ClientID = 'NMI'
Again this SQL works fine in MS SQL manager. I then get this error on previewing the report:
Exception class EOleException with message 'The multi-part identifier "S.ClientID" could not be bound'.
Is FR doing something odd to the SQL internally?
Select S.ClientID,Description,CAST((EndTime-StartTime) SECOND AS
INTEGER)/3600 As ElapsedTime From Schedule S JOIN Clients C
ON(S.ClientID=C.ClientID) WHERE S.ClientID = 'NMI'
This runs fine in the EDB Manager but when I use it in a report I get this error:
ElevateDB Error #700: (Expected column name expression but instead
found "S"."ClientD")'.
If the WHERE clause is removed there is no error when running the
report. It also works if I remove the S. part from S.ClientID but I need this for compatability with other DB engines.
In MS SQL 2008 by ADO (using ZEOS 5) I have:
Select S.ClientID,Description,datediff(hour,StartTime,EndTime) As ElapsedTime From Schedule S JOIN Clients C
ON(S.ClientID=C.ClientID) WHERE S.ClientID = 'NMI'
Again this SQL works fine in MS SQL manager. I then get this error on previewing the report:
Exception class EOleException with message 'The multi-part identifier "S.ClientID" could not be bound'.
Is FR doing something odd to the SQL internally?