August 11, 2010 at 6:09 am
Hello again,
still busy with conversion of T-sql 2000 to 2008 and I've got a little problem :
SQL server throws this error :
The column prefix 'a' does not match with a table name or alias name used in the query.
when I try to execute this select :
SELECT f.exec_date, f.plannings_organism, f.seq_nbr, f.bs_pilot, f.tail_number, ceiling(datediff(mi, atd, ata) / 5.0) * 5 - (CEILING(CONVERT(int, ((abs(datediff(mi,
atd, sunrise)) + datediff(mi, atd, sunrise)) / 2 - (abs(datediff(mi, ata, sunrise)) + datediff(mi, ata, sunrise)) / 2 + (abs(datediff(mi, sunset, ata)) + datediff(mi,
sunset, ata)) / 2 - (abs(datediff(mi, sunset, atd)) + datediff(mi, sunset, atd)) / 2)) / 5.0)) * 5 Day, (CEILING(CONVERT(int, ((abs(datediff(mi, atd, sunrise))
+ datediff(mi, atd, sunrise)) / 2 - (abs(datediff(mi, ata, sunrise)) + datediff(mi, ata, sunrise)) / 2 + (abs(datediff(mi, sunset, ata)) + datediff(mi, sunset, ata))
/ 2 - (abs(datediff(mi, sunset, atd)) + datediff(mi, sunset, atd)) / 2)) / 5.0)) * 5 Nite, ceiling(datediff(mi, atd, ata) / 5.0) * 5 total, f.bs_status,
bsa.organismID bs_organismID, Seat = 'BS',
a.type_name, aa.organismid sponsorid, q_day = CASE WHEN ISNULL((ceiling(datediff(mi, atd, ata) / 5.0)
* 5 - (CEILING(CONVERT(int, ((abs(datediff(mi, atd, sunrise)) + datediff(mi, atd, sunrise)) / 2 - (abs(datediff(mi, ata, sunrise)) + datediff(mi, ata, sunrise))
/ 2 + (abs(datediff(mi, sunset, ata)) + datediff(mi, sunset, ata)) / 2 - (abs(datediff(mi, sunset, atd)) + datediff(mi, sunset, atd)) / 2)) / 5.0)) * 5), 0)
= 0 THEN 0 ELSE 1 END, q_nite = CASE WHEN ISNULL(((CEILING(CONVERT(int, ((abs(datediff(mi, atd, sunrise)) + datediff(mi, atd, sunrise))
/ 2 - (abs(datediff(mi, ata, sunrise)) + datediff(mi, ata, sunrise)) / 2 + (abs(datediff(mi, sunset, ata)) + datediff(mi, sunset, ata)) / 2 - (abs(datediff(mi,
sunset, atd)) + datediff(mi, sunset, atd)) / 2)) / 5.0)) * 5), 0) = 0 THEN 0 ELSE 1 END, ldg_base, to_base
FROM aircraft a ,aircraft_type at ,flights f left join person_allocation bsa on bsa.number = f.bs_pilot
AND f.bs_pilot IS NOT NULL 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) AND a.tail_number = f.tail_number
left join aircraft_allocation aa on aa.tail_number = f.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)
where a.type_name = at.type_name AND at.simulator = 0 AND f.atd IS NOT NULL
I've been searching and modifying different things but I can't find why I get that error, the table aircraft is specified in the FROM clause ...
thanks
August 11, 2010 at 6:24 am
What about alias ata?
where ata.type_name = a.type_name AND at.simulator = 0 AND f.atd IS NOT NULL
If I were you I'd chuck out this query and start again, it looks like it's been written by trial and error. Start with the FROM clause because it mixes ANSI join types and has references to aliases which don't exist. It shouldn't be too hard if you know what it's meant to do, you will get plenty of help and advice, it will visually scan better (you will be able to read and understand what it's meant to do) and will almost certainly run a gazillion times better.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 11, 2010 at 6:48 am
Chris Morris-439714 (8/11/2010)
What about alias ata?
where ata.type_name = a.type_name AND at.simulator = 0 AND f.atd IS NOT NULL
Yes I've edited my post to correct this. It's not like that in my code.
Can you explain me more about "those ANSI join mixes" please, maybee it will be a good start for me.
I have to remove *= and =* operators in an older version of that query.
Thanks
August 11, 2010 at 7:04 am
donmakaveli41 (8/11/2010)
Chris Morris-439714 (8/11/2010)
What about alias ata?
where ata.type_name = a.type_name AND at.simulator = 0 AND f.atd IS NOT NULLYes I've edited my post to correct this. It's not like that in my code.
Can you explain me more about "those ANSI join mixes" please, maybee it will be a good start for me.
I have to remove *= and =* operators in an older version of that query.
Thanks
You're welcome.
Let's start with the FROM list and joins.
With each change you do, you must test the new query against the old. Working with the FROM list should be straightforward because all you're going to change is rowcounts - so compare the old version with the modified version.
You must have a baseline to begin - try this against the original query. If the rowcount is the same and all columns are populated, then it will do.
SELECT a.[type_name],
at.[type_name],
f.tail_number,
bsa.organismID,
aa.organismid
FROM
aircraft a, -- old-style join
aircraft_type at ,
flights f
LEFT JOIN person_allocation bsa on bsa.number = f.bs_pilot AND f.bs_pilot IS NOT NULL -- new-style join
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)
AND a.tail_number = f.tail_number
LEFT JOIN aircraft_allocation aa on aa.tail_number = f.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)
WHERE a.type_name = at.type_name AND at.simulator = 0 AND f.atd IS NOT NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 11, 2010 at 7:20 am
well I tried the little query you gave me, but I get the same error :
The column prefix 'a' does not match with a table name or alias name used in the query.
I've also tried a solution without mixing "old and new join style"
August 11, 2010 at 7:24 am
donmakaveli41 (8/11/2010)
well I tried the little query you gave me, but I get the same error :The column prefix 'a' does not match with a table name or alias name used in the query.
I've also tried a solution without mixing "old and new join style"
Well it could be - and probably is - because of the mix of join types. I've no data to test against. Here's a conversion to newer JOIN syntax:
SELECT a.[type_name],
at.[type_name],
f.tail_number,
bsa.organismID,
aa.organismid
FROM aircraft a
INNER JOIN aircraft_type at ON at.[type_name] = a.[type_name] AND at.simulator = 0
INNER JOIN flights f ON f.tail_number = a.tail_number AND f.atd IS NOT NULL
LEFT JOIN person_allocation bsa on bsa.number = f.bs_pilot AND f.bs_pilot IS NOT NULL -- new-style join
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)
LEFT JOIN aircraft_allocation aa on aa.tail_number = f.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)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy