sql - concatenate a subselect with multiple values?

Hi!

I have this select:

select
                e.fid,e.feature_id,e.feature,e.fsize,e.ipbase,
                e.ipvoice,e.advsecurityk9,e.spservicesk9,
                e.advipservicesk9,e.entbase,e.entservicesk9,
                e.adventerprisek9,e.comments
        from
                refprd6 e
        where
                feature != 'zzz name not found'

I want to add a return column to the above that contains the concatenated results, seperated by a pipe (|), of:

select
       plat
from
       refplats6
WHERE
       fid = (e.fid from 1st statement above) order by plat

Thanks!
LVL 2
ventolinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DanielztCommented:
try this:

select
               e.fid,e.feature_id,e.feature,e.fsize,e.ipbase,
               e.ipvoice,e.advsecurityk9,e.spservicesk9,
               e.advipservicesk9,e.entbase,e.entservicesk9,
               e.adventerprisek9,e.comments,
               (select plat from   refplats6
                 WHERE  fid = e.fid )
       from
               refprd6 e
       where
               feature != 'zzz name not found'

if for each e.fid, there are more than one fid:

select
               e.fid,e.feature_id,e.feature,e.fsize,e.ipbase,
               e.ipvoice,e.advsecurityk9,e.spservicesk9,
               e.advipservicesk9,e.entbase,e.entservicesk9,
               e.adventerprisek9,e.comments,
               f.plat
       from
               refprd6 e , refplats6 f
       where
               e.feature != 'zzz name not found' and e.fid = f.fid
      order by f.plat


good luck!

   

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ventolinAuthor Commented:
Hi, The first one is closer to what I want, but it fails as the subselect returns more than one value. What I want is for these multiple values be concatenated into one string and returns.
chetankrCommented:
You could write a PL/SQL function that does the concatenation for you.

CREATE OR REPLACE FUNCTION func1( id IN refplats6.fid%TYPE)
RETURN VARCHAR2
IS
DECLARE
  c1_rec  refplats6.plat%TYPE;
  l_retval VARCHAR2(1000); -- change this 1000 according to your needs
  CURSOR c1 IS
      SELECT plat FROM refplats6 WHERE fid = id;
BEGIN
  l_retval := '';
  FOR c1_rec IN c1 LOOP
      /* concatenate here*/
      l_retval := l_retval + c1_rec + '|' ;
   END LOOP;
    result := l_retval;
END;

And run you query as :

select
               e.fid,e.feature_id,e.feature,e.fsize,e.ipbase,
               e.ipvoice,e.advsecurityk9,e.spservicesk9,
               e.advipservicesk9,e.entbase,e.entservicesk9,
               e.adventerprisek9,e.comments,
               func1(e.fid)
       from
               refprd6 e
       where
               feature != 'zzz name not found'

Please spare me if there are syntax errors. I dont have access to Oracle now
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

fwphilbCommented:
Can't think of a way of doing this in a single query. Is there a reason why you don't want to use a database function? There maybe other ways (if so would be interested in the solution), but I'd probably use a function and then reference this function in any required selects.

For example:

CREATE OR REPLACE FUNCTION Plat_Function(pk_fid NUMBER) RETURN VARCHAR2 AS

  CURSOR plat_cur IS SELECT plat
                     FROM   refplat6
                     WHERE  fid = pk_fid
                     ORDER BY plat;

  p_output                        VARCHAR2(32767);
  p_join                          VARCHAR2(1);
BEGIN
  FOR plat_rec IN plat_cur LOOP
    p_output := p_output||p_join||plat_rec.plat;
    p_join := '|';
  END LOOP;

  RETURN p_output;

END Plat_Function;
/

could be used in your original query via:

select
               plat_function(e.fid),e.fid,e.feature_id,e.feature,e.fsize,e.ipbase,
               e.ipvoice,e.advsecurityk9,e.spservicesk9,
               e.advipservicesk9,e.entbase,e.entservicesk9,
               e.adventerprisek9,e.comments
       from
               refprd6 e
       where
               feature != 'zzz name not found'

Hope this helps,

Phil
ventolinAuthor Commented:
Hi Phil,

This looks good, but doesn't compile with a missing keyword error. I don't know much about PL/SQL and would love help getting this function to work. Thanks!
Bigfam5Commented:
The only thing that might cause a problem is the parameter definition.

Just try

CREATE OR REPLACE FUNCTION Plat_Function
        (pk_fid IN NUMBER)      RETURN  VARCHAR2 AS

 p_output     VARCHAR2(32767);
 p_join       VARCHAR2(1);
BEGIN
 FOR plat_rec IN
            (SELECT plat FROM   refplat6
                WHERE  fid = pk_fid ORDER BY plat) LOOP
   p_output := p_output||p_join||plat_rec.plat;
   p_join := '|';
 END LOOP;
 RETURN p_output;
END Plat_Function;
/


ventolinAuthor Commented:
Hi, Using the above, I get this on show errors:

LINE/COL ERROR
-------- --------------------------------------------------------
8/14     PL/SQL: SQL Statement ignored
8/33     PLS-00201: identifier 'REFPLAT6' must be declared
10/3     PL/SQL: Statement ignored
10/33    PLS-00364: loop index variable 'PLAT_REC' use is invalid
fwphilbCommented:
Hi,

Bigfam5's parameter correction should do the business, looks like a typo on my part.

The error you are receiving is probably because reflat6 is not recognised as a table, does it exist in your schema?

Phil
ventolinAuthor Commented:
Ah...  a typo. It works now. Who should I give the points to? Is there any way to make this run faster?
fwphilbCommented:
Hi,

The code itself isn't doing too much, so not a huge room for improvement in this area. Your best bet would be to add an index to the refplats6 table on the fid column such as:

CREATE INDEX refplats6_i1 ON refplats6 (fid);

and a further index on the refprd6 table might also help such as:

CREATE INDEX refprd6_i1 ON refprd6 (feature);

Leave the point allocation decision down to yourself, but believe there is a split option which might be useful in this situation.

All the best,

Phil
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.