Without sample data it's almost impossible for us to give you a definitive answer but here are some techniques for you to try
1.Try returning just a few of the columns from
[SEI_LIVE].[GL_TX_WITH_OB_VIEW_SEP_AUG]
E.g.
SELECT distinct
GL.LEDTYP_0 AS 'Ledger Type',
GL.LED_0 AS 'Ledger_Code',
GL.FIY_0 AS Fiscal_Year,
GL.CPY_0 AS 'Company_Code',
GL.PER_0 AS PERIOD
FROM
[SEI_LIVE].[GL_TX_WITH_OB_VIEW_SEP_AUG] GL
ORDER BY GL.LEDTYP_0
That will presumably return the expected number of rows
2. Then try adding just the first JOIN
LEFT JOIN
COMPANY Company
ON GL.CPY_0 = Company.CPY_0
and see what that does to the row count. Add the joins in one-by one until you start getting the extra rows.
3. Now you need to examine the data. Are you missing something in the
ON
Clause that is causing the extra rows? Is the data duplicated on that table and needs fixing? Etc Etc. Without your data I have to be a bit vague here - sorry.
If you are not comfortable with sub-queries try
Common Table Expressions[
^] which might be easier to follow. For example that point 1 above could become
;with cte as
(
SELECT
GL.LEDTYP_0 AS 'Ledger Type',
GL.LED_0 AS 'Ledger_Code',
GL.FIY_0 AS Fiscal_Year,
GL.CPY_0 AS 'Company_Code',
GL.PER_0 AS PERIOD
FROM
[SEI_LIVE].[GL_TX_WITH_OB_VIEW_SEP_AUG] GL
) select * from cte
ORDER BY 'Ledger Type'
If you follow the steps I've suggested above, then you could introduce ROW_NUMBER() based on an appropriate PARTITION BY inside the cte. then use
select * from cte where [row number] = 1;