top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Dynamic operators in where clause in Informatica?

+1 vote
503 views

Is it possible to create dynamic sql operator in Informatica using SQL Transformation. For eg.

SELECT p.id 
FROM products p
WHERE p.weight ?operator? '30'
where ?operator? can have values: <, > , =

or even: in, not in

posted Jun 7, 2014 by anonymous

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote

The SQL Editor window of SQL Transform allows to use parameter binding (?parameter?) and string substitution (~string~). You need the later:

SELECT p.id 
FROM products p
WHERE p.weight ~operator~ '30'
answer Jun 11, 2014 by Shweta Singh
+1 vote

Just as an alternative, you can use a parameter for the whole condition like -
$$sq_param = p.weight > '30'

answer Jun 12, 2014 by Shatark Bajpai
Similar Questions
0 votes

Is it possible to create dynamic sql operator in Informatica using SQL Transformation. For eg.

SELECT p.id 
FROM products p
WHERE p.weight ?operator? '30'
where ?operator? can have values: <, > , =

or even: in, not in

+1 vote

Using Informatica 9.1.0

Scenario

Get the Dimension key generated and inserted to the Fact table from the Fact load.

I have to load the Fact table with a dimension key along with other columns. This dimension record is created from within the same mapping. There are five different sessions using the same mapping and executes simultaneously to load the Fact table. In this case I'm using a dynamic lookup with 'Synchronize dynamic cache' enabled to get unique dimension records generated from the 5 sessions using some conditions. The dimension ID is generated using the Sequence-ID in associated expression of the lookup. When a single session alone is run it worked perfectly fine. But when the sessions were run parallely it started to show unique key violation error as random sessions tried to insert the same sequence which was already there.

To fix the issue I had to give persistent lookup cache enabled and Cache file name prefix. But I did not find this solution or this issue in any of the forums or in INFA communities. So not sure this is the right way of doing it or this is a bug of some kind.

Please let me know if you had similar issue or some different thoughts.

Thanks in advance

...