November 10, 2008 at 1:04 pm
I am trying to convert this particular code to newer sql syntax that uses Outer joins. I did few but having problems with this one.
I also trying to encapsulate this code into view then try to do Right click on view -> then Design , but was not successful to get newer syntax.
Can someone from here try to convert the last line of the code to newer SQL syntax which will use Left Outer Joins.
Code :
SELECT m.vid,
m.mwhid,
m.begin_time,
m.end_time,
DATEDIFF(minute, m.begin_time, m.end_time) AS [duration],
m.miletenths / 10.0 AS [miles],
m.mwh_type,
CASE m.mwh_type
WHEN 0 THEN 'unassigned'
WHEN 1 THEN 'driving'
ELSE 'unknown'
END AS [typedesc],
m.trucknum,
md.logged_in AS [logged]
FROM mwh AS m,
mwh_did AS md,
drivers AS d
WHERE md.vid = m.vid
AND md.mwhid = m.mwhid
AND ( CASE WHEN m.did IS NOT NULL THEN m.did ELSE md.did END ) *= d.did
Thanks.
November 10, 2008 at 1:18 pm
I believe this is the code you want:
[font="Courier New"]SELECT
m.vid,
m.mwhid,
m.begin_time,
m.end_time,
DATEDIFF(minute, m.begin_time, m.end_time) AS [duration],
m.miletenths / 10.0 AS [miles],
m.mwh_type,
CASE m.mwh_type
WHEN 0 THEN 'unassigned'
WHEN 1 THEN 'driving'
ELSE 'unknown'
END AS [typedesc],
m.trucknum,
md.logged_in AS [logged]
FROM
mwh AS m INNER JOIN
mwh_did AS md ON
m.vid = md.vid AND
m.mqhid = md.mwhid LEFT OUTER JOIN
drivers AS d ON
CASE WHEN m.did IS NOT NULL THEN m.did ELSE md.did END = d.did
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2008 at 1:40 pm
Thanks Jack.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply