2

Query for FatherNum NAME OACT to JDT1

 7 months ago
source link: https://community.sap.com/t5/financial-management-q-a/query-for-fathernum-name-oact-to-jdt1/qaq-p/13608707
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Query for FatherNum NAME OACT to JDT1

GAP_C

Discoverer

Saturday

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


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK