On Mon, 8 Aug 2011 18:31:01 +0100, Roy Lambert <roy@lybster.me.uk>
wrote:

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

It allows aliases. It just gets confused if the alias is the same name
as a field in another table.

On Mon, 8 Aug 2011 18:31:03 +0100, Roy Lambert <roy@lybster.me.uk>
wrote:

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

I see all the fields: ClientID,Description,ElapsedTime

Michael

>>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).
>
>It allows aliases. It just gets confused if the alias is the same name
>as a field in another table.

Fair enough so would I. What about just choosing a different alias say DisambiguatedClientID?

Roy Lambert [Team Elevate]

Michael

>I see all the fields: ClientID,Description,ElapsedTime

I just did something I should have done at the start and checked out the error message in the manual (Tim's generally VERY good at getting the error numbers right)

Error 700 is

EDB_ERROR_COMPILE (700) An error was found in the <ObjectType> at line <Line> and column <Column> (<ErrorMessage>)This error is raised whenever an error is encountered while compiling an SQL expression, statement, or routine. The specific error message is indicated within the parentheses.

I would definitely ask FastReport support. If it was ElevateDB you just have to get the error in EDBManager as well. Combine that with a different MS SQL error with the same SQL and it really says its FastReport.

I'm happy and willing to try and see what I can do but I need a sample database and app.

Roy Lambert [Team Elevate]

Malcolm,

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

Logically, the WHERE clause is executed after the JOINs.  However, in the
case of INNER JOINs or WHERE conditions on tables that aren't the target of
an OUTER JOIN, EDB (and other engines) will execute the WHERE conditions
*before* the joins to cut down on the expense of the joins.

--
Tim Young
Elevate Software
www.elevatesoft.com

Michael,

<< ElevateDB Error #700: (Expected column name expression but instead found
"S"."ClientD")'.  >>

You should see an error line and column number in the error message.  That
should indicate where in the query there's a problem.

The fact that the column reference is enclosed in double-quotes in the
error, but not in the original SQL, indicates that FR is "massaging" the SQL
prior to sending it to EDB, so it is very likely that something is going
wrong at that point.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< ElevateDB Error #700: (Expected column name expression but instead found
>"S"."ClientD")'. >>
>
>You should see an error line and column number in the error message. That
>should indicate where in the query there's a problem.
>
>The fact that the column reference is enclosed in double-quotes in the
>error, but not in the original SQL, indicates that FR is "massaging" the SQL
>prior to sending it to EDB, so it is very likely that something is going
>wrong at that point.

I'm baffled. From this <<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.>> FastReport isn't sending it to the query just being used as a store. Depending on how the query is opened I don't see how the frxDBDataset or other bits of FastReport could have a chance to alter the query.

Roy Lambert [Team Elevate]

Roy,

<< I'm baffled. From this <<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.>> FastReport isn't sending it to the query just being used as
a store. Depending on how the query is opened I don't see how the
frxDBDataset or other bits of FastReport could have a chance to alter the
query. >>

I don't know anything about FR, but you can use a simple test to prove what
I'm saying - just pop a query into the EDB Manager that references a valid
table with a correlation name, but then references an invalid column name
using the table correlation name, like this:

SELECT * FROM customer c
WHERE c.customerno=1000

(customerno doesn't exist)

Then check out the error message - EDB does not add double-quotes around the
column name in the error message, therefore the double-quotes are not being
added in the error message, and they're certainly not there in the original
query.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>SELECT * FROM customer c
>WHERE c.customerno=1000
>
>(customerno doesn't exist)
>
>Then check out the error message - EDB does not add double-quotes around the
>column name in the error message, therefore the double-quotes are not being
>added in the error message, and they're certainly not there in the original
>query.

I don't think its ElevateDB. I'm positive the fault lies with FastReport, but I don't know if its re-writing the query. The reason I was asking Michael about what was in the report's data section is I wondered if FastReport was doing a call along the lines of FieldByName('"S"."ClientID"').AsString but since I don't have the source I can't check it out.

Would a call like the above also give a 700 error? If so would it use the stuff sent at it ie would it be quoted?

Roy Lambert [Team Elevate]

On Thu, 11 Aug 2011 13:22:25 +0100, Roy Lambert <roy@lybster.me.uk>
wrote:

>I don't think its ElevateDB. I'm positive the fault lies with FastReport, but I don't know if its re-writing the query. The reason I was asking Michael about what was in the report's data section is I wondered if FastReport was doing a call along the lines of FieldByName('"S"."ClientID"').AsString but since I don't have the source I can't check it out.

The FR people said FR does no SQL massaging of any kind, they just use
the passed dataset. I also agree it's nothing to do with EDB. So it
must be something really strange in my app. As soon as I have more
time I'll strip this down to figure out what's wrong. Thanks everyone.