DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic is not resolved, but there is a WORKAROUND.
Author Message
joycerecacho
Participant



Joined: 26 Aug 2008
Posts: 292

Points: 5411

Post Posted: Mon Jun 03, 2019 10:53 pm Reply with quote Back to top

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi everybody,

Ive been trying to use the Hive Connector but it returns the following error:

[SQLSTATE 42S22] java.sql.SQLSyntaxErrorException: [IBM][Hive JDBC Driver][Hive]Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 6:32 Invalid table alias or column reference 'nao informado': (possible column names are: nom...)

When I run the same statement through command line, it perfectly finishes, but through Datastage it does not.

Simple queries through connector does return however when I try commands like 'case when...' the error above returns.

Ive searched a bit and read that it was worthy to inform at the URL string connection the following parameter: UseNativeQuery=1 but it didnt work neither.

Any tip?


Here is the statement:

select
nome.num_cpf_cnpj,
obt.num_cpf_cnpj as num_cpf_cnpj_2,
nome.dat_nsc,
case
when nome.dat_nsc is null then 'nao informado'
when cast(months_between(current_date,cast(nome.dat_nsc as date))/12 as int) > 16 then 'maior idade'
else 'menor idade'
end as flag_idade,
case when obt.num_cpf_cnpj is null then 'nao'
else 'sim'
end as flag_obito

from
owner.bd_test1_golden_record nome

left join owner.bd_test2_golden_record obt
on nome.num_cpf_cnpj = obt.num_cpf_cnpj

where nome.num_cpf_cnpj = '00000000000000'


PS: metadata columns and query columns are equal.

Id really appreciate any help/tip.

Best regards,

_________________
Joyce A. Recacho
So Paulo/SP
Brazil
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42959
Location: Denver, CO
Points: 221538

Post Posted: Tue Jun 04, 2019 10:03 am Reply with quote Back to top

Just fishing here but is it actually the case statement that is the issue? It seems to think your string assignment inside it is a table alias or a column reference. Perhaps a test might help clarify the issue... in your shoes I'd try another case statement but with it using actual column references rather than strings. That might help determine where to go next.

_________________
-craig

Now I've got that feeling once again, I can't explain; you would not understand
this is now who I am. I have become comfortably numb.
Rate this response:  
Not yet rated
joycerecacho
Participant



Joined: 26 Aug 2008
Posts: 292

Points: 5411

Post Posted: Tue Jun 04, 2019 11:03 am Reply with quote Back to top

Hi Chulett.
Ive tried so many ways using 'case' command, unsuccessfully.
So I changed commands and used command 'if' instead.
It perfectly worked.

Sintax: if (<condition>,'ok_result','else_result')

Probably java (hive connector) does not interpret properly the command.

Thats it, it might be a bug to be corrected by IBM.

Thank you so much.

Best regards,

_________________
Joyce A. Recacho
So Paulo/SP
Brazil
Rate this response:  
Not yet rated
Display posts from previous:

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours