You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by ankit beohar <an...@gmail.com> on 2016/08/11 07:00:47 UTC
Phoenix Ifnull
Hi All,
I want to implement If null in apache phoenix like mysql.
select ifnull(rowkey,myvalue),ifnull(col1,mycolvalue) from table where
rowkey='abc';
I tried below query but did not work:-
select case when APP_KEY is null then 'nullvalue' else APP_KEY end from
"CheckDump";
Best Regards,
ANKIT BEOHAR
Re: Phoenix Ifnull
Posted by Michael McAllister <mm...@homeaway.com>.
Seeing as we’re talking COALESCE and NULLs, depending on the version Ankit is running, this could also be the issue in PHOENIX-2994:-
https://issues.apache.org/jira/browse/PHOENIX-2994
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image001.png@01D1F472.F62D5470]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: Lukáš Lalinský <la...@gmail.com>
Reply-To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
Date: Friday, August 12, 2016 at 4:57 AM
To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
Subject: Re: Phoenix Ifnull
I think this is a problem with the WHERE clause. NULL values are neither equal nor not-equal to any other values. You might need to add "OR API_KEY IS NULL" to the WHERE clause.
Lukas
On Fri, Aug 12, 2016 at 9:51 AM, ankit beohar <an...@gmail.com>> wrote:
Hi Lukáš/James,
I have one table in which only one rowkey is available and for my null check case I am firing below queries and in that null check is not working:-
[nline image 1]
Please see and let me know if I am doing right thing or missed something.
Best Regards,
ANKIT BEOHAR
On Thu, Aug 11, 2016 at 9:18 PM, James Taylor <ja...@apache.org>> wrote:
Lukáš is correct, but if the CASE WHEN variant of the same didn't work, please file a JIRA (ideally with a unit test that repros the problem).
Thanks,
James
On Thu, Aug 11, 2016 at 12:20 AM, Lukáš Lalinský <la...@gmail.com>> wrote:
On Thu, Aug 11, 2016 at 9:00 AM, ankit beohar <an...@gmail.com>> wrote:
I want to implement If null in apache phoenix like mysql.
select ifnull(rowkey,myvalue),ifnull(col1,mycolvalue) from table where rowkey='abc';
I tried below query but did not work:-
select case when APP_KEY is null then 'nullvalue' else APP_KEY end from "CheckDump";
The standard SQL function for this is coalesce(col, 'nullvalue').
https://phoenix.apache.org/language/functions.html#coalesce
Lukas
Re: Phoenix Ifnull
Posted by Lukáš Lalinský <la...@gmail.com>.
I think this is a problem with the WHERE clause. NULL values are neither
equal nor not-equal to any other values. You might need to add "OR API_KEY
IS NULL" to the WHERE clause.
Lukas
On Fri, Aug 12, 2016 at 9:51 AM, ankit beohar <an...@gmail.com>
wrote:
> Hi Lukáš/James,
>
> I have one table in which only one rowkey is available and for my null
> check case I am firing below queries and in that null check is not working:-
>
> [image: Inline image 1]
>
> Please see and let me know if I am doing right thing or missed something.
>
> Best Regards,
> ANKIT BEOHAR
>
>
> On Thu, Aug 11, 2016 at 9:18 PM, James Taylor <ja...@apache.org>
> wrote:
>
>> Lukáš is correct, but if the CASE WHEN variant of the same didn't work,
>> please file a JIRA (ideally with a unit test that repros the problem).
>>
>> Thanks,
>> James
>>
>> On Thu, Aug 11, 2016 at 12:20 AM, Lukáš Lalinský <la...@gmail.com>
>> wrote:
>>
>>> On Thu, Aug 11, 2016 at 9:00 AM, ankit beohar <an...@gmail.com>
>>> wrote:
>>>
>>>> I want to implement If null in apache phoenix like mysql.
>>>>
>>>> select ifnull(rowkey,myvalue),ifnull(col1,mycolvalue) from table where
>>>> rowkey='abc';
>>>>
>>>> I tried below query but did not work:-
>>>>
>>>> select case when APP_KEY is null then 'nullvalue' else APP_KEY end from
>>>> "CheckDump";
>>>>
>>>
>>> The standard SQL function for this is coalesce(col, 'nullvalue').
>>>
>>> https://phoenix.apache.org/language/functions.html#coalesce
>>>
>>> Lukas
>>>
>>
>>
>
Re: Phoenix Ifnull
Posted by ankit beohar <an...@gmail.com>.
Hi Lukáš/James,
I have one table in which only one rowkey is available and for my null
check case I am firing below queries and in that null check is not working:-
[image: Inline image 1]
Please see and let me know if I am doing right thing or missed something.
Best Regards,
ANKIT BEOHAR
On Thu, Aug 11, 2016 at 9:18 PM, James Taylor <ja...@apache.org>
wrote:
> Lukáš is correct, but if the CASE WHEN variant of the same didn't work,
> please file a JIRA (ideally with a unit test that repros the problem).
>
> Thanks,
> James
>
> On Thu, Aug 11, 2016 at 12:20 AM, Lukáš Lalinský <la...@gmail.com>
> wrote:
>
>> On Thu, Aug 11, 2016 at 9:00 AM, ankit beohar <an...@gmail.com>
>> wrote:
>>
>>> I want to implement If null in apache phoenix like mysql.
>>>
>>> select ifnull(rowkey,myvalue),ifnull(col1,mycolvalue) from table where
>>> rowkey='abc';
>>>
>>> I tried below query but did not work:-
>>>
>>> select case when APP_KEY is null then 'nullvalue' else APP_KEY end from
>>> "CheckDump";
>>>
>>
>> The standard SQL function for this is coalesce(col, 'nullvalue').
>>
>> https://phoenix.apache.org/language/functions.html#coalesce
>>
>> Lukas
>>
>
>
Re: Phoenix Ifnull
Posted by James Taylor <ja...@apache.org>.
Lukáš is correct, but if the CASE WHEN variant of the same didn't work,
please file a JIRA (ideally with a unit test that repros the problem).
Thanks,
James
On Thu, Aug 11, 2016 at 12:20 AM, Lukáš Lalinský <la...@gmail.com> wrote:
> On Thu, Aug 11, 2016 at 9:00 AM, ankit beohar <an...@gmail.com>
> wrote:
>
>> I want to implement If null in apache phoenix like mysql.
>>
>> select ifnull(rowkey,myvalue),ifnull(col1,mycolvalue) from table where
>> rowkey='abc';
>>
>> I tried below query but did not work:-
>>
>> select case when APP_KEY is null then 'nullvalue' else APP_KEY end from
>> "CheckDump";
>>
>
> The standard SQL function for this is coalesce(col, 'nullvalue').
>
> https://phoenix.apache.org/language/functions.html#coalesce
>
> Lukas
>
Re: Phoenix Ifnull
Posted by Lukáš Lalinský <la...@gmail.com>.
On Thu, Aug 11, 2016 at 9:00 AM, ankit beohar <an...@gmail.com>
wrote:
> I want to implement If null in apache phoenix like mysql.
>
> select ifnull(rowkey,myvalue),ifnull(col1,mycolvalue) from table where
> rowkey='abc';
>
> I tried below query but did not work:-
>
> select case when APP_KEY is null then 'nullvalue' else APP_KEY end from
> "CheckDump";
>
The standard SQL function for this is coalesce(col, 'nullvalue').
https://phoenix.apache.org/language/functions.html#coalesce
Lukas