Adding number of days to date in DB2

Question

I have a table that looks for a row with eff_dte = WK_BCC_DATES. WK_BCC_DATES is a variable computed in a PL1 program, now I need this computation done in one query so QMF will do the computation. The computation is WK_BCC_DATES = DTE1 + NO_DAYS.

SELECT SUBSTR(PARM_VALUE,1,10)                  
FROM  BCD75DBA.BCCA6000 T60                    
WHERE T60.COUNTRY_CODE           = '896'       
AND  T60.SUBSIDIARY_CODE        = '01'        
AND  T60.PARM_NAME              = 'BCC_DATES' 
AND  T60.EFF_DTE = (SELECT MAX(T60A.EFF_DTE)              
                    FROM   BCD75DBA.BCCA6000 T60A           
                    WHERE  T60A.COUNTRY_CODE    = '896'     
                    AND    T60A.SUBSIDIARY_CODE = '01'      
                    AND    T60A.PARM_NAME = 'BCC_DATES')`

and

SELECT SUBSTR(PARM_VALUE,1,3)                  
FROM  BCD75DBA.BCCA6000 T60                    
WHERE T60.COUNTRY_CODE           = '896'       
AND  T60.SUBSIDIARY_CODE        = '01'        
AND  T60.PARM_NAME              = 'BCC_DAYS' 
AND  T60.EFF_DTE = (SELECT MAX(T60A.EFF_DTE)                
                    FROM   BCD75DBA.BCCA6000 T60A           
                    WHERE  T60A.COUNTRY_CODE    = '896'     
                    AND    T60A.SUBSIDIARY_CODE = '01'      
                    AND    T60A.PARM_NAME = 'BCC_DAYS')`

I tried grouping the first query AS DTE1 and then the second AS NO_DAYS but I am having an error "not valid in the context it is used".

Please advise what else I can do. I am using DB2 v9. Thanks.

Answer 1

So, you want to combine these queries? That's actually pretty easy. It's a little hard to tell what was causing the error without your combined query, but you may have been putting the alias out of place.
(For future reference, your tables appear to be some for of an EAV - Entity Attribute Value. Use that as a search term for future queries)

I think a slight re-writing will help you out here:

WITH Most_Recent_Rows AS (SELECT parm_name, parm_value,
                                 ROW_NUMBER() OVER(PARTITION BY parm_name 
                                                   ORDER BY eff_dte DESC) AS rn
                          FROM BCD75DBA.BCCA6000
                          WHERE country_code = '896'
                                AND subsidiary_code = '01'
                                AND parm_name IN ('BCC_DAYS', 'BCC_DATES'))
SELECT CAST(SUBSTR(bcc_dates.parm_value, 1, 10) AS DATE) + 
                              CAST(SUBSTR(bcc_days.parm_value, 1, 3) AS INTEGER) DAYS 
FROM Most_Recent_Rows bcc_days
JOIN Most_Recent_Rows bcc_dates
     ON bcc_dates.parm_name = 'BCC_DATES'
        AND bcc_dates.rn = 1
WHERE bcc_days.parm_name = 'BCC_DAYS'
      AND bcc_days.rn = 1

---

Incidentally, I think you were trying for something like the following:

SELECT bcc_days, bcc_dates
FROM (SELECT SUBSTR(rw.parm_value, 1, 3) AS bcc_days
      FROM BCD75DBA.BCCA6000 rw
      JOIN (SELECT country_code, subsidiary_code, parm_name, MAX(eff_date) AS eff_date
            FROM BCD75DBA.BCCA6000 
            WHERE parm_name = 'BCC_DAYS'
            GROUP BY country_code, subsidiary_code, parm_name) ref
        ON ref.country_code = rw.country_code
           AND ref.subsidiary_code = rw.subsidiary_code
           AND ref.parm_name = rw.parm_name
           AND ref.eff_date = rw.eff_date) bcc_days
CROSS JOIN (SELECT SUBSTR(rw.parm_value, 1, 10) AS bcc_dates
            FROM BCD75DBA.BCCA6000 rw
            JOIN (SELECT country_code, subsidiary_code, parm_name, MAX(eff_date) AS eff_date
                  FROM BCD75DBA.BCCA6000 
                  WHERE parm_name = 'BCC_DATES'
                  GROUP BY country_code, subsidiary_code, parm_name) ref
              ON ref.country_code = rw.country_code
                 AND ref.subsidiary_code = rw.subsidiary_code
                 AND ref.parm_name = rw.parm_name
                 AND ref.eff_date = rw.eff_date) bcc_dates

This is less ideal, due to the repletion in the sub-subqueries. This can be combined with the previous part of the answer (using a CTE for the GROUP BY, then joining twice). I'm not sure which approach will yield better performance actually.

Neither query has been tested, due to lack of sample data and desired results, and the fact I don't currently have an instance.