Convert varchar to int in a where clause

Question

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 31-Dec-16 1:38am

Member 12929280

Updated 31-Dec-16 2:30am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Michael_Davies · Answer 1 · 2016-12-31T02:30:00

"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