Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Displaying data from 3 tables (Oracle)?

Posted on 2007-11-22
29
Medium Priority
?
596 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 11
  • 5
29 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 20335799
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 74

Expert Comment

by:sdstuber
ID: 20335804
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
ID: 20335955
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
Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

 
LVL 25

Expert Comment

by:imitchie
ID: 20335958
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
ID: 20336078
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 74

Expert Comment

by:sdstuber
ID: 20336156
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
ID: 20336166
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
ID: 20336232
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
ID: 20336259
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
ID: 20337980
ORA-00909 Invalid number of arguments
Line 2, column 11
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20338093
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
ID: 20338313
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 74

Expert Comment

by:sdstuber
ID: 20339459
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
ID: 20339811
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 20340369
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
ID: 20364154
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
ID: 20364750
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 74

Expert Comment

by:sdstuber
ID: 20364974
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
ID: 20463613
Would this happen to have compile errors because it doesn't have the ";" and "/" at the end of the statement?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20464480
yes
0
 

Author Comment

by:barney75
ID: 20465317
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 74

Expert Comment

by:sdstuber
ID: 20465376
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
ID: 20465921
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 74

Expert Comment

by:sdstuber
ID: 20465998
what's the statement that gets that error?
0
 

Author Comment

by:barney75
ID: 20470620
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 74

Expert Comment

by:sdstuber
ID: 20471100
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
ID: 20471508
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 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 20471557
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
ID: 31410585
Thanks sdstube, working perfect... much appreciated!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

730 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