Solved

Query with two tables one with many fields.

Posted on 2011-03-16
18
478 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:mona4980
  • 6
  • 5
  • 4
  • +1
18 Comments
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
please post a sample from your tables and based on this, post the result that you are trying to get
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 150 total points
Comment Utility
take a look here
http://www.db2ude.com/?q=node/114

you might use the xml agg function as well
0
 
LVL 1

Author Comment

by:mona4980
Comment Utility
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
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 150 total points
Comment Utility
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
 
LVL 1

Author Comment

by:mona4980
Comment Utility
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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 200 total points
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
you can also use the xml function that is described in the link i posted
0
 
LVL 1

Author Comment

by:mona4980
Comment Utility
Kent,
I am not sure if I have the permissions you refer to. So I would be open to your solution.
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
 
LVL 1

Author Comment

by:mona4980
Comment Utility
the comment table is SRVCOM. the fields are
SVCORD   CHAR  7
SVCRMK   CHAR  40
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Ok.

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


0
 
LVL 1

Author Comment

by:mona4980
Comment Utility
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
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 200 total points
Comment Utility

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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
 
LVL 1

Author Closing Comment

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

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

Title # Comments Views Activity
Data quality checks 2 30
Determine next b-weekly date 12 52
Do we need servers??? 5 109
Insert with SET how to handle join 6 25
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

13 Experts available now in Live!

Get 1:1 Help Now