oracle sql query to mssql query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

oracle sql query to mssql query

Author

Topic

vishal.shah
Starting Member

4 Posts

Posted - 2014-06-13 : 07:19:08

Hi there,
we need to extract data from oracle to mssql. we have created a view in mssql and need to populate that view with data from oracle. the mssql and oracle servers have been linked. originally we never had mssql and just used oracle as a platform but our new software uses mssql hence we need to bring in data to mssql from dbs on oracle server that was used by previous program.

i need to modify the query below so it runs successfully on mssql. any ideas? currently it keeps on giving me errors saying incorrect syntax near ')'. i have changed the query as much as i can i.e changing NVL to ISNULL etc but am now hitting a wall interms of progress..please assist. thank you

SELECT
v.RKCONO, v.RKDIVI,
v.RKBAKY, v.RKBIT1,
v.RKBIT2, ISNULL (d3.EAAITM, '000') AS DIM3,
p.HierarchyIdentity2 AS DIM4,
v.RVPERI AS PERIOD,
v.RVYEA4, SUM (v.RVACAD + v.RVACAC) AS BALANCE
FROM tagetik.tagetic_query v,
COGNOS.PRODUCT_HIERARCHY4 p,
(SELECT EAAITM
FROM MVXJDTA.FCHACC
WHERE EAAITP = 3) d3,
(SELECT EAAITM
FROM MVXJDTA.FCHACC
WHERE EAAITP = 4) d4,
WHERE v.RKBAKY = 44
AND d3.EAAITM(+) = v.RKBIT3
AND d4.EAAITM(+) = v.RKBIT4
AND p.ItemGroup(+) = ISNULL (d4.EAAITM, 'UKN')
GROUP BY v.RKCONO, v.RKDIVI, v.RKBAKY,
v.RKBIT1, v.RKBIT2, v.RVPERI, ISNULL (d3.EAAITM, '000'),
p.HierarchyIdentity2, v.RVYEA4;

Vishal Shah

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-13 : 07:54:50

Making some assumptions about the table schemas and understanding that Oracle uses (+) to simulate a left join:


SELECT
	v.RKCONO, v.RKDIVI,
	v.RKBAKY, v.RKBIT1,
	v.RKBIT2, ISNULL (d3.EAAITM, '000') AS DIM3,
	p.HierarchyIdentity2 AS DIM4,
	v.RVPERI AS PERIOD,
	v.RVYEA4, SUM (v.RVACAD + v.RVACAC) AS BALANCE
FROM tagetik.tagetic_query v

left join	(SELECT EAAITM
	FROM MVXJDTA.FCHACC
	WHERE EAAITP = 3) d3
on d3.EAAITM = v.RKBIT3
left join (SELECT EAAITM
	FROM MVXJDTA.FCHACC
	WHERE EAAITP = 4) d4
on d4.EAAITM = v.RKBIT4
left join	COGNOS.PRODUCT_HIERARCHY4 p
on p.ItemGroup = ISNULL (d4.EAAITM, 'UKN')

WHERE v.RKBAKY = 44
--AND d3.EAAITM(+) = v.RKBIT3
--AND d4.EAAITM(+) = v.RKBIT4
--AND p.ItemGroup(+) = ISNULL (d4.EAAITM, 'UKN')
GROUP BY v.RKCONO, v.RKDIVI, v.RKBAKY,
v.RKBIT1, v.RKBIT2, v.RVPERI, ISNULL (d3.EAAITM, '000'),
p.HierarchyIdentity2, v.RVYEA4;

vishal.shah
Starting Member

4 Posts

Posted - 2014-06-13 : 11:18:12

Hi,

Thank you so much for your assistance. I have modified this slightly to match the names with those in oracle and it works fine!!!

Vishal Shah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources