SQL - Is it possible to print all records or only one record taking into account count(*) in a table-CodePudding

I am trying to find a way to print the result taking into account if exists records in a table or not. If yes I should print all the records in a table, otherwise I should print only one record.

Example:

I have the Table Example:

ColA	Colb	ColC

If select count(*) from Example > 0 THEN Return

ColA	Colb	ColC
1	VA1	NULL
2	VB1	NULL
3	NULL	VA2

If select count(*) from Example <= 0 THEN Return

ColA	Colb	ColC
Result	NA	NA

Is it possible to do something like that? I am doing the development using PRESTO.

Thanks you in advance

CodePudding user response：

Try:

SELECT ColA,ColB,ColC
FROM Example
union
SELECT 
   'Result' as ColA,
   'NA' as ColB,
   'NA' as ColC
WHERE NOT EXISTS (SELECT ColA,ColB,ColC
                  FROM Example)

It is not needed to select the 3 columns in the sub-query, but currently I copy/pasted the query. It can be replaced by SELECT 1 or SELECT NULL (which is a matter of taste...)

CodePudding user response：

We could introduce a dummy/default row via a union, and then retain it only in the event of the Example table being empty:

 WITH cte AS (
    SELECT ColA, ColB, ColC, 0 AS pos FROM Example
    UNION ALL
    SELECT 'Result', 'NA, 'NA', 1
)

SELECT ColA, ColB, ColC
FROM cte
WHERE pos = 0 OR NOT EXISTS (SELECT 1 FROM cte WHERE pos = 0);