Invalid query in IDT but not in UDT

[first_last]

I have a query with a subquery that will execute correctly in SQL Developer. For some reason, running the same query in IDT will generate an Invalid Query [ORA-009000] error.

I've tried three ways of representing the query in IDT.

1) As a filter object (in the business layer):

CMS_FOLDER.SNAPSHOT_ID = 
(   
SELECT Max(SNAPSHOT_ID)    
FROM   EYE_SNAPSHOT    
WHERE  SNAPSHOT_NAME = 'CMS'      
AND    SNAPSHOT_RESULT = '1'  
)

2) As a filter on the CMS_FOLDER table (in the data foundation):

CMS_FOLDER.SNAPSHOT_ID = 
(   
SELECT Max(SNAPSHOT_ID)    
FROM   EYE_SNAPSHOT    
WHERE  SNAPSHOT_NAME = 'CMS'      
AND    SNAPSHOT_RESULT = '1'  
)

3) As a derived table (in the data foundation):

SELECT *
FROM  CMS_FOLDER
WHERE
(  
  CMS_FOLDER.SNAPSHOT_ID =  
  (    
    SELECT Max(SNAPSHOT_ID)    
    FROM   EYE_SNAPSHOT    
    WHERE  SNAPSHOT_NAME = 'CMS'    
    AND    SNAPSHOT_RESULT = '1'  
  )
)

Oddly enough, the derived-table option works correctly in UDT.

The data-foundation in IDT is marked as ANSI92=Yes.

What is the problem with IDT?

[Joe_Peters]

ORA-00900 is an invalid SQL error, so it must be doing something bad. Are you getting this error when parsing the objects or when creating a query? If when creating a query, check to see what the entire generated SQL statement looks like, that may shed some light on what's going on.

[first_last]

I parsed the SQL when creating the DT in IDT--no errors. I parsed the filters, too--no errors.

The full query, in each case, looks like #3.

[mhmohammed]

Hi Craig,

Instead of using * (asterisk) in the outer query, list out all the columns, that should do the trick.

Thanks,
Mahboob Mohammed

SELECT Col1, Col2, Col3,... ColN
 FROM  CMS_FOLDER
WHERE(  
  CMS_FOLDER.SNAPSHOT_ID =(SELECTMax(SNAPSHOT_ID)FROM   EYE_SNAPSHOT    
    WHERE  SNAPSHOT_NAME ='CMS'AND    SNAPSHOT_RESULT ='1'))<br>

[ayman_salem]

When you cut and paste the SQL text, sometimes they contain hidden characters (CR, LF).

Therefore, try writing the SQL in one line to make sure none (CR, LF) are included.

Also, make sure that the character ' is correct (it changed when copying and pasting).

[first_last]

I would be inclined to agree with you, but I copied the same query from the same source. The query worked in one DF but no the other.

I tried what you suggested, in any case, but it had no effect.

[first_last]

I created a new data foundation, added the table and the filter, and I was able to see the table's values. By the way, the ANSI92 setting had no effect.

Either 1) the DF was corrupted or 2) there's a bug in IDT. I'm guessing that #2 is more likely.