Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Posts
    12

    Unanswered: Updating a table without a cursor

    Hello

    The example beneath is somewhat simplified.

    I have a table called DIM-month, containing information about months. It contains the next columns.

    Month-key
    Year
    Month-number
    Month-name

    I also have a table DIM-day. It contains the next columns.
    Day-key
    Date
    Month-key
    Weekend-indication
    Holiday-indication

    Now I added two columns to DIM-month.
    Number-weekend-days (this is the aggregation for a specific month of number of records in DIM-day where weekend-indication = Y)
    Number-holiday-days (this is the aggregation for a specific month of number of records in DIM-day where holiday-indication = Y)

    How do I update these two columns for every row, every month in DIM-month without using a cursor (in for instance a Cobol program)?
    So in a single SQL?

    Using DB2 z/Os version 11

    Thnx
    Ron

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 46
    Hi Ron,

    Can you provide a test case with the following statements?
    Code:
    CREATE TABLE Month-key ...;
    CREATE TABLE Day-key ...;
    INSERT INTO Month-key ...;
    INSERT INTO Day-key ...;
    Regards,
    Mark.

  3. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 46
    Should be something like this:
    Code:
    update DIM_month m
    set (Number_weekend_days, Number_holiday_days) = (
    select
      count(case Weekend_indication when 'Y' then 1 end) Number_weekend_days
    , count(case Holiday_indication when 'Y' then 1 end) Number_holiday_days
    from DIM_day d
    where d.Month_key=m.Month_key
    )
    ;
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •