I have a table
desc emp_consol;
Name Null? Type
----------------- -------- ------------
SSN_NO VARCHAR2(20)
SALARY NUMBER
SAVINGS NUMBER
DEBT NUMBER
TIME_LOADED DATE
and a function
create or replace function emp_vals(SSN IN VARCHAR2) RETURN VARCHAR2 IS
SSNLIST VARCHAR2(4000) := '';
BEGIN
FOR P IN (SELECT TIME_LOADED, salary, savings, debt from emp_consol where SSN_NO=SSN)
loop
SSNLIST := SSNLIST || P.TIME_LOADED || ' ' || P.salary || ' ' || P.savings || ' ' || P.debt || '|';
END LOOP;
RETURN SUBSTR(SSNLIST,1,LENGTH(SSNLIST)-1);
END;
/
The table has a record for each month savings, debt, income for each employee
If I execute the query
select SSN_NO, emp_vals(SSN_NO) FROM emp_consol group by SSN_NO;
I get a row for each SSN with all the related information for each month - e.g. if empl_consol has 8 records for two SSNs this query will two records However if I execute the query
select SSN_NO, emp_vals(SSN_NO) FROM emp_consol group by SSN_NO, salary, savings, debt, emp_vals(SSN_NO);
I get a listing for all the records in emp_consol table in the format I described - e.g. if empl_consol has 8 records for two SSNs this query will return 8 records. This ends up in a SSN being repeated several times.
Can someone explain why is this so?
I guess I do not understand GROUP BY as well I though I did.
Thank You
Riz



---------------------------------
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
Oracle LazyDBA home page