QueryRecord and NULLs

[Boris Tyukin <bo...@boristyukin.com>]

I am struggling for an hour now with a very simple thing.

I need to add 3 new fields to a record and set them to NULL but it does not
work.

I tried null instead - same thing. I checked Calcite docs and I do not see
anything special about NULL. And I know you can do it in SQL.

This works:

SELECT
*
,'' as unit_cerner_alias
,'' as room_cerner_alias
,'' as bed_cerner_alias
FROM FLOWFILE

But this does not:

SELECT
*
,NULL as unit_cerner_alias
,NULL as room_cerner_alias
,NULL as bed_cerner_alias
FROM FLOWFILE

Then I use LookupRecord processor to populate them or leave with NULL

Re: QueryRecord and NULLs

[Boris Tyukin <bo...@boristyukin.com>]

thanks so much, Koji, you saved my day!!

I've tried something similar with COALESCE but your trick worked best.

I was able to build a pretty complex flow that pulls data from 2 different
databases, does 3 lookups and I did not have to define schemas manually
(only lookup keys/fields). Actually used your github examples for merging
CSV files and Lookup example - they helped a lot to get started. Thanks for
doing all this work and sharing! you rock!

Boris

On Thu, Mar 7, 2019 at 8:26 PM Koji Kawamura <ij...@gmail.com> wrote:

> Using NULLIF can be a workaround. I was able to populate new columns with
> null.
>
> SELECT
> *
> ,NULLIF(5, 5) as unit_cerner_alias
> ,NULLIF(5, 5) as room_cerner_alias
> ,NULLIF(5, 5) as bed_cerner_alias
> FROM FLOWFILE
>
> On Fri, Mar 8, 2019 at 7:57 AM Boris Tyukin <bo...@boristyukin.com> wrote:
> >
> > I am struggling for an hour now with a very simple thing.
> >
> > I need to add 3 new fields to a record and set them to NULL but it does
> not work.
> >
> > I tried null instead - same thing. I checked Calcite docs and I do not
> see anything special about NULL. And I know you can do it in SQL.
> >
> > This works:
> >
> > SELECT
> > *
> > ,'' as unit_cerner_alias
> > ,'' as room_cerner_alias
> > ,'' as bed_cerner_alias
> > FROM FLOWFILE
> >
> > But this does not:
> >
> > SELECT
> > *
> > ,NULL as unit_cerner_alias
> > ,NULL as room_cerner_alias
> > ,NULL as bed_cerner_alias
> > FROM FLOWFILE
> >
> > Then I use LookupRecord processor to populate them or leave with NULL
>

Re: QueryRecord and NULLs

[Koji Kawamura <ij...@gmail.com>]

Using NULLIF can be a workaround. I was able to populate new columns with null.

SELECT
*
,NULLIF(5, 5) as unit_cerner_alias
,NULLIF(5, 5) as room_cerner_alias
,NULLIF(5, 5) as bed_cerner_alias
FROM FLOWFILE

On Fri, Mar 8, 2019 at 7:57 AM Boris Tyukin <bo...@boristyukin.com> wrote:
>
> I am struggling for an hour now with a very simple thing.
>
> I need to add 3 new fields to a record and set them to NULL but it does not work.
>
> I tried null instead - same thing. I checked Calcite docs and I do not see anything special about NULL. And I know you can do it in SQL.
>
> This works:
>
> SELECT
> *
> ,'' as unit_cerner_alias
> ,'' as room_cerner_alias
> ,'' as bed_cerner_alias
> FROM FLOWFILE
>
> But this does not:
>
> SELECT
> *
> ,NULL as unit_cerner_alias
> ,NULL as room_cerner_alias
> ,NULL as bed_cerner_alias
> FROM FLOWFILE
>
> Then I use LookupRecord processor to populate them or leave with NULL