June 11, 2011 at 8:45 am
Hello,
We are trying to convert our SQL 2000 queries to SQL 2005 queries. But one of the queries is giving me
problems.
Here was the original query that worked and I get 7 rows.
SELECT ym_rail_rack_plan.plant_id,
ym_rail_rack_plan.yard_id,
ym_rail_rack_plan.track_id,
ym_rail_rack_plan.spot_id,
ym_rail_rack_plan.sched_date_time,
ym_rail_rack_plan.session_no,
ym_rail_rack_plan.car_init,
ym_rail_rack_plan.car_no,
ym_rail_rack_plan.order_no,
ym_rail_rack_plan.switch_req_gen,
ym_spot.spot_id,
ym_spot.position,
ym_rail_rack_plan.finalized_user_id,
ym_rail_rack_plan.finalized_date_time,
ym_rail_rack_plan.line_item_no,
ym_rail_rack_plan.load_compart_no,
ym_rail_rack_plan.instruct_code,
ym_spot.plant_id,
ym_spot.yard_id,
ym_spot.track_id,
ym_rail_rack_plan.req_date_time,
ym_rail_rack_plan.comments
FROM ym_spot,
ym_rail_rack_plan
WHERE ( ym_spot.spot_id *= ym_rail_rack_plan.spot_id) and
( ym_spot.track_id *= ym_rail_rack_plan.track_id) and
( ym_spot.yard_id *= ym_rail_rack_plan.yard_id) and
( ym_spot.plant_id *= ym_rail_rack_plan.plant_id) and
( ym_spot.track_id = '10' ) AND
( ym_spot.yard_id = 'S3' ) AND
( ym_spot.plant_id = '10' ) AND
( ym_rail_rack_plan.sched_date_time = '06/20/11 00:00' ) AND
( ym_rail_rack_plan.session_no = 1 )
ORDER BY ym_spot.position ASC
With the new query I am not getting any rows.
SELECT ym_rail_rack_plan.plant_id,
ym_rail_rack_plan.yard_id,
ym_rail_rack_plan.track_id,
ym_rail_rack_plan.spot_id,
ym_rail_rack_plan.sched_date_time,
ym_rail_rack_plan.session_no,
ym_rail_rack_plan.car_init,
ym_rail_rack_plan.car_no,
ym_rail_rack_plan.order_no,
ym_rail_rack_plan.switch_req_gen,
ym_spot.spot_id,
ym_spot.position,
ym_rail_rack_plan.finalized_user_id,
ym_rail_rack_plan.finalized_date_time,
ym_rail_rack_plan.line_item_no,
ym_rail_rack_plan.load_compart_no,
ym_rail_rack_plan.instruct_code,
ym_spot.plant_id,
ym_spot.yard_id,
ym_spot.track_id,
ym_rail_rack_plan.req_date_time,
ym_rail_rack_plan.comments
FROM ym_spot left outer join ym_rail_rack_plan
on ( ym_spot.spot_id = ym_rail_rack_plan.spot_id) and
( ym_spot.track_id = ym_rail_rack_plan.track_id) and
( ym_spot.yard_id = ym_rail_rack_plan.yard_id) and
( ym_spot.plant_id = ym_rail_rack_plan.plant_id)
WHERE ( ( ym_spot.track_id = '10' ) AND
( ym_spot.yard_id = 'S3' ) AND
( ym_spot.plant_id = '10' ) AND
( ym_rail_rack_plan.sched_date_time = '06/20/11 00:00' ) AND
( ym_rail_rack_plan.session_no = 1 ) )
ORDER BY ym_spot.position ASC
Any help would be appreciated.
Thanks
June 11, 2011 at 9:26 am
Not having your data, it's hard for me test this, but you can try moving the other WHERE criteria into the JOIN clause. When dealing with OUTER JOINs, filter criteria in the WHERE clause can act to turn the query into an INNER JOIN. In those cases you may need to filter as part of the JOIN instead of the WHERE clause. You should only have to do that for the conditions that apply to columns from the OUTER table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 11, 2011 at 9:31 am
That worked. Thank you
June 11, 2011 at 10:00 am
Yasir Masood (6/11/2011)
That worked. Thank you
Not a problem. Glad I could help. Also, thanks for posting that it worked. Makes it better for someone reading this later.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply