SQL Server view column conversion problem

5 pts.
Tags:
Data conversion
SQL Server 2005
SQL Server Query
SQL Server views
I have a view with a column SWID. The source column is Varchar while in the view creation it has been cast to INT. The source table has a non-numeric value "CITY", while during the view creation, this record have been filtered out. When I select all records from the view, it's working well. However, when I specify WHERE SWID = 26 in the query, it has problem 'Conversion failed when converting the nvarchar value 'CITY' to data type int'. Please anyone help me out.

Software/Hardware used:
SQL Server 2005

Answer Wiki

Thanks. We'll let you know when a new response is added.

That’s because the processing is being done on the base table. If you want to search the view using an integer on that column you’ll need to put an index on the view.

This will require that you rebuild the view with the SCHEMABINDING option, then you can build a clustered index on the view, then put an index on the column SWID.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    It could be just a typo, but you said the source column was varchar, but the error message says "...when converting the nvarchar value ..." If the records with non-numeric data were filtered out when creating the view and the column was in fact cast to a numberic data type, you should not get errors when querying the view using an integer as criteria. Can you post the view creation command and the complete query that is giving the error ?
    69,510 pointsBadges:
    report
  • carlosdl
    Ok, I use SQL Server just for testing, so my knowledge is limited. I made a simplified test, and got no errors:
    CREATE TABLE test_table (
    v_swid VARCHAR(20),
    i_other_column INTEGER)
    
    INSERT INTO test_table VALUES ('1',1);
    INSERT INTO test_table VALUES ('2',2);
    INSERT INTO test_table VALUES ('CITY',10);
    INSERT INTO test_table VALUES ('11',11);
    INSERT INTO test_table VALUES ('CITY',20);
    INSERT INTO test_table VALUES ('21',21);
    
    CREATE VIEW v_test AS
    SELECT CAST(v_swid AS INTEGER) swid, i_other_column
    FROM test_table
    WHERE v_swid != 'CITY';
    
    SELECT * FROM v_test;
    
    (4 row(s) affected)
    
    SELECT * FROM v_test WHERE swid = 11;
    
    (1 row(s) affected)
    69,510 pointsBadges:
    report
  • Rmb1981
    [...] Rmb1981 was having a SQL Server view column conversion problem and Mr. Denny and Carlosdl tried to help him [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following