You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Karthik Nandagiri <ka...@costrategix.com> on 2022/01/17 07:58:26 UTC
i need sql syntax for CASEWHEN with multiple conditions
Hi Team,
I want to write below SQL with Ignite SQL case.
SELECT c.postalcode,CASE WHEN(c.postalcode is not null and
(length(c.postalcode)=5 or length(c.postalcode)=10 or
length(c.postalcode)=9) and
REGEXP_LIKE(c.postalcode,'(^\\d{5}$)|(^\\d{9}$)|(\\d{5}-\\d{4})$','i'))
THEN 1 WHEN (c.postalcode='00000' AND (length(c.postalcode)=0 or
length(c.postalcode)<5)) THEN 0 else 0 END AS col from
customers as c join zipcodelkup as l on c.postalcode=l.postalcode;
Is this type of case statement valid in Ignite? if yes please suggest some
examples?
not able to find any examples here on the ignite documentation below
https://ignite.apache.org/docs/latest/sql-reference/system-functions
Regards
Karthik N
Re: i need sql syntax for CASEWHEN with multiple conditions
Posted by Stephen Darlington <st...@gridgain.com>.
Not tested, but something like: CASEWHEN (condition1, ’New’, CASEWHEN(condition2, ‘Average’, CASEWHEN (condition3, ‘Old’, ‘Old)))
It’s just an if/then statement, so you need to nest.
> On 17 Jan 2022, at 10:09, Karthik Nandagiri <ka...@costrategix.com> wrote:
>
> Thank you Stephen, I have already read this document link. But it does not explain the syntax for multiple when conditions.
>
> Like below
>
> SELECT somecolumn,CASE WHEN condition1 THEN 'New'
> WHEN condition2 THEN 'Average'
> WHEN condition3 1990 THEN 'Old'
> ELSE 'Old' END AS condition FROM Cars;
>
> how to write above as ignite sql?
>
> Regards
> Karthik N
>
>
>
>
> On Mon, Jan 17, 2022 at 3:21 PM Stephen Darlington <stephen.darlington@gridgain.com <ma...@gridgain.com>> wrote:
> Different syntax, but it looks like the CASEWHEN function should do what you need:
>
> https://ignite.apache.org/docs/latest/sql-reference/system-functions#casewhen <https://ignite.apache.org/docs/latest/sql-reference/system-functions#casewhen>
>
>> On 17 Jan 2022, at 07:58, Karthik Nandagiri <karthik.nandagiri@costrategix.com <ma...@costrategix.com>> wrote:
>>
>> Hi Team,
>>
>> I want to write below SQL with Ignite SQL case.
>>
>> SELECT c.postalcode,CASE WHEN(c.postalcode is not null and (length(c.postalcode)=5 or length(c.postalcode)=10 or length(c.postalcode)=9) and REGEXP_LIKE(c.postalcode,'(^\\d{5}$)|(^\\d{9}$)|(\\d{5}-\\d{4})$','i')) THEN 1 WHEN (c.postalcode='00000' AND (length(c.postalcode)=0 or length(c.postalcode)<5)) THEN 0 else 0 END AS col from customers as c join zipcodelkup as l on c.postalcode=l.postalcode;
>>
>> Is this type of case statement valid in Ignite? if yes please suggest some examples?
>> not able to find any examples here on the ignite documentation below
>> https://ignite.apache.org/docs/latest/sql-reference/system-functions <https://ignite.apache.org/docs/latest/sql-reference/system-functions>
>>
>> Regards
>> Karthik N
>>
>>
>>
>
Re: i need sql syntax for CASEWHEN with multiple conditions
Posted by Karthik Nandagiri <ka...@costrategix.com>.
Thank you Stephen, I have already read this document link. But it does not
explain the syntax for multiple when conditions.
Like below
SELECT somecolumn,CASE WHEN condition1 THEN 'New'
WHEN condition2 THEN 'Average'
WHEN condition3 1990 THEN 'Old'
ELSE 'Old' END AS condition FROM Cars;
how to write above as ignite sql?
Regards
Karthik N
On Mon, Jan 17, 2022 at 3:21 PM Stephen Darlington <
stephen.darlington@gridgain.com> wrote:
> Different syntax, but it looks like the CASEWHEN function should do what
> you need:
>
>
> https://ignite.apache.org/docs/latest/sql-reference/system-functions#casewhen
>
> On 17 Jan 2022, at 07:58, Karthik Nandagiri <
> karthik.nandagiri@costrategix.com> wrote:
>
> Hi Team,
>
> I want to write below SQL with Ignite SQL case.
>
> SELECT c.postalcode,CASE WHEN(c.postalcode is not null and
> (length(c.postalcode)=5 or length(c.postalcode)=10 or
> length(c.postalcode)=9) and
> REGEXP_LIKE(c.postalcode,'(^\\d{5}$)|(^\\d{9}$)|(\\d{5}-\\d{4})$','i'))
> THEN 1 WHEN (c.postalcode='00000' AND (length(c.postalcode)=0 or
> length(c.postalcode)<5)) THEN 0 else 0 END AS col from
> customers as c join zipcodelkup as l on c.postalcode=l.postalcode;
>
> Is this type of case statement valid in Ignite? if yes please suggest some
> examples?
> not able to find any examples here on the ignite documentation below
> https://ignite.apache.org/docs/latest/sql-reference/system-functions
>
> Regards
> Karthik N
>
>
>
>
>
Re: i need sql syntax for CASEWHEN with multiple conditions
Posted by Stephen Darlington <st...@gridgain.com>.
Different syntax, but it looks like the CASEWHEN function should do what you need:
https://ignite.apache.org/docs/latest/sql-reference/system-functions#casewhen
> On 17 Jan 2022, at 07:58, Karthik Nandagiri <ka...@costrategix.com> wrote:
>
> Hi Team,
>
> I want to write below SQL with Ignite SQL case.
>
> SELECT c.postalcode,CASE WHEN(c.postalcode is not null and (length(c.postalcode)=5 or length(c.postalcode)=10 or length(c.postalcode)=9) and REGEXP_LIKE(c.postalcode,'(^\\d{5}$)|(^\\d{9}$)|(\\d{5}-\\d{4})$','i')) THEN 1 WHEN (c.postalcode='00000' AND (length(c.postalcode)=0 or length(c.postalcode)<5)) THEN 0 else 0 END AS col from customers as c join zipcodelkup as l on c.postalcode=l.postalcode;
>
> Is this type of case statement valid in Ignite? if yes please suggest some examples?
> not able to find any examples here on the ignite documentation below
> https://ignite.apache.org/docs/latest/sql-reference/system-functions <https://ignite.apache.org/docs/latest/sql-reference/system-functions>
>
> Regards
> Karthik N
>
>
>