How to also retrieve row/record count from following DB2 query?

Question

Need to also select the total record count given the following query. How would I go about doing this?


SELECT DISTINCT t.Creator AS TABLE_SCHEMA, t.Name AS Table_Name, c.Name AS COLUMN_NAME, 
c.COLTYPE AS DATA_TYPE, CASE WHEN c.KEYSEQ = 1 THEN 'Primary Key' ELSE NULL END AS CONSTRAINT_TYPE, NULL AS DATA_PRECISION, NULL AS DATA_SCALE 
    FROM SYSIBM.SYSTABLES t 
        INNER JOIN SYSIBM.SYSCOLUMNS c ON c.TBNAME = t.Name
    WHERE t.Creator='MY_SCHEMA_NAME' AND t.Name='MY_TABLE_NAME'

Any help would be appreciated, thanks

How do you want to get the total record count exactly? In an additional column with the same value for all rows? Somehow else? — Mark Barinstein
– Mark Barinstein, Commented Mar 2, 2020 at 7:16
Do you want to get the number of tables your query returned? Or the number of rows in each of those tables? — user330315
– user330315, Commented Mar 2, 2020 at 12:43
@Mark Barinstein - yes, an additional column with the same value for all rows. — weovibewvoibweoivwoiv
– weovibewvoibweoivwoiv, Commented Mar 2, 2020 at 13:14
@a_horse_woth_no_name number of rows in each of those tables — weovibewvoibweoivwoiv
– weovibewvoibweoivwoiv, Commented Mar 2, 2020 at 13:15

Mark Barinstein · Accepted Answer · 2020-03-02 14:04:15Z

1

For any SELECT statement including your one:

SELECT T.*, COUNT(1) OVER() AS ROW_COUNT
FROM
(
SELECT DISTINCT t.Creator AS TABLE_SCHEMA, t.Name AS Table_Name, c.Name AS COLUMN_NAME, 
c.COLTYPE AS DATA_TYPE, CASE WHEN c.KEYSEQ = 1 THEN 'Primary Key' ELSE NULL END AS CONSTRAINT_TYPE, NULL AS DATA_PRECISION, NULL AS DATA_SCALE 
    FROM SYSIBM.SYSTABLES t 
        INNER JOIN SYSIBM.SYSCOLUMNS c ON c.TBNAME = t.Name
    WHERE t.Creator='MY_SCHEMA_NAME' AND t.Name='MY_TABLE_NAME'
) T;

answered Mar 2, 2020 at 14:04

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-03-02 14:12:48Z

0

Need to also select the total record count given the following query.

The SELECT DISTINCT seems superfluous. The system tables should not have duplicates. If you are getting duplicates, it is probably because the JOIN conditions are not complete -- you should join on both the table name and creator.

So just use COUNT(*)

SELECT COUNT(*) 
FROM SYSIBM.SYSTABLES t INNER JOIN
     SYSIBM.SYSCOLUMNS c
     ON c.TBNAME = t.Name AND
        c.TBCREATOR = t.TBCREATOR
WHERE t.Creator = 'MY_SCHEMA_NAME' AND
      t.Name = 'MY_TABLE_NAME';

edited Mar 2, 2020 at 14:12

answered Mar 2, 2020 at 12:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

weovibewvoibweoivwoiv Over a year ago

I want to still be able to select the other values I need from the same query though

Gordon Linoff Over a year ago

@weovibewvoibweoivwoiv . . . That is not what your question is asking. This answers the question that you actually asked. You can, however, add count(*) over () to the fixed query (with the right join condition) to add an additional column with the count.

Collectives™ on Stack Overflow

How to also retrieve row/record count from following DB2 query?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related