Add temperature to gas usage (part 2)

After part 1 it is time to load the data in the database. We will use linq for it. In a previous article I I have shown how to get and build linq to oracle. With that in mind, we start creating the table. After this we create the linq proxy classes using dbmetal. And finally the code to degrees process.

Let’s start with the table:

drop table deg
/

create table deg
(
  degkey    number not null,
  degdat    number not null,
  degtim    number not null,
  degcelcius    number not null,
  constraint deg_pkey primary key (degkey),
  constraint degdat_fkey foreign key (degdat) references dat (datkey),
  constraint degtim_fkey foreign key (degtim) references tim (timkey)
)
/

drop sequence degkey_seq
/

create sequence degkey_seq
  start with 1
  increment by 1
  nomaxvalue
/

create or replace trigger degkey_trigger
  before insert on deg
  for each row
  begin
    select degkey_seq.nextval into :new.degkey from dual;
  end;
/

After this we use dbmetal to generate the helper classes:

dbmetal /provider:oracle /conn:”Data Source=XE;User Id=gwe;Password=gwe;” /database=gwe /code:gwe.cs

The problem here is that the databasename is the same as a tablename in the schema. To solve this edit the gwe.cs file and change the DataContext name to GWE (all capitals). Also change the constructors to GWE.

Then the code to the degrees.cs:

using System;
using System.Web.Services.Protocols;
using System.Net;
using System.Xml;
using System.Text.RegularExpressions;

using System.Collections.Generic;
using System.Linq;
using DbLinq.Linq.Mapping;
using System.Data.OracleClient;

public class Degrees
{
  public static void Main (string [] args)
  {
    try {
    string degrees=”";
    string success=”";
    GlobalWeatherSoapClient w = new GlobalWeatherSoapClient();

    // We load the string in an xml reader to parse it and find the temperature
    XmlReader reader = XmlReader.Create(new System.IO.StringReader(w.GetWeather(“Groningen”,”Netherlands”)));
    reader.MoveToContent();
    // Parse the file and find the Temperature element its value
    while (reader.Read())
    {
      if (reader.NodeType==XmlNodeType.Element && reader.Name==”Temperature”)
      {
        // Do some regex to find the temperature in celcius
        reader.Read(); degrees = Regex.Replace(Regex.Replace(reader.Value,”^.*\\(“,”"),” C\\).*$”,”");
      }
      if (reader.NodeType==XmlNodeType.Element && reader.Name==”Status”)
      {
        reader.Read(); success = reader.Value.Trim();
      }
    }
    reader.Close();

    System.Console.WriteLine(degrees + success);

    if (success==”Success”) {
      System.DateTime d = System.DateTime.Now;

      string connStr = “User Id=gwe;Password=gwe;Data Source=XE”;

      GWE db = new GWE(new OracleConnection(connStr));
      // get datkey
      var q1 = (from p in db.Tims where p.TimHour==d.Hour select p).Single();
      var q2 = (from p in db.Dat where p.DatYYYYMmDd==(d.Year*10000)+(d.Month*100)+d.Day select p).Single();
      DEG deg = new DEG();
      deg.DEGKey=1; // dummy
      deg.DEGCeLcIUs=Convert.ToDecimal(degrees);
      deg.DEGdAt=q2.DatKey;
      deg.DEGTim=q1.TimKey;
      db.DEG.InsertOnSubmit(deg); 
      db.SubmitChanges();
    }
    }
    catch(Exception ex) // catch all exceptions so the batch continues
    {
      Console.WriteLine(ex.ToString());
    }
  }
}

build:

csc /r:dblinq.dll,dblinq.oracle.dll degrees.cs gwe.cs GlobalWeather.cs

And schedule every hour in windows.

Add temperature to gas usage (part 1)

Wouldn’t it be nice if we can extend the previous ‘datawarehouse’ with the temperatures? so we can compare the gas (heating) or electricity (airco) usage with the current or average day temperature.

Let’s create a .net application which gets the current temperature from a webservice every hour and adds them to a new fact table. We gonna use Windows Communication Foundation and Linq in this example.

In this part we gonna create a webservice client to the weatherservice. In the next part we gonna add the linq part to load the temperature in the oracle database.

I found a pretty nice webservice that is free and also has the european weather:

http://www.webservicex.net/globalweather.asmx

First thing is to create a stub for the webservice:

svcutil.exe http://www.webservicex.net/globalweather.asmx?wsdl

This creates a GlobalWeather.cs stub and an output.config file which contains the bindings.

Ignore the errors, this is because there are other than soap ports in the wsdl.

rename the output.config to degrees.exe.config

create the client degrees.cs:

using System;
using System.Web.Services.Protocols;
using System.Net;
using System.Xml;
using System.Text.RegularExpressions;
public class SetCoverImage
{
public static void Main (string [] args)
{
GlobalWeatherSoapClient w = new GlobalWeatherSoapClient();

// The service returns a plain string
Console.WriteLine(w.GetWeather(“Groningen”,”Netherlands”));

// We load the string in an xml reader to parse it and find the temperature
XmlReader reader = XmlReader.Create(new System.IO.StringReader(w.GetWeather(“Groningen”,”Netherlands”)));
reader.MoveToContent();
// Parse the file and find the Temperature element its value
while (reader.Read())
{
if (reader.NodeType==XmlNodeType.Element && reader.Name==”Temperature”)
{
// Do some regex to find the temperature in celcius
reader.Read(); Console.WriteLine(Regex.Replace(Regex.Replace(reader.Value,”^.*\\(“,”"),” C\\).*$”,”"));
}
if (reader.NodeType==XmlNodeType.Element && reader.Name==”Status”)
{
reader.Read(); Console.WriteLine(reader.Value.Trim());
}
}
reader.Close();
}
}

csc degrees.cs GlobalWeather.cs

degrees.exe

<?xml version="1.0" encoding="utf-16"?>
<CurrentWeather>
  <Location>Groningen Airport Eelde, Netherlands (EHGG) 53-08N 006-35E 4M</Location>
  <Time>May 23, 2008 - 07:25 AM EDT / 2008.05.23 1125 UTC</Time>
  <Wind> from the NE (050 degrees) at 8 MPH (7 KT) (direction variable):0</Wind>
  <Temperature> 69 F (21 C)</Temperature>
  <DewPoint> 39 F (4 C)</DewPoint>
  <RelativeHumidity> 32%</RelativeHumidity>
  <Pressure> 30.00 in. Hg (1016 hPa)</Pressure>
  <Status>Success</Status>
</CurrentWeather>
21
Success

The service returns the temperature in my region.

Next step is to load it into the database. (see part 2)

Gas, Water and Electricity Registration

Over here in Holland the utilities are getting more expensive everyday. Time for me to make a small registration tool to get some insigth information in the consumption of the resources. I want the resources (facts) to layout againsts dates and times. So I need two dimensions: date and time. I also need a fact table in which I register the usage of the resources every hour. Because it is not possible to get a sample of the meters every hour, I created a procedure which splits the difference between the previous and the current resource usage over the hours that are between. I know it isn’t the excact resource usage, but the more samples I have the more accurate it will get. The nice thing about splitting the usage over the hours is that querying the results gets very easy.

Over here in Holland we have another ‘problem’. We have peak and dew hours for the electricity. Also the certain holidays and the weekends are dew prices.

Lets start with creating the schema:

create user gwe identified by gwe;
grant connect,resources to gwe;
connect gwe/gwe

After this, create the dat dimension as showed in the previous blog.

Now the time dimension:

create table tim
(
 timkey    number not null,
 timhour    number(2) not null,
 timelekhoog    varchar2(1) not null,
 constraint tim_pkey primary key (timkey)
)
/

drop sequence timkey_seq
/

create sequence timkey_seq
 start with 1
 increment by 1
 nomaxvalue
/

create or replace trigger timkey_trigger
 before insert on tim
 for each row
 begin
 select timkey_seq.nextval into :new.timkey from dual;
 end;
/

create unique index timhour_idx1 on tim (timhour)
/

insert into tim (timhour,timelekhoog) values (0,'Y');
insert into tim (timhour,timelekhoog) values (1,'Y');
insert into tim (timhour,timelekhoog) values (2,'Y');
insert into tim (timhour,timelekhoog) values (3,'Y');
insert into tim (timhour,timelekhoog) values (4,'Y');
insert into tim (timhour,timelekhoog) values (5,'Y');
insert into tim (timhour,timelekhoog) values (6,'Y');
insert into tim (timhour,timelekhoog) values (7,'N');
insert into tim (timhour,timelekhoog) values (8,'N');
insert into tim (timhour,timelekhoog) values (9,'N');
insert into tim (timhour,timelekhoog) values (10,'N');
insert into tim (timhour,timelekhoog) values (11,'N');
insert into tim (timhour,timelekhoog) values (12,'N');
insert into tim (timhour,timelekhoog) values (13,'N');
insert into tim (timhour,timelekhoog) values (14,'N');
insert into tim (timhour,timelekhoog) values (15,'N');
insert into tim (timhour,timelekhoog) values (16,'N');
insert into tim (timhour,timelekhoog) values (17,'N');
insert into tim (timhour,timelekhoog) values (18,'N');
insert into tim (timhour,timelekhoog) values (19,'N');
insert into tim (timhour,timelekhoog) values (20,'N');
insert into tim (timhour,timelekhoog) values (21,'N');
insert into tim (timhour,timelekhoog) values (22,'N');
insert into tim (timhour,timelekhoog) values (23,'Y');

commit
/

Now the fact table and the foreignkeys:

create table gwe
(
 gwekey    number not null,
 gwedat    number not null,
 gwetim    number not null,
 gweelekhoog   number(10,5) not null,
 gweeleklaag   number(10,5) not null,
 gwegas        number(10,5) not null,
 gwewater      number(10,5) not null,
 constraint gwe_pkey primary key (gwekey),
 constraint gwedat_fkey foreign key (gwedat) references dat (datkey),
 constraint gwetim_fkey foreign key (gwetim) references tim (timkey)
)
/

drop sequence gwekey_seq
/

create sequence gwekey_seq
 start with 1
 increment by 1
 nomaxvalue
/

create or replace trigger gwekey_trigger
 before insert on gwe
 for each row
 begin
 select gwekey_seq.nextval into :new.gwekey from dual;
 end;
/

declare l_datkey number;
begin
 select datkey into l_datkey from dat where datdate=trunc(sysdate)-1;
 insert into gwe (
 gwedat
 ,gwetim
 ,gweelekhoog
 ,gweeleklaag
 ,gwegas
 ,gwewater
 ) values (
 l_datkey
 ,1
 ,0
 ,0
 ,0
 ,0
 );
end;
/

commit;

The fact table is initialized with 0 values at the date yesterday.

Now we can use the following stored procedure to add meter measures.

create or replace procedure tussenstand( p_datumin date
                                        ,p_elekhoog number
                                        ,p_eleklaag number
                                        ,p_gas number
                                        ,p_water number
                                       ) as
        l_prevdatum date;
        l_diffelekhoog number(10,5);
        l_diffeleklaag number(10,5);
        l_diffgas number(10,5);
        l_diffwater number(10,5);
        l_inselekhoog number(10,5);
        l_inseleklaag number(10,5);
        l_datum date;
        l_prevkey number;
        l_aantalelekhoog number;
        l_aantaleleklaag number;
        l_aantalgas number;
        l_aantalwater number;
        l_elekhoog varchar2(1);
        l_datkey number;
        l_timkey number;
        l_weekend varchar2(1);
        l_feestdag varchar2(1);
begin
  select  max(gwekey)
    into  l_prevkey
    from  gwe
  ;
  select  p_elekhoog-sum(gweelekhoog)
         ,p_eleklaag-sum(gweeleklaag)
         ,p_gas-sum(gwegas)
         ,p_water-sum(gwewater)
    into  l_diffelekhoog
         ,l_diffeleklaag
         ,l_diffgas
         ,l_diffwater
    from gwe
  ;
  select datdate + (timhour/24)
    into l_prevdatum
    from gwe,dat,tim
   where     gwekey=l_prevkey
         and gwedat=datkey
         and gwetim=timkey
  ;
  l_datum := l_prevdatum+(1/24);
  l_aantalelekhoog := 0;
  l_aantaleleklaag := 0;
  l_aantalgas := 0;
  l_aantalwater := 0;

  while (l_datum<=p_datumin)
  loop
    select datweekend, datholiday
      into l_weekend, l_feestdag
      from dat
     where datdate = trunc(l_datum)
    ;
    select timelekhoog
      into l_elekhoog
      from tim
     where timhour=to_number(to_char(l_datum,'HH24'))
    ;
    if (l_elekhoog='N' or l_weekend='Y' or l_feestdag='Y') then
      l_aantaleleklaag := l_aantaleleklaag + 1;
    else
      l_aantalelekhoog := l_aantalelekhoog + 1;
    end if;
    l_aantalgas := l_aantalgas + 1;
    l_aantalwater := l_aantalwater + 1;

    l_datum := l_datum + (1/24);
  end loop;

  if l_aantaleleklaag=0 then l_aantaleleklaag:=1; end if;
  if l_aantalelekhoog=0 then l_aantalelekhoog:=1; end if;
  if l_aantalwater=0 then l_aantalwater:=1; end if;
  if l_aantalgas=0 then l_aantalgas:=1; end if;

  l_diffgas := l_diffgas / l_aantalgas;
  l_diffwater := l_diffwater / l_aantalwater;
  l_diffeleklaag := l_diffeleklaag / l_aantaleleklaag;
  l_diffelekhoog := l_diffelekhoog / l_aantalelekhoog;

  l_datum := l_prevdatum + (1/24);

  while (l_datum <= p_datumin)
  loop
    select datkey
          ,datweekend
          ,datholiday
      into l_datkey
          ,l_weekend
          ,l_feestdag
      from dat
     where datdate = trunc(l_datum)
    ;
    select timkey
          ,timelekhoog
      into l_timkey
          ,l_elekhoog
      from tim
     where timhour=to_number(to_char(l_datum,'HH24'))
    ;
    if (l_elekhoog='N' or l_weekend='Y' or l_feestdag='Y') then
      l_inseleklaag := l_diffeleklaag;
      l_inselekhoog := 0;
    else
      l_inselekhoog := l_diffelekhoog;
      l_inseleklaag := 0;
    end if;
    insert into gwe (
        gwedat
       ,gwetim
       ,gweelekhoog
       ,gweeleklaag
       ,gwegas
       ,gwewater
      ) values (
        l_datkey
       ,l_timkey
       ,l_inselekhoog
       ,l_inseleklaag
       ,l_diffgas
       ,l_diffwater
      );
    l_datum := l_datum + (1/24);
  end loop;
end;
/

After adding some measures, we can use the following views in our Business Intelligence tool to create nice graphs:

create or replace view elek as
select null link, datyyyyww label, sum(gweelekhoog+gweeleklaag) value
from "GWE"."GWE", "GWE"."DAT"
where gwedat=datkey and datwwcum >
  ( select max(datwwcum)-30
      from gwe.dat, gwe.gwe where datkey=gwedat)
group by datwwcum,datyyyyww
order by datwwcum
/
create or replace view gas as
select null link, datyyyyww label, sum(gwegas) value
from "GWE"."GWE", "GWE"."DAT"
where gwedat=datkey and datwwcum >
  ( select max(datwwcum)-30
      from gwe.dat, gwe.gwe where datkey=gwedat)
group by datwwcum,datyyyyww
order by datwwcum
/
create or replace view water as
select null link, datyyyyww label, sum(gwewater) value
from "GWE"."GWE", "GWE"."DAT"
where gwedat=datkey and datwwcum >
  ( select max(datwwcum)-30
      from gwe.dat, gwe.gwe where datkey=gwedat)
group by datwwcum,datyyyyww
order by datwwcum
/

Oracle Date Dimension

In my opinion it is always handy to have a detailed date dimension in a database where you can always join against. This is handy to get the current month or all mondays that aren’t holidays.

select datdate from dat where datyyyymm = (select datyyyymm from dat where datdate=trunc(sysdate));

select datdate from dat where datdayeng = ‘Monday’ and datholiday=’Y’ ;

in the underlying code the dat dimension is created.

create table dat
(
 datkey    number not null,
 datdate    date not null,
 dattype    varchar2(3) not null,
 datyyyy    number(4) not null,
 datyyyymm    number(6) not null,
 datyyyymmdd    number(8) not null,
 datyyyyww     number(8) not null,
 datmm        number(2) not null,
 datww        number(2) not null,
 datdd        number(2) not null,
 datmmcum      number not null,
 datwwcum      number not null,
 datddcum    number not null,
 datmontheng   varchar2(25) not null,
 datmonthned   varchar2(25) not null,
 datdayofweek  number(1) not null,
 datdayeng    varchar2(25) not null,
 datdayned    varchar2(25) not null,
 datweekend    varchar2(1) not null,
 datholiday    varchar2(1) not null,
 constraint dat_pkey primary key (datkey)
)
/

drop sequence datkey_seq;

create sequence datkey_seq
 start with 1
 increment by 1
 nomaxvalue
/

create or replace trigger datkey_trigger
 before insert on dat
 for each row
 begin
 select datkey_seq.nextval into :new.datkey from dual;
 end;
/

create unique index datdate_idx1 on dat (datdate)
/

declare g_startdate date := '01-JAN-1950';
 g_enddate date := '31-DEC-2100';
 d date := g_startdate;
 g_weekcum number := 0;
 g_week number := 0;
begin
 while d < g_enddate
 loop
 if (g_week <> to_char(d,'YYYYIW')) then
 g_week := to_char(d,'YYYYIW');
 g_weekcum := g_weekcum + 1;
 end if;
 insert into dat (
 datdate
 ,dattype
 ,datyyyy
 ,datyyyymm
 ,datyyyymmdd
 ,datyyyyww
 ,datmm
 ,datww
 ,datdd
 ,datmmcum
 ,datwwcum
 ,datddcum
 ,datmontheng
 ,datmonthned
 ,datdayofweek
 ,datdayeng
 ,datdayned
 ,datweekend
 ,datholiday
 ) values (
 d
 ,'DAT'
 ,to_char(d,'YYYY')
 ,to_char(d,'YYYYMM')
 ,to_char(d,'YYYYMMDD')
 ,to_char(d,'YYYYIW')
 ,to_char(d,'MM')
 ,to_char(d,'IW')
 ,to_char(d,'DD')
 ,trunc(months_between(d,g_startdate))
 ,g_weekcum
 ,d - to_date(g_startdate) + 1
 ,to_char(d,'FMMonth','NLS_DATE_LANGUAGE=American')
 ,to_char(d,'FMMonth','NLS_DATE_LANGUAGE=Dutch')
 ,to_char(d,'D')        -- Monday first weekday
 ,to_char(d,'FMDay','NLS_DATE_LANGUAGE=American')
 ,to_char(d,'FMDay','NLS_DATE_LANGUAGE=Dutch')
 ,case when to_char(d,'FMDAY','NLS_DATE_LANGUAGE=American') = 'SATURDAY' then 'Y'
 when to_char(d,'FMDAY','NLS_DATE_LANGUAGE=American') = 'SUNDAY' then 'Y'
 else 'N'
 end
 ,'N'
 );
 d := d + 1;
 end loop;
end;
/

-- First day of week is monday
alter session set nls_territory = "the netherlands"
/
update dat set datdayofweek=to_char(datdate,'D')
/
-- Kerst
update dat set datholiday='Y' where datmm=12 and (datdd=25 or datdd=26)
/
-- Koninginnedag
update dat set datholiday='Y' where datmm=4 and ((datdd=30 and datdayofweek!=7) or (datdd=29 and datdayofweek=6))
/
-- 5 mei
update dat set datholiday='Y' where datmm=5 and mod(datyyyy,5)=0
/
update dat set datholiday='Y' where datdate=to_date('19500409','YYYYMMDD') or datdate-1=to_date('19500409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19500409','YYYYMMDD') or datdate-1=to_date('19500409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19510325','YYYYMMDD') or datdate-1=to_date('19510325','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19520413','YYYYMMDD') or datdate-1=to_date('19520413','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19530405','YYYYMMDD') or datdate-1=to_date('19530405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19540418','YYYYMMDD') or datdate-1=to_date('19540418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19550410','YYYYMMDD') or datdate-1=to_date('19550410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19560401','YYYYMMDD') or datdate-1=to_date('19560401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19570421','YYYYMMDD') or datdate-1=to_date('19570421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19580406','YYYYMMDD') or datdate-1=to_date('19580406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19590329','YYYYMMDD') or datdate-1=to_date('19590329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19600417','YYYYMMDD') or datdate-1=to_date('19600417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19610402','YYYYMMDD') or datdate-1=to_date('19610402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19620422','YYYYMMDD') or datdate-1=to_date('19620422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19630414','YYYYMMDD') or datdate-1=to_date('19630414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19640329','YYYYMMDD') or datdate-1=to_date('19640329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19650418','YYYYMMDD') or datdate-1=to_date('19650418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19660410','YYYYMMDD') or datdate-1=to_date('19660410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19670326','YYYYMMDD') or datdate-1=to_date('19670326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19680414','YYYYMMDD') or datdate-1=to_date('19680414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19690406','YYYYMMDD') or datdate-1=to_date('19690406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19700329','YYYYMMDD') or datdate-1=to_date('19700329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19710411','YYYYMMDD') or datdate-1=to_date('19710411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19720402','YYYYMMDD') or datdate-1=to_date('19720402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19730422','YYYYMMDD') or datdate-1=to_date('19730422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19740414','YYYYMMDD') or datdate-1=to_date('19740414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19750330','YYYYMMDD') or datdate-1=to_date('19750330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19760418','YYYYMMDD') or datdate-1=to_date('19760418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19770410','YYYYMMDD') or datdate-1=to_date('19770410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19780326','YYYYMMDD') or datdate-1=to_date('19780326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19790415','YYYYMMDD') or datdate-1=to_date('19790415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19800406','YYYYMMDD') or datdate-1=to_date('19800406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19810419','YYYYMMDD') or datdate-1=to_date('19810419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19820411','YYYYMMDD') or datdate-1=to_date('19820411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19830403','YYYYMMDD') or datdate-1=to_date('19830403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19840422','YYYYMMDD') or datdate-1=to_date('19840422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19850407','YYYYMMDD') or datdate-1=to_date('19850407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19860330','YYYYMMDD') or datdate-1=to_date('19860330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19870419','YYYYMMDD') or datdate-1=to_date('19870419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19880403','YYYYMMDD') or datdate-1=to_date('19880403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19890326','YYYYMMDD') or datdate-1=to_date('19890326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19900415','YYYYMMDD') or datdate-1=to_date('19900415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19910331','YYYYMMDD') or datdate-1=to_date('19910331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19920419','YYYYMMDD') or datdate-1=to_date('19920419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19930411','YYYYMMDD') or datdate-1=to_date('19930411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19940403','YYYYMMDD') or datdate-1=to_date('19940403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19950416','YYYYMMDD') or datdate-1=to_date('19950416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19960407','YYYYMMDD') or datdate-1=to_date('19960407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19970330','YYYYMMDD') or datdate-1=to_date('19970330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19980412','YYYYMMDD') or datdate-1=to_date('19980412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19990404','YYYYMMDD') or datdate-1=to_date('19990404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20000423','YYYYMMDD') or datdate-1=to_date('20000423','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20010415','YYYYMMDD') or datdate-1=to_date('20010415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20020331','YYYYMMDD') or datdate-1=to_date('20020331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20030420','YYYYMMDD') or datdate-1=to_date('20030420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20040411','YYYYMMDD') or datdate-1=to_date('20040411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20050327','YYYYMMDD') or datdate-1=to_date('20050327','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20060416','YYYYMMDD') or datdate-1=to_date('20060416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20070408','YYYYMMDD') or datdate-1=to_date('20070408','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20080323','YYYYMMDD') or datdate-1=to_date('20080323','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20090412','YYYYMMDD') or datdate-1=to_date('20090412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20100404','YYYYMMDD') or datdate-1=to_date('20100404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20110424','YYYYMMDD') or datdate-1=to_date('20110424','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20120408','YYYYMMDD') or datdate-1=to_date('20120408','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20130331','YYYYMMDD') or datdate-1=to_date('20130331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20140420','YYYYMMDD') or datdate-1=to_date('20140420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20150405','YYYYMMDD') or datdate-1=to_date('20150405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20160327','YYYYMMDD') or datdate-1=to_date('20160327','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20170416','YYYYMMDD') or datdate-1=to_date('20170416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20180401','YYYYMMDD') or datdate-1=to_date('20180401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20190421','YYYYMMDD') or datdate-1=to_date('20190421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20200412','YYYYMMDD') or datdate-1=to_date('20200412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20210404','YYYYMMDD') or datdate-1=to_date('20210404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20220417','YYYYMMDD') or datdate-1=to_date('20220417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20230409','YYYYMMDD') or datdate-1=to_date('20230409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20240331','YYYYMMDD') or datdate-1=to_date('20240331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20250420','YYYYMMDD') or datdate-1=to_date('20250420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20260405','YYYYMMDD') or datdate-1=to_date('20260405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20270328','YYYYMMDD') or datdate-1=to_date('20270328','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20280416','YYYYMMDD') or datdate-1=to_date('20280416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20290401','YYYYMMDD') or datdate-1=to_date('20290401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20300421','YYYYMMDD') or datdate-1=to_date('20300421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20310413','YYYYMMDD') or datdate-1=to_date('20310413','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20320328','YYYYMMDD') or datdate-1=to_date('20320328','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20330417','YYYYMMDD') or datdate-1=to_date('20330417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20340409','YYYYMMDD') or datdate-1=to_date('20340409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20350325','YYYYMMDD') or datdate-1=to_date('20350325','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20360413','YYYYMMDD') or datdate-1=to_date('20360413','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20370405','YYYYMMDD') or datdate-1=to_date('20370405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20380425','YYYYMMDD') or datdate-1=to_date('20380425','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20390410','YYYYMMDD') or datdate-1=to_date('20390410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20400401','YYYYMMDD') or datdate-1=to_date('20400401','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20410421','YYYYMMDD') or datdate-1=to_date('20410421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20420406','YYYYMMDD') or datdate-1=to_date('20420406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20430329','YYYYMMDD') or datdate-1=to_date('20430329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20440417','YYYYMMDD') or datdate-1=to_date('20440417','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20450409','YYYYMMDD') or datdate-1=to_date('20450409','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20460325','YYYYMMDD') or datdate-1=to_date('20460325','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20470414','YYYYMMDD') or datdate-1=to_date('20470414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20480405','YYYYMMDD') or datdate-1=to_date('20480405','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20490418','YYYYMMDD') or datdate-1=to_date('20490418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20500410','YYYYMMDD') or datdate-1=to_date('20500410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20510402','YYYYMMDD') or datdate-1=to_date('20510402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20520421','YYYYMMDD') or datdate-1=to_date('20520421','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20530406','YYYYMMDD') or datdate-1=to_date('20530406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20540329','YYYYMMDD') or datdate-1=to_date('20540329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20550418','YYYYMMDD') or datdate-1=to_date('20550418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20560402','YYYYMMDD') or datdate-1=to_date('20560402','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20570422','YYYYMMDD') or datdate-1=to_date('20570422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20580414','YYYYMMDD') or datdate-1=to_date('20580414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20590330','YYYYMMDD') or datdate-1=to_date('20590330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20600418','YYYYMMDD') or datdate-1=to_date('20600418','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20610410','YYYYMMDD') or datdate-1=to_date('20610410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20620326','YYYYMMDD') or datdate-1=to_date('20620326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20630415','YYYYMMDD') or datdate-1=to_date('20630415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20640406','YYYYMMDD') or datdate-1=to_date('20640406','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20650329','YYYYMMDD') or datdate-1=to_date('20650329','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20660411','YYYYMMDD') or datdate-1=to_date('20660411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20670403','YYYYMMDD') or datdate-1=to_date('20670403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20680422','YYYYMMDD') or datdate-1=to_date('20680422','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20690414','YYYYMMDD') or datdate-1=to_date('20690414','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20700330','YYYYMMDD') or datdate-1=to_date('20700330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20710419','YYYYMMDD') or datdate-1=to_date('20710419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20720410','YYYYMMDD') or datdate-1=to_date('20720410','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20730326','YYYYMMDD') or datdate-1=to_date('20730326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20740415','YYYYMMDD') or datdate-1=to_date('20740415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20750407','YYYYMMDD') or datdate-1=to_date('20750407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20760419','YYYYMMDD') or datdate-1=to_date('20760419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20770411','YYYYMMDD') or datdate-1=to_date('20770411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20780403','YYYYMMDD') or datdate-1=to_date('20780403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20790423','YYYYMMDD') or datdate-1=to_date('20790423','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20800407','YYYYMMDD') or datdate-1=to_date('20800407','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20810330','YYYYMMDD') or datdate-1=to_date('20810330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20820419','YYYYMMDD') or datdate-1=to_date('20820419','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20830404','YYYYMMDD') or datdate-1=to_date('20830404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20840326','YYYYMMDD') or datdate-1=to_date('20840326','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20850415','YYYYMMDD') or datdate-1=to_date('20850415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20860331','YYYYMMDD') or datdate-1=to_date('20860331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20870420','YYYYMMDD') or datdate-1=to_date('20870420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20880411','YYYYMMDD') or datdate-1=to_date('20880411','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20890403','YYYYMMDD') or datdate-1=to_date('20890403','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20900416','YYYYMMDD') or datdate-1=to_date('20900416','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20910408','YYYYMMDD') or datdate-1=to_date('20910408','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20920330','YYYYMMDD') or datdate-1=to_date('20920330','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20930412','YYYYMMDD') or datdate-1=to_date('20930412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20940404','YYYYMMDD') or datdate-1=to_date('20940404','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20950424','YYYYMMDD') or datdate-1=to_date('20950424','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20960415','YYYYMMDD') or datdate-1=to_date('20960415','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20970331','YYYYMMDD') or datdate-1=to_date('20970331','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20980420','YYYYMMDD') or datdate-1=to_date('20980420','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20990412','YYYYMMDD') or datdate-1=to_date('20990412','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('21000328','YYYYMMDD') or datdate-1=to_date('21000328','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19500518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19510503','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19520522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19530514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19540527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19550519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19560510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19570530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19580515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19590507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19600526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19610511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19620531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19630523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19640507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19650527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19660519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19670504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19680523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19690515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19700507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19710520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19720511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19730531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19740523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19750508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19760527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19770519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19780504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19790524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19800515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19810528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19820520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19830512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19840531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19850516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19860508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19870528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19880512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19890504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19900524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19910509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19920528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19930520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19940512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19950525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19960516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19970508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19980521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19990513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20000601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20010524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20020509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20030529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20040520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20050505','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20060525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20070517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20080501','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20090521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20100513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20110602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20120517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20130509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20140529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20150514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20160505','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20170525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20180510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20190530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20200521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20210513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20220526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20230518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20240509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20250529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20260514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20270506','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20280525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20290510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20300530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20310522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20320506','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20330526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20340518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20350503','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20360522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20370514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20380603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20390519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20400510','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20410530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20420515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20430507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20440526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20450518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20460503','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20470523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20480514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20490527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20500519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20510511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20520530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20530515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20540507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20550527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20560511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20570531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20580523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20590508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20600527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20610519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20620504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20630524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20640515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20650507','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20660520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20670512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20680531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20690523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20700508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20710528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20720519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20730504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20740524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20750516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20760528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20770520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20780512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20790601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20800516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20810508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20820528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20830513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20840504','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20850524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20860509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20870529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20880520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20890512','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20900525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20910517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20920508','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20930521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20940513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20950602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20960524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20970509','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20980529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20990521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('21000506','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19500528','YYYYMMDD') or datdate-1=to_date('19500528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19510513','YYYYMMDD') or datdate-1=to_date('19510513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19520601','YYYYMMDD') or datdate-1=to_date('19520601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19530524','YYYYMMDD') or datdate-1=to_date('19530524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19540606','YYYYMMDD') or datdate-1=to_date('19540606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19550529','YYYYMMDD') or datdate-1=to_date('19550529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19560520','YYYYMMDD') or datdate-1=to_date('19560520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19570609','YYYYMMDD') or datdate-1=to_date('19570609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19580525','YYYYMMDD') or datdate-1=to_date('19580525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19590517','YYYYMMDD') or datdate-1=to_date('19590517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19600605','YYYYMMDD') or datdate-1=to_date('19600605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19610521','YYYYMMDD') or datdate-1=to_date('19610521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19620610','YYYYMMDD') or datdate-1=to_date('19620610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19630602','YYYYMMDD') or datdate-1=to_date('19630602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19640517','YYYYMMDD') or datdate-1=to_date('19640517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19650606','YYYYMMDD') or datdate-1=to_date('19650606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19660529','YYYYMMDD') or datdate-1=to_date('19660529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19670514','YYYYMMDD') or datdate-1=to_date('19670514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19680602','YYYYMMDD') or datdate-1=to_date('19680602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19690525','YYYYMMDD') or datdate-1=to_date('19690525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19700517','YYYYMMDD') or datdate-1=to_date('19700517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19710530','YYYYMMDD') or datdate-1=to_date('19710530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19720521','YYYYMMDD') or datdate-1=to_date('19720521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19730610','YYYYMMDD') or datdate-1=to_date('19730610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19740602','YYYYMMDD') or datdate-1=to_date('19740602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19750518','YYYYMMDD') or datdate-1=to_date('19750518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19760606','YYYYMMDD') or datdate-1=to_date('19760606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19770529','YYYYMMDD') or datdate-1=to_date('19770529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19780514','YYYYMMDD') or datdate-1=to_date('19780514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19790603','YYYYMMDD') or datdate-1=to_date('19790603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19800525','YYYYMMDD') or datdate-1=to_date('19800525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19810607','YYYYMMDD') or datdate-1=to_date('19810607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19820530','YYYYMMDD') or datdate-1=to_date('19820530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19830522','YYYYMMDD') or datdate-1=to_date('19830522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19840610','YYYYMMDD') or datdate-1=to_date('19840610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19850526','YYYYMMDD') or datdate-1=to_date('19850526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19860518','YYYYMMDD') or datdate-1=to_date('19860518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19870607','YYYYMMDD') or datdate-1=to_date('19870607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19880522','YYYYMMDD') or datdate-1=to_date('19880522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19890514','YYYYMMDD') or datdate-1=to_date('19890514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19900603','YYYYMMDD') or datdate-1=to_date('19900603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19910519','YYYYMMDD') or datdate-1=to_date('19910519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19920607','YYYYMMDD') or datdate-1=to_date('19920607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19930530','YYYYMMDD') or datdate-1=to_date('19930530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19940522','YYYYMMDD') or datdate-1=to_date('19940522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19950604','YYYYMMDD') or datdate-1=to_date('19950604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19960526','YYYYMMDD') or datdate-1=to_date('19960526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19970518','YYYYMMDD') or datdate-1=to_date('19970518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19980531','YYYYMMDD') or datdate-1=to_date('19980531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('19990523','YYYYMMDD') or datdate-1=to_date('19990523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20000611','YYYYMMDD') or datdate-1=to_date('20000611','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20010603','YYYYMMDD') or datdate-1=to_date('20010603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20020519','YYYYMMDD') or datdate-1=to_date('20020519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20030608','YYYYMMDD') or datdate-1=to_date('20030608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20040530','YYYYMMDD') or datdate-1=to_date('20040530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20050515','YYYYMMDD') or datdate-1=to_date('20050515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20060604','YYYYMMDD') or datdate-1=to_date('20060604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20070527','YYYYMMDD') or datdate-1=to_date('20070527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20080511','YYYYMMDD') or datdate-1=to_date('20080511','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20090531','YYYYMMDD') or datdate-1=to_date('20090531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20100523','YYYYMMDD') or datdate-1=to_date('20100523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20110612','YYYYMMDD') or datdate-1=to_date('20110612','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20120527','YYYYMMDD') or datdate-1=to_date('20120527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20130519','YYYYMMDD') or datdate-1=to_date('20130519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20140608','YYYYMMDD') or datdate-1=to_date('20140608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20150524','YYYYMMDD') or datdate-1=to_date('20150524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20160515','YYYYMMDD') or datdate-1=to_date('20160515','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20170604','YYYYMMDD') or datdate-1=to_date('20170604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20180520','YYYYMMDD') or datdate-1=to_date('20180520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20190609','YYYYMMDD') or datdate-1=to_date('20190609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20200531','YYYYMMDD') or datdate-1=to_date('20200531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20210523','YYYYMMDD') or datdate-1=to_date('20210523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20220605','YYYYMMDD') or datdate-1=to_date('20220605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20230528','YYYYMMDD') or datdate-1=to_date('20230528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20240519','YYYYMMDD') or datdate-1=to_date('20240519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20250608','YYYYMMDD') or datdate-1=to_date('20250608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20260524','YYYYMMDD') or datdate-1=to_date('20260524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20270516','YYYYMMDD') or datdate-1=to_date('20270516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20280604','YYYYMMDD') or datdate-1=to_date('20280604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20290520','YYYYMMDD') or datdate-1=to_date('20290520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20300609','YYYYMMDD') or datdate-1=to_date('20300609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20310601','YYYYMMDD') or datdate-1=to_date('20310601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20320516','YYYYMMDD') or datdate-1=to_date('20320516','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20330605','YYYYMMDD') or datdate-1=to_date('20330605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20340528','YYYYMMDD') or datdate-1=to_date('20340528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20350513','YYYYMMDD') or datdate-1=to_date('20350513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20360601','YYYYMMDD') or datdate-1=to_date('20360601','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20370524','YYYYMMDD') or datdate-1=to_date('20370524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20380613','YYYYMMDD') or datdate-1=to_date('20380613','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20390529','YYYYMMDD') or datdate-1=to_date('20390529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20400520','YYYYMMDD') or datdate-1=to_date('20400520','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20410609','YYYYMMDD') or datdate-1=to_date('20410609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20420525','YYYYMMDD') or datdate-1=to_date('20420525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20430517','YYYYMMDD') or datdate-1=to_date('20430517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20440605','YYYYMMDD') or datdate-1=to_date('20440605','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20450528','YYYYMMDD') or datdate-1=to_date('20450528','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20460513','YYYYMMDD') or datdate-1=to_date('20460513','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20470602','YYYYMMDD') or datdate-1=to_date('20470602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20480524','YYYYMMDD') or datdate-1=to_date('20480524','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20490606','YYYYMMDD') or datdate-1=to_date('20490606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20500529','YYYYMMDD') or datdate-1=to_date('20500529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20510521','YYYYMMDD') or datdate-1=to_date('20510521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20520609','YYYYMMDD') or datdate-1=to_date('20520609','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20530525','YYYYMMDD') or datdate-1=to_date('20530525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20540517','YYYYMMDD') or datdate-1=to_date('20540517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20550606','YYYYMMDD') or datdate-1=to_date('20550606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20560521','YYYYMMDD') or datdate-1=to_date('20560521','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20570610','YYYYMMDD') or datdate-1=to_date('20570610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20580602','YYYYMMDD') or datdate-1=to_date('20580602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20590518','YYYYMMDD') or datdate-1=to_date('20590518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20600606','YYYYMMDD') or datdate-1=to_date('20600606','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20610529','YYYYMMDD') or datdate-1=to_date('20610529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20620514','YYYYMMDD') or datdate-1=to_date('20620514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20630603','YYYYMMDD') or datdate-1=to_date('20630603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20640525','YYYYMMDD') or datdate-1=to_date('20640525','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20650517','YYYYMMDD') or datdate-1=to_date('20650517','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20660530','YYYYMMDD') or datdate-1=to_date('20660530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20670522','YYYYMMDD') or datdate-1=to_date('20670522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20680610','YYYYMMDD') or datdate-1=to_date('20680610','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20690602','YYYYMMDD') or datdate-1=to_date('20690602','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20700518','YYYYMMDD') or datdate-1=to_date('20700518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20710607','YYYYMMDD') or datdate-1=to_date('20710607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20720529','YYYYMMDD') or datdate-1=to_date('20720529','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20730514','YYYYMMDD') or datdate-1=to_date('20730514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20740603','YYYYMMDD') or datdate-1=to_date('20740603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20750526','YYYYMMDD') or datdate-1=to_date('20750526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20760607','YYYYMMDD') or datdate-1=to_date('20760607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20770530','YYYYMMDD') or datdate-1=to_date('20770530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20780522','YYYYMMDD') or datdate-1=to_date('20780522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20790611','YYYYMMDD') or datdate-1=to_date('20790611','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20800526','YYYYMMDD') or datdate-1=to_date('20800526','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20810518','YYYYMMDD') or datdate-1=to_date('20810518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20820607','YYYYMMDD') or datdate-1=to_date('20820607','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20830523','YYYYMMDD') or datdate-1=to_date('20830523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20840514','YYYYMMDD') or datdate-1=to_date('20840514','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20850603','YYYYMMDD') or datdate-1=to_date('20850603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20860519','YYYYMMDD') or datdate-1=to_date('20860519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20870608','YYYYMMDD') or datdate-1=to_date('20870608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20880530','YYYYMMDD') or datdate-1=to_date('20880530','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20890522','YYYYMMDD') or datdate-1=to_date('20890522','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20900604','YYYYMMDD') or datdate-1=to_date('20900604','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20910527','YYYYMMDD') or datdate-1=to_date('20910527','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20920518','YYYYMMDD') or datdate-1=to_date('20920518','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20930531','YYYYMMDD') or datdate-1=to_date('20930531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20940523','YYYYMMDD') or datdate-1=to_date('20940523','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20950612','YYYYMMDD') or datdate-1=to_date('20950612','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20960603','YYYYMMDD') or datdate-1=to_date('20960603','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20970519','YYYYMMDD') or datdate-1=to_date('20970519','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20980608','YYYYMMDD') or datdate-1=to_date('20980608','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('20990531','YYYYMMDD') or datdate-1=to_date('20990531','YYYYMMDD');
update dat set datholiday='Y' where datdate=to_date('21000516','YYYYMMDD') or datdate-1=to_date('21000516','YYYYMMDD');

commit
/

I think it is handy to create this dimension in it’s own schema:

create user dat identified by dat;
grant connect,resource to dat;
grant create public synonym to dat;
connect dat/dat
run the above script
grant select on dat to public;
create public synonym dat for dat;

Now everyone can use the dat dimension.

Linq to Oracle Autogenerated Key

We build on previous sample. In this example we add an autogenerated primary key. This is done by adding a sequence and an insert trigger in oracle:

SQL> create sequence degrees_seq
  2  start with 1
  3  increment by 1
  4  nomaxvalue;

Sequence created.

SQL>
SQL> create trigger degrees_trigger
  2  before insert on degrees
  3  for each row
  4  begin
  5  select degrees_seq.nextval into :new.degkey from dual;
  6  end;
  7  /

Trigger created.

SQL>

As we can see in linq.cs generated file the column DEGKey is attached with the IsDbGenerated = true attribute. This says that the .Net environment knows the column is updated from the database, which is what we want.

Now we get a new sample oracletest2.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using DbLinq.Linq.Mapping;
using System.Data.OracleClient;

class OracleTest
{
  static void Main(string[] args)
  {
     string connStr = “User Id=linq;Password=linq;Data Source=XE”;

     Linq db = new Linq(new OracleConnection(connStr)); // Linq is the mapperclass created with DbMetal

     Console.WriteLine(“Insert temperature 1:”);
     Degrees a = new Degrees();
     a.DEGKey = 1; // Must be set but doesn’t matter because is database generated
     a.DEGDate = System.DateTime.Now;
     a.DEGCeLcIUs = 1;
     db.Degrees.InsertOnSubmit(a);
     System.Threading.Thread.Sleep(5000);
     Console.WriteLine(“Insert temperature 2:”);
     Degrees b = new Degrees();
     b.DEGKey = 1; // Must be set but doesn’t matter because is database generated
     b.DEGDate = System.DateTime.Now;
     b.DEGCeLcIUs = 2;
     db.Degrees.InsertOnSubmit(b);
     System.Threading.Thread.Sleep(5000);
     Console.WriteLine(“Insert temperature 3:”);
     Degrees c = new Degrees();
     c.DEGKey = 1; // Must be set but doesn’t matter because is database generated
     c.DEGDate = System.DateTime.Now;
     c.DEGCeLcIUs = 3;
     db.Degrees.InsertOnSubmit(c);
     Console.WriteLine(“Key values before submit:”);
     Console.WriteLine(“a.DEGKey:”+a.DEGKey);
     Console.WriteLine(“b.DEGKey:”+b.DEGKey);
     Console.WriteLine(“c.DEGKey:”+c.DEGKey);
     db.SubmitChanges();
     Console.WriteLine(“Key values after submit (Should be synchronized but are not)”);
     Console.WriteLine(“a.DEGKey:”+a.DEGKey);
     Console.WriteLine(“b.DEGKey:”+b.DEGKey);
     Console.WriteLine(“c.DEGKey:”+c.DEGKey);

     Console.WriteLine(“——”);

     Console.WriteLine(“Select all temperatures (now the keys are synced): “);
     var q3 = from p in db.Degrees select p;
     Console.WriteLine(“Fired sql:”);
     Console.WriteLine(db.GetQueryText(q3));
     Console.WriteLine(“Result:”);
     Console.WriteLine(“DEGKEY – DEGDATE – DEGCELCIUS”);
     foreach (var v in q3)
     {
       Console.WriteLine(v.DEGKey + ” – ” + v.DEGDate + ” – ” + v.DEGCeLcIUs);  // For some reason DbMetal makes a very cryptic property name
     }
     Console.WriteLine(“——”);
   }
 }

Mind the comments in the code. A flaw in dblinq is that on the SubmitChanged the primarykey field should be updated which aren’t. For the rest it works nice.

build:

csc /r:dblinq.dll,dblinq.oracle.dll linq.cs oracletest2.cs

and run:

oracletest2.exe

have fun..

Linq for Oracle sample

Start with a database in my situation XE on localhost.

C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> create user linq identified by linq;
User created.
SQL> grant connect,resource to linq;
Grant succeeded.
SQL> connect linq/linq
Connected.
SQL> create table degrees
  2  (
  3    degkey number primary key
  4   ,degdate date
  5   ,degcelcius number
  6  )
  7  /
Table created.
SQL>

Download the dblinq library from : http://code2code.net/DB_Linq/

or get the latest via svn which has VisualMetal:
svn checkout http://dblinq2007.googlecode.com/svn/trunk/

Goto the directory and do the following to build the oracle libraries:

cd DbLinq.Oracle
msbuild

This will build the DbLinq.dll and DbLinq.Oracle.dll in bin\Debug

cd DbMetal
msbuild

This will build the DbMetal tool which creates the database to objects mapper classes. Ignore the copy errors at the end, it works without the copying because the oracle assemblies are in the GAC. DbMetal is created in the bin directory.

Run the command to create the mapper classes.

dbmetal /provider:oracle /conn:”Data Source=XE;User Id=linq;Password=linq;” /database=linq /code:linq.cs

create a new working directory (c:\dotnet\oracle) and copy the linq.cs, DbLinq.dll and DbLinq.Oracle.dll to this directory.

Create the test program oracletest.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using DbLinq.Linq.Mapping;
using System.Data.OracleClient;

class OracleTest
{
static void Main(string[] args)
{
string connStr = “User Id=linq;Password=linq;Data Source=XE”;

Linq db = new Linq(new OracleConnection(connStr)); // Linq is the mapperclass created with DbMetal

Console.WriteLine(“Select and delete all temperatures:”);
var q1 = from p in db.Degrees select p;
//db.Degrees.RemoveAll(q1); // Not implemented
Console.WriteLine(“Fired sql:”);
Console.WriteLine(db.GetQueryText(q1));
Console.WriteLine(“Result:”);
foreach (var v in q1)
{
Console.WriteLine(v.DEGKey + ” – ” + v.DEGDate + ” – ” + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
db.Degrees.DeleteOnSubmit(v);
}
db.SubmitChanges();
Console.WriteLine(“——”);

Console.WriteLine(“Select temperatures (none):”);
var q2 = from p in db.Degrees select p;
Console.WriteLine(“Fired sql:”);
Console.WriteLine(db.GetQueryText(q2));
Console.WriteLine(“Result:”);
Console.WriteLine(“DEGKEY – DEGDATE – DEGCELCIUS”);
foreach (var v in q2)
{
Console.WriteLine(v.DEGKey + ” – ” + v.DEGDate + ” – ” + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
Console.WriteLine(“——”);

Console.WriteLine(“Insert temperature 1:”);
Degrees a = new Degrees();
a.DEGKey = 1;
a.DEGDate = System.DateTime.Now;
a.DEGCeLcIUs = 1;
db.Degrees.InsertOnSubmit(a);
System.Threading.Thread.Sleep(5000);
Console.WriteLine(“Insert temperature 2:”);
Degrees b = new Degrees();
b.DEGKey = 2;
b.DEGDate = System.DateTime.Now;
b.DEGCeLcIUs = 2;
db.Degrees.InsertOnSubmit(b);
System.Threading.Thread.Sleep(5000);
Console.WriteLine(“Insert temperature 3:”);
Degrees c = new Degrees();
c.DEGKey = 3;
c.DEGDate = System.DateTime.Now;
c.DEGCeLcIUs = 3;
db.Degrees.InsertOnSubmit(c);

db.SubmitChanges();
Console.WriteLine(“——”);

Console.WriteLine(“Select all temperatures:”);
var q3 = from p in db.Degrees select p;
Console.WriteLine(“Fired sql:”);
Console.WriteLine(db.GetQueryText(q3));
Console.WriteLine(“Result:”);
Console.WriteLine(“DEGKEY – DEGDATE – DEGCELCIUS”);
foreach (var v in q3)
{
Console.WriteLine(v.DEGKey + ” – ” + v.DEGDate + ” – ” + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
Console.WriteLine(“——”);

Console.WriteLine(“Select all temperatures higher than 1 and add 5 to these:”);
var q4 = from p in db.Degrees where p.DEGCeLcIUs>1 select p;
Console.WriteLine(“Fired sql:”);
Console.WriteLine(db.GetQueryText(q4));
Console.WriteLine(“Result:”);
Console.WriteLine(“DEGKEY – DEGDATE – DEGCELCIUS”);
foreach (var v in q4)
{
Console.WriteLine(v.DEGKey + ” – ” + v.DEGDate + ” – ” + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
foreach (var v in q4)
{
v.DEGCeLcIUs+=5;
}
db.SubmitChanges();
Console.WriteLine(“——”);

Console.WriteLine(“Select all temperatures:”);
var q5 = from p in db.Degrees select p;
Console.WriteLine(“Fired sql:”);
Console.WriteLine(db.GetQueryText(q5));
Console.WriteLine(“Result:”);
Console.WriteLine(“DEGKEY – DEGDATE – DEGCELCIUS”);
foreach (var v in q5)
{
Console.WriteLine(v.DEGKey + ” – ” + v.DEGDate + ” – ” + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
Console.WriteLine(“——”);

}
}

Compile and run:

csc /r:dblinq.dll,dblinq.oracle.dll *.cs

oracletest:

Select and delete all temperatures:
Fired sql:
SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY
 FROM LINQ.DEGREES p$
Result:
1 - 5/22/2008 7:07:48 PM - 1
2 - 5/22/2008 7:07:53 PM - 2
3 - 5/22/2008 7:07:58 PM - 3
------
Select temperatures (none):
Fired sql:
SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY
 FROM LINQ.DEGREES p$
Result:
DEGKEY - DEGDATE - DEGCELCIUS
------
Insert temperature 1:
Insert temperature 2:
Insert temperature 3:
------
Select all temperatures:
Fired sql:
SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY
 FROM LINQ.DEGREES p$
Result:
DEGKEY - DEGDATE - DEGCELCIUS
1 - 5/22/2008 7:10:14 PM - 1
2 - 5/22/2008 7:10:19 PM - 2
3 - 5/22/2008 7:10:24 PM - 3
------
Select all temperatures higher than 1 and add 5 to these:
Fired sql:
SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY
 FROM LINQ.DEGREES p$ WHERE p$.DEGCELCIUS > 1
Result:
DEGKEY - DEGDATE - DEGCELCIUS
2 - 5/22/2008 7:10:19 PM - 2
3 - 5/22/2008 7:10:24 PM - 3
------
Select all temperatures:
Fired sql:
SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY
 FROM LINQ.DEGREES p$
Result:
DEGKEY - DEGDATE - DEGCELCIUS
1 - 5/22/2008 7:10:14 PM - 1
2 - 5/22/2008 7:10:19 PM - 7
3 - 5/22/2008 7:10:24 PM - 8
------

Have fun…