SELECT r.id, r.hid,
    (CASE WHEN dt.dt_type = 0 THEN v.str_value
         WHEN dt.dt_type IN  (1, 3) THEN to_char(v.num_value)
         WHEN dt.dt_type = 2 THEN to_char(v.dat_value)
         /*when dt.dt_type >= 4 then v.blob_value*/
         END) VALUE,
     (SELECT COUNT(1) FROM d_v_add_dir_rows r1 WHERE r1.HID = r.Id) HASCHILDRENS

      FROM d_v_add_directories d,
           d_v_add_dir_rows r,
           d_v_add_dir_columns c,
           d_v_add_datatypes dt,
           d_v_add_dir_values v,
             TABLE(d_pkg_cse_accesses.GET_ID_WITH_RIGHTS(:LPU, 'ADD_DIR_ROWS', 3, :CABLAB)) t1
    WHERE r.pid = d.id
      AND c.pid = d.id AND c.is_visible = 1
      AND d.ad_code = :code
      AND d.version = d_pkg_versions.GET_VERSION_BY_LPU(1, :LPU, 'ADD_DIRECTORIES')
      AND v.D_COLUMN_ID = c.id
      AND v.d_row = r.id
      AND t1.COLUMN_VALUE = r.ID
      AND c.data_type_id = dt.id
      AND ((r.hid = :parent) OR (r.hid IS NULL AND :parent IS NULL))
    ORDER BY r.r_order