Cacher is the code snippet organizer for pro developers

SELECT a.*, b.NumberOfRows 
FROM MYLIB.MYTABLE1 a 
CROSS JOIN 
(SELECT COUNT(*) AS NumberOfRows FROM MYLIB.MYTABLE2 as c WHERE c.fieldc1=9999) as b
WHERE a.fielda1=9999

SELECT a.*, b.NumberOfRows
FROM (
    SELECT column1, column2, ROW_NUMBER() OVER (ORDER BY :sortColumn :sortOrder) as ROW_NUMBER
    FROM MYLIB.MYTABLE
    WHERE column1='Y'
        AND column2 LIKE('%MYSTRING%')
    ORDER BY :sortColumn :sortOrder
) a
CROSS JOIN (
    SELECT COUNT(*) as NumberOfRows
    FROM MYLIB.MYTABLE
    WHERE column1='Y'
        AND column2 LIKE('%MYSTRING%')
) b
WHERE a.column2 LIKE('%MYSTRING%') AND a.ROW_NUMBER >= 1 AND a.ROW_NUMBER <= 10

SELECT a.*, b.NumberOfRows
FROM MYTABLE a
CROSS JOIN (SELECT COUNT(*) AS NumberOfRows FROM MYTABLE) b