Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Question

i am getting the above error when am trying to run the following  queries:...The error is  basically in the bold section

SELECT P.Age, DA.SchedDeptim, A.AirlID 
FROM PassengTable P INNER JOIN PassengReserve PR 
ON(PR.PassengID = P.PassengID) INNER JOIN ReservTable R
ON(R.ReservID = PR.ReservID) INNER JOIN ReservLegsTable RL
ON(RL.ReservID = R.ReservID) INNER JOIN FligLegTable FL 
 ON(FL.LegNo = RL.LegNo) INNER JOIN DepAirpTable DA ON(DA.LegNo = FL.LegNo) INNER JOIN FligNoTable F
 ON(F.LegNo = FL.LegNo) INNER JOIN AirlTable A ON(A.AirlID = F.AirlID) 
 WHERE P.Age BETWEEN 4 AND 8 AND DAY(DA.SchedDeptim) = DAY('2012-09-24 09:20') AND A.AirlID = 'MA'

GO

CREATE PROCEDURE Passenger_with_Age_category
@AirlineID varchar(50),
@Infant varchar(50) = '0 AND 2',
@Children varchar(50) = '2 AND 11',
@Youth varchar(50) = '12 AND 17',
@Adult varchar(50) = '18 AND 64',
@Senior varchar(50) = '65 AND 150',
@date varchar(50) = '2012-09-24 09:20'
AS
 SELECT (SELECT P.Age, DA.SchedDeptim, A.AirlID FROM PassengTable P INNER JOIN PassengReserve PR ON(PR.PassengID = P.PassengID) 
  INNER JOIN ReservTable R ON(R.ReservID = PR.ReservID) 
  INNER JOIN ReservLegsTable RL ON(RL.ReservID = R.ReservID) 
  INNER JOIN FligLegTable FL ON(FL.LegNo = RL.LegNo)
  INNER JOIN DepAirpTable DA ON(DA.LegNo = FL.LegNo)
  INNER JOIN FligNoTable F ON(F.LegNo = FL.LegNo) 
  INNER JOIN AirlTable A ON(A.AirlID = F.AirlID) WHERE A.AirlID = @AirlineID AND P.Age 
  BETWEEN @Infant AND  @date )   
  AS 'Infants',

Answer 1

Hi soomsoom

Try this code:

CREATE PROCEDURE Passenger_with_Age_category @AirlineID VARCHAR(50)
	,@Infant VARCHAR(50) = '0 AND 2'
	,@Children VARCHAR(50) = '2 AND 11'
	,@Youth VARCHAR(50) = '12 AND 17'
	,@Adult VARCHAR(50) = '18 AND 64'
	,@Senior VARCHAR(50) = '65 AND 150'
	,@date VARCHAR(50) = '2012-09-24 09:20'
AS
SELECT P.Age
	,DA.SchedDeptim
	,A.AirlID
FROM PassengTable P
INNER JOIN PassengReserve PR ON (PR.PassengID = P.PassengID)
INNER JOIN ReservTable R ON (R.ReservID = PR.ReservID)
INNER JOIN ReservLegsTable RL ON (RL.ReservID = R.ReservID)
INNER JOIN FligLegTable FL ON (FL.LegNo = RL.LegNo)
INNER JOIN DepAirpTable DA ON (DA.LegNo = FL.LegNo)
INNER JOIN FligNoTable F ON (F.LegNo = FL.LegNo)
INNER JOIN AirlTable A ON (A.AirlID = F.AirlID)
WHERE A.AirlID = @AirlineID
	AND P.Age BETWEEN @Infant
		AND @date

---

Regards Harsh

Answer 2

Construction you are using:

SELECT (...<Sub-select>...) AS Infants

means that your sub-select is a value for column "Infants"

If you wish to use it as a table, you need to use sub-select in the FROM clause.

Answer 3

So where exactly should i put the clause?

Answer 4

You can try query proposed by Harsh.

Answer 5

I tried yes..but the thing is that i need a new column infants to be added 

Answer 6

And what should be the value of this column?

Please post:

1. create table scripts with some sample data

2. full query, not only the "infants" part.

Answer 7

Hi soomsoom,

Try code like this to add extra column

CREATE PROCEDURE Passenger_with_Age_category @AirlineID VARCHAR(50)
	,@Infant VARCHAR(50) = '0 AND 2'
	,@Children VARCHAR(50) = '2 AND 11'
	,@Youth VARCHAR(50) = '12 AND 17'
	,@Adult VARCHAR(50) = '18 AND 64'
	,@Senior VARCHAR(50) = '65 AND 150'
	,@date VARCHAR(50) = '2012-09-24 09:20'
AS
SELECT *
	,< < AddNewColumnLogicHere > >
FROM (
	SELECT P.Age
		,DA.SchedDeptim
		,A.AirlID
	FROM PassengTable P
	INNER JOIN PassengReserve PR ON (PR.PassengID = P.PassengID)
	INNER JOIN ReservTable R ON (R.ReservID = PR.ReservID)
	INNER JOIN ReservLegsTable RL ON (RL.ReservID = R.ReservID)
	INNER JOIN FligLegTable FL ON (FL.LegNo = RL.LegNo)
	INNER JOIN DepAirpTable DA ON (DA.LegNo = FL.LegNo)
	INNER JOIN FligNoTable F ON (F.LegNo = FL.LegNo)
	INNER JOIN AirlTable A ON (A.AirlID = F.AirlID)
	WHERE A.AirlID = @AirlineID
		AND P.Age BETWEEN @Infant
			AND @date
	) TEMP

---

Regards Harsh

Answer 8

Msg 102, Level 15, State 1, Procedure Passenger_with_Age_category, Line 10
Incorrect syntax near '<'.

Answer 9

Hi soomsoom,

That an example query, you need to replace the value << AddNewColumnLogicHere >> with you own calculated query, Otherwise use this:

CREATE PROCEDURE Passenger_with_Age_category @AirlineID VARCHAR(50)
	,@Infant VARCHAR(50) = '0 AND 2'
	,@Children VARCHAR(50) = '2 AND 11'
	,@Youth VARCHAR(50) = '12 AND 17'
	,@Adult VARCHAR(50) = '18 AND 64'
	,@Senior VARCHAR(50) = '65 AND 150'
	,@date VARCHAR(50) = '2012-09-24 09:20'
AS
SELECT *
	
FROM (
	SELECT P.Age
		,DA.SchedDeptim
		,A.AirlID
	FROM PassengTable P
	INNER JOIN PassengReserve PR ON (PR.PassengID = P.PassengID)
	INNER JOIN ReservTable R ON (R.ReservID = PR.ReservID)
	INNER JOIN ReservLegsTable RL ON (RL.ReservID = R.ReservID)
	INNER JOIN FligLegTable FL ON (FL.LegNo = RL.LegNo)
	INNER JOIN DepAirpTable DA ON (DA.LegNo = FL.LegNo)
	INNER JOIN FligNoTable F ON (F.LegNo = FL.LegNo)
	INNER JOIN AirlTable A ON (A.AirlID = F.AirlID)
	WHERE A.AirlID = @AirlineID
		AND P.Age BETWEEN @Infant
			AND @date
	) TEMP

---

Regards Harsh

Answer 10

>> i am getting the above error when am trying to run the following queries:<<

The short answer is that the nested SELECT in outermost SELECT list is screwed up and returns more than one row. But even if it worked, it would destroy performance, be bitch to maintain and tell the world that you are bad SQL programmer. 

The design flaw of adding “table-” to table names is so bad that it has a name! You are tibbling and good SQL programmers laugh at you. The rest of your code is awful and shows no understanding of RDBMS, how to write SQL or basic data modeling. 

How amazing that everything in the world is the magical VARCHAR(50) data type! You even put a date into this insanely oversized data type! 

I got a laugh out of “DAY('2012-09-24 09:20:00')” because you can look it up on a calendar and save re-computing it over and over. It is a constant! 

Your eight table join should have been done in the DDL with REFERENCES clauses. 

Another example of your bad code is “P.age_category BETWEEN @Infant AND @date)” would fail your in one of my SQL classes. How can a category between a baby and some vague date on a calendar? 

Apparently you thought that SQL would replace @infant with the text '0 AND 2' (invalid SQL syntax and absurd), and compile! But then we have @date as '2012-09-24 09:20:00' (you did not even know that you need the seconds in that temporal display format!)

There is a tee-shirt I like with says “On a scale from 1 to 10, what color is your favorite letter of the alphabet?”

Your code is that silly. Please stop programming before your ignorance and stupidity hurt people. 

Without DDL we cannot really help you. Until you educate yourself, you are not worth helping. We will just be kludging bad code to help you keep a job for which you are not qualified. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL