Click here to Skip to main content
15,917,328 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi, guys! I am trying to conver a variable that is varchar to int in a where clause, but every time I try I get an error message: 'Conversion failed when converting the varchar value 'Total Number of Seats' to data type int.'


can anyone help me??

thanks!

What I have tried:

select * from ( select maker as 'Maker', name as 'Name', type as 'Type', start_date as 'Start Date', end_date as 'End Date', model as 'Model', CASE TYPE WHEN 'NARROW' THEN (N_N_SEATS_SECTOR_L+N_N_SEATS_SECTOR_R)*N_ROWS ELSE (W_N_SEATS_SECTOR_C+W_N_SEATS_SECTOR_L+W_N_SEATS_SECTOR_R)*N_ROWS END as 'Total Number of Seats', CASE TYPE WHEN 'NARROW' THEN (N_N_SEATS_SECTOR_L+N_N_SEATS_SECTOR_R)*N_ROWS_w ELSE (W_N_SEATS_SECTOR_C+W_N_SEATS_SECTOR_L+W_N_SEATS_SECTOR_R)*N_ROWS_w END as 'Number of Seats Class' from aircraft) a where cast( 'Total Number of Seats' as int) >= 200
Posted
Updated 31-Dec-16 2:30am
Comments
Dave Kreskowiak 31-Dec-16 13:45pm    
Having a cast in a WHERE clause is a bad idea. It slows down your query tremendously as every row in a table has to have this cast operation performed on it before it can be evaluated by the WHERE clause. Doing so suggests you have a design problem with your database.
Maciej Los 2-Jan-17 2:06am    
What RDBMS?
Are you sure that above query returns varchar?

1 solution

"Total Number of Seats" is a string literal that is not numeric, it cannot be converted to a number.

Try not using spaces in your names, change;

(W_N_SEATS_SECTOR_C+W_N_SEATS_SECTOR_L+W_N_SEATS_SECTOR_R)*N_ROWS END as 'Total Number of Seats'

To;

(W_N_SEATS_SECTOR_C+W_N_SEATS_SECTOR_L+W_N_SEATS_SECTOR_R)*N_ROWS END as Total_Number_of_Seats

Then:

where cast(Total_Number_of_Seats as int) >= 200
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900