Skip to Content
0
Aug 17, 2023 at 04:28 PM

Joining the OACT table with the OACG

77 Views

Hello,

I am trying to pull the OACT table and the OACG table into one query. I need the source code from the OACG table to show on the same line as its corresponding account. When I run a query with the OACG table alone, the source code appears as I need it. Once I add the OACT table the source code is blank (see below).

SELECT

t1.segment_0 + '-' + t1.segment_1 as "GLacct",

t1.segment_1 as 'GL Store',

t1.AcctName as 'GL Description',

T2.[Source] as 'Fin Statement Type',

t1.currtotal as "Acct Balance"

FROM JDT1 T0 with (nolock)

Left JOIN OACT T1 with (nolock) ON T0.Account = T1.AcctCode

Left JOIN OACG T2 ON T1.[Category] = T2.[AbsId]

WHERE

T0.RefDate >= [%2]

AND T0.RefDate <= [%1] and

t1.segment_1 <> '020' and

t1.segment_1 <> '021' and

t1.segment_1 <> '026' and

t1.segment_1 <> '028' and

t1.segment_1 <> '039'and

t1.segment_1 <> '040' and

t1.segment_1 <> '042' and

t1.segment_1 <> '044' and

t1.segment_1 <> '047' and

t1.segment_1 <> '052'

Group by

t1.segment_0,

t1.segment_1,

t1.AcctName,

t1.currtotal,

T2.[Source]

Order by

t1.segment_0,

t1.segment_1

I tried Inner & left join but nothing works. If someone could please point me into the right direction I would greatly appreciate it.

Thank you,