I’m using DynamicWhere and setting a fieldexpression to “Member_Nbr” which is an aliased column for MEMBER_NBR
on my query in the Schema. I get an ORA-01008 error of invalid identifier. The rendered SQL has Member_Nbr in Quotes so I used “MEMBER_NBR” which is the original column name in the fieldexpression and it was also rendered in the SQL as “Member_Nbr” rather than “MEMBER_NBR” which would of course work just fine.
More information on this one… I have table A and table B both in a query and joined on a column BANKCODE which is in each table. I would normally build the where expression from (BANKCODE=:BANK_CODE) where BANK_CODE is the parameter. This builds correctly, but when the query is executed it throws and ORA error because BANKCODE exists in both tables and needs to be compared to the BANK_CODE parameter. I can’t get a.BANKCODE or b.BANKCODE to parse with WhereParser.Parse. Even if I alias the a.BANKCODE column as BANK_CODE DA says it can’t find it.
FieldExpression has a constructor that allows to set a table name and a field name separately. Still I would recommend you to change your SQL statement instead from something like
SELECT
FieldA,
FieldB
FROM
TABLE_1
INNER JOIN TABLE_2 ON
...
to something like
SELECT
FieldA,
FieldB
FROM (
SELECT
FieldA,
FieldB
FROM
TABLE_1
INNER JOIN TABLE_2 ON
...
)
Ie to move the join itself into a subquery. This shouldnt imply any performance hit, as the DB server’s SQL optimizer should unwrap the subquery. At the same time this will resolve any possible field name conflicts.
I’m also getting this error
An exception of type ‘RemObjects.DataAbstract.DAException’ occurred in RemObjects.DataAbstract.Server.dll but was not handled in user code
Additional information: CloseRound expected
on
WhereExpression dynamicWhere = WhereParser.Parse("(TableName.BANKCODE=:BANK_CODE)");
Unfortunately WhereParser.Parse doesn’t support the TableName.FiledName notation. This class is used in the query processing in the place where use of TableName.FiledName cannot happen at all.
That said WhereParser is an internal class that is discouraged to be used in the customer code. Note that Data Abstract exposes API to store DynamicWhere in the XML and to parse XML into Dynamic Where.