November 15, 2011 at 11:37 pm
Dear Friends,
I am having a oralce select statement like:
SELECT count(*)
FROM employee_master e,
client_master cl,
city_master c,
state_master s,
branch_master b
WHERE cl.city_id = c.city_id(+)
AND s.state_id(+) = c.state_id
AND b.branch_code = cl.sourceid
AND e.rm_code = cl.rm_code
Output Returns:923274 row count
Now I need to convert this statement to SQL I Use like
SELECT count(*)
FROM employee_master@orcl2.bajajcapital.com e,
client_master cl left outer join
city_master c on cl.city_id=c.city_id
join
state_master s on s.state_id = c.state_id,
branch_master b
WHERE
b.branch_code = cl.sourceid
AND e.rm_code = cl.rm_code
Output Returns:907192 Row Count
That shows different result from oracle result.I am not getting where is logical error in my sql select statment.
So Plz help me and send a SQL Script equivalent to following query:
SELECT count(*)
FROM employee_master e,
client_master cl,
city_master c,
state_master s,
branch_master b
WHERE cl.city_id = c.city_id(+)
AND s.state_id(+) = c.state_id
AND b.branch_code = cl.sourceid
AND e.rm_code = cl.rm_code
Thanks!!
November 16, 2011 at 11:19 am
Any reason not to run the traditional query syntax on SQL Server side?
What happens if you do that?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 17, 2011 at 7:25 am
Try
X.abc=Y.abc(+) (side with nulls) => X LEFT JOIN Y ON X.ABC=Y.ABC
X.abc(+)=Y.abc => X RIGHT JOIN Y ON X.ABC.Y.ABC
SELECT count(*)
FROM employee_master e
INNER JOIN client_master cl
ON e.rm_code=cl.rm_code
INNER JOIN branch_master b
ON cl.sourceid=b.branch_code
left join city_master c
ON cl.city_id=c.city_id
LEFT join state_master s
ON c.state_id=s.state_id
November 17, 2011 at 10:09 pm
Your query is perfect and I got the perfect result..
But How I am not getting:
You have written that
X.abc=Y.abc(+) (side with nulls) => X LEFT JOIN Y ON X.ABC=Y.ABC
X.abc(+)=Y.abc => X RIGHT JOIN Y ON X.ABC=Y.ABC
my query is:
SELECT count(*)
FROM employee_master e,
client_master cl,
city_master c,
state_master s,
branch_master b
WHERE cl.city_id = c.city_id(+)--=>there should be a LEFT JOIN
AND s.state_id(+) = c.state_id--=>There should be a RIGHT JOIN
AND b.branch_code = cl.sourceid
AND e.rm_code = cl.rm_code
And according to me there should be one left join and one right join but you have write only left join in both cases.so plz explain it in details.
Thanks a lot:-)
November 18, 2011 at 5:40 am
I've converted the right-join in a left join (as they are rewritable),didn't feel like reordening my whole query.
Easiest is to remember (+) means: if not found add a null record.
Doublecheck the outcome when rewriting old syntax to ansi syntax.
In addition to the left/right join filter (on...) you might also need to check for nulls in the WHERE-clause.
Comment in code.
SELECT count(*)
FROM employee_master e
INNER JOIN client_master cl
ON e.rm_code=cl.rm_code /*where e.rm_code=cl.rm_code: must match->inner join */
INNER JOIN branch_master b /*where cl.sourceid=b.branch_code: must match->inner join */
ON cl.sourceid=b.branch_code
left join city_master c /*where cl.city_id = c.city_id(+): show c.city_id if possible, otherwise null*/
ON cl.city_id=c.city_id
LEFT join state_master s /* where s.state_id(+) = c.state_id: show s.state_id if possible otherwise null; have the city table on the left (left join:show left table at least once) and state table to the right (show if possible)*/
ON c.state_id=s.state_id
with right join (have to check this one)
SELECT count(*)
FROM employee_master e
INNER JOIN client_master cl ON e.rm_code=cl.rm_code
INNER JOIN branch_master b ON cl.sourceid=b.branch_code
left join
(select c.city_id from state_master s right join /*show right table atleast once*/ city_master c on s.state_id=c.state_id) cities
on cl.city_id=cities.city_id
November 18, 2011 at 6:47 am
Did you play around with the Microsoft Migration Assistant for Oracle ( to sqlserver ) ?
Always nice to give it a try .... this is free MS software 🙂
Have a look at http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16742
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 18, 2011 at 10:39 pm
Hello Dear,
I am using tool SSMA But it takes a lots of time and consuming lots of space
in comparision to oracle.is there any other tool to migrate our oracle data to
Sql server R2.
Thanks!!
November 18, 2011 at 10:40 pm
Thanks m allmost satisfied with you..:-)
November 19, 2011 at 2:22 am
I don't get the combination of your previous two replies :crazy:
As long as I have enough space, I don't care about it during any migration.
It may indeed consume your resources. For migration purposes, I think that is allowed.
Only after having performed everything needed after migration (shrink, update stats and rebuild of all indexes) then I would compare space usage,if that is of any concern.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 30, 2012 at 1:16 pm
the (+) operator in an Oracle join condition should be translated into an outer join
February 24, 2012 at 12:15 am
-
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 24, 2012 at 10:38 am
ALZDBA (11/18/2011)
Did you play around with the Microsoft Migration Assistant for Oracle ( to sqlserver ) ?
SSMA is fantastic to migrate data but is not that good when the time comes to translate code - other than very simple statements.
In my experience... migrate data using SSMA and re-write all your code from scratch.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 11, 2021 at 12:39 pm
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply