Ibm i View - may be creating Duplicate Records

Posted on 2013-09-10
Medium Priority
Last Modified: 2014-01-14
I am seeing what seems to be duplicate records from this view. Do you think this View is causing duplicate records?

create view astccdta.acaudia AS                      
( select d.IARCC9,b.ItQty#,a.IhDocd,b.ItPRT#        
       from astdta.OEINHDIH a                        
 join astdta.ICDETLIT     b on                      
 b.Itcom# = a.IhCom#                                
 b.ItTrn# = a.IhIdc#                                
 left join astdta.ICECTLCT      c on                
 c.CtCom#= b.ItCom# and                              
 c.CtCtr# = ' ' and                                  
 c.CtPrt# =b.ItPrt#                                  
left join astdta.Audia d                        
on d.IAprt# = b.ItPrt#                          
where b.ItTscc = 'I'   and  a.IHDOCD > 20120105  
and d.IARCC9 > ''                                
Question by:Philky101
LVL 18

Accepted Solution

Dave Ford earned 500 total points
ID: 39481631
What does the data look like when you run the SELECT all by itself (without the view) ?

I suspect the culprit lies in your joins, but you didn't post any sample data, so it's pretty much impossible for us to say.

-- DaveSlash
LVL 18

Expert Comment

by:Dave Ford
ID: 39481632
It would help greatly if you posted a sample of the data your seeing .... plus a sample of what you expected to see.
LVL 17

Assisted Solution

Murphey earned 500 total points
ID: 39489398
Depending on the contents of the file and/or joined files,

If you need unique values, use the "Group By" clause.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

LVL 27

Assisted Solution

tliotta earned 500 total points
ID: 39489955
There's no reason for us to think that "duplicate" rows wouldn't appear in the output from your SELECT statement. We don't know your data, so rows that appear to be "duplicate" seem perfectly possible.

Is there a purpose for including ICECTLCT in the JOINs? No data from it is used.
LVL 13

Assisted Solution

sameer2010 earned 500 total points
ID: 39529522
Did you mean INNER JOIN in place of LEFT on ICECTLCT?
If you meant to use it as existence check then you better write EXISTS query.
If other tables like astdta.OEINHDIH, astdta.ICDETLIT have multiple rows for your combination of join, then you can get multiple rows.
LVL 18

Expert Comment

by:Dave Ford
ID: 39598045
Philky101, it's been a month and a half since you asked this question, and it's been about a month since the last response.

Is this still an issue? Did you get your problem resolved? If so, how?

-- DaveSlash

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Determining the an SCCM package name from the Package ID
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

624 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