FilterSQL and aliased columns in queries

[grexhide]

Act:

Code: Select all

SmartQuery1.SQL := 'SELECT OWNER SCHEMA, A.* FROM SYS.ALL_OBJECTS A';
SmartQuery1.FilterSQL := 'SCHEMA = ''SYS''';
SmartQuery1.Active := True;

ORA-00904: "SCHEMA: invalid identifier"

In a SQL Monitor ODAC seems to run following:

Code: Select all

SELECT OWNER SCHEMA, A.* FROM SYS.ALL_OBJECTS A
WHERE SCHEMA = 'SYS' /code]



[b]Expected: [/b]

In a case of FilterSQL, aliased columns and Oracle versions >= 9 FilterSQL must be act as following:

[code]SELECT * FROM
(
  SELECT OWNER SCHEMA, A.* FROM SYS.ALL_OBJECTS A
)
WHERE SCHEMA = 'SYS' 

Affected: all versions of ODAC

[Plash]

We'll consider possibility to implement the functionality that you have suggested, in either of the next ODAC versions.
For current ODAC version you should change the FilterSQL property to 'OWNER = ''SYS'''.

[jfudickar]

I'm not shure if i like your suggestion.

It should be optional.

The current way is the default oracle way.

Greetings
Jens