| Can anyone help me code a simple function? [message #41364] |
Sun, 22 December 2002 13:33  |
Rami
Messages: 7
Registered: February 2002
|
Junior Member |
|
|
The situation is this. I have a table where the PK is CHARGE_ID (number); another field is CASE_ID (number). CASE_ID is a nonuniqe field as there can be many CHARGE_IDs per CASE_ID. What I want to do is assign a '1' (number) to the minimun CHARGE_ID for each CASE_ID. Essentially I am looking to create a calculated column that will have '1' next to the minimimun CHARGE_ID for each CASE_ID. That way if I do a sum of all the '1' in the calculated column, I will get a count of the distinct number of CASE_IDs; and yes I can only get a distinct count of CASE_IDs in this manner (count distinct is not applicable).
The table I am using is JIWT_CHARGE
The applicable columns are JIWT_CHARGE.CHARGE_ID
and JIWT_CHARGE.CASE_ID.
Here is my lick at a function which accepts the CASE_ID, uses a CURSOR to import data, and tries to associate a '1' or a '0' with a CHARGE_ID.
=========================================
CREATE OR REPLACE FUNCTION JIWFN_COG_CASE_COUNT
(v_case IN NUMBER)
RETURN NUMBER IS
case_count number(1);
CURSOR c1 IS
SELECT charge_id charge_id, case_id case_id
FROM jiwt_charge
WHERE case_id = v_case;
BEGIN
FOR charge_record IN c1 LOOP
IF (charge_record.charge_id = min(charge_record)) THEN
case_count := 1;
ELSE
case_count :=0;
END IF;
END LOOP;
END;
/
=======================
It doesn't work. Any help would be greatly appreciated.
|
|
|
|
| Re: Can anyone help me code a simple function? [message #41365 is a reply to message #41364] |
Sun, 22 December 2002 14:42  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Several comments:
1) Your statement that 'count distinct is not applicable' does not make sense. If we can figure out the minimum CHARGE_ID for each CASE_ID, we certainly know how many distinct case_id values exist.
2) Storing a calculated value is almost always a bad idea - especially since you say you just want to store it to figure out the number of case_id values. Again, why would 'count(distinct case_id)' be any different than summing this stored calculated value?
Even if you ignore all this, the minimum charge_id for each case_id can be determined in a single SQL statement - no PL/SQL is needed or should be used.
|
|
|
|
| Re: Can anyone help me code a simple function? [message #41367 is a reply to message #41364] |
Sun, 22 December 2002 16:56 |
Rami
Messages: 7
Registered: February 2002
|
Junior Member |
|
|
In reference to your second statement:
[[2) Storing a calculated value is almost always a bad idea - especially since you say you just want to store it to figure out the number of case_id values. Again, why would 'count(distinct case_id)' be any different than summing this stored calculated value?]]
I am actually creating an extract file from this table. This extract file will have charge_ids (pk) along side the case_ids; which as noted will not be unique (1:M - case_id:charge_id).
The program that will interprest this extract file; Cognos Transformer (if your familiar with DW front ends), cannot perform a distinct count on the number of case_ids in the file; a software limitation. Hence, I need a 3rd column in the extract file which will have a value of '1' if the charge_id is the min(charge_id) for each case_id; this way if I sum this column, I'll get a distinct count of the case_ids.
I do somewhat understand your assertion that no PL/SQL is needed or should be used; I think I'm making it more complicated then it is.
Thanks for your comments.
|
|
|
|
| Re: Can anyone help me code a simple function? [message #41372 is a reply to message #41367] |
Mon, 23 December 2002 13:31 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Ok, fair enough - but that is a pretty big software limitation.
I would recommend not writing a function to mark on a case by case basis, but just update them all as:
update t
set marker_column = 1
where charge_id in (select min(charge_id)
from t
group by case_id); |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
