IBM Mainframe Forum

by **Papya013** » Wed Mar 24, 2021 3:09 pm

Hi Team,

I am working on of the migration project and the table counts are validated once DB2 tables are loaded to Cloud platform.

The integration tool expects the counts of each table in a row format with a comma delimiter.

Select all

SELECT COUNT(*) FROM SCHEMA_NAME.TABLE1
UNION ALL
SELECT COUNT(*) FROM SCHEMA_NAME.TABLE2
UNION ALL
SELECT COUNT(*) FROM SCHEMA_NAME.TABLE3
 

The above Query results are as mentioned below

Select all

120
130
140

Could you please help me to get this result in below mentioned format.

120,130,140

Thanks in advance !

by **sergeyken** » Wed Mar 24, 2021 8:39 pm

One of straightforward solutions might be like this

Select all

SELECT T1.CNT,
    ',', T2.CNT,
    ',', T3.CNT
FROM (   
   SELECT COUNT(*) CNT, 1 ID 
   FROM SCHEMA_NAME.TABLE1
)                    T1,             
(   
   SELECT COUNT(*) CNT, 1 ID 
   FROM SCHEMA_NAME.TABLE2
)                    T2,
(   
   SELECT COUNT(*) CNT, 1 ID 
   FROM SCHEMA_NAME.TABLE3
)                    T3
WHERE T1.ID = T2.ID
  AND T1.ID = T3.ID
;
 

But I doubt if this approach would help in any migration project...

by **Papya013** » Thu Mar 25, 2021 9:32 am

Hi Team,

This worked for now. Thank you very much !!!!!

by **sergeyken** » Thu Mar 25, 2021 7:12 pm

Papya013 wrote:Hi Team,

This worked for now. Thank you very much !!!!!

I did not mention this explicitly, but it looks like you plan to create data in CSV format (but including only one single line - looks strange for migration project).

Nevertheless, any CSV format assumes character only data fields. Your SQL statement will produce mixed data, binary + character (unless you run it under SPUFI, or similar). Most likely, you would need to convert all extracted non-character fields to character format, using various SQL functions, like CAST, and others.

IBM Mainframe Forum

Convert Rows to Columns with Comma Delimiter

Convert Rows to Columns with Comma Delimiter

Re: Convert Rows to Columns with Comma Delimiter

Re: Convert Rows to Columns with Comma Delimiter

Re: Convert Rows to Columns with Comma Delimiter