Skip to Content

Invalid query in IDT but not in UDT

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?

Add comment
10|10000 characters needed characters exceeded

  • 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.

  • 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.

  • Get RSS Feed

3 Answers

  • Apr 19 at 03:53 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 19 at 04:17 PM

    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).

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • Apr 22 at 02:57 PM

    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>
    Add comment
    10|10000 characters needed characters exceeded