Solved

Query with two tables one with many fields.

Posted on 2011-03-16
18
482 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
[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
  • 6
  • 5
  • 4
  • +1
18 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35149595
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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35149605
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
ID: 35149611
take a look here
http://www.db2ude.com/?q=node/114

you might use the xml agg function as well
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 1

Author Comment

by:mona4980
ID: 35149673
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 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 150 total points
ID: 35149744
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
ID: 35149819
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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35150035
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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35150044
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:Kent Olsen
Kent Olsen earned 200 total points
ID: 35150080
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
 
LVL 37

Expert Comment

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

Author Comment

by:mona4980
ID: 35150112
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:Kent Olsen
ID: 35150153
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
ID: 35150195
the comment table is SRVCOM. the fields are
SVCORD   CHAR  7
SVCRMK   CHAR  40
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 35150219
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
ID: 35150240
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:Kent Olsen
Kent Olsen earned 200 total points
ID: 35150286

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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35150764
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
ID: 35150886
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

Technology Partners: 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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…

739 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