Displaying data from 3 tables (Oracle)?

Hello,

This is a tricky one... Using only the prod_name hardcoded in the query -  I am trying to display...



Prod_id             prod_name      PROVIDER                             CUSTOMERS
-----------------------------------------------------------------------------------------------------------------
pr001            Sorny Camera      co003 Sorny Manufacturer      co001 Shop Electronic, co002 Comp World



...Given the 3 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


Companies

Company_id      company_name
--------------------------------
co001            Shop Electronic
co002            Comp World
co003            Sorny Manufacturer



This is my attempt

SELECT pr.prod_id, prod_name, client_id as PROVIDER,  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'

which displays

Prod_id             prod_name      PROVIDER      CUSTOMERS
------------------------------------------------------------------------
pr001            Sorny Camera      co003            co001 Shop Electronic
pr001            Sorny Camera      co003            co002 Comp World


Any suggestions?
barney75Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
since you are building the aggregate of company_id and company_name you don't want to include  those in your group by.

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' GROUP BY pr.prod_id, prod_name, client_id, comp.company_name


Note, stragg itself uses commas as a delimiter,  since you're aggregating a pair of columns that are concatenated with a comma your results will look a little funny.

For instance, if you have the following data....
prod_id  = 1
prod_name = barney
client_id = 10
comp.company_name= mycompany
co.company_id = 5
co.company_name = company_five
co.company_id = 6
co.company_name = company_six
co.company_id=7
co.company_name =company_seven


then you'll get results like this....

prod_id    prod_name    provider     customers
1    barney   10,mycompany    5,company_five,6,company_six,7,company_seven
0
 
sdstuberCommented:
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
0
 
sdstuberCommented:
note, your concatenated list of customers can't be longer than 4000 characters, otherwise it can't be returned in sql
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
barney75Author Commented:
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
0
 
imitchieCommented:
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'

Open in new window

0
 
barney75Author Commented:
ORA-00909 Invalid number of arguments for the rowconcat function and
ORA-00712 Invalid number if i add '' to the end of line 23...
0
 
sdstuberCommented:
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
0
 
imitchieCommented:
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
0
 
barney75Author Commented:
imitchie, i still get ORA-00712 Invalid number...

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

?
0
 
imitchieCommented:
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
0
 
barney75Author Commented:
ORA-00909 Invalid number of arguments
Line 2, column 11
0
 
imitchieCommented:
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

Open in new window

0
 
barney75Author Commented:
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
0
 
sdstuberCommented:
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.
0
 
barney75Author Commented:
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)...
0
 
sdstuberCommented:
what about the type?

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

STRING_AGG_TYPE,  any compile errors in it?
0
 
barney75Author Commented:
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
0
 
imitchieCommented:
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'

Open in new window

0
 
sdstuberCommented:
yes,  stragg is created with "create or replace"

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

0
 
barney75Author Commented:
Would this happen to have compile errors because it doesn't have the ";" and "/" at the end of the statement?
0
 
sdstuberCommented:
yes
0
 
barney75Author Commented:
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;
/
0
 
sdstuberCommented:
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?
0
 
barney75Author Commented:
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" !
0
 
sdstuberCommented:
what's the statement that gets that error?
0
 
barney75Author Commented:
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
0
 
sdstuberCommented:
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?


0
 
barney75Author Commented:
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'
0
 
barney75Author Commented:
Thanks sdstube, working perfect... much appreciated!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.