Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding Where Condition on Select query on a View : Error converting data type varchar to bigint. Expand / Collapse
Author
Message
Posted Tuesday, March 18, 2014 10:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:12 AM
Points: 4, Visits: 123
Hi,

I am building a View with an integer column by filtering data from Master table and below is the sample script. And Select Query on the View with WHERE condition is throwing the following exception.


Create table MasterEmployees (EmpID varchar(50))

Insert into MasterEmployees values ('12345'),('ABCDEF'),('9876543210')

----------------Creating the view ------------------------

Create view ChildEmployees

AS

Select Cast(EmpiD as bigint) as EmpID from MasterEmployees where ISNUMERIC(EmpID)=1

---------------------------Issue ----------------------------

Executing Select query on View without WHERE condition is working fine.

Adding WHERE condition to the Query like below

Select * from ChildEmployees where Empid =12345 is throwing below exception

[color=#FF0000]
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint
.[/color]


Post #1552308
Posted Tuesday, March 18, 2014 10:47 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 7,208, Visits: 13,669
The numeric test and the cast to bigint have to occur together and in the correct order.

CREATE VIEW ChildEmployees

AS

SELECT x.EmpID
FROM MasterEmployees
CROSS APPLY (
SELECT EmpiD CASE
WHEN ISNUMERIC(EmpID) = 1 THEN CAST(EmpiD AS BIGINT)
ELSE NULL END
) x
WHERE x.EmpID IS NOT NULL



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1552314
Posted Tuesday, March 18, 2014 8:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:12 AM
Points: 4, Visits: 123
It worked.
Thanks a Ton
Post #1552446
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse