Talend community forum / Netezza and ELT UPDATE

kaucic · 2012-03-23 06:40:25

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

pedro · 2012-03-23 07:31:39

Hi Darko

Code:

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

kaucic · 2012-03-23 08:33:15

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

Uploaded Images

Last edited by kaucic (2012-03-23 08:38:34)

pedro · 2012-03-23 09:42:37

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

kaucic · 2012-03-23 12:15:46

The sql is working in tNetezzaRow. I tried with:
- (tNetezzaConnectuion -> tNetezzaRow) - working
- (tJDBCConnection -> tJDBCRow) - working

I'm lost HELP PLEASE !!!

bye d

pedro · 2012-03-26 04:25:11

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

Announcement

Unanswered posts

#1 2012-03-23 06:40:25

Netezza and ELT UPDATE

#2 2012-03-23 07:31:39

Re: Netezza and ELT UPDATE

Code:

#3 2012-03-23 08:33:15

Re: Netezza and ELT UPDATE

#4 2012-03-23 09:42:37

Re: Netezza and ELT UPDATE

#5 2012-03-23 12:15:46

Re: Netezza and ELT UPDATE

#6 2012-03-26 04:25:11

Re: Netezza and ELT UPDATE

Board footer