Coalesce Function in SQL

[Prototype]

Has anyone tried using COALESCE to concatenate data of many rows in a single row? I know this is possible in SQL Server, any ideas if I can do the same in SQL RPGLE or from STRSQL command line?

Ta

[DeadManWalks]

Re: Coalesce Function in SQL

Just to confirm, take a field from row X, and another field from row X + Y, and possibly another field from row X + Y + O. We do have a pro that will answer this, but my initial thought is not in the same way as MSSql or Oracle. Do you know what rows and what fields that you want, or is it dynamic? This is possible to do in RpgLeSql but I would have to see more.

[Prototype]

Re: Coalesce Function in SQL

Just to confirm, take a field from row X, and another field from row X + Y, and possibly another field from row X + Y + O.

Exactly what I need, I know it's possible in MS SQL using below (found with bit of googling)

 
[COLOR=#00008b]DECLARE[/COLOR] @Names VARCHAR([COLOR=#800000]8000[/COLOR])  
[COLOR=#00008b]SELECT[/COLOR] @Names = [COLOR=#00008b]COALESCE[/COLOR](@Names + [COLOR=#800000]', '[/COLOR], [COLOR=#800000]''[/COLOR]) + Name [COLOR=#00008b]FROM[/COLOR] People

[B.Hauser]

Re: Coalesce Function in SQL

Coalesce does not concatenate multiple rows or columns or other information, COALESCE converts a NULL Value into a Default value.

Depending on what you need you may try an recursive common table expression (RCTE), a hierarchical query clause in composition with the scalar function SYS_CONNECT_BY_PATH or write your own user defined function.

Birgitta

[Prototype]

Re: Coalesce Function in SQL

Coalesce does not concatenate multiple rows or columns or other information, COALESCE converts a NULL Value into a Default value.

Depending on what you need you may try an recursive common table expression (RCTE), a hierarchical query clause in composition with the scalar function SYS_CONNECT_BY_PATH or write your own user defined function.

Birgitta

Thanks Brigitta for introducing me to the world of CTE's and Recursive queries. I really hope that I would be able to write them myself in near future. (currently reading various articles on the same)

On a side note I am on V5 so can't use SYS_CONNECT_BY_PATH. But I looked at it's use and now I know how FTP sites list their folder structures, quite fascinating!!

[B.Hauser]

Re: Coalesce Function in SQL

The hierarchical query clause was introduced with PTF in Release 7.1.
For more information you may read the following article:
Hierarchical Queries with DB2 Connect By
A new method for recursively processing data relationships


Birgitta