You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Dan Horne <da...@redbone.co.nz> on 2019/12/09 00:36:09 UTC
Subquery failing - is there an alternative?
Hi All
I'm trying to run the following subquery but it returns an error that says
"cannot recognize input near 'select' 'max' '(' in expression specification"
select id,
first_name,
last_name,
change_seq
from person_source
where change_seq >
(select max(change_seq) from person_target);
If I replace the sub query with the actual maximum change_seq it works.
Is there another construct that should work?
Regards
Dan
Re: Subquery failing - is there an alternative?
Posted by Dan Horne <da...@redbone.co.nz>.
Alas, we’re on Hive 2.1
On Mon, 9 Dec 2019 at 9:28 PM, Vineet G <vg...@gmail.com> wrote:
> What version of hive are you using? Support for scalar subqueries was
> added in 2.2 (ref: HIVE-15544
> <https://issues.apache.org/jira/browse/HIVE-15544>)
>
> Vineet
>
> On Dec 9, 2019, at 7:58 AM, Devopam Mittra <de...@gmail.com> wrote:
>
> Please try with subquery alias .
> Regards
>
>
> On Mon, Dec 9, 2019, 6:06 AM Dan Horne <da...@redbone.co.nz> wrote:
>
>> Hi All
>>
>> I'm trying to run the following subquery but it returns an error that
>> says "cannot recognize input near 'select' 'max' '(' in expression
>> specification"
>>
>> select id,
>> first_name,
>> last_name,
>> change_seq
>> from person_source
>> where change_seq >
>> (select max(change_seq) from person_target) A;
>>
>> If I replace the sub query with the actual maximum change_seq it works.
>>
>> Is there another construct that should work?
>>
>> Regards
>>
>> Dan
>>
>
>
Re: Subquery failing - is there an alternative?
Posted by Vineet G <vg...@gmail.com>.
What version of hive are you using? Support for scalar subqueries was added in 2.2 (ref: HIVE-15544 <https://issues.apache.org/jira/browse/HIVE-15544>)
Vineet
> On Dec 9, 2019, at 7:58 AM, Devopam Mittra <de...@gmail.com> wrote:
>
> Please try with subquery alias .
> Regards
>
>
> On Mon, Dec 9, 2019, 6:06 AM Dan Horne <dan.horne@redbone.co.nz <ma...@redbone.co.nz>> wrote:
> Hi All
>
> I'm trying to run the following subquery but it returns an error that says "cannot recognize input near 'select' 'max' '(' in expression specification"
>
> select id,
> first_name,
> last_name,
> change_seq
> from person_source
> where change_seq >
> (select max(change_seq) from person_target) A;
>
> If I replace the sub query with the actual maximum change_seq it works.
>
> Is there another construct that should work?
>
> Regards
>
> Dan
Re: Subquery failing - is there an alternative?
Posted by Devopam Mittra <de...@gmail.com>.
Please try with subquery alias .
Regards
On Mon, Dec 9, 2019, 6:06 AM Dan Horne <da...@redbone.co.nz> wrote:
> Hi All
>
> I'm trying to run the following subquery but it returns an error that says
> "cannot recognize input near 'select' 'max' '(' in expression specification"
>
> select id,
>
> first_name,
>
> last_name,
>
> change_seq
>
> from person_source
>
> where change_seq >
>
> (select max(change_seq) from person_target) A;
>
>
> If I replace the sub query with the actual maximum change_seq it works.
>
>
> Is there another construct that should work?
>
>
> Regards
>
>
> Dan
>