Results 1 to 3 of 3

Thread: SQL Advice

  1. #1
    Join Date
    Sep 2003

    Unanswered: SQL Advice

    DB2 V7.2 on z/OS:

    Hello, I got one of those SQL's where I have figured the way to get the data I want, but I am not sure, whether it's the best way: The table consists of 12 date fields (d1,d2,d3.....), The user would enter a date range (start and end dates) and I have to display the date column along with the number of rows matched within the user specified date range like:

    d1 51
    d2 31

    The way I figured to do this is:
    select 'd1', count(*) from t1 where d1 between :h1 and :h2
    union all
    select 'd2', count(*) from t1 where d2 between :h1 and :h2....

    Is there a better way to do this and avoid those multiple passes..... it's not UDB and I can't do those COUNT OVER()....
    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Provided Answers: 4
    I think you should normalise your table with the 12 date fileds.
    nr_row maps the 12 records in the normalised table to one row of the original table, nr_d_column is the number of the d column in the original table (d1 is 1, d2 is 2, ..., d12 is 12)
      nr_row       INTEGER  NOT NULL,
      nr_d_column  SMALLINT NOT NULL,
      d            DATE     NOT NULL,
      CONSTRAINT pk_t PRIMARY KEY (nr_row, nr_d_column)
    SELECT 'd'||CHAR(nr_d_column),
    FROM t1
    WHERE d BETWEEN :h1 AND :h2
    ORDER BY nr_row, nr_d_column
    In this case, your users will only get a record back when there is at least 1 match.

    If they want a record, even when the result is 0, you could use:
    SELECT 'd'||CHAR(nr_d_column),
           SUM(CASE WHEN d BETWEEN :h1 AND :h2
                    THEN 1
                    ELSE 0
    FROM t1
    ORDER BY nr_row, nr_d_column
    Perhaps someone knows a more elegant way for this last query.


  3. #3
    Join Date
    Sep 2003
    Wim: modifying the table design is out of the question here, too many programs will have to be changed....

    sum(case...) does give a much better performance though, didn't think it would work on z/OS; but it does.

Posting Permissions

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