Postgres is not supported via ODBC.

Please see the official ODBC support statement: Informatica Support Statement for ODBC drivers with Informatica products

Hi Inti,

Please share the SQL query which you are executing.

Thanks,

Smitha

Hi Inti,

Effective with 10.4 there is a native connector for PostGreSQL.

Please note the following:

• Generally speaking ODBC is not certified/supported.
• ODBC can be used if Pushdown Optimization is enabled.

In addition to this, it is only supports for Source or Full pushdown and the ODBC subtype as PostgreSQL must be selected as per documentation.

• This means that the session would need to be able to SUCCESSULLY pushdown the mapping logic either to the source only or to both the source and target.
• Pushdown to target only is not supported.

Generally speaking a CREATE TABLE statement is not a SELECT statement, even though it may *contain* one (as in your case). And in a Source Qualifier you can only use SELECT statements, nothing else.

Regards,

Nico

Hi Inti,

Only Select statements should be used within Source Qualifier SQL query. SQL queries/functions such as drop, create, insert, and if are not supported in Source Qualifier SQL query. You can use Select statement with the following clauses: From, Where, Join, Union, Order By, or subqueries.

By design, Source Qualifier is used to represent the rows that the Integration Service reads when it runs a session. SQL query is a custom query that replaces the default query the Integration Service uses to read data from sources represented in this Source Qualifier transformation. A custom query overrides entries for a custom join or a source filter.

Please refer the below document for more details on the same:

https://knowledge.informatica.com/s/article/151750?language=en_US

Please let us know if this helped.

Regards,

Asmita

As stated by others, Create query cannot be used in the Source Qualifier.

You may use a Pre-SQL option at session to create a table prior to the session execution or use the Post-SQL to drop the temp table created.

Hope this helps.

Regards,

Rajan

As stated others, Create statement cannot be used in SQL query override. You can create a store procedure and try calling its has a source on it.

In order to give better avice, we need to understand this part of the whole process better.

What exactly are you trying to do here?

Why do you want to create a temporary table while reading data to be processed?

Can't these two process steps be separated? If not, why not?

Which DBMS do we talk about? Temporary tables have different properties in different DBMS, that's why I'm asking.

Regards,

Nico

I've never worked with PostGres myself, so I don't know how exactly a temporary table is set up there. Does this temporary table exist only during that particular DB session, or is this something like a "materialised view" in Oracle (which can be used in multiple processes)?

Honestly I still don't understand what this temp table is good for. If it's "stable" beyond the process during which it was created, for example a "staging" table should work fine as a replacement. Then you would simply need to extract ID and SYS_NAME from this "staging table" via a standard relational SQ and aggregate these data in an Aggregator transformation.

Either way, a SQ must contain only SELECT statements, nothing else. So you have to revise the whole process such that the creation of this "table" is separated from the actual aggregation process.

Regards,

Nico

I have not tried this in Oracle/Post but this does work in MS SQL.  The below block works just fine in a SQL Override to create the global temp in the scope of the Select statement and like temp tables removes it when the session is done.

BEGIN

select '1' as 'ID'

into #temp;

SELECT CUSTOMER.C_CUSTKEY, CUSTOMER.C_NAME, CUSTOMER.C_ADDRESS, CUSTOMER.C_NATIONKEY, CUSTOMER.C_PHONE, CUSTOMER.C_ACCTBAL, CUSTOMER.C_MKTSEGMENT, CUSTOMER.C_COMMENT , #temp.ID

FROM

CUSTOMER

inner join #temp on 1=1;

END

Thanks,

Scott