Subquery SQL DB2

Question

I am trying to create a subquery (for a particular column) inside my base query. the code is as follows.

 SELECT    z.po_id,
          max
              (
              select   etcdc.ship_evnt_tms
              FROM     covinfos.shipment_event etcdc
              WHERE    etcdc.ship_evnt_cd = '9P'
              AND      etcdc.ship_id=scdc.ship_id
              ORDER BY etcdc.updt_job_tms desc
              FETCH first ROW only) AS llp_estimated_delivery_cdc
FROM      covinfos.ibm_plant_order z
LEFT JOIN covinfos.ipo_line_to_case a
ON        z.po_id = a.po_id
LEFT JOIN covinfos.shipment scdc
ON        (
                    a.ship_id = scdc.ship_id
          AND       a.ship_to_loc_code = scdc.ship_to_loc_code
          AND       scdc.loc_type = 'CDC')
GROUP BY  z.po_id 

There seems to be some kind of typo somewhere based on the error message that pops up when I try to run the code.

BIC00004. DAL01008. An error occurred while accessing the database.
ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , ). SQLCODE=-104, 
SQLSTATE=42601, DRIVER=3.62.56; THE CURSOR SQL_CURLH200C1 IS NOT IN A 
PREPARED STATE. SQLCODE=-514, SQLSTATE=26501, DRIVER=3.62.56

However, at plain sight or at least my sight, there is nothing that spots the misstake. Furthermore, running the subselect in a blank sheet (outside the base query, new one) it does correctly.

Thanks

Answer 1

You would probably be best to remove the co-related sub-select, and just join to a plain sub-select. E.g.

SELECT    z.po_id,
          max(ship_evnt_tms) AS llp_estimated_delivery_cdc
FROM      covinfos.ibm_plant_order z
LEFT JOIN covinfos.ipo_line_to_case a
ON        z.po_id = a.po_id
LEFT JOIN covinfos.shipment scdc
ON        a.ship_id = scdc.ship_id
AND       a.ship_to_loc_code = scdc.ship_to_loc_code
AND       scdc.loc_type = 'CDC'
LEFT JOIN
    (    select ship_id
         ,      ship_evnt_tms
         FROM
         (   select  ship_id
            ,        ship_evnt_tms
            ,        row_number() over(partition by ship_id order by updt_job_tms desc) as RN
            FROM     covinfos.shipment_event
            WHERE    ship_evnt_cd = '9P'
         ) s
         WHERE RN = 1
    )  AS etcdc
ON        etcdc.ship_id=scdc.ship_id
GROUP BY  z.po_id 

P.S. you could just INNER JOINs unless you want to include po_id's with no ship_evnt_tms

Answer 2

Try adding parentheses around the sub-select. At least this then parses Data Studio using z/OS validation

SELECT    z.po_id,
          max
              ((
              select   etcdc.ship_evnt_tms
              FROM     covinfos.shipment_event etcdc
              WHERE    etcdc.ship_evnt_cd = '9P'
              AND      etcdc.ship_id=scdc.ship_id
              ORDER BY etcdc.updt_job_tms desc
              FETCH first ROW only)) AS llp_estimated_delivery_cdc
FROM      covinfos.ibm_plant_order z
LEFT JOIN covinfos.ipo_line_to_case a
ON        z.po_id = a.po_id
LEFT JOIN covinfos.shipment scdc
ON        (
                    a.ship_id = scdc.ship_id
          AND       a.ship_to_loc_code = scdc.ship_to_loc_code
          AND       scdc.loc_type = 'CDC')
GROUP BY  z.po_id 

Still, I'm not sure this is a very nice bit of SQL code.