CODEWITHSUNDEEP
apache-beam
Nagesh Singh Chauhan
Nagesh Singh Chauhan

Reputation: 794

How to concatenate 2 or more columns in beamSql

I'm trying to concatenate 2 columns using delimiter as "."
code :

PCollection<BeamRecord> first = apps.apply(BeamSql.query(
            "SELECT *,('CatLib' || 'ProdKey') AS CatLibKey from PCOLLECTION"));

How shall I specify delimiter between 2 columns ?

Upvotes: 0

Views: 450

Answers (2)

Nagesh Singh Chauhan
Nagesh Singh Chauhan

Reputation: 794

Thanks @Impulse The Fox. I have modified my query to :

PCollection<BeamRecord> first = apps.apply(BeamSql.query(
            "SELECT Outlet, CatLib, ProdKey, Week, SalesComponent, DuetoValue, PrimaryCausalKey, CausalValue, ModelIteration, Published, (CatLib || '.' || ProdKey) AS CatLibKey from PCOLLECTION"));

and this worked perfectly.

Upvotes: 0

janssen-dev
janssen-dev

Reputation: 2771

I'd say go for

SELECT
    COALESCE(CatLib, '') || '.' || COALESCE(ProdKey, '') AS CatLibKey,
    (any other columns here...)
FROM
    PCOLLECTION;

but in SQL there is no "Select everything but column X" or "Select everything else" so you'd have to write down every name of the column you want to select.

Upvotes: 1

Related Questions