Phoenix1979

 Profile Answers by Phoenix1979 

• Mar 8th, 2010

 

The Lookup transformation will automatically order by the columns that is being returned. To bypass or suppress the order by put a "--" after the new order by clause:

auto generated from lookup:

select a, b
from table
order by a, b

with bypass:

select a , b
from table
order by a --

madhu.tella

 Profile Answers by madhu.tella 

• Mar 25th, 2010

 

Lookup override is nothing but just simply overriding the default sql which is generated by lookup by the runtime, default SQL contains SELECT and ORDER BY caluse and the order by clause orders the data based on the ports order which appear in the lookup ports tab.

If you want to makes changes in query to the default order of the ports, we need to provide two hyphens after the SQL, which means the SQL which is generated by Informaitca server will be commented.

Ex: Lookup ports in the look up:
empno
ename
sal

Default SQL: SELECT empno, ename, sal FROM emp ORDER BY empno, ename, sal;

User defind SQL: The SQL you want to modify in the lookup as
SELECT empno, sal, name FROM emp ORDER BY empno, sal, ename;

The SQL should be written like this

SELECT empno, sal, name FROM emp ORDER BY empno, sal, ename

While executing the workflow informatica treats the SQL like below:

SELECT empno, sal, name FROM emp ORDER BY empno, sal, ename
SELECT empno, ename, sal FROM emp ORDER BY empno, ename, sal

The SQL which is generated by informatica will be treated as a comment, so it will execute the first query only.

khurana29_amit

 Profile Answers by khurana29_amit 

• May 16th, 2010

 

If I write the overide query for lookup without order by and  "--" at the end then will it take order by from default query?