Subquerying results in no row returned although records exist
One of the subqueries (derived tables) returns 0 rows and thus the whole query - which is a cross join of 5 tables - returns 0 rows as well.

You could rewrite with proper, explicit joins. If you make them outer (LEFT) joins, then you'll get results if one of the subqueries has 0 rows:

  SELECT EMP.EMPID ID, 
         COALESCE(PAID_TOTAL, 0) AS PAID_TOTAL,
         COALESCE(PAID_CURRENT, 0) AS PAID_CURRENT,
         COALESCE(PAID_PAST, 0) AS PAID_PAST,
         COALESCE(PAID_OT, 0) AS PAID_OT
  FROM EMPLOYEES EMP
    LEFT JOIN (
      SELECT EMPID,SUM(AMOUNT)PAID_TOTAL
      FROM PAYMENTS
      WHERE PERIOD&lt;='201207' AND ST=1
      GROUP BY EMPID
      ) PY_TOTAL ON EMP.EMPID=PY_TOTAL.EMPID
    ---
    --- more LEFT joins
    ---
    LEFT JOIN (
      SELECT EMPID,SUM(AMOUNT)PAID_OT
      FROM PAYMENTS
      WHERE PERIOD='201207' AND PAYMENTTYPE=2 AND ST=1
      GROUP BY EMPID
    ) PY_OT ON EMP.EMPID=PY_OT.EMPID ;




But it could be written even simpler, with only one join to Payments table:

  SELECT 
      EMP.EMPID AS ID, 
      COALESCE(SUM(CASE WHEN PERIOD&lt;='201207' AND ST=1 THEN AMOUNT END), 0)
          AS PAID_TOTAL,
      COALESCE(SUM(CASE WHEN PERIOD='201207' AND PAYMENTTYPE=1 AND ST=1 THEN AMOUNT END), 0)
          AS PAID_CURRENT,
      COALESCE(SUM(CASE WHEN PERIOD&lt;'201207' AND PAYMENTTYPE=1 AND ST=1 THEN AMOUNT END), 0)
          AS PAID_PAST,
      COALESCE(SUM(CASE WHEN PERIOD='201207' AND PAYMENTTYPE=2 AND ST=1 THEN AMOUNT END), 0)
          AS PAID_OT
  FROM EMPLOYEES  EMP
    LEFT JOIN PAYMENTS  P
      ON EMP.EMPID = P.EMPID
  GROUP BY EMP.EMPID ;


This may not be as efficient or need different indexes but it can easily be changed to include in the results only employees with at least one payment:

  FROM EMPLOYEES  EMP
    INNER JOIN PAYMENTS  P
      ON EMP.EMPID = P.EMPID


or employees with at least one payment in the examined period/restrictions:

  FROM EMPLOYEES  EMP
    INNER JOIN PAYMENTS  P
      ON  EMP.EMPID = P.EMPID
      AND (P.PERIOD&lt;='201207' AND P.ST=1)


The (P.PERIOD&lt;='201207' AND P.ST=1) condition is the OR of all the 4 conditions, simplified.