Ibm i View - may be creating Duplicate Records

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#                                
 and                                                
 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 > ''                                
)
Philky101Asked:
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.

Dave FordSoftware Developer / Database AdministratorCommented:
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
0

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
Dave FordSoftware Developer / Database AdministratorCommented:
It would help greatly if you posted a sample of the data your seeing .... plus a sample of what you expected to see.
0
MurpheyApplication ConsultantCommented:
Depending on the contents of the file and/or joined files,

If you need unique values, use the "Group By" clause.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

tliottaCommented:
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.
0
sameer2010Commented:
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.
0
Dave FordSoftware Developer / Database AdministratorCommented:
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
0
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.