| Can anyone help me code a simple function? [message #4566] |
Sun, 22 December 2002 13:37  |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
