I am seeing a very strange problem. I have the following SQL:

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 EDB Mgr but when I use it in a report (Fast Reports)
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. Why would it work in EDB Mgr but not the report? Any ideas
what might be wrong here?

Michael


In general terms if it works in EDBManager it should work in your app. EDBManager is just a Delphi app after all.

Question: How do you use it in FastReport? I have some fairly complex ones with subselects and joins that don't give a problem. All I use is frxDBDataset.

The only suggestion I do have right now is to drop the S. No idea if the query will still work or anything.

Roy Lambert [Team Elevate]

On Fri, 5 Aug 2011 18:06:58 +0100, Roy Lambert <roy@lybster.me.uk>
wrote:

>Michael
>
>
>In general terms if it works in EDBManager it should work in your app. EDBManager is just a Delphi app after all.
>
>Question: How do you use it in FastReport? I have some fairly complex ones with subselects and joins that don't give a problem. All I use is frxDBDataset.
>
>The only suggestion I do have right now is to drop the S. No idea if the query will still work or anything.

The report's SQL is stored in a report variable. I read that, insert
the WHERE clause, assign that to a TEDBQuery which is hooked to the
frxDBDataset. Using the debugger I get the SQL in the query right
before printing and it runs in EDB Mgr but gives the error on
previewing the report. The report previews fine BTW after closing the
error box. Taking out the S works but I need it there for
compatability with other database engines. I get a different but
similar error using MS SQL BTW.

Michael,

The error message references "S"."ClientD" instead of "S"."ClientID"; was it a typo in your message or is it the real error message  ?

--
Fernando Dias
[Team Elevate]

Em 05-08-2011 17:06, Michael Fullerton escreveu:
> I am seeing a very strange problem. I have the following SQL:
>
> 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 EDB Mgr but when I use it in a report (Fast Reports)
> 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. Why would it work in EDB Mgr but not the report? Any ideas
> what might be wrong here?
>

On Fri, 05 Aug 2011 20:14:16 +0100, Fernando Dias
<fernandodiasAremovthis.easygate.com.pt> wrote:

>Michael,
>
>The error message references "S"."ClientD" instead of "S"."ClientID"; was it a typo in your message or is it the real error message  ?

It's a typo Fernando. Sorry about that.

Michael,

Just a guess... perhaps some extra invisible character at the end of the query expression stored in the report...

Fernando Dias
[Team Elevate]

Michael

>The report's SQL is stored in a report variable. I read that, insert
>the WHERE clause, assign that to a TEDBQuery which is hooked to the
>frxDBDataset. Using the debugger I get the SQL in the query right
>before printing and it runs in EDB Mgr but gives the error on
>previewing the report. The report previews fine BTW after closing the
>error box. Taking out the S works but I need it there for
>compatability with other database engines. I get a different but
>similar error using MS SQL BTW.

I only bought FastReports recently and I think I had a similar problem. The fact that it goes away if you take the S. out and that you have a similar problem with MS SQL pretty much says its FastReport - it obviously doesn't like table correlation names.

As I was typing another thought went through my head put in a specific alias eg S.ClientID AS ClientID and see if that works. If it does then you should be able to apply that to any SQL database.

Roy Lambert [Team Elevate]

Michael Fullerton wrote:

> Taking out the S works but I need it there for
> compatability with other database engines. I get a different but
> similar error using MS SQL BTW.

Are you sure you require the S. in the where clause for other engines?

As I understand it the WHERE is applied to the result set *after* any
JOINs so there should be no need to qualify the column as there is only
one "ClientID" in the result set .  <but what would I know!>

Malcolm

>Are you sure you require the S. in the where clause for other engines?
>
>As I understand it the WHERE is applied to the result set *after* any
>JOINs so there should be no need to qualify the column as there is only
>one "ClientID" in the result set . <but what would I know!>

I'm assumed Michael knows what he's talking about when he posted "I need it there for compatability with other database engines". MS rubbish

Roy Lambert [Team Elevate]

Roy Lambert wrote:

> I'm assumed Michael knows what he's talking about when he posted "I
> need it there for compatability with other database engines". MS
> rubbish
>
> Roy Lambert [Team Elevate]

I also assume he is smarter than I am.  
But I did a little Google for sql qualifiers in with clause and found
very little said about them.  One article said not valid .. but a few
paras down it said they were.  Mostly there was no mention and
certainly no examples. <shrug>