SQL Expression all of a sudden causing an error

[alwayslrN]

I am using Crystal 9 and SQL Server 2000.

I have report that until this week has been working fine for months. Just ran it fine last Tuesday. For some reason, today, when it is run there is a SQL Expression that returns the following error:

Error in compiling SQL Expression Query Engine Error 42000 The column prefix 'LOANHIST' does not match with a table name or alias name in a query.

Here is the SQL Expression

(select sum(b.prin_bal_p)
from balhist b
where exists (select *
from loanhist l2
where l2.accounting_date = b.accounting_date and
{fn LEFT(l2.loan_name,{fn LENGTH(l2.loan_name)}-8)} = {fn LEFT(LOANHIST."LOAN_NAME",{fn LENGTH(LOANHIST."LOAN_NAME")}-8)} and
l2.loan = b.loan) and
b.accounting_date = PORTHIST."ACCOUNTING_DATE")

I have not idea why there is an issue all of a sudden AND LOANHIST is a table be used. When I did a test getting rid of the line with the LOANHIST table I get the same error for PORTHIST.

This is the most bizarre thing and I have to get this resolved sooner than ASAP. I have no time to create a view.

Any suggestions.

Thanks

 

[synapsevampire]

Why does this:

{fn LEFT(l2.loan_name,{fn LENGTH(l2.loan_name)}-8)} = {fn LEFT(LOANHIST."LOAN_NAME",{fn LENGTH(LOANHIST."LOAN_NAME")}-8)}

reference LOANHIST as a table name? It's been aliased as l2, and I'm not sure what it is intended to do anyway since it appears to be comparing the same values of loanhist to itself...

Is it supposed to reference the B table?

-k

 

[alwayslrN]

LOANHIST is the table in the main report. As the SQL expression shows, I am extracting a balance, but for information in the report I need to reference that balance by a loan name. The loan name is not in the table with the balances, but rather in the loanhist table. Thus, the SQL expression is saying for the loan in balhist match on the l2.loan, but not only that take the l2.loan that matches to the main reports LOANHIST.LOAN_NAME. I hope that clears that up.

BTW - I just did a very simple report just putting in the details the LOANHIST.LOAN and then creating the following SQL Expression

(select l.loan
from loanhist l
where "LOANHIST"."LOAN" = l.loan )

The table I am using is LOANHIST the reaulting SQL in Show SQL Query is

SELECT "LOANHIST"."LOAN"
FROM "LMS_NYL"."dbo"."LOANHIST" "LOANHIST"

As you can see from the test SQL Expression when I double click on Loan from the list of fields in Loanhist the result is "LOANHIST"."LOAN". When I hit the check to double check the SQL expression, I receive the same error. This is blowing my mind as this should be working. I do not know when the double quotes started happening, but since then, that is when I have started having issues.

Thanks

 

[alwayslrN]

I made another quick and dirty report which has the Show SQL Statement of

SELECT "PORTHIST"."LOAN"
FROM "LMS_NYL"."dbo"."PORTHIST" "PORTHIST"

and a test SQL Expression of

(select p.loan
from loanmast p
where "PORTHIST"."LOAN" = p.loan)

And I get the same error. PORTHIST does not match...blah blah blah

 

[synapsevampire]

Ahh, OK, I wondered if that was the case.

You can use real SQl instead of the FN stuff in crystal:

Something like:

LEFT(l2.loan_name,LEN(l2.loan_name)-8) = LEFT(LOANHIST.LOAN_NAME, LEN(LOANHIST.LOAN_NAME)-8)

Get it working in Query Analyzer and just paste it into a SQL Expression and it should work fine.

I avoid the Crystal functions and use real SQL directly. Note that if you check the Database->Show SQL Query you'll see what the report is passing as SQL as it converts your formula using Crystal functions, but again,if you know SQL, don't bother with them, use the real thing.

-k

 

[alwayslrN]

Hi k

Thanks -

I got rid of the fn stuff and where it read LOANHIST."LOAN_NAME" I changed it to "LOAN_NAME". The report works fine now, but I am still very concerned that up until this week the report was working fine. It disturbs me to not understand what happened making me have to make this change.

 

[alwayslrN]

K - I also wanted to let you know I found something on

http://www.cogniza.com/blog/?p=26.

I was having an additional problem with it saying PORTHIST does not match after I got rid of the FN stuff. When I got rid of the alias in the aggregate function I no longer received the PORTHIST does not match error.