August 11, 2010 at 2:52 am
Hi everybody,
thanks to give me a little attention.
I've a little probleme while converting a view from old t-sql to the new syntax, particulary with the ' =* ' and ' *= ' trick.
The old syntax give me more than 252000 rows but with the new one, i've got 1436 rows.
I've tried to put the old syntax into the view builder of SQL 2000 and it rebuild a new code without =* and *= but with many other things that I don't need.
With this code, I've 1436 rows too.
So my new code and the sql view builder code, give the same result (data are equals too)
Here is the old code :
SELECT [...]
FROM
flights f,
aircraft_allocation aa,
person bs,
person fs,
person_allocationfsa,
person_allocationbsa
WHERE bs.number =* f.bs_pilot
AND fs.number =* f.fs_pilot
AND(fsa.organismID = 5 Or bsa.organismId = 5)
ANDf.tail_number*=aa.tail_number
AND(Sign(datediff(mi,f.exec_date,aa.begin_date)) *
ISNULL(sign(datediff(mi,f.exec_date,aa.end_date)),1)= -1
ORSign(datediff(mi,f.exec_date,aa.begin_date)) = 0
ORsign(datediff(mi,f.exec_date,aa.end_date)) = 0)
ANDf.fs_pilot*=fsa.number
AND(Sign(datediff(mi,f.exec_date,fsa.join_date)) *
ISNULL(sign(datediff(mi,f.exec_date,fsa.leave_date)),1)= -1
ORSign(datediff(mi,f.exec_date,fsa.join_date)) = 0
ORsign(datediff(mi,f.exec_date,fsa.leave_date)) = 0)
ANDf.bs_pilot*=bsa.number
AND(Sign(datediff(mi,f.exec_date,bsa.join_date)) *
ISNULL(sign(datediff(mi,f.exec_date,bsa.leave_date)),1)= -1
ORSign(datediff(mi,f.exec_date,bsa.join_date)) = 0
ORsign(datediff(mi,f.exec_date,bsa.leave_date)) = 0)
Here is my code:
SELECT [...]
FROM flights f LEFT OUTER JOIN person bs on bs.Number=f.bs_pilot
LEFT OUTER JOIN person fs on fs.Number = f.fs_pilot
LEFT OUTER JOIN aircraft_allocation aa on f.tail_Number = aa.tail_number
LEFT OUTER JOIN person_allocation fsa on f.fs_pilot = fsa.Number
LEFT OUTER JOIN person_allocation bsa on f.bs_pilot = bsa.Number
WHERE
(fsa.organismID = 5 Or bsa.organismId = 5)
AND(Sign(datediff(mi,f.exec_date,aa.begin_date)) *
ISNULL(sign(datediff(mi,f.exec_date,aa.end_date)),1)= -1
ORSign(datediff(mi,f.exec_date,aa.begin_date)) = 0
ORsign(datediff(mi,f.exec_date,aa.end_date)) = 0)
AND(Sign(datediff(mi,f.exec_date,fsa.join_date)) *
ISNULL(sign(datediff(mi,f.exec_date,fsa.leave_date)),1)= -1
ORSign(datediff(mi,f.exec_date,fsa.join_date)) = 0
ORsign(datediff(mi,f.exec_date,fsa.leave_date)) = 0)
AND(Sign(datediff(mi,f.exec_date,bsa.join_date)) *
ISNULL(sign(datediff(mi,f.exec_date,bsa.leave_date)),1)= -1
ORSign(datediff(mi,f.exec_date,bsa.join_date)) = 0
ORsign(datediff(mi,f.exec_date,bsa.leave_date)) = 0)
Can someone explain me what's wrong. Am I doing something wrong ?
Thanks
August 11, 2010 at 3:27 am
You probably need to put the filters in the JOINs so that all the flights will show.
Something like:
SELECT [...]
FROM flights f,
LEFT JOIN person bs
ON f.bs_pilot = bs.number
LEFT JOIN person fs
ON F.fs_pilot = fs.number
LEFT JOIN person_allocation fsa
ON f.fs_pilot = fsa.number
AND (Sign(datediff(mi,f.exec_date,fsa.join_date)) *
ISNULL(sign(datediff(mi,f.exec_date,fsa.leave_date)),1)= -1
OR Sign(datediff(mi,f.exec_date,fsa.join_date)) = 0
OR sign(datediff(mi,f.exec_date,fsa.leave_date)) = 0)
LEFT JOIN aircraft_allocation aa
ON f.tail_number = aa.tail_number
AND (Sign(datediff(mi,f.exec_date,aa.begin_date)) *
ISNULL(sign(datediff(mi,f.exec_date,aa.end_date)),1)= -1
OR Sign(datediff(mi,f.exec_date,aa.begin_date)) = 0
OR sign(datediff(mi,f.exec_date,aa.end_date)) = 0)
LEFT JOIN person_allocation bsa
ON f.bs_pilot = bsa.number
AND (fsa.organismID = 5 OR bsa.organismId = 5)
AND (Sign(datediff(mi,f.exec_date,bsa.join_date)) *
ISNULL(sign(datediff(mi,f.exec_date,bsa.leave_date)),1)= -1
OR Sign(datediff(mi,f.exec_date,bsa.join_date)) = 0
OR sign(datediff(mi,f.exec_date,bsa.leave_date)) = 0)
August 11, 2010 at 3:34 am
Yes that's it.
Thank you verry much.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply