You Asked
Tom,
create table date_dim(date_id integer primary key,actual_dt date ,mth_mm integer,quarter integer,year_month integer);
insert into date_dim values(2952,'30-jan-08',1,1,200801);
insert into date_dim values(2981,'28-feb-08',2,1,200802);
insert into date_dim values(3288,'31-dec-08',12,4,200812);
create table transaction(transaction_dt_id integer,effective_dt_id integer,expiration_dt_id integer,
accounting_dt_id integer,
booking_month_id integer,calendar_month_id integer
);
insert into transaction(transaction_dt_id ,effective_dt_id,expiration_dt_id,accounting_dt_id,booking_month_id,calendar_month_id) values (2952,2981,2952,3288,2952,2952);
insert into transaction(transaction_dt_id ,effective_dt_id,expiration_dt_id,accounting_dt_id,booking_month_id,calendar_month_id)
values (2981,2981,2952,3288,2952,2952);
select efd.actual_dt eff_dt,efd.mth_mm eff_month,trd.actual_dt transaction_dt,
exd.actual_dt expn_dt,acd.quarter accounting_quarter,bkd.mth_mm booking_month,clm.mth_mm calendar_month
from transaction trn ,date_dim efd,date_dim exd,date_dim trd,date_dim acd,date_dim bkd,date_dim clm
where trn.transaction_dt_id = trd.date_id
and trn.effective_dt_id = efd.date_id
and trn.expiration_dt_id = exd.date_id
and trn.accounting_dt_id = acd.date_id
and trn.booking_month_id = bkd.date_id
and trn.calendar_month_id = clm.date_id
Is there a better of writing this kind of queries?.i mean without refering to the date_dim multiple times through alias
and joining the table on different columns.if not is there a better way of desinging to satisfy similar requirements?
Thanks in advance.
-Sambi
and Tom said...
in this case, no, there is not.
You have six attributes, six unrelated/uncorrelated attributes. They could be foreign keys to six different tables in this case, it would be no different.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment