Account Code and Name Query with Parent and Child in same Row

[RahF]

Hello Experts

I found an awesome query which I managed to modify to suit, but need it modified a bit more and I havent been able to resolve it, a bit out of my capacity and i am not sure if it can be achieved

So, what I need is the results to show the Parent part number and names to be repeated so that the parent code and name and in the same rows as the child code and names
I have attached two txt files one with the Actual results I get when I run the query, and one txt file which what is my expectation of the results when I run the query (for privacy reasons I have cleared the data from names columns)
Below is the query (i tried to upload the files in Excel format, but unable to do so, hence had to save as txt)

SELECT 
    TBL1.[GROUP],
    TBL1.[Main Account] AS [Main Account],
    TBL1.[Main Account Name] AS [Main Account Name],
    TBL1.[Sub Account] AS [Sub Account],
    TBL1.[Sub Account Name] AS [Sub Account Name],
    TBL1.[Detailed Account] AS [Detailed Account],
    TBL1.[Detailed Account Name] AS [Detailed Account Name],
    TBL1.[Segmented Account] AS [Segmented Account],
    TBL1.[Segmented Account Name] AS [Segmented Account Name],
    TBL1.[Sub-Segmented Account] AS [Sub-Segmented Account],
    TBL1.[Sub-Segmented Account Name] AS [Sub-Segmented Account Name]
FROM (
    SELECT 
        t0.groupmask, 
        t0.grpline, 
        t0.levels, 
        T0.[AcctCode] AS [Main Account], 
        T0.[AcctName] AS [Main Account Name],
        '-' AS [Sub Account], 
        '-' AS [Sub Account Name],
        '-' AS [Detailed Account],
        '-' AS [Detailed Account Name],
        T0.[Segment_0] + '-' + T0.[Segment_1] + '-' + T0.[Segment_2] AS [Segmented Account],
        '-' AS [Segmented Account Name],
        '-' AS [Sub-Segmented Account],
        '-' AS [Sub-Segmented Account Name],
        (CASE T0.[GroupMask] 
            WHEN 1 THEN 'Asset' 
            WHEN 2 THEN 'Liabilities' 
            WHEN 3 THEN 'Equity' 
            WHEN 4 THEN 'Revenue' 
            WHEN 5 THEN 'COGS' 
            WHEN 6 THEN 'Expenses' 
            WHEN 7 THEN 'Other Income & Expense' 
        END) AS [GROUP]
    FROM OACT T0 
    WHERE t0.Levels = '1'
    
    UNION ALL
    
    SELECT 
        t0.groupmask,
        t0.grpline,
        t0.levels,
        '-',
        '-',
        T0.[AcctCode] AS [Sub Account], 
        T0.[AcctName] AS [Sub Account Name],
        '-' AS [Detailed Account],
        '-' AS [Detailed Account Name],
        '-' AS [Segmented Account],
        '-' AS [Segmented Account Name],
        '-' AS [Sub-Segmented Account],
        '-' AS [Sub-Segmented Account Name],
        (CASE T0.[GroupMask] 
            WHEN 1 THEN 'Asset' 
            WHEN 2 THEN 'Liabilities' 
            WHEN 3 THEN 'Equity' 
            WHEN 4 THEN 'Revenue' 
            WHEN 5 THEN 'COGS' 
            WHEN 6 THEN 'Expenses' 
            WHEN 7 THEN 'Other Income & Expense' 
        END) AS [GROUP]
    FROM OACT T0 
    WHERE t0.Levels = '2'
    
    UNION ALL
    
    SELECT 
        t0.groupmask,
        t0.grpline,
        t0.levels,
        '-',
        '-',
        '-' AS [Sub Account],
        '-' AS [Sub Account Name],
        T0.[AcctCode] AS [Detailed Account], 
        T0.[AcctName] AS [Detailed Account Name],
        '-' AS [Segmented Account],
        '-' AS [Segmented Account Name],
        '-' AS [Sub-Segmented Account],
        '-' AS [Sub-Segmented Account Name],
        (CASE T0.[GroupMask] 
            WHEN 1 THEN 'Asset' 
            WHEN 2 THEN 'Liabilities' 
            WHEN 3 THEN 'Equity' 
            WHEN 4 THEN 'Revenue' 
            WHEN 5 THEN 'COGS' 
            WHEN 6 THEN 'Expenses' 
            WHEN 7 THEN 'Other Income & Expense' 
        END) AS [GROUP]
    FROM OACT T0 
    WHERE t0.Levels = '3'
    
    UNION ALL
    
    SELECT 
        t0.groupmask,
        t0.grpline,
        t0.levels,
        '-',
        '-',
        '-' AS [Sub Account],
        '-' AS [Sub Account Name],
        '-' AS [Detailed Account],
        '-' AS [Detailed Account Name],
        T0.[Segment_0] + '-' + T0.[Segment_1] + '-' + T0.[Segment_2] AS [Segmented Account],
        T0.[AcctName] AS [Segmented Account Name],
        '-' AS [Sub-Segmented Account],
        '-' AS [Sub-Segmented Account Name],
        (CASE T0.[GroupMask] 
            WHEN 1 THEN 'Asset' 
            WHEN 2 THEN 'Liabilities' 
            WHEN 3 THEN 'Equity' 
            WHEN 4 THEN 'Revenue' 
            WHEN 5 THEN 'COGS' 
            WHEN 6 THEN 'Expenses' 
            WHEN 7 THEN 'Other Income & Expense' 
        END) AS [GROUP]
    FROM OACT T0 
    WHERE t0.Levels = '4'
    
    UNION ALL
    
    SELECT 
        t0.groupmask,
        t0.grpline,
        t0.levels,
        '-',
        '-',
        '-' AS [Sub Account],
        '-' AS [Sub Account Name],
        '-' AS [Detailed Account],
        '-' AS [Detailed Account Name],
        '-' AS [Segmented Account],
        '-' AS [Segmented Account Name],
        T0.[Segment_0] + '-' + T0.[Segment_1] + '-' + T0.[Segment_2] AS [Sub-Segmented Account], 
        T0.[AcctName] AS [Sub-Segmented Account Name],
        (CASE T0.[GroupMask] 
            WHEN 1 THEN 'Asset' 
            WHEN 2 THEN 'Liabilities' 
            WHEN 3 THEN 'Equity' 
            WHEN 4 THEN 'Revenue' 
            WHEN 5 THEN 'COGS' 
            WHEN 6 THEN 'Expenses' 
            WHEN 7 THEN 'Other Income & Expense' 
        END) AS [GROUP]
    FROM OACT T0 
    WHERE t0.Levels = '5'
) TBL1
ORDER BY
    TBL1.groupmask,
    TBL1.grpline,
    TBL1.levels

Regards
Rah

[Johan_Hakkesteegt]

Hi Rah, could you please add a screenshot showing a part of your Chart of Accounts, that shows the structure? Just to visualize what your expected result is based on. Regards, Johan

[RahF]

Hi Johan
Please see below screenshot

[Johan_Hakkesteegt · Accepted solution]

Hi Rah,

Here is a query that should give you the expected result:

select L1.[GroupMask]
      ,(CASE L1.[GroupMask] 
            WHEN 1 THEN 'Asset' 
            WHEN 2 THEN 'Liabilities' 
            WHEN 3 THEN 'Equity' 
            WHEN 4 THEN 'Revenue' 
            WHEN 5 THEN 'COGS' 
            WHEN 6 THEN 'Expenses' 
            WHEN 7 THEN 'Other Income & Expense' 
        END) AS [GROUP]
	   ,L1.AcctCode AS [Main account]
	   ,L1.AcctName AS [Main account name]
	   ,L2.AcctCode AS [Sub account]
	   ,L2.AcctName AS [Sub account name]
	   ,L3.AcctCode AS [Detailed account]
	   ,L3.AcctName AS [Detailed account name]
from (SELECT * FROM OACT WHERE Levels = '3') L3
     LEFT OUTER JOIN (SELECT * FROM OACT WHERE Levels = '2') L2 ON L3.FatherNum = L2.AcctCode
	 LEFT OUTER JOIN (SELECT * FROM OACT WHERE Levels = '1') L1 ON L2.FatherNum = L1.AcctCode
order by 1, 3, 5, 7

Please note that I have assumed a maximum of 3 levels, based on your screenshot and expected result text file. If you need more levels, you can add those by editing the SELECT and FROM clauses. I have given the tables aliases according to the level. I.e. L3 is level 3 accounts, L2 is level 2 accounts, etc.
If you want more levels, in the FROM clause, just start with the lowest level, and add subsequently higher levels in order from lowest to highest. In the SELECT clause you add the account codes and names from highest to lowest.

Regards,

Johan

[Johan_Hakkesteegt · Accepted solution]

N.B. the GROUP column has hard-coded names, with 7 groups. Your Chart of Accounts may have more or less groups, and the names of these groups in the query may not correspond with the ones in your B1 system..

[RahF · Accepted solution]

Perfect, I can modify to suit the levels we have.
Thanks a lot @Johan_Hakkesteegt 

[RahF]

Hi Johan

Please see below