2 Replies Latest reply on Sep 30, 2021 6:56 AM by Jharana Patra

    SQL Lookup Transformation Throwing Errors

    Diane Royer Guru

      I'm trying to set up a mapping that will use a lookup transformation that contains an SQL Query.  Everything looks fine.  I am able to preview the data and see what I want.  However, when I try to run the whole mapping, I get an error message that says "RR_4035 SQL Error [ FnName: Execute -- [informatica][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.]. "  I don't understand this error.  My query is as follows:  SELECT next value for dbo.jexsnk_receipt_header_Seq  AS NewReceiptHeader_sql FROM z_mlc_current_sess.  My source type is set to Query and my Multiple Matches is set to Return Any Row.  The only input field is a reference field.  The Lookup Condition is that the reference fields is less than the value returned.  There is no Order By clause anywhere.  I even tried just outputting the results of this lookup to a flat file with one row.  I still get this error message.  Does anyone know how I can resolve this error?

        • 1. Re: SQL Lookup Transformation Throwing Errors
          Nico Heinze Guru

          I'm a PowerCenter guy, so I may well be wrong here, so please take this with a grain of salt.


          In PowerCenter, when you build a lookup query yourself, the Integration Service always adds an ORDER BY clause in the end (to sort the data by the lookup attributes, if I recall correctly, but that's not the relevant point here).

          Again: the Integration Service ALWAYS adds an ORDER BY clause on its own.


          The only way to "disable" this additional ORDER BY clause in PowerCenter is to finish the query with a double minus sign "--". The double minus sign introduces a comment in most SQL dialects, so the ORDER BY clause appended by the Integration Service will effectively become a comment (and hence not executed).


          You might want to try appending a double minus sign at the end of your query.

          Be careful to NOT hit the Enter key after the second minus sign, otherwise the ORDER BY clause will be appended in a new line, and that will effectively re-enable the ORDER BY clause (which you don't want).


          Again, this is PowerCenter knowledge, I don't know whether it applies to ICS / IICS as well.




          • 2. Re: SQL Lookup Transformation Throwing Errors
            Jharana Patra Guru

            Hi Daine ,


            To validate the above stated you can run the details in verbose mode and share the logs

            we can have a check.


            Please also have a look on the below if it helps.