Netezza and ELT UPDATE

[_AnonymousUser]

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

[Anonymous]

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

[_AnonymousUser]

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

[Anonymous]

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

[_AnonymousUser]

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

[Anonymous]

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