March 18, 2014 at 10:35 am
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
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
March 18, 2014 at 10:47 am
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
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
March 18, 2014 at 8:27 pm
It worked.
Thanks a Ton
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply