Query with two tables one with many fields.

I have two tables. The first has the service failure information for an order ,SRVFAIL. it is linked to the table ORDER on ORODR# = SVORD#. This is a one to one relationship and works just fine with an inner join. The problem is when I join the comment table, SRVCOM, The way the table structure is, which I can not change, in SRVCOM there could be multiple comments for each order number and therefore there could be many rows. So when I join the tables I get many rows for each service failure but I only want one row for each service failures and include ALL of the comments. This is an Idea of what I have: SELECT ORODR#,SRVCODE,SRVCOM FROM ORDER INNER JOIN SRVFAIL ON ORODR# = SVORD# INNER JOIN SRVCOM ON SVCORD WHERE ORODR = '118111'.

Oh by the way this is a pass through query so the # are OK as it is DB2.
LVL 1
mona4980Asked:
Who is Participating?
 
momi_sabagCommented:
take a look here
http://www.db2ude.com/?q=node/114

you might use the xml agg function as well
0
 
momi_sabagCommented:
your best way around this will be to write a db2 user function which gets a svord number as a parameter and returns all the comments for that svord as a single string
0
 
HainKurtSr. System AnalystCommented:
please post a sample from your tables and based on this, post the result that you are trying to get
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mona4980Author Commented:
Results Now

ORODR#  SVCODE   SRVCOM
118111     WEATH     Driver late because
118111      WEATH    of snow on the road
118111     WEATH     and got stuck in ditch

Results wanted
ORODR#   SVCODE   SRVCOM
118111     WEATH    Driver late because of snow on the road and got stuck in ditch
0
 
HainKurtSr. System AnalystCommented:
you need a function that gets orodr and returns all comments concatenated...

ctreate function getComments(p_orodr varchar) returns varchar
begin
  open cursor c as select * from comments where orodr=p_orodr order by commentid;
  loop cursor until c.eof
    result = result + c.comment
  end loop
  return result;
end;

then your select will be simple as this:

select t.*, getComment(orodr) as Comments from ORDER

syntax may not be correct, but it shows how to do it using a stored function
0
 
mona4980Author Commented:
I will work on that. I am not sure how to pass a parameter using ODBC from MS ACESS to AS400. Any suggestions with that?
0
 
HainKurtSr. System AnalystCommented:
getComment(orodr) should be in DB2
and run this from access

select t.*, getComment(orodr) as Comments from ORDER

or better create a view in DB2 as

create view v_ORDER as
select t.*, getComment(orodr) as Comments from ORDER

then from access just run

select * from v_order
0
 
HainKurtSr. System AnalystCommented:
just missed alias t in all queries above

select o.*, getComment(o.orodr) as Comments from ORDER o

or

select t.*, getComment(t.orodr) as Comments from ORDER t



0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Mona,

You don't necessarily need a function to collect the comments, it can also be done with recursive SQL.  I'll be glad to help with this if you'd like.

A function will be easier for you to understand, at least initially.  But you'll need the authority to save and/or modify the function.  (Talk to your DBA.)  If that's a challenge in your shop, let's write a recursive solution.  You can do that with no more than permission to read the table(s).


Kent
0
 
momi_sabagCommented:
you can also use the xml function that is described in the link i posted
0
 
mona4980Author Commented:
Kent,
I am not sure if I have the permissions you refer to. So I would be open to your solution.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  

First check out the link that Momi posted.  It has several ways to do this.  (Method 1 is Momi's suggestion of XML, method 4 is the recursive solution.  Method's 2 and 3 use the user-defined function and are really the same solution -- they just use different languages to accomplish it.)

Also, can you post a description of your comments table?


Kent
0
 
mona4980Author Commented:
the comment table is SRVCOM. the fields are
SVCORD   CHAR  7
SVCRMK   CHAR  40
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ok.

If there are multiple comments for the same item, how do you know the order that will be used to assemble them?


0
 
mona4980Author Commented:
There in lies the problem. There is nothing that makes sense with the table structure. No sequence numbers or anything. Just an order number and multiple rows of comments.
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Then you'll have to accept that the comments get assembled in a potentially random order.  There is nothing in DB2 (or Oracle, MySQL, SQL Server, etc.) that says that the order that you take something out of the database is the same order that they were stored.  In fact, you could query the table twice and get a different order each time.  In practice, especially with small tables, every query after the first will generally give you the same order as they will be read from memory in page number order.

0
 
HainKurtSr. System AnalystCommented:
something like this with XML
SELECT ORODR#,SRVCODE, (
  SELECT replace(replace(xml2clob(xmlagg(xmlelement(c.SRVCOM a, word))),'<A>',''),'</A>',' ') 
  FROM SRVCOM as c 
  WHERE c.SVCORD = f.SVCORD
  ORDER BY ?????
) as Comments
 FROM ORDER as o 
      INNER JOIN SRVFAIL as f ON o.ORODR# = f.SVORD# 
WHERE ORODR = '118111'

Open in new window

0
 
mona4980Author Commented:
I appreciate all of your help. It appears as though I need to get more familiar with this type of programming before I continue on. I am not familiar with XML and how to get the info from AS400 nor do I know how to create and access saved procedures on AS400 either. it looks like I can learn quite a bit by looking at Q & A on this site. stay tuned. :-)
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.