topic Re: Invalid Character Constant in tOracleRow in Talend Studio

Invalid Character Constant in tOracleRow

Anonymous — Tue, 17 Apr 2012 10:27:45 GMT

Hi All,
When i try to run the below SQL query in tOracleRow_1 i am getting the following error "Invalid Character Constant". The below is the query which i use
"SELECT
p.sku,
md.name AS merch_div,
replace(replace(b.name, ',' , ' '), '"', '') AS brand_name,
replace(pg.name, ',' , ' ') AS product_grp,
(p.sku)||'-'||replace(replace(p.title, ',' , ' '), '"', '') AS title,
mg.name AS md_group,
p.list_description AS description,
p.season||case p.year
when 2014 then '2020'
when 2014 then '2019'
when 2014 then '2018'
when 2014 then '2017'
.
.
.
when 2001 then '2001'
when 2000 then '2000'
END AS Season
FROM public_bcs.products p
JOIN public_bcs.prod_group pg ON p.prod_group_id = pg.prod_group_id
JOIN public_bcs.merch_group_link_prod_group mgpg ON mgpg.prod_group_id = pg.prod_group_id
JOIN public_bcs.merchandise_groups mg ON mg.merch_group_id = mgpg.merch_group_id
JOIN public_bcs.merchandise_divisions md ON mg.merch_div_id = md.id
JOIN public_bcs.brand b on p.brand_id = b.brand_id
WHERE p.inactive = '0'
AND SUBSTR(p.sku,1,3) != 'GSO'"

Can any one help me what changes should be done to my SQl query to run correctly. I use the same query in SQL Developer it run's perfectly but why is it throwing error when running the same query in tOracleRow.
Kindly help me out.
Thanks and Regards,
Pavan

Re: Invalid Character Constant in tOracleRow

Anonymous — Tue, 17 Apr 2012 11:02:21 GMT

Hi Pavan
The difference between SQL Developer and tOracleRow is that in tOracleRow the query is a String.
Can you point out which line causes this error judging from the error log?

replace(replace(b.name, ',' , ' '), '"', '')

Why do you put double quotation here?
Maybe you have to change it like '\"'.
Regards,
Pedro

Re: Invalid Character Constant in tOracleRow

Anonymous — Tue, 17 Apr 2012 11:20:05 GMT

Hi Pedro,
The issue is resolved we need to keep a ('\"\') where ever the quotation marks are present. But i get another strange error "Key can not be resolved or is not a field". Why am i getting this errors? Can you help me out how to resolve these errors. I attached images of the error and my job. The query in the tOracleRow is same as the above query given and in tOracleOutput i had configured "Create Table if not Exists" and default action "Insert"
Thanks and Regards,
Pavan

Hi Pavan
The difference between SQL Developer and tOracleRow is that in tOracleRow the query is a String.
Can you point out which line causes this error judging from the error log?
replace(replace(b.name, ',' , ' '), '"', '')
Why do you put double quotation here?
Maybe you have to change it like '\"'.
Regards,
Pedro

Re: Invalid Character Constant in tOracleRow

Anonymous — Tue, 17 Apr 2012 18:09:07 GMT

Hi All,
Can any one tell me how to solve the issue mentioned in the above one?
Thanks and Regards,
Pavan

Re: Invalid Character Constant in tOracleRow

Anonymous — Wed, 18 Apr 2012 02:52:14 GMT

Hi Pavan
Linking tOracleRow with tOracleOutput directly can not pass query result into Oracle DB.
The job should be like this.
tOracleRow--main-->tParseRecordSet--main-->tOracleOutput
Add a column in tOracleRow and select "Object" data type.
Check Advanced settings-> "Propagate QUERY's recordset" and select the object column.
Or you can get a scenario about tParseRecordSet in TOS document.
Regards,
Pedro