CODEWITHSUNDEEP
sql-serverdb2business-intelligence
aze
aze

Reputation: 39

Script SQL Dim Table Date on IBM DB2

I'am a db2 user and it's my first time using this database i am used to use SQLserver database , so my probleme is that i have a script i always use on order to generate a table date in sql server but when i used the same code it didin't work on my db2 too much error lignes , this is my code :

       use dw_test_pfe 
       SET LANGUAGE 'FRENCH';

        WITH DateCTE
        AS (
        SELECT cast('20000915' AS DATETIME) Datevalue

     UNION ALL

   SELECT datevalue + 1
   FROM DateCTE
   WHERE datevalue + 1 < = '20201231'
    )
  SELECT CAST(CONVERT(VARCHAR(8), Datevalue, 112) AS INT) AS Temps_PK
,CAST(Datevalue AS DATE) AS [Date]
,cast(DATENAME(d, datevalue) AS NVARCHAR(10))+' - '+cast(DATENAME(Month, DateValue) AS NVARCHAR(30))+' - '+cast(DATENAME(year, DateValue) AS NVARCHAR(30)) AS [Jour_Moi_Annee]
,cast(DATENAME(year, DateValue) AS INT) AS [Year]
,cast(DATENAME(year, DateValue) + REPLICATE('0', 2 - LEN(Month(DateValue))) + CAST(Month(DateValue) AS VARCHAR) AS INT) AS ID_Month
,cast(MONTH(DateValue) AS INT) AS [Month]
,cast(DATENAME(Month, DateValue) AS NVARCHAR(30)) AS [Lib_Month]
,cast(DATENAME(d, datevalue) AS INT) AS [Day]
,DATEPART(DW, datevalue) AS [Id_Lib_Day]
,cast(DATENAME(DW, Datevalue) AS NVARCHAR(10)) AS [Lib_Day]
,cast(DATENAME(WEEK, Datevalue) AS INT) AS [Week]
,cast(DATENAME(dayofyear, Datevalue) AS INT) AS [DayOfYear]
,cast(DATENAME(DW, Datevalue) AS NVARCHAR(10))+' - '+cast(DATENAME(d, datevalue) AS NVARCHAR(10))+' - 
'+cast(DATENAME(Month, DateValue)AS NVARCHAR(30))  AS [Jour_mois_lettre]
 into DimTemps FROM DateCTE D

   ORDER BY Datevalue
   OPTION (MAXRECURSION 0)

this is my code it works well on sqlserver base but i can't use it on db2 server , all the code is red when i put this script if you guys can adjust my code or bring a new one , i need just to generate a dimension date thank you all

Upvotes: 0

Views: 375

Answers (1)

nfgl
nfgl

Reputation: 3202

Edit:Since you probably run the statements against DB2 9.7 (not supported since sept 2017) or even older you have first tou create the table then insert the data

set current locale lc_time 'fr-fr';
create table DimTemps as (
  with DateCTE(dateValue) as (
    values date('2000-09-15')
    union all
    select dateValue + 1 day from dateCte where dateValue < '2020-12-31'
  )
  select
    int(dec(datevalue, 8)) as Temps_PK,
    dateValue "Date",
    to_char(dateValue, 'dd - month - yyyy') as "Jour_Moi_Annee",
    year(datevalue) as "Year",
    year(datevalue) * 100 + month(datevalue) as "Month",
    to_char(datevalue, 'month') as  "Lib_Month",
    day(datevalue) as "Day",
    dayofweek(datevalue) as "Id_Lib_Day",
    to_char(datevalue, 'day') as "Lib_Day",
    week(datevalue) as "Week",
    dayofyear(datevalue) as "DayOfYear",
    to_char(dateValue, 'day - dd - month') as "Jour_mois_lettre"
  from datecte
);

then

insert into DimTemps 
      with DateCTE(dateValue) as (
        values date('2000-09-15')
        union all
        select dateValue + 1 day from dateCte where dateValue < '2020-12-31'
      )
      select
        int(dec(datevalue, 8)) as Temps_PK,
        dateValue "Date",
        to_char(dateValue, 'dd - month - yyyy') as "Jour_Moi_Annee",
        year(datevalue) as "Year",
        year(datevalue) * 100 + month(datevalue) as "Month",
        to_char(datevalue, 'month') as  "Lib_Month",
        day(datevalue) as "Day",
        dayofweek(datevalue) as "Id_Lib_Day",
        to_char(datevalue, 'day') as "Lib_Day",
        week(datevalue) as "Week",
        dayofyear(datevalue) as "DayOfYear",
        to_char(dateValue, 'day - dd - month') as "Jour_mois_lettre"
      from datecte

Upvotes: 2

Related Questions