Click here to Skip to main content
14,980,646 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am getting more rows then I expect returned by my query.

I believe it has something to do with my join statements.

There are multiple tables that have different information in them from SAGE X3 System

SQL
SELECT distinct
    GL.LEDTYP_0 AS 'Ledger Type',
    APL.LANMES_0 AS 'Ledger_Type_Description',
    GL.LED_0 AS 'Ledger_Code',
    A9.TEXTE_0 AS 'Ledger_Code_Description',
    GL.COA_0 AS 'Chart_Of_account_Code',
    A10.TEXTE_0 AS 'Chart_Acct_Description',
    Company.CPYNAM_0 AS 'Company_Description',
    GL.FIY_0 AS Fiscal_Year,
    GL.CPY_0 AS 'Company_Code',
    GL.PER_0 AS PERIOD,
    
	GL.OFFACC_0 AS 'Partner_Code',
	Partner.BPRNAM_0 AS 'Partner_Description',
	GL.ACC_0 AS 'Account Code', 
	A11.TEXTE_0 AS 'Account Description',
	GL.NUM_0 AS 'Document_Number', 
	GL.DES_0 AS 'Document Description',
	GL.CCE_0 'Value Stream', 
	A.TEXTE_0 AS 'Value Stream Name',
	GL.CCE_1 AS 'Product_Line',
	A1.TEXTE_0 AS 'Product_Line_Name',
	GL.CCE_2 AS 'CropYear',
	A2.TEXTE_0 AS 'CropYear Name',
	GL.CCE_3 AS Project,
	A3.TEXTE_0 AS 'ProjectName',
	GL.CCE_4 AS Department,
	A4.TEXTE_0 AS Department_Name,
	GL.ACCDAT_0 AS AccountingDate, 
	Format(GL.PSTLED_0, 'C') AS 'Ledger_Posting',
	
	GL.CAT_0 AS 'Journal_Category',
	GL.NEWFIYNUM_0 AS 'NewFiscalYear',
	GL.NEWPERNUM_0 AS 'NewFiscalPeriod'
	
FROM
    [SEI_LIVE].[GL_TX_WITH_OB_VIEW_SEP_AUG] GL 
    LEFT JOIN
        COMPANY Company 
        ON GL.CPY_0 = Company.CPY_0 
    LEFT JOIN
        GACCOUNT GA 
        ON GL.ACC_0 = GA.ACC_0 
    LEFT JOIN
        PINVOICED INVOICED 
        ON GL.NUM_0 = INVOICED.NUM_0 
    LEFT JOIN
        APLSTD APL 
        ON GL.LEDTYP_0 = APL.LANNUM_0 
        AND APL.LANCHP_0 = '2644' 
        AND LAN_0 = 'ENG' 
    LEFT JOIN
        BPARTNER Partner 
        ON GL.OFFACC_0 = Partner.BPRNUM_0 
    LEFT JOIN
        ATEXTRA A10 
        ON GL.COA_0 = A10.IDENT1_0 
        AND A10.CODFIC_0 = 'GCOA' 
        AND A10.ZONE_0 = 'DESTRA' 
        AND A10.LANGUE_0 = 'ENG' 
    LEFT JOIN
        ATEXTRA A11 
        ON GL.COA_0 = A11.IDENT1_0 
        AND GL.ACC_0 = A11.IDENT2_0 
        AND A11.CODFIC_0 = 'GACCOUNT' 
        AND A11.ZONE_0 = 'DESTRA' 
        AND A11.LANGUE_0 = 'ENG' 
    LEFT JOIN
        ATEXTRA A 
        ON GL.DIE_0 = A.IDENT1_0 
        AND GL.CCE_0 = A.IDENT2_0 
        AND A.CODFIC_0 = 'CACCE' 
        AND A.ZONE_0 = 'DESTRA' 
        AND A.LANGUE_0 = 'ENG' 
    LEFT JOIN
        ATEXTRA A1 
        ON GL.DIE_1 = A1.IDENT1_0 
        AND GL.CCE_1 = A1.IDENT2_0 
        AND A1.CODFIC_0 = 'CACCE' 
        AND A1.ZONE_0 = 'DESTRA' 
        AND A1.LANGUE_0 = 'ENG' 
    LEFT JOIN
        ATEXTRA A2 
        ON GL.DIE_2 = A2.IDENT1_0 
        AND GL.CCE_2 = A2.IDENT2_0 
        AND A2.CODFIC_0 = 'CACCE' 
        AND A2.ZONE_0 = 'DESTRA' 
        AND A2.LANGUE_0 = 'ENG' 
    LEFT JOIN
        ATEXTRA A3 
        ON GL.DIE_3 = A1.IDENT1_0 
        AND GL.CCE_3 = A1.IDENT2_0 
        AND A3.CODFIC_0 = 'CACCE' 
        AND A3.ZONE_0 = 'DESTRA' 
        AND A3.LANGUE_0 = 'ENG' 
    LEFT JOIN
        ATEXTRA A4 
        ON GL.DIE_4 = A4.IDENT1_0 
        AND GL.CCE_4 = A4.IDENT2_0 
        AND A4.CODFIC_0 = 'CACCE' 
        AND A4.ZONE_0 = 'DESTRA' 
        AND A4.LANGUE_0 = 'ENG'
	
    LEFT JOIN
        ATEXTRA A9 
        ON GL.LED_0 = A9.IDENT1_0 
        AND A9.CODFIC_0 = 'GLED' 
        AND A9.ZONE_0 = 'DESTRA' 
        AND A9.LANGUE_0 = 'ENG' 
WHERE
    ---GL.ACC_0 = '17000' 
     YEAR(GL.ACCDAT_0) > 2019


What I have tried:

I have tried Row number technique I not good at subqueries at all
Posted
Updated 14-Jun-21 5:06am

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.
SQL
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
SQL
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
SQL
;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
SQL
select * from cte where [row number] = 1;
   
Comments
Wendelius 14-Jun-21 11:08am
   
Nice instructions!
Jörgen Andersson 21-Jun-21 5:31am
   
VG
Without seeing the actual data you have and the result along with the expected it's quite impossible to say what is wrong with the query, or the data.

However, since you have already used DISTINCT you won't have duplicates in the result. So the next step would be to investigate the data you receive and see what is different between the rows in a set where you would expect only one row. This helps you to identify which join produces unwanted rows.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900