On v9.7:


db2 "select varchar(count(0)) from (select count(c1) from test) as tmp"

1
-----------
1

1 record(s) selected.




On v8.2:

db2 "select varchar(count(0)) from (select count(c1) from test) as tmp"
SQL0440N No authorized routine named "VARCHAR" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884



I need to cast the result of the following query into a varchar:

db2 "select count(0) from (select count(c1) from test) as tmp"


v8 doesn't accept integer as an input to varchar(). Is there a way to accomplish this on v8?

Try CAST(blah as VARCHAR(88))

By the way, "select '1' from sysibm.sysdummy1" will also work, as the query always returns 1...

Make it CAST(CAST(blah as CHAR(88)) as VARCHAR(88))

Thanks, Nick. Using two CAST worked:

db2 "select cast(cast(count(0) as char(88)) as varchar(88)) from (select count(c1) from test) as tmp"

1
----------------------------------------------------------------------------------------
1

1 record(s) selected.


I'll try using sysibm.sysdummy1

why even run a query? the answer is always going to be 1

Shh, Bella is booking over time

This is not my query and I'm still trying to understand the purpose of count(0)... doing multiple things at the same time so had no time to really think about your response to my other posting but I will.

Could you please explain why the following query (attaching it here) returns 0.

most likely it is because the subquery returned no rows -- try running the subquery by itself

by the way, apologies to n_i, who had the same answer as i did in post #3 (i.e. it's always going to return 1)

Or how to replace "SELECT COUNT(0) AS "COUNT" " without changing the purpose of it. This count(0) is causing the query to fail with sql0901n which is what I'm trying to resolve.

change it to SELECT COUNT(*) ...

I think logically the entire query is equivalent to something like By the way, since the subquery is an outer join, the subtraction one of the SUM()s can be problematic, because both values could be null.