DB2 loop / union select help

I have code that I run one month at a time to look for events that occur during each period. I subsequently join all the data into one large dataset. I’d like instead to set a couple of variables and accomplish a single dataset via some kind of loop, but I am db2 impaired.

I’ve included an example of the code for a specific month (Aug 2006). The code is unecessarily complex because, when it was developed, DB2 couldn't do group by's on derived column names.

I usually change the two dates (‘2006-08-01’ at the third select and then about ½ way through the code) and rerun for each month. I’d like instead to establish a loop that has a begin month and an end month defined and then loops through each month appending the dataset together.

Appreciate any help!

SELECT
CASE WHEN BU IS NULL THEN '00 - xx' ELSE BU END AS BUSINESS_UNIT,
DIV AS DIVISION,
DEPT AS DEPARTMENT,
CC AS COST_CENTER,
JCODE AS JOB_DESCRIPTION,
TV4 AS DATE,
COUNT(DISTINCT TV5) AS POSITIONS,
COUNT(DISTINCT TV6) AS EMPLOYEES
FROM
(SELECT
 UNIT_DEPTIDE CONCAT ' - ' CONCAT UNIT_DESC AS BU,
DIV_DEPTIDE CONCAT ' - ' CONCAT DIV_DESC AS DIV,
DEPT_DEPTIDE CONCAT ' - ' CONCAT DEPT_DESC AS DEPT,
SUBSTR(TV1,07,04) CONCAT ' - ' CONCAT CC_DESC AS CC,
TV2 CONCAT ' - ' CONCAT TV3 AS JCODE,
TV4,TV5, TV6
FROM
(SELECT
   A.EMPLID AS TV6,
   A.DEPTID AS TV1,
   A.JOBCODE AS TV2,
   B.DESCR AS TV3,
   A.POSITION_NBR AS TV5,
   A.EMPL_STATUS,
   A.LOCATION,
   A.REG_TEMP,
   A.FULL_PART_TIME,
   A.STD_HOURS,
   B.SETID,
   B.JOBCODE,
   B.EFFDT,
   ('2006-08-01') AS TV4
FROM
   HRQRY.PS_JOB A,
   HRQRY.PS_EMPLMT_SRCH_QRY A1,
   HRQRY.PS_JOBCODE_TBL B,
   HRQRY.PS_SET_CNTRL_REC B2
WHERE
   B.JOBCODE = A.JOBCODE
   AND B2.SETCNTRLVALUE = A.BUSINESS_UNIT
   AND B2.RECNAME = 'JOBCODE_TBL'
   AND B2.SETID = B.SETID
   AND A.EMPLID = A1.EMPLID
   AND A.EMPL_RCD = A1.EMPL_RCD
   AND A1.ROWSECCLASS = 'ALLPANLS'
   AND (A.EFFDT =(
      SELECT
         MAX(A_ED.EFFDT)
      FROM
         HRQRY.PS_JOB A_ED
      WHERE
         A.EMPLID = A_ED.EMPLID
         AND A.EMPL_RCD = A_ED.EMPL_RCD
         AND A_ED.EFFDT <= '2006-08-01'
   )
   AND A.EFFSEQ =(
      SELECT
         MAX(A_ES.EFFSEQ)
      FROM
         HRQRY.PS_JOB A_ES
      WHERE
         A.EMPLID = A_ES.EMPLID
         AND A.EMPL_RCD = A_ES.EMPL_RCD
         AND A.EFFDT = A_ES.EFFDT
   )
   AND A.JOBCODE <> '000004'
   AND A.REG_TEMP = 'R'
   AND A.STD_HOURS > 19.99
   AND A.EMPL_STATUS IN ('A',
   'L',
   'P',
   'S')
   AND B.EFFDT =(
      SELECT
         MAX(B_ED.EFFDT)
      FROM
         HRQRY.PS_JOBCODE_TBL B_ED
      WHERE
         B.SETID = B_ED.SETID
         AND B.JOBCODE = B_ED.JOBCODE
         AND B_ED.EFFDT <= A.EFFDT
   ))
ORDER BY
   7,
   2 DESC,
   4) AS T1
LEFT OUTER JOIN
(SELECT
P.DEPTID AS CC_DEPTIDE, P.DESCR AS CC_DESC,
SUBSTR(P.DEPTID,01,06) AS DEPT_DEPTIDE, QDT.DESCR AS DEPT_DESC,
SUBSTR(P.DEPTID,01,04) AS DIV_DEPTIDE, QDV.DESCR AS DIV_DESC,
SUBSTR(P.DEPTID,01,02) AS UNIT_DEPTIDE, QUN.DESCR AS UNIT_DESC
FROM
HRQRY.PS_DEPT_TBL AS P
LEFT OUTER JOIN
(SELECT SETID, DEPTID, DESCR
FROM HRQRY.PS_DEPT_TBL D1
WHERE EFFDT =
(SELECT MAX(EFFDT)
FROM HRQRY.PS_DEPT_TBL D2
WHERE D2.SETID = D1.SETID
AND D2.DEPTID = D1.DEPTID
AND D2.EFFDT <= CURRENT DATE)) AS QDT
ON P.SETID = QDT.SETID
AND SUBSTR(P.DEPTID,01,06) = QDT.DEPTID
LEFT OUTER JOIN
(SELECT SETID, DEPTID, DESCR
FROM HRQRY.PS_DEPT_TBL D3
WHERE EFFDT =
(SELECT MAX(EFFDT)
FROM HRQRY.PS_DEPT_TBL D4
WHERE D4.SETID = D3.SETID
AND D4.DEPTID = D3.DEPTID
AND D4.EFFDT <= CURRENT DATE)) AS QDV
ON P.SETID = QDV.SETID
AND SUBSTR(P.DEPTID,01,04) = QDV.DEPTID
LEFT OUTER JOIN
(SELECT SETID, DEPTID, DESCR
FROM HRQRY.PS_DEPT_TBL D5
WHERE EFFDT =
(SELECT MAX(EFFDT)
FROM HRQRY.PS_DEPT_TBL D6
WHERE D6.SETID = D5.SETID
AND D6.DEPTID = D5.DEPTID
AND D6.EFFDT <= CURRENT DATE)) AS QUN
ON P.SETID = QUN.SETID
AND SUBSTR(P.DEPTID,01,02) = QUN.DEPTID
WHERE (LENGTH(RTRIM(LTRIM(P.DEPTID))) = 10)
AND SUBSTR(P.DEPTID,01,02) <> 'TE'
AND P.EFFDT = (SELECT MAX(EFFDT)
FROM HRQRY.PS_DEPT_TBL D2
WHERE D2.DEPTID = P.DEPTID
AND D2.EFFDT <= CURRENT DATE)) AS T2

ON T1.TV1=T2.CC_DEPTIDE) AS T3
WHERE SUBSTR(BU,01,02) <> '31' OR BU IS NULL
GROUP BY
BU, DIV, DEPT, CC, JCODE, TV4
WITH UR
pfluggAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi pflugg,

That's quite a bit of SQL for a single query.  :)  

My guess is that this can be made quite a bit simpler and/or efficient.  But it is probably a fair effort to do so.

Let me think on this for a bit and I'll be back to you.  By some change, have you run an explain plan on this?


Kent
0
momi_sabagCommented:
this query actually works ?
you have an order by in the middle,
i don't think db2 will accept that
0
pfluggAuthor Commented:
yes, it actually works. am happy to make it simpler. it grew and grew over time, and had some has sub selects because we couldn't do groupby's on derived column names.

have not run an explain plan on it.

0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Kent OlsenDBACommented:
You can group by a derived column, but you need a wrapper around it

select a, sum(b), count(*)
from
(
  select a, b from sometable, anothertable where sometable.id = anothertable.id
) t0
group by a;

0
pfluggAuthor Commented:
yeah, that's what this code is doing. the top part is a wrapper.
0
Kent OlsenDBACommented:

What version of DB2 are you using and what is your host platform?

Do you have any of the OLAP extensions?

Kent
0
Kent OlsenDBACommented:
Hi momi,

>you have an order by in the middle,
>i don't think db2 will accept that

Db2 allows this, but several RDBMS don't.  Oracle comes to mind....


Kent
0
Kent OlsenDBACommented:

Hi pflugg,

I reformatted the SQL so that I could make better sense of it and I must say that the SQL really isn't too bad.  I don't see any gross inefficiencies in it.

I do have a couple of comments.  First -- INDENT!  There are quite a few styles for indenting SQL.  Find one that you like and stick with it.  It makes queries much easier to quickly understand.  Second -- left().  DB2 has  left() function that returns characters from the left.  left (Somestring, 2) is shorthand for substr (Somestring, 1, 2).  No significant change to the explain plan here, but it's sometimes easier to grok the SQL.


So back to the original question.  You could put a date in a table and query based on this value.  Or you could put the query into a stored procedure/function and pass the date to the sp/function.  Any preference?

Kent

0
pfluggAuthor Commented:
can you shoot back the formatted sql? thanks for taking the time to do that!!
 on your questions,
1) we're using db2 v9 on unix
2) if i can, i'd like to do it in straight sql.
3) i have limited access to create anything other than views. the dba generally frowns on stored procedures for our reporting purposes.
4) i could have the dba create a temporary table that i can cram something into. we do that in a couple of other cases. he'd be much more inclined to do that rather than go the stored procedure route.
0
Kent OlsenDBACommented:

I'm just thinking about ways to "pass" a date to the script.  A parameter to a SP/function is easy, so is reading the date from another table.  Other solutions get a bit more "creative".  :)


SELECT CASE WHEN BU IS NULL THEN '00 - xx' ELSE BU END AS BUSINESS_UNIT, DIV AS DIVISION, DEPT AS DEPARTMENT, CC AS COST_CENTER, JCODE AS JOB_DESCRIPTION, TV4 AS DATE, COUNT(DISTINCT TV5) AS POSITIONS, COUNT(DISTINCT TV6) AS EMPLOYEES
FROM
(
  SELECT UNIT_DEPTIDE CONCAT ' - ' CONCAT UNIT_DESC AS BU,   DIV_DEPTIDE CONCAT ' - ' CONCAT DIV_DESC AS DIV,   DEPT_DEPTIDE CONCAT ' - ' CONCAT DEPT_DESC AS DEPT,   SUBSTR(TV1,07,04) CONCAT ' - ' CONCAT CC_DESC AS CC,   TV2 CONCAT ' - ' CONCAT TV3 AS JCODE,   TV4,TV5, TV6
  FROM
  (
    SELECT
      A.EMPLID AS TV6, A.DEPTID AS TV1, A.JOBCODE AS TV2, B.DESCR AS TV3, A.POSITION_NBR AS TV5, A.EMPL_STATUS, A.LOCATION, A.REG_TEMP, A.FULL_PART_TIME, A.STD_HOURS, B.SETID, B.JOBCODE, B.EFFDT, ('2006-08-01') AS TV4
    FROM
      HRQRY.PS_JOB A,
      HRQRY.PS_EMPLMT_SRCH_QRY A1,
      HRQRY.PS_JOBCODE_TBL B,
      HRQRY.PS_SET_CNTRL_REC B2
    WHERE B.JOBCODE = A.JOBCODE
      AND B2.SETCNTRLVALUE = A.BUSINESS_UNIT
      AND B2.RECNAME = 'JOBCODE_TBL'
      AND B2.SETID = B.SETID
      AND A.EMPLID = A1.EMPLID
      AND A.EMPL_RCD = A1.EMPL_RCD
      AND A1.ROWSECCLASS = 'ALLPANLS'
      AND (A.EFFDT =
      (
        SELECT MAX(A_ED.EFFDT)
        FROM HRQRY.PS_JOB A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= '2006-08-01'
      )
      AND A.EFFSEQ =
      (
        SELECT MAX(A_ES.EFFSEQ)
        FROM HRQRY.PS_JOB A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT
      )
      AND A.JOBCODE <> '000004'
      AND A.REG_TEMP = 'R'
      AND A.STD_HOURS > 19.99
      AND A.EMPL_STATUS IN ('A', 'L', 'P', 'S')
      AND B.EFFDT =
      (
        SELECT MAX(B_ED.EFFDT)
        FROM HRQRY.PS_JOBCODE_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.JOBCODE = B_ED.JOBCODE
          AND B_ED.EFFDT <= A.EFFDT
      ))
    ORDER BY 7, 2 DESC, 4
  ) AS T1
  LEFT OUTER JOIN
  (
    SELECT P.DEPTID AS CC_DEPTIDE, P.DESCR AS CC_DESC, SUBSTR(P.DEPTID,01,06) AS DEPT_DEPTIDE, QDT.DESCR AS DEPT_DESC, SUBSTR(P.DEPTID,01,04) AS DIV_DEPTIDE, QDV.DESCR AS DIV_DESC, SUBSTR(P.DEPTID,01,02) AS UNIT_DEPTIDE, QUN.DESCR AS UNIT_DESC
    FROM HRQRY.PS_DEPT_TBL AS P
    LEFT OUTER JOIN
    (
      SELECT SETID, DEPTID, DESCR
      FROM HRQRY.PS_DEPT_TBL D1
      WHERE EFFDT =
      (
        SELECT MAX(EFFDT)
        FROM HRQRY.PS_DEPT_TBL D2
        WHERE D2.SETID = D1.SETID
          AND D2.DEPTID = D1.DEPTID
          AND D2.EFFDT <= CURRENT DATE
      )
    ) AS QDT
      ON P.SETID = QDT.SETID
     AND SUBSTR(P.DEPTID,01,06) = QDT.DEPTID
    LEFT OUTER JOIN
    (
      SELECT SETID, DEPTID, DESCR
      FROM HRQRY.PS_DEPT_TBL D3
      WHERE EFFDT =
      (
        SELECT MAX(EFFDT)
        FROM HRQRY.PS_DEPT_TBL D4
        WHERE D4.SETID = D3.SETID
          AND D4.DEPTID = D3.DEPTID
          AND D4.EFFDT <= CURRENT DATE
      )
    ) AS QDV
      ON P.SETID = QDV.SETID
     AND SUBSTR(P.DEPTID,01,04) = QDV.DEPTID
    LEFT OUTER JOIN
    (
      SELECT SETID, DEPTID, DESCR
      FROM HRQRY.PS_DEPT_TBL D5
      WHERE EFFDT =
      (
        SELECT MAX(EFFDT)
        FROM HRQRY.PS_DEPT_TBL D6
        WHERE D6.SETID = D5.SETID
          AND D6.DEPTID = D5.DEPTID
          AND D6.EFFDT <= CURRENT DATE
      )
    ) AS QUN
      ON P.SETID = QUN.SETID
     AND SUBSTR(P.DEPTID,01,02) = QUN.DEPTID
    WHERE (LENGTH(RTRIM(LTRIM(P.DEPTID))) = 10)
      AND SUBSTR(P.DEPTID,01,02) <> 'TE'
      AND P.EFFDT =
      (
        SELECT MAX(EFFDT)
        FROM HRQRY.PS_DEPT_TBL D2
        WHERE D2.DEPTID = P.DEPTID
          AND D2.EFFDT <= CURRENT DATE
      )
  ) AS T2
    ON T1.TV1=T2.CC_DEPTIDE
) AS T3
WHERE SUBSTR(BU,01,02) <> '31'
   OR BU IS NULL
GROUP BY BU, DIV, DEPT, CC, JCODE, TV4
WITH UR

0
pfluggAuthor Commented:
sounds familiar. we've had many areas were we've had to be creative and do things by ugly brute force.
0
Kent OlsenDBACommented:

The two occurrences of the date.

...
A.REG_TEMP, A.FULL_PART_TIME, A.STD_HOURS, B.SETID, B.JOBCODE, B.EFFDT, ('2006-08-01') AS TV4
...
          AND A_ED.EFFDT <= '2006-08-01'
...


Can this date be programmatically generated or is this an "on demand" kind of report?

Kent
0
pfluggAuthor Commented:
another thought may be to do something where you select based upon the 1st day of each month. that, in effect, is what's going on -- it's grabbing a count as of the 1st of each month. that would get rid of any looping or such. don't know the syntax, but the logic would be something like the code below. dunno. may be a crazy idea.

select count(id)
from the_big_sql_mess
where
  eff_date between '2006-01-01' and '2007-04-01 and
  dayofmonth(eff_date) = 1

0
pfluggAuthor Commented:
it's could be programatically generated, probably. now()-something; now()-2*something for the previous month, and so forth
0
pfluggAuthor Commented:
kent: have to run out for a little while, but will check back in the early evening. thanks much for your help!
0
Kent OlsenDBACommented:

If you don't mind the report for all months in the database, it's almost trivial.

select .....
from ....
group by left(cast(somedate as char(10)), 7);

If you want/ to do date math by range, that's easy too.

select
  somedate - (days(somedate-1)) days as start_of_month,
  somedate - (days(somedate-1)) days + 1 month - 1 day as end_of_next_month
from ....


Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pfluggAuthor Commented:
i think doing that i'd get all dates, though, not one date per month (specifically the first of each month). sorry, am being obtuse i'm sure.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.