February 12, 2009 at 6:55 am
Here I want to deal with 3 tables ,
Flight , Airlines , Cities
In a way to get a result that matches both city_from (startingFrom) & city_to ( destination )
with the desired parameters ,
I think anyone with a medium experience can guess what is inside of each of them and also how they relate to each other ,
For more clarifying :
Cities : city_id , city_name
Airlines : airline_id , (int) city_from , (int) city_to
consider that city_from & city_to refer to city_id
I wrote a Sproc as follows ,
it gives me these errors : [edited ]
Msg 2714, Level 16, State 3, Procedure SearchFlightByCities, Line 29
There is already an object named 'SearchFlightByCities' in the database.
Msg 1087, Level 15, State 2, Line 3
Must declare the table variable "@FlightsLook".
Previous Error ( Before Edit )
Msg 102, Level 15, State 1, Procedure SearchFlightByCities, Line 7
Incorrect syntax near '@destination'.
Msg 137, Level 15, State 2, Procedure SearchFlightByCities, Line 37
Must declare the scalar variable "@startingFrom".
Msg 1087, Level 15, State 2, Line 3
Must declare the table variable "@FlightsLook".
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:MHM
-- =============================================
CREATE PROCEDURE SearchFlightByCities
(
@startingFrom nchar(33)
@destination nchar(33)
@RowNum int )
AS
-- BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--
DECLARE @FlightsLook TABLE
(
row int ,
flight_number char(10) ,
city_from nchar(33) ,
--city_to nchar(33) ,
departure_date datetime ,
arrival_datedatetime,
aircraft_type nvarchar(50),
price char(10)
)
--
INSERT INTO @FlightsLook
SELECT ROW_NUMBER() OVER (ORDER BY fff.flight_id) AS Row ,
fff.flight_number , ccc.city_name , fff.departure_date , fff.arrival_date , fff.aircraft_type , fff.price
FROM Flights fff
JOIN Airlines aaa ON fff.airline_id = aaa.airline_id
JOIN Cities ccc ON aaa.StartFrom = ccc.city_id
-- That is for getting the items that their Start is from the same city
WHERE ccc.city_name = @startingFrom
-- GO ( Removed , Thanks )
--
-- procedure
SELECT * FROM @FlightsLook
JOIN Airlines aaa ON fff.airline_id = aaa.airline_id
JOIN Cities ccc ON aaa.Destination = ccc.city_id
WHERE ccc.city_name = @destination
-- END
GO
----------------------
Thanks for any help
February 12, 2009 at 6:57 am
You're missing some commas between the parameters declaration.
CREATE PROCEDURE SearchFlightByCities
(
@startingFrom nchar(33) ,
@destination nchar(33) ,
@RowNum int )
...
-- Gianluca Sartori
February 12, 2009 at 7:07 am
Yes , thanks , but after adding them ,
it gives me this error :
Msg 1087, Level 15, State 2, Line 3
Must declare the table variable "@FlightsLook".
It seems that still something is missed
February 12, 2009 at 7:15 am
Why have you got a GO in the middle of the stored proc? This will create the stored Proc at that point, then the rest is just a seperate query (where the table in question is not declared)
February 12, 2009 at 7:24 am
In addition to the above, the table alias [fff] has not been assigned in this statement:
-- procedure
SELECT * FROM @FlightsLook
JOIN Airlines aaa ON fff.airline_id = aaa.airline_id
JOIN Cities ccc ON aaa.Destination = ccc.city_id
You will develop faster and retain a great deal more hair if you forget about creating a sproc until the code you want to put into it is correct.
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
February 12, 2009 at 7:48 am
But the fff is defined 3 lines beneath the
Insert Into ,
Wasn't that correct ?
and about the GO statement , thank you for giving me such hints
February 12, 2009 at 8:27 am
feodon (2/12/2009)
But the fff is defined 3 lines beneath theInsert Into ,
Wasn't that correct ?
and about the GO statement , thank you for giving me such hints
Yes it is, but table aliases don't work like that. They are statement-specific.
SELECT * FROM @FlightsLook fff
JOIN Airlines aaa ON fff.airline_id = aaa.airline_id
JOIN Cities ccc ON aaa.Destination = ccc.city_id
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
February 12, 2009 at 8:46 am
thanks ,please consider ,
I used fff for Flights table not for @FlightsLook ,
Isn't it a misconception ,
the thing that you say , is it the answer , you mean ?
or that refers to another mistake ?
Still the problem exists ...
the Error :
Msg 2714, Level 16, State 3, Procedure SearchFlightByCities, Line 29
There is already an object named 'SearchFlightByCities' in the database.
Msg 1087, Level 15, State 2, Line 3
Must declare the table variable "@FlightsLook".
February 12, 2009 at 8:57 am
Whichever table you want fff to be the alias for, you have to define it in the statement.
If you need to refer to the flight table, you need to JOIN it in the query and assign it the alias fff - it's not relevant that you used the alias in a previous statement.
Regarding the errors, the first one is because you can't use CREATE PROCEDURE for a proc that already exists, you need to either drop the procedure that has already been created first, or use ALTER PROCEDURE instead.
Regarding the second one, you must still have the GO in the wrong place!
February 12, 2009 at 9:09 am
Thanks , Let me try ...
February 12, 2009 at 9:40 am
really thanks HowardW
you were absolutely right ,
Now I came up to the point that "Chris Morris" told
the error now is :
Msg 4104, Level 16, State 1, Procedure SearchFlightByCities, Line 41
The multi-part identifier "fff.airline_id" could not be bound.
But I'm baffled about it ,
could anyone alter this part of the code for making that work ,
All your help is appreciated ,
--
INSERT INTO @FlightsLook
SELECT ROW_NUMBER() OVER (ORDER BY fff.flight_id) AS Row ,
fff.flight_number , ccc.city_name , fff.departure_date , fff.arrival_date , fff.aircraft_type , fff.price
FROM Flights fff
JOIN Airlines aaa ON fff.airline_id = aaa.airline_id
JOIN Cities ccc ON aaa.StartFrom = ccc.city_id
-- That is for getting the items that their Start is from the same city
WHERE ccc.city_name = @startingFrom
--GO
--
-- procedure
SELECT * FROM @FlightsLook
JOIN Airlines aaa ON fff.airline_id = aaa.airline_id
JOIN Cities ccc ON aaa.Destination = ccc.city_id
WHERE ccc.city_name = @destination
-- END
GO
I dont know how can I refer to fff.flight_number
when the FROM keyword is beneath of that
fff.flight_number , ccc.city_name , fff.departure_date , fff.arrival_date , fff.aircraft_type , fff.price
FROM Flights fff
Should I use Flights.flight_number instead of the alias ,
Is it the solution or there is another way ?
February 12, 2009 at 9:53 am
DECLARE @startingFrom nchar(33), @destination nchar(33), @RowNum int
SELECT @startingFrom = [YOUR VALUE IN HERE], @destination = [YOUR VALUE IN HERE]
DECLARE @FlightsLook TABLE
(
row int ,
flight_number char(10) ,
city_from nchar(33) ,
--city_to nchar(33) ,
departure_date datetime ,
arrival_date datetime,
aircraft_type nvarchar(50),
price char(10)
)
INSERT INTO @FlightsLook
SELECT ROW_NUMBER() OVER (ORDER BY fff.flight_id) AS Row ,
fff.flight_number , ccc.city_name , fff.departure_date , fff.arrival_date , fff.aircraft_type , fff.price
FROM Flights fff
JOIN Airlines aaa ON fff.airline_id = aaa.airline_id
JOIN Cities ccc ON aaa.StartFrom = ccc.city_id
-- That is for getting the items that their Start is from the same city
WHERE ccc.city_name = @startingFrom
SELECT *
FROM @FlightsLook fff -- using the same alias [fff] if you want because this is a new statement
JOIN Airlines aaa ON fff.airline_id = aaa.airline_id
JOIN Cities ccc ON aaa.Destination = ccc.city_id
WHERE ccc.city_name = @destination
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
February 12, 2009 at 10:17 am
feodon (2/12/2009)
the error now is :Msg 4104, Level 16, State 1, Procedure SearchFlightByCities, Line 41
The multi-part identifier "fff.airline_id" could not be bound.
But I'm baffled about it ,
could anyone alter this part of the code for making that work ,
All your help is appreciated
The table variable @FlightsLook does not have the column [airline_id]. Put it into the select list of your first query.
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
February 12, 2009 at 11:08 am
Really thanks Chris ,
you saved me hours , and helped me alot ,
big thanks ,
I'll try it now ,
-MHM-
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply