Ok,
I have a Dataware house that I am pulling records from using Oracle
SQL. I have a select statement that looks like the one below. Now what
I need to do is where the astrics are **** create a case statement or
whatever it is in Oracle to say that for this record if a 1/19/2005
record exists then End_Date needs to be=1/19/2005 else get
End_Date=12/31/9999. Keep in mind that a record could have both a
1/19/2005 and 12/31/9999 instance of that account record. If 1/19
exists that takes presedent if it doesnt then 12/31/9999. The problem
is that the fields I pull from the table where the end_date is in
question change based on which date I pull(12/31/9999 being the most
recient which in some cases as you see I dont want.) so they are not
identical. This is tricky.
Please let me know if you can help.
SELECT
COLLECTOR_RESULTS.USER_ID,
COLLECTOR_RESULTS.LETTER_CODE,
COLLECTOR_RESULTS.ACCT_NUM AS ACCT_NUM,
COLLECTOR_RESULTS.ACTIVITY_DATE,
COLLECTOR_RESULTS.BEGIN_DATE,
COLLECTOR_RESULTS.COLLECTION_ACTIVITY_CODE,
COLLECTOR_RESULTS.PLACE_CALLED,
COLLECTOR_RESULTS.PARTY_CONTACTED_CODE,
COLLECTOR_RESULTS.ORIG_FUNC_AREA,
COLLECTOR_RESULTS.ORIG_STATE_NUMBER,
COLLECTOR_RESULTS.CACS_FUNCTION_CODE,
COLLECTOR_RESULTS.CACS_STATE_NUMBER,
COLLECTOR_RESULTS.STATE_POSITION,
COLLECTOR_RESULTS.TIME_OBTAINED,
COLLECTOR_RESULTS.TIME_RELEASED,
COLLECT_ACCT_SYS_DATA.DAYS_DELINQUENT_NUM,
sum(WMB.COLLECT_ACCT_SYS_DATA.PRINCIPAL_AMT)As PBal,
FROM
COLLECTOR_RESULTS,
COLLECT_ACCT_SYS_DATA,
COLLECT_ACCOUNT
WHERE
COLLECT_ACCOUNT.ACCT_NUM=COLLECT_ACCT_SYS_DATA.ACCT_NUM(+)
AND
COLLECT_ACCOUNT.LOCATION_CODE=COLLECT_ACCT_SYS_DATA.LOCATION_CODE(+)
AND COLLECT_ACCOUNT.ACCT_NUM=COLLECTOR_RESULTS.ACCT_NUM(+)
AND COLLECT_ACCOUNT.LOCATION_CODE=COLLECTOR_RESULTS.LOCATION_CODE(+)
AND
COLLECTOR_RESULTS.ACTIVITY_DATE =
to_date(''01/19/2005'',''mm/dd/yyyy'')
AND COLLECT_ACCOUNT.END_DATE = to_date(''12/31/9999'',''mm/dd/yyyy'')
AND COLLECT_ACCT_SYS_DATA.END_DATE = *****************
Received on Thu Jan 20 2005 - 13:15:12 CST
