topic Re: Netezza and ELT UPDATE in Talend Studio

Netezza and ELT UPDATE

_AnonymousUser — Fri, 23 Mar 2012 05:40:25 GMT

Hi,

I'm using Talend for ELT (with nzjdbc).

When I want do do UPDATE:

update table_1 o
set pattern = i.pattern
from table_2 i
where o.id = i.id

I get error:
org.netezza.error.NzSQLException: ERROR: (2) This form of correlated query is not supported - consider rewriting

This UPDATE is working through nzsql.

Netezza is:
Model Number TwinFin 3
Software Revision 6.0.P10
Talend:
Talend Enterprise Data Integration Team edition 5.0.1
Thanks and bye
Darko

Re: Netezza and ELT UPDATE

Anonymous — Fri, 23 Mar 2012 06:31:39 GMT

Hi Darko

update table_1 o
set pattern = i.pattern
from table_2 i
where o.id = i.id

I don't see anything wrong here.
Could you show me the screenshot of your job?
Regards,
Pedro

Re: Netezza and ELT UPDATE

_AnonymousUser — Fri, 23 Mar 2012 07:33:15 GMT

It's not the same UDPATE sentence but it's the same thing.
I thing this is JDBC fault
thanks and bye d
talend log:
Updating with 1:
UPDATE TMP_SKODE_KNJIZBE_KLJUCI SET LIKVIDATOR_NAKAZILA_ID= OSEBE.OSEBA_ID FROM PSB0010 INNER JOIN OSEBE ON OSEBE.EZSO = PSB0010.OSB000_ID_POS WHERE PSB0010.PSA000_ID = TMP_SKODE_KNJIZBE_KLJUCI.KNJIZBA_NK1 AND PSB0010.PSB000_ID = TMP_SKODE_KNJIZBE_KLJUCI.KNJIZBA_NK2
Exception in component tELTJDBCOutput_2
org.netezza.error.NzSQLException: ERROR: (2) This form of correlated query is not supported - consider rewriting
at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:276)
at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
at org.netezza.sql.NzConnection.execute(NzConnection.java:2639)
at org.netezza.sql.NzStatement._execute(NzStatement.java:848)
at org.netezza.sql.NzPreparedStatament.executeUpdate(NzPreparedStatament.java:197)
at etl_skode.polni_skode_knjizbe_kljuci_elt_0_2.polni_skode_knjizbe_kljuci_ELT.tELTJDBCMap_2Process(polni_skode_knjizbe_kljuci_ELT.java:913)
at etl_skode.polni_skode_knjizbe_kljuci_elt_0_2.polni_skode_knjizbe_kljuci_ELT.runJobInTOS(polni_skode_knjizbe_kljuci_ELT.java:3803)
at etl_skode.polni_skode_knjizbe_kljuci_elt_0_2.polni_skode_knjizbe_kljuci_ELT.main(polni_skode_knjizbe_kljuci_ELT.java:3624)

nzsql:
DEV_DIM(ADMIN)=> UPDATE TMP_SKODE_KNJIZBE_KLJUCI SET LIKVIDATOR_NAKAZILA_ID= OSEBE.OSEBA_ID FROM PSB0010 INNER JOIN OSEBE ON OSEBE.EZSO = PSB0010.OSB000_ID_POS WHERE PSB0010.PSA000_ID = TMP_SKODE_KNJIZBE_KLJUCI.KNJIZBA_NK1 AND PSB0010.PSB000_ID = TMP_SKODE_KNJIZBE_KLJUCI.KNJIZBA_NK2 ;
UPDATE 21147

Re: Netezza and ELT UPDATE

Anonymous — Fri, 23 Mar 2012 08:42:37 GMT

Hi
Try to run this sql in tNetezzaRow.
If still you get an error, that means the sql is wrong.
JDBC ELT component can generate normal sql.
But we can't sure it's right in Netezza.
Regards,
Pedro

Re: Netezza and ELT UPDATE

_AnonymousUser — Fri, 23 Mar 2012 11:15:46 GMT

The sql is working in tNetezzaRow. I tried with:
- (tNetezzaConnectuion -> tNetezzaRow) - working
- (tJDBCConnection -> tJDBCRow) - working
I'm lost HELP PLEASE !!!
bye d

Re: Netezza and ELT UPDATE

Anonymous — Mon, 26 Mar 2012 03:25:11 GMT

Hi
I think this is due to tELTJDBC components.
They can't generate SQL which is standard for Netezza.
You can report it on BugTracker for ELT Netezza components.
Regards,
Pedro