Working Hard Coded SQL throwing "Invalid Identifier" error when verified

[squish88]

OK,

  So I took a query I wrote (that contains a sub-select) from Oracle, and cut/pasted it into an SQL object.  When I verified it, I got this:

UDA-SQL-0107 A general exception has occurred during the operation "prepare". ORA-00904: "CONFIRMATION STATUS IND": invalid identifier RSV-SRV-0042

I have the correct Data Source (there's only one in the model), and I've tried it as both Pass-Through and Native...   The query runs fine in Oracle so there's no issue with the SQL or the column that Cognos seems to think is missing....

I can even take the sub-select (which is the only place that "missing" column exists in the query) and put it in it's own SQL object, and have it verify with NO issues.  It only seems to have an issue when it tries to recognize it in a sub select...  Any ideas as to why this is happening???

[almeids]

Sounds like some kind of syntactic problem - maybe something to do with the spaces in the name, or how you are aliasing the column in the subselect?  Haven't done much with hardcoded SQL (nor should you be! - ed.) so sorry I can't be more specific, and also not sure if this suggestion makes sense: since the subselect works OK in its own sql object, is there some means by which you can use it that way?

[blom0344]

Better post the entire SQL as such.

One hint (my 2 cents) : try to rewrite the SQL using the 'with' clause:

Common table expression = CTE

This is supported by Oracle from 9i onwards.
Cognos FM has a setting to write every model query subject using the 'with'  clause. I have no proof , but maybe this has a benefit as well for plain SQL queries.