Generate_Series() Equivalent in Db2

generate_series() equivalent in DB2

The where clause needs to be a bit more explicit about the bounds of the recursion in order for DB2 to suppress the warning. Here's a slightly adjusted version that does not trigger the warning:

with dummy(id) as (
    select 2 from SYSIBM.SYSDUMMY1    
    union all
    select id + 1 from dummy where id < 4
)
select id from dummy

how do produce counts for every month around a reference date

Easy way is you create a month table. 100 years will need 1200 rows

allMonths

 month_id
 date 

How can I get a table of dates from the first day of the month, two months ago, to yesterday?

SELECT 
      YT.id, 
      YT.ref_dt, 
      YT.start_dt, 
      YT.end_dt, 
      MONTHS_BETWEEN(YT.ref_dt, allDates.date) as MONTHDIFF
FROM YourTable YT
INNER JOIN allDates
        ON allDates BETWEEEN YT.START_DT AND YT.END_DT

IBM DB2: Generate list of dates between two dates

I am using ,ostly DB2 for iSeries, so I will give you an SQL only solution that works on it. Currently I don't have an access to the server, so the query is not tested but it should work. EDIT Query is already tested and working

SELECT
    d.min + num.n DAYS
FROM
    -- create inline table with min max date
    (VALUES(DATE('2015-02-28'), DATE('2016-03-01'))) AS d(min, max)
INNER JOIN
    -- create inline table with numbers from 0 to 999
    (
        SELECT
            n1.n + n10.n + n100.n AS n
        FROM
            (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
        CROSS JOIN
            (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
        CROSS JOIN
            (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
    ) AS num
ON
    d.min + num.n DAYS<= d.max
ORDER BY
    num.n;

if you don't want to execute the query only once, you should consider creating a real table with values for the loop:

CREATE TABLE dummy_loop AS (
    SELECT
        n1.n + n10.n + n100.n AS n
    FROM
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
    CROSS JOIN
        (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
    CROSS JOIN
        (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
) WITH DATA;

ALTER TABLE dummy_loop ADD PRIMARY KEY (dummy_loop.n);

It depends on the reason for which you like to use it, but you could even create table for lets say for 100 years. It will be only 100*365 = 36500 rows with just a date field, so the table will be quite small and fast for joins.

CREATE TABLE dummy_dates AS (
    SELECT
        DATE('1970-01-01') + (n1.n + n10.n + n100.n) DAYS AS date
    FROM
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
    CROSS JOIN
        (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
    CROSS JOIN
        (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
) WITH DATA;

ALTER TABLE dummy_dates ADD PRIMARY KEY (dummy_dates.date);

And the select query could look like:

SELECT
    *
FROM
    dummy_days
WHERE
    date BETWEEN(:startDate, :endDate);

EDIT 2: Thanks to @Lennart suggestion I have changed TABLE(VALUES(..,..,..)) to VALES(..,..,..) because as he said TABLE is a synonym to LATERAL that was a real surprise for me.

EDIT 3: Thanks to @godric7gt I have removed TIMESTAMPDIFF and will remove from all my scripts, because as it is said in the documentation:

These assumptions are used when converting the information in the second argument, which is a timestamp duration, to the interval type specified in the first argument. The returned estimate may vary by a number of days. For example, if the number of days (interval 16) is requested for the difference between '1997-03-01-00.00.00' and '1997-02-01-00.00.00', the result is 30. This is because the difference between the timestamps is 1 month, and the assumption of 30 days in a month applies.

It was a real surprise, because I was always trust this function for days difference.

Looking for DB2 SQL query

you can go with below qyery for this type of output --

-- you want to check with the row where effective date is the maximum for the item name column then you can take only those records and then we can put year condition on those records.

SELECT NAME, Id1, Id2, Effdate
  FROM Table_Name t_1
 WHERE Effdate =
       (SELECT (t_2.Effdate) 
        FROM Table_Name t_2 
        WHERE t_2.NAME = t_1.NAME
        and t_2.id1 = t_1.id1
        and t_2.id2 = t_1.id2
        GROUP BY t_2.name,t_2.id1,t_2.id2)
   AND Your_Year_Variable_Value BETWEEN t_1.Yearfrom AND t_1.Yearto

DB2 sp_rename equivalent

It depends on the object.

For tables and indexes, you can use the RENAME sql statement.

If you want to rename a column, you can use ALTER TABLE ... RENAME COLUMN.

For other objects, you probably have to drop and recreate it.

Hope that helps.

Generate a random number in the range 1 - 10

If by numbers between 1 and 10 you mean any float that is >= 1 and < 10, then it's easy:

select random() * 9 + 1

This can be easily tested with:

# select min(i), max(i) from (
    select random() * 9 + 1 as i from generate_series(1,1000000)
) q;
       min       |       max
-----------------+------------------
 1.0000083274208 | 9.99999571684748
(1 row)

If you want integers, that are >= 1 and < 10, then it's simple:

select trunc(random() * 9 + 1)

And again, simple test:

# select min(i), max(i) from (
    select trunc(random() * 9 + 1) as i from generate_series(1,1000000)
) q;
 min | max
-----+-----
   1 |   9
(1 row)

---

Related Topics