philipdm_at_msn.com wrote:
> 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 = *****************
So use the CASE statement:
http://www.psoug.org
click on Morgan's Library
click on CASE Statement
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
Received on Thu Jan 20 2005 - 17:03:41 CST
