cancel
Showing results for 
Search instead for 
Did you mean: 

Query for FatherNum NAME OACT to JDT1

GAP_C
Discoverer
0 Kudos

Hi, I'have created a Query to get a summary of the General Ledger but I would like to have a column with the father account Name, I have the Father account from OACT but I'm not able to make a "vlookup" backwards on the table to cross the FatherNumber with the AccountName to get the Fathers account name.

This is what I have

SELECT
T0."Account",
case
when left(T0."Account",1)='1' then '1-Activo'
when left(T0."Account",1)='2' then '2-Pasivo'
when left(T0."Account",1)='3' then '3-Patrimonio'
when left(T0."Account",1)='4' then '4-Ventas'
when left(T0."Account",1)='5' then '5-Costos'
when left(T0."Account",1)='6' then '6-Gastos'
when left(T0."Account",1)='7' then '6-Gastos'
when left(T0."Account",1)='8' then '6-Gastos'
when left(T0."Account",1)='9' then '7-Saldo Inicial'
else left(T0."Account",1) end as AccountCod,
T2."AcctName",
T2."Levels",
T1."Number",
T0."Debit",
T0."Credit",
Case
When left(T0."Account",1)<4 then (T0."Debit"-T0."Credit")
When left(T0."Account",1)>=4 then (T0."Credit"-T0."Debit")
End as Saldo,
T0."TransId",
T0."ContraAct",
T0."TaxDate",
T0."LicTradNum",
T0."ShortName",
T0."Ref3Line",
T0."LineMemo",
T1."Memo",
T1."FolioPref",
T1."FolioNum",
T2."FatherNum"
T3."AcctName" as FatherName <- NOT WORKING
FROM JDT1 T0
INNER JOIN OJDT T1 ON T0."TransId" = T1."TransId"
INNER JOIN OACT T2 ON T0."Account" = T2."AcctCode"
RIGHT JOIN OACT T3 ON T2."AcctCode" = T3."FatherNum" <- NOT WORKING

 

thanks in advance

Accepted Solutions (1)

Accepted Solutions (1)

GAP_C
Discoverer
0 Kudos

SELECT
T0."Account",
case
when left(T0."Account",1)='1' then '1-Activo'
when left(T0."Account",1)='2' then '2-Pasivo'
when left(T0."Account",1)='3' then '3-Patrimonio'
when left(T0."Account",1)='4' then '4-Ventas'
when left(T0."Account",1)='5' then '5-Costos'
when left(T0."Account",1)='6' then '6-Gastos'
when left(T0."Account",1)='7' then '6-Gastos'
when left(T0."Account",1)='8' then '6-Gastos'
when left(T0."Account",1)='9' then '7-Saldo Inicial'
else left(T0."Account",1) end as AccountCod,
T2."AcctName",
T2."Levels",
T1."Number",
T0."Debit",
T0."Credit",
Case
When left(T0."Account",1)<4 then (T0."Debit"-T0."Credit")
When left(T0."Account",1)>=4 then (T0."Credit"-T0."Debit")
End as Saldo,
T0."TransId",
T0."ContraAct",
T0."TaxDate",
T0."LicTradNum",
T0."ShortName",
T0."Ref3Line",
T0."LineMemo",
T1."Memo",
T1."FolioPref",
T1."FolioNum",
T2."FatherNum",
T3."AcctName" as FatherName
FROM JDT1 T0
INNER JOIN OJDT T1 ON T0."TransId" = T1."TransId"
INNER JOIN OACT T2 ON T0."Account" = T2."AcctCode"
JOIN OACT T3 ON T2."FatherNum" = T3."AcctCode"

Answers (0)