ORA-00926: missing VALUES keyword

[Former Member]

Hi,

can someone please help me out here...

I try to insert my Variable Definition into a table so with every run it gets stored...

I tried different ways of the SQL insert statement

sql('TARGET_TABLE', 'INSERT INTO TARGET_TABLE VALUES ({job_name()})'); - failed

sql('TARGET_TABLE', 'INSERT INTO TARGET_TABLE (jobname) VALUES ({job_name()})');  - failed

sql('TARGET_TABLE', 'INSERT INTO TARGET_TABLE (number) VALUES (1)');  - failed

I keep getting the same error message again and again ... I checked the documentation but its the same there as I did tried several times.. also inputs from other reports mentioned I tried too...

Component Version of Designer ist 12.2.2

Thanks for your help,

BR

Roland

[paul_power]

Hi Roland,

We would need to see the ddl for the table the data is being inserted into.

Regards,

Paul

[Former Member]

Dear Bernhard...

I guess you haven't seen this reply from me to all.. Sorry I am new here...

yes its a Oracle schema...

Table looks like that:

BKW_PROCESSDATE 1  Y DATE  None 0  

BKW_CUTOFFDATE 2  Y DATE  None 0  

BKW_EXCLBOOK 3  Y VARCHAR2 (2000 Byte)  None 0  

BKW_EXCLSTATUS 4  Y VARCHAR2 (16 Byte)  None 0  

BKW_PRODUCT 5  Y VARCHAR2 (16 Byte)  None 0  

BKW_ALLCOUNTERPARTIES 6  Y NUMBER (1)  None 0  

BKW_SPECCOUNTERPARTIES 7  Y VARCHAR2 (16 Byte)  None 0  

BKW_LOADGENERAL 8  Y NUMBER (1)  None 0  

BKW_LOADTRADE 9  Y NUMBER (1)  None 0  

BKW_LOADINTERNALTRADE 10  Y NUMBER (1)  None 0  

BKW_EXLCTRADES 11  Y VARCHAR2 (4000 Byte)  None 0  

SQL Skripts being tried:

sql( 'EHALLEGRO','insert into EHALLEGRO.ALLEGRO.BKW_MAP_MIG_MIG_VARIABLE (bkw_jobname,bkw_processdate,bkw_cutoffdate,bkw_exclbook,bkw_exclstatus,bkw_product,bkw_allcounterparties,bkw_speccounterparties,bkw_loadgeneral,bkw_loadtrade,bkw_loadinternaltrade,bkw_exlctrades) values ([job_name( )],[sysdate( )],[$GV_CUTOFF_DATE],[$GV_EXCLUDED_BOOKIDS],[$GV_EXCLUDED_CONTRACT_STATUS],[$GV_PRODUCT_LIST],[$GV_INCL_ALL_COUNTERPARTIES],[$GV_INCL_SPEC_COUNTERPARTY],[$GV_LOAD_GENERAL],[$GV_LOAD_TRADE],[$GV_LOAD_INTERNAL_TRADE],[$GV_EXCLUDED_TRADES]))');

sql( 'EHALLEGRO','insert into EHALLEGRO.ALLEGRO.BKW_MAP_MIG_MIG_VARIABLE values ([job_name( )],[sysdate( )],[$GV_CUTOFF_DATE],[$GV_EXCLUDED_BOOKIDS],[$GV_EXCLUDED_CONTRACT_STATUS],[$GV_PRODUCT_LIST],[$GV_INCL_ALL_COUNTERPARTIES],[$GV_INCL_SPEC_COUNTERPARTY],[$GV_LOAD_GENERAL],[$GV_LOAD_TRADE],[$GV_LOAD_INTERNAL_TRADE],[$GV_EXCLUDED_TRADES]))');

If I try it without the datastore.schema.tablename it doesn't find the table..

Thanks for your assistance here.

Cheers

Roland

[former_member218171]

Hi Roland,

Have you tried:

sql('TARGET_TABLE', 'INSERT INTO TARGET_TABLE (jobname) VALUES ('1')');

OR

sql('TARGET_TABLE', 'INSERT INTO YYY.TARGET_TABLE (jobname) VALUES ('1')');

where YYY=database schema

What RDBMS are you using? Oracle? Please post the table structure.

Cheers!

[Former Member]

Dear Bernhard...

I guess you haven't seen this reply from me to all.. Sorry I am new here...

yes its a Oracle schema...

Table looks like that:

BKW_PROCESSDATE 1  Y DATE  None 0  

BKW_CUTOFFDATE 2  Y DATE  None 0  

BKW_EXCLBOOK 3  Y VARCHAR2 (2000 Byte)  None 0  

BKW_EXCLSTATUS 4  Y VARCHAR2 (16 Byte)  None 0  

BKW_PRODUCT 5  Y VARCHAR2 (16 Byte)  None 0  

BKW_ALLCOUNTERPARTIES 6  Y NUMBER (1)  None 0  

BKW_SPECCOUNTERPARTIES 7  Y VARCHAR2 (16 Byte)  None 0  

BKW_LOADGENERAL 8  Y NUMBER (1)  None 0  

BKW_LOADTRADE 9  Y NUMBER (1)  None 0  

BKW_LOADINTERNALTRADE 10  Y NUMBER (1)  None 0  

BKW_EXLCTRADES 11  Y VARCHAR2 (4000 Byte)  None 0  

SQL Skripts being tried:

sql( 'EHALLEGRO','insert into EHALLEGRO.ALLEGRO.BKW_MAP_MIG_MIG_VARIABLE (bkw_jobname,bkw_processdate,bkw_cutoffdate,bkw_exclbook,bkw_exclstatus,bkw_product,bkw_allcounterparties,bkw_speccounterparties,bkw_loadgeneral,bkw_loadtrade,bkw_loadinternaltrade,bkw_exlctrades) values ([job_name( )],[sysdate( )],[$GV_CUTOFF_DATE],[$GV_EXCLUDED_BOOKIDS],[$GV_EXCLUDED_CONTRACT_STATUS],[$GV_PRODUCT_LIST],[$GV_INCL_ALL_COUNTERPARTIES],[$GV_INCL_SPEC_COUNTERPARTY],[$GV_LOAD_GENERAL],[$GV_LOAD_TRADE],[$GV_LOAD_INTERNAL_TRADE],[$GV_EXCLUDED_TRADES]))');

sql( 'EHALLEGRO','insert into EHALLEGRO.ALLEGRO.BKW_MAP_MIG_MIG_VARIABLE values ([job_name( )],[sysdate( )],[$GV_CUTOFF_DATE],[$GV_EXCLUDED_BOOKIDS],[$GV_EXCLUDED_CONTRACT_STATUS],[$GV_PRODUCT_LIST],[$GV_INCL_ALL_COUNTERPARTIES],[$GV_INCL_SPEC_COUNTERPARTY],[$GV_LOAD_GENERAL],[$GV_LOAD_TRADE],[$GV_LOAD_INTERNAL_TRADE],[$GV_EXCLUDED_TRADES]))');

If I try it without the datastore.schema.tablename it doesn't find the table..

Thanks for your assistance here.

Cheers

Roland