none
Oracle Connect By Prior keeps saying invalid character

    Question

  • I have the following command:

    SELECT UNIQUE xt_ref, xt_refid, xt_repid FROM

    (  SELECT XTR.REFER as xt_ref, XTR.XTID as xt_id,
              XTO.XNAME as xt_refid, XTO.REPOSIT as xt_repid,         
              XTH.PARRENT as parrent, XTH.CHILDREFER as child_ref   
       FROM XReferences XTR                                 
              INNER JOIN XTable XTO ON XTO.XTID = XTR.XTID  
              LEFT OUTER JOIN XTree XTH ON XTR.XTID = XTH.PARRENT )

       START WITH xt_ref = :INPUT_REFER
       CONNECT BY PRIOR child_ref = xt_ref;

     

     

    Which is used to get a flat view of a hierarchical structure where all childs are uniqely represented (if the exists several places in the structure thats filtered out...

     

    The Query has been tested in Oracle SQL Developer where it works as expected.

     

    When i den try to use the OracleClient -> OracleCommand  (System.Data.OracleClient.OracleCommand)

    I Keep getting an ORA-00911: Invalid character

     

    And since its working in the Oracle SQL Developer, and that "Connect by prior" should be valid SQL for Oracle as well... im having a hard time to find out whats the actual problem is... can it be that the OracleClient in some way does not support the above?... and in that case how?... i mean its SQL it just has to pass on to Oracle...

    Thursday, December 27, 2007 10:55 AM

Answers

  •  

    Hi,

     

    Try removing semicolon (";") from your statement.

     

    Let me know the results please.

     

    HTH,

    Thursday, December 27, 2007 5:23 PM

All replies

  •  

    Hi,

     

    Try removing semicolon (";") from your statement.

     

    Let me know the results please.

     

    HTH,

    Thursday, December 27, 2007 5:23 PM
  • Against my highest beliefs, that worked...

     

    Ive always learned that ending Queries with ";" was the right thing to do...

    Is there any logic explanaition to why that does not apply here... and that it is infact the other way arround?...

     

    Just currious...

     

    But it solved the problem...

     

    Friday, December 28, 2007 10:40 AM