Solved

Displaying data from 3 tables (Oracle)?

Posted on 2007-11-22
29
563 Views
Last Modified: 2010-04-21
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?
0
Comment
Question by:barney75
  • 13
  • 11
  • 5
29 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
note, your concatenated list of customers can't be longer than 4000 characters, otherwise it can't be returned in sql
0
 

Author Comment

by:barney75
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:barney75
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:barney75
Comment Utility
imitchie, i still get ORA-00712 Invalid number...

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

?
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:barney75
Comment Utility
ORA-00909 Invalid number of arguments
Line 2, column 11
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:barney75
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Comment

by:barney75
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
what about the type?

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

STRING_AGG_TYPE,  any compile errors in it?
0
 

Author Comment

by:barney75
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Comment

by:barney75
Comment Utility
Would this happen to have compile errors because it doesn't have the ";" and "/" at the end of the statement?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
yes
0
 

Author Comment

by:barney75
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Comment

by:barney75
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
what's the statement that gets that error?
0
 

Author Comment

by:barney75
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Comment

by:barney75
Comment Utility
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:barney75
Comment Utility
Thanks sdstube, working perfect... much appreciated!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now