topic tSQLTemplateMerge in Oracle (can't use constant in insert clause) in Talend Studio

tSQLTemplateMerge in Oracle (can't use constant in insert clause)

Anonymous — Thu, 11 Jul 2013 14:18:54 GMT

Hi All
Talend 5.2 , Oracle 11g
I did simple example with tSQLTemplateMerge in Oracle
Added in SQLTemplate :
OracleMerge (instead of 2 templates that were there)
Simple merge works ok.

but say if I want in insert clause for field src_sys_id use not some source field , but constant
I can't do that thru user interface ! (I can choose only field names from combobox but not place there fixed value)
How Can I do this ?
I try to use additional ouptut clause - but have
java.sql.BatchUpdateException: error occurred during batching: ORA-00917: missing comma

Re: tSQLTemplateMerge in Oracle (can't use constant in insert clause)

Anonymous — Fri, 12 Jul 2013 03:59:24 GMT

Hi
Try to add double quote, "1", I just do a quick test on Mysql database and it works!
Shong

Re: tSQLTemplateMerge in Oracle (can't use constant in insert clause)

Anonymous — Fri, 12 Jul 2013 08:55:48 GMT

Hi
Try to add double quote, "1", I just do a quick test on Mysql database and it works!
Shong

Tried - not work - same error
java.sql.BatchUpdateException: error occurred during batching: ORA-00917: missing comma
I wonder how to resolve such prolems in General ?
looking into java code ?

int ADDITIONAL_INSERT_COLUMNS_LENGTH_tSQLTemplateMerge_1 = 1;
				String[] ADDITIONAL_INSERT_COLUMNS_TRG_COLUMN_VALUE_tSQLTemplateMerge_1 = new String;
				String[] ADDITIONAL_INSERT_COLUMNS_TRG_COLUMN_NAME_tSQLTemplateMerge_1 = new String;
				ADDITIONAL_INSERT_COLUMNS_TRG_COLUMN_VALUE_tSQLTemplateMerge_1 = "1";
				ADDITIONAL_INSERT_COLUMNS_TRG_COLUMN_NAME_tSQLTemplateMerge_1 = "SRC_SYS_ID";
				int INSERT_MAPPING_LENGTH_tSQLTemplateMerge_1 = 5;
....
if (hasAdditionalInsertColumns) {
						for (int i = 0; i < ADDITIONAL_INSERT_COLUMNS_TRG_COLUMN_NAME_tSQLTemplateMerge_1.length; i++) {
							if (flag) {
								insertColumnsStr += ",";
								insertValuesStr += ",";
							} else {
								flag = true;
							}
							insertColumnsStr += ("A2_PROD" + "." + ADDITIONAL_INSERT_COLUMNS_TRG_COLUMN_NAME_tSQLTemplateMerge_1);
							insertValuesStr += ("A0_PROD" + "." + ADDITIONAL_INSERT_COLUMNS_TRG_COLUMN_VALUE_tSQLTemplateMerge_1);
						}
					}

Re: tSQLTemplateMerge in Oracle (can't use constant in insert clause)

Anonymous — Fri, 12 Jul 2013 09:35:27 GMT

Hi Fedor
I did a test on version 5.3.1 and it works, please see my screenshots, it might be a bug on v5.2 which you are using, have a try to download the latest version 5.3.1 and test it again.
Shong

Re: tSQLTemplateMerge in Oracle (can't use constant in insert clause)

Anonymous — Fri, 12 Jul 2013 09:58:13 GMT

Now TI think there is no senese to use SQLTemlate components
I do it using tOracleRow
just insert there SQL :
Merge into
a2_prod Targ
using
(
Select * from a0_prod a
where a.stg_product_id < 100
) S1
on ( Targ.stg_product_id = S1.stg_product_id )
when not matched
then insert ( stg_product_id, product_code, product_name, vendor_code, product_desc , src_sys_id, load_id )
values ( s1.stg_product_id, s1.product_code, s1.product_name, s1.vendor_code, s1.product_desc , -1 , -1 )

it took 10 min
and with SQL*Templates it still not works

Re: tSQLTemplateMerge in Oracle (can't use constant in insert clause)

Anonymous — Fri, 12 Jul 2013 10:02:42 GMT

Hi Fedor
I did a test on version 5.3.1 and it works, please see my screenshots, it might be a bug on v5.2 which you are using, have a try to download the latest version 5.3.1 and test it again.
Shong

I think Additional fields - it is fields which are Not exist in Source DB
and in my case I needed to use Constant (-1) for Src_sys_id which were present in Both tables
Problem was that I could' not input manually field in Insert columns
ps Anyway - Thanks for answer
Now I think - using direct SQLs is best way

Re: tSQLTemplateMerge in Oracle (can't use constant in insert clause)

Anonymous — Mon, 15 Jul 2013 02:54:56 GMT

I think Additional fields - it is fields which are Not exist in Source DB
and in my case I needed to use Constant (-1) for Src_sys_id which were present in Both tables
Problem was that I could' not input manually field in Insert columns

The fields here should exist in the target table, you don't need to add these fields in the schema, which you want to defined in 'Additional Insert column' table, just input manually the fields in Insert columns, if you can't input the fields, it should be a bug on the version you are using. Anyway, you have find an alternative way right now and you are satisfied with it.

Shong