Creating fact table with year of 9999

Question

I'm building a simple fact table in oracle based on a customer status where a customer has a status, 'Active' and 'Lost' and a date they started with that status and a date they ended.

A sample 3 rows would be;

CustID | status | date_start | date_end   
---------------------------------------
  1    | active |   1/1/13   |  1/12/14  
  1    | lost   | 1/12/14    | 31/12/9999  
  2    |active  | 1/12/14    | 31/12/9999

Here, cust 1 was active and then was lost. When a account status is current (as of today) the end date column is 31/12/9999. Cust 2 is active as of today

My question is, how can I bring this into a fact table?

CREATE TABLE temp AS 
SELECT  CS.contract_status_id , to_char(ASH.Contract_Status_Start, 'DD/MM/YYYY') AS cust_status_start_date, to_char(ASH.CONTRACT_STATUS_END, 'DD/MM/YYYY') As cust_status_end_date
FROM account_status_history ASH,   
customer_status_dim CS
WHERE ASH.contract_status = CS.contract_status

Fact Table:

CREATE TABLE customer_status_fact AS
SELECT T.cust_status_start_date, T.cust_status_end_date, T.contract_status_id,
count(T.contract_status_id) AS TOTAL_ACCOUNTS
FROM temp T
GROUP BY T.cust_status_start_date, T.cust_status_end_date, T.contract_status_id

And testing it;

select sum(F.TOTAL_ACCOUNTS), CS.contract_status_txt 
from customer_status_fact F, customer_status_dim CS

where F.contract_status_id = CS.contract_status_id
and F.cust_status_start_date <= sysdate 
and F.cust_status_end_date = '31/12/9999'
group by CS.contract_status_txt

I can't seem to get oracle to recognise the year 9999 Any help is appreciated

Answer 1

and F.cust_status_end_date = '31/12/9999'

'31/12/9999' is NOT a DATE, it is a string enclosed within single-quotation marks. You must use TO_DATE to explicitly convert it into a DATE.

For example,

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> SELECT to_date('31/12/9999 23:59:59','DD/MM/YYYY HH24:MI:SS') FROM dual;

TO_DATE('31/12/9999
-------------------
31/12/9999 23:59:59

SQL>

OR,

SQL> SELECT to_date(5373484, 'J') + (1 - 1/24/60/60) FROM dual;

TO_DATE(5373484,'J'
-------------------
31/12/9999 23:59:59

SQL>

CREATE TABLE temp AS SELECT CS.contract_status_id , to_char(ASH.Contract_Status_Start, 'DD/MM/YYYY') AS cust_status_start_date, to_char(ASH.CONTRACT_STATUS_END, 'DD/MM/YYYY') As cust_status_end_date

Why would you create a table with DATEs converted to a STRING? You should let the dates as it is. You should use TO_CHAR only for display purpose. For any date calculations, let the date remain as a date.