3 Replies Latest reply on Nov 6, 2012 9:01 AM by nheinze

    XML-Import didn't import SQL query of a SQL Transformation

    New Member

      Hi all,

       

      After the XML import of some mappings without errors, we noted that in all SQL Transformation objects the SQL queries were missing.

       

      Does anybody have any idea why this happened?

       

       

      We currently have Version 9.1.0 HotFix3 in use.

       

      Best regards,

      Christian

        • 1. Re: XML-Import didn't import SQL query of a SQL Transformation
          Guru

          Hi Christian,

           

          do these SQL queries contain double quotes " ? If so, I fear there will be no other solution but to manually copy-and-paste these SQL queries (at least until 9.1 HF2 the XML exporter creates invalid XML files if e.g. transformation descriptions contain double quotes).

           

          In any case I would open a support case, this doesn't sound normal to me, and in fact I haven't experienced this one myself yet.

           

          Regards,

          Nico

          • 2. Re: XML-Import didn't import SQL query of a SQL Transformation
            New Member

            Hi Nico,

             

            Thanks for your response.

            I checked our SQL query. Unfortunately there are no double quotes...

            Does Informatica have trouble also with other characters in SQL statements?

             

            Here is our SQL query which causes traouble:

             

            select B.ETL_PROZESS_STATUS as ETL_PROZESS_STATUS_FUNKTION  from (

            WITH TMP_TABLE AS

              ( SELECT DISTINCT B.FUNKTION AS FUNKTION_PARENT,

                C.FUNKTION                 AS FUNKTION_CHILD

              FROM GRIS04.RMETA_PROZESS_ABHAENGIGKEIT A

              JOIN

                (SELECT *

                FROM G04.RMETA_PROZESS_FUNKTION C

                JOIN GRIS04.RMETA_FUNKTION F

                ON F.IT_FUNKTION        = C.FUNKTION

                ) B ON A.PROZESS_PARENT = B.PROZESS

              JOIN

                (SELECT *

                FROM G04.RMETA_PROZESS_FUNKTION C

                JOIN G04.RMETA_FUNKTION F

                ON F.IT_FUNKTION       = C.FUNKTION

                ) C ON A.PROZESS_CHILD = C.PROZESS

              )

            SELECT DISTINCT FUNKTION_CHILD,

              TO_CHAR(CONNECT_BY_ROOT FUNKTION_PARENT) AS FUNKTION_PARENT

            FROM TMP_TABLE

              START WITH FUNKTION_PARENT     =  ?IT_FUNKTION?

              CONNECT BY NOCYCLE PRIOR FUNKTION_CHILD = FUNKTION_PARENT )  A left JOIN RMETA_FUNKTION_JOB B

              ON A.FUNKTION_CHILD = B.IT_FUNKTION

            WHERE A.FUNKTION_CHILD IN

              (SELECT IT_FUNKTION FROM G04.RMETA_FUNKTION WHERE PROZESS_RELEVANT_FLAG = 1 )

             

            Best regards,

            Christian

            • 3. XML-Import didn't import SQL query of a SQL Transformation
              Guru

              I can't see anything in the SQL query which looks odd. Except that the whole query looks odd to me.

               

              Please, open a support case for this. The only other suggestion I have is probably out of scope for you: rebuild the whole mapping from scratch such that you don't need such ugly SQL queries issued by a SQLT.

              Other than that, please take a closer look at the Transformation Guide, chapter SQL Transformation; there are some guidelines about what can be done with a SQLT and what cannot be done.

              Also, could you please try setting up this query as one single long line (without any line breaks) in the SQLT? Maybe the line end characters cause trouble...

               

              Good luck and please let us know what you find,

              Nico