I am running the query below on a SQL 2005 server and connect to an Oracle database via linked server. I get the following error:
Msg 170, Level 15, State 1, Line 62
Line 62: Incorrect syntax near ‘)’.
select
a.segment2,
a.segment3,
a.segment5,
a.PeriodMovement , b.MANUAL_JOURNAL_TOT
from (
select glc.segment2+glc.segment5 key1,
glc.segment2,
glc.segment3,
glc.segment5,
sum(ISNULL(accounted_dr,0)-ISNULL(accounted_cr,0)) as PeriodMovement
from XLA.XLA_AE_LINES l,
XLA.XLA_AE_LINE_ACS acs,
GL.GL_CODE_COMBINATIONS glc
where l.ae_header_id = acs.ae_header_id
and l.accounting_date <= CONVERT(date, GETDATE-1, 'DD MON YYYY')
and l.gl_transfer_mode_code = 'S'
and l.code_combination_id = glc.code_combination_id
and l.ae_line_num = acs.ae_line_num
and acs.analytical_criterion_code ='CONTNOJNLTYPE'
--and glc.segment5 = 'IIFS1921'
and glc.account_type IN ('L','A','S')
--and glc.segment2 not in (390000, 950000,980000)
group by
glc.segment2,
glc.segment5,glc.segment3
-- ,acs.ac1
UNION ALL
select glc.segment2+glc.segment5,
glc.segment2,
glc.segment3,
glc.segment5,
sum(ISNULL(accounted_dr,0)-ISNULL(accounted_cr,0))
from XLA.XLA_AE_LINES l,
XLA.XLA_AE_LINE_ACS acs,
GL.GL_CODE_COMBINATIONS glc
where l.ae_header_id = acs.ae_header_id
and l.accounting_date between '01-JUL-2009' and CONVERT(date, GETDATE-1, 'DD MON YYYY')
and l.gl_transfer_mode_code = 'S'
and l.code_combination_id = glc.code_combination_id
and l.ae_line_num = acs.ae_line_num
and acs.analytical_criterion_code ='CONTNOJNLTYPE'
--and glc.segment5 = 'IIFS1921'
and glc.account_type IN ('R','E')
--and glc.segment2 not in (390000, 950000,980000)
group by
glc.segment2,
glc.segment5,glc.segment3
-- ,acs.ac1 )
) a,
(
select ADJ_ACCOUNT+ADJ_BUSINESS_UNIT key2,
ADJ_BUSINESS_UNIT,
ADJ_ACCOUNT, SUM(ADJ_JNL_AMOUNT) as MANUAL_JOURNAL_TOT
FROM XXUK.XXXLA_MANUAL_JNL_ADJ_EVENTS
WHERE
ADJ_ACCOUNT NOT LIKE '000%'
and ADJ_APPROVED_STATUS = 'Y'
GROUP BY
ADJ_BUSINESS_UNIT,
ADJ_ACCOUNT) b
where a.key1 = b.key2(+)