Netezza and ELT UPDATE

Not applicable

Netezza and ELT UPDATE

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
One Star

Re: Netezza and ELT UPDATE

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
Not applicable

Re: Netezza and ELT UPDATE

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
One Star

Re: Netezza and ELT UPDATE

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
Not applicable

Re: Netezza and ELT UPDATE

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

Re: Netezza and ELT UPDATE

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