Re: Oracle help!! SQL CASE statement

phili..._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 = *****************
An aside from your question, never do this:

SELECT *
FROM A, B
WHERE A.C1 = B.C1(+)
AND B.C2 = 'FOO'; This will not give you an outer join.

Beyond that, your English is almost as bad as your SQL.

"... 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."

If you want a clear answer, you must write a clear question. Try writing it out carefully, like this:
"Each row in COLLECT_ACCOUNT may join to multiple rows in COLLECT_ACCT_SYS_DATA. I want to write my query so that if the COLLECT_ACCOUNT End_Date is 1/19/2005, then the COLLECT_ACCT_SYS_DATA End_Date must equal 1/19/2005; otherwise, the COLLECT_ACCT_SYS_DATA End_Date must equal 12/31/9999." Received on Thu Jan 20 2005 - 16:56:59 CST