Displaying data from 3 tables (Oracle)?

you need to build a string aggregate  here's Tom Kyte's stragg

CREATE OR REPLACE TYPE "STRING_AGG_TYPE" AS OBJECT(
    total   VARCHAR2(4000),
    STATIC FUNCTION odciaggregateinitialize(sctx IN OUT string_agg_type)
        RETURN NUMBER,
    MEMBER FUNCTION odciaggregateiterate(SELF IN OUT string_agg_type, VALUE IN VARCHAR2)
        RETURN NUMBER,
    MEMBER FUNCTION odciaggregateterminate(SELF IN string_agg_type, returnvalue OUT VARCHAR2, flags IN NUMBER)
        RETURN NUMBER,
    MEMBER FUNCTION odciaggregatemerge(SELF IN OUT string_agg_type, ctx2 IN string_agg_type)
        RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY string_agg_type
IS
    STATIC FUNCTION odciaggregateinitialize(sctx IN OUT string_agg_type)
        RETURN NUMBER
    IS
    BEGIN
        sctx    := string_agg_type(NULL);
        RETURN odciconst.success;
    END;
    MEMBER FUNCTION odciaggregateiterate(SELF IN OUT string_agg_type, VALUE IN VARCHAR2)
        RETURN NUMBER
    IS
    BEGIN
        SELF.total    := SELF.total || ',' || VALUE;
        RETURN odciconst.success;
    END;
    MEMBER FUNCTION odciaggregateterminate(SELF IN string_agg_type, returnvalue OUT VARCHAR2, flags IN NUMBER)
        RETURN NUMBER
    IS
    BEGIN
        returnvalue    := LTRIM(SELF.total, ',');
        RETURN odciconst.success;
    END;
    MEMBER FUNCTION odciaggregatemerge(SELF IN OUT string_agg_type, ctx2 IN string_agg_type)
        RETURN NUMBER
    IS
    BEGIN
        SELF.total    := SELF.total || ctx2.total;
        RETURN odciconst.success;
    END;
END;
/

CREATE OR REPLACE FUNCTION stragg(input VARCHAR2)
    RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
    USING string_agg_type;
/

SELECT pr.prod_id, prod_name, client_id as PROVIDER,  stragg(co.company_id || ' ' || co.company_name) as CUSTOMERS
FROM Products pr
join customers cu on cu.prod_id=co.prod_id
join companies co on cu.company_id=co.company_id
WHERE prod_name = 'Sorny Camera'
group by pr.prod_id,prod_name,client_id

note, your concatenated list of customers can't be longer than 4000 characters, otherwise it can't be returned in sql

That gives me "ORA-06575 Package or Function STRAGG is in an invalid state".


Would it be easier if i wanted to display this? Or would i still need an aggregate function?

Prod_id             prod_name          PROVIDER          CUSTOMERS
-----------------------------------------------------------------------------------------------------------------
pr001            Sorny Camera         co003                co001,  co002



...From just the two tables below?

Products

Prod_id         Prod_name            client_id
--------------------------------------------
pr001         Sorny camera            co003


Customers

Cust_id    Prod_id      Company_id            
--------------------------------------
cu001         pr001      co001
cu002         pr001      co002

borrowing a function from http://www.orafaq.com/faq/map_rows_to_a_column

SQL> CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
  2    ret  VARCHAR2(4000);
  3    hold VARCHAR2(4000);
  4    cur  sys_refcursor;
  5  BEGIN
  6    OPEN cur FOR q;
  7    LOOP
  8      FETCH cur INTO hold;
  9      EXIT WHEN cur%NOTFOUND;
 10      IF ret IS NULL THEN
 11        ret := hold;
 12      ELSE
 13        ret := ret || ',' || hold;
 14      END IF;
 15    END LOOP;
 16    RETURN ret;
 17  END;
 18  /
 
SELECT pr.prod_id, prod_name, client_id as PROVIDER,
rowconcat (concat(
'select co.company_id || '' '' || co.company_name ',
'from companies co on co.company_id=', cu.company_id
) as CUSTOMER
FROM Products pr
join customers cu on cu.prod_id=co.prod_id
WHERE prod_name = 'Sorny Camera'

Select allOpen in new window

ORA-00909 Invalid number of arguments for the rowconcat function and
ORA-00712 Invalid number if i add '' to the end of line 23...

are there compilation errors in the type or the function?  if so, what are they?   I use stragg all the time.

Is this a pre-9i db?  userdefined aggregates aren't supported until 9

oops...

SELECT pr.prod_id, prod_name, client_id as PROVIDER,
rowconcat (concat(
'select co.company_id || '' '' || co.company_name ',
'from companies co on co.company_id=', cu.company_id
)) as CUSTOMER

match the brackets.. sorry

imitchie, i still get ORA-00712 Invalid number...

the company_id is a NUMBER
the company_name is a VARCHAR2(100 Char)

?

does it tell you where it fails?

SELECT pr.prod_id, prod_name, client_id as PROVIDER,
rowconcat (concat(
'select co.company_id || '' '' || co.company_name ',
'from companies co on co.company_id=', STR(cu.company_id)
)) as CUSTOMER

ORA-00909 Invalid number of arguments
Line 2, column 11

oracle only allows 2 strings at a time for concat.. working too much with mysql and sql server

SELECT pr.prod_id, prod_name, client_id as PROVIDER,
rowconcat (
'select co.company_id || '' '' || co.company_name ' ||
'from companies co on co.company_id=' || STR(cu.company_id)
) as CUSTOMER

Select allOpen in new window

Ok, almost, i'm getting another vague error (makes me appreciate the stack traces in java!)

ORA-00933 SQl command not properly ended
ORA-06512 at 'schema1.ROWCONCAT' line 6

barney75,  what are the errors in stragg and the underlying type?  I definitely don't recommend the rowconcat method.  It's going to be a much more expensive operation than using an aggregate.  That's why the user-defined aggregates were invented.

If you're on a pre-9i db, then you have to use the rowconcat method but on 9i and up, user-aggregates are the way to go.

Let me know what errors you're getting and I'll try to help you get them compiled.

sdstuber, the message i am getting is:

ORA-06575 Package or function STRAGG is in an invalid state.
Error at line 1 column 56 (which is the stragg)

and the function itself is marked with a red x (i'm using sql developer)...

what about the type?

stragg uses a type, what's the status of the type?

STRING_AGG_TYPE,  any compile errors in it?

Doesn't seem to be any errors in the type.  Should the contents of the file be showing "create or replace" ?

create or replace FUNCTION stragg(input VARCHAR2)
    RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
    USING string_agg_type

was that error on this query?

SELECT pr.prod_id, prod_name, client_id as PROVIDER,
rowconcat (
'select co.company_id || '' '' || co.company_name ' ||
'from companies co on co.company_id=' || STR(cu.company_id)
) as CUSTOMER
FROM Products pr
join customers cu on cu.prod_id=co.prod_id
WHERE prod_name = 'Sorny Camera'

Select allOpen in new window

yes,  stragg is created with "create or replace"

CREATE OR REPLACE FUNCTION stragg(input VARCHAR2)
    RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
    USING string_agg_type;
/

Would this happen to have compile errors because it doesn't have the ";" and "/" at the end of the statement?

yes

Ok, this could be a stupid question, but how do i create the function so that it has the ";" and the "/" ? If i run the exact script below as it is, it shows without them!

CREATE OR REPLACE FUNCTION stragg(input VARCHAR2)
    RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
    USING string_agg_type;
/

The ; is end of the create or replace statement

the / is the sqlplus command to run the statement.

It is not part of the function text itself.


are you sure the error is in that function and not in the underlying string_agg_type?

ah no - i managed to figure out how to edit the function - added the semicolon and the compile error is gone.  However, i can't run the statement now because of "ORA-00937 not a single-group group function" !

what's the statement that gets that error?

SELECT pr.prod_id, prod_name, client_id || ', ' || comp.company_name  as PROVIDER,  co.company_id || ', ' || co.company_name as CUSTOMERS
FROM Products pr, Companies comp
join Customers cu on cu.prod_id=co.prod_id
join Companies co on cu.company_id=co.company_id
WHERE prod_name = 'Sorny Camera' AND prod_sn='002AB' AND prod_model='DSC'

If i use this group by clause then the SQL will run, but the 'CUSTOMERS' column from the rows whre the prod_id are identical are not concatenated:
group by pr.prod_id, prod_name, client_id, comp.company_name, co.company_id, co.company_name

you don't have any aggregates (group functions) in your query.

you're not using stragg so I don't see how it could be the problem.

what's the query with stragg in it that generates the error?

My mistake...

SELECT pr.prod_id, prod_name, client_id || ', ' || comp.company_name  as PROVIDER,  stragg(co.company_id || ', ' || co.company_name) as CUSTOMERS
FROM Products pr, Companies comp
join Customers cu on cu.prod_id=co.prod_id
join Companies co on cu.company_id=co.company_id
WHERE prod_name = 'Sorny Camera' AND prod_sn='002AB' AND prod_model='DSC'

Thanks sdstube, working perfect... much appreciated!