Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

order by and functions

Highlighted

order by and functions

New Contributor

Hi,

 

I am trying to run the query in Hive

 

select concat(col1 , "\t", col2 ) from table1  order by col2, col1

 

but it gives following error:

 

Error while compiling statement: FAILED: SemanticException [Error 10004]: line 1:60 Invalid table alias or column reference 'col2': (possible column names are: _c0)

 

If I run

 

select col1 ,col2 from table1 order by col2, col1

it works fine but i need to output with tab delimiter (and sorted first by col2)

 

Any hint on how can I make the query run?

 

Thanks in advance

 

O.

3 REPLIES 3

Re: order by and functions

Explorer

Hi @omrubi .

 

Please try like below(just replace here deli.):-

 

CONCAT('hadoop','-','hive')

 

CONCAT_WS('-','hadoop','hive')

 

This is final solution in working Mode:-

 

SELECT CONCAT(CONCAT("(", CONCAT_WS("\t", col1, col2)), ")") as col3
FROM your_table

 

 

 

Thanks

HadoopHelp 

Re: order by and functions

New Contributor

Hi, thanks for trying to help but that did not solves the problem.

The issue comes from using order by and concat

Re: order by and functions

Explorer

Hi @omrubi .

your query is not correct :-

please try below query:-

 

SELECT col1,col2, CONCAT(CONCAT(CONCAT_WS("\t", col1, col2))) as col3
FROM table1 ORDER BY col1,col2

 

 

 

 

Thanks

HadoopHelp

Don't have an account?
Coming from Hortonworks? Activate your account here