It sounds like FastReports isn't doing a true pass-through to the database,
but trying to pre-parse it for some reason--perhaps to try to optimize the
output or figure out many pages to print.

David Cornelius
Cornelius Concepts

David


>It sounds like FastReports isn't doing a true pass-through to the database,
>but trying to pre-parse it for some reason--perhaps to try to optimize the
>output or figure out many pages to print.

I just tried

SELECT P._Forename, P._Surname, C._Name, J._JobTitle
FROM Contacts P
JOIN Career J ON J._fkContacts = P._ID
JOIN Companies C ON J._fkCompanies = C._ID
WHERE P._Surname LIKE 'P%'

and that's fine.

Michael when you look at the report definition what does it have under data?

Roy Lambert [Team Elevate]

David,

As far as I could understand, FR doesn't have any role in the execution of the query, Michael is only using a FR variable to store the sql statement and retrieve it later, assigning it to a TEDBQuery and execute it. Thats why the only thing I could think of is if for some reason FR is adding a spurious character to the sql string, but its only a guess, of course.

--
Fernando Dias
[Team Elevate]

On Sat, 6 Aug 2011 08:12:16 +0100, Roy Lambert <roy@lybster.me.uk>
wrote:

>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.

I get what you're saying but it's an EDB SQL error. Why and how would
FR be changing the SQL? EDB is passing it a dataset not SQL right?

>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.

No that doesn't work. You get an ambiguous field error in MS SQL.

On Sat, 6 Aug 2011 09:53:18 -0400, "Malcolm"
<malcolm@spam.will.bounce> wrote:

>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>

AFAIK all major DB engines require table qualification of a field when
the table source is ambiguous. DBISAM and EDB are the only two I know
of that let you get away with this. Just one of the reasons I like
Elevate so much.

On Sun, 7 Aug 2011 08:44:56 +0100, Roy Lambert <roy@lybster.me.uk>
wrote:

>David
>
>
>>It sounds like FastReports isn't doing a true pass-through to the database,
>>but trying to pre-parse it for some reason--perhaps to try to optimize the
>>output or figure out many pages to print.
>
>I just tried
>
>SELECT P._Forename, P._Surname, C._Name, J._JobTitle
>FROM Contacts P
>JOIN Career J ON J._fkContacts = P._ID
>JOIN Companies C ON J._fkCompanies = C._ID
>WHERE P._Surname LIKE 'P%'

Is the Surname field in any of the other tables?

>>and that's fine.
>
>Michael when you look at the report definition what does it have under data?

Not sure what you mean. You mean the "Select Report Datasets" form?
frxDataset1 and frxDataset2.

On Sat, 06 Aug 2011 00:21:38 +0100, Fernando Dias
<fernandodiasAremovthis.easygate.com.pt> wrote:

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

Well the SQL I create I throw in the TEDBQuery and open it to make
sure it's valid. I then close the query and preview the report,
getting the errror. If there was some bad character in the SQL text
the error should also be seen when I open the query.

Michael

>I get what you're saying but it's an EDB SQL error. Why and how would
>FR be changing the SQL? EDB is passing it a dataset not SQL right?

If FastReport is asking for a field and giving ElevateDB a name it doesn't recognise that should do it. I didn't buy the source so I can't try tracing things through.

>>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.
>
>No that doesn't work. You get an ambiguous field error in MS SQL.

What? MS SQL doesn't allow column alias names. I find that very hard to believe (not using it I don't find it impossible).

Roy Lambert [Team Elevate]

Michael

>>Michael when you look at the report definition what does it have under data?
>
>Not sure what you mean. You mean the "Select Report Datasets" form?
>frxDataset1 and frxDataset2.

In the report designer on the right hand side the Data tab (this is version 4.x)

Roy Lambert [Team Elevate].

Michael


Is there a chance of you posting a sample of the database and a project with the FastReport to the binaries so I can try things here?


Roy Lambert