July 26, 2012 at 6:13 am
So I have this table where I have a bigint column being the primary key with autoincrement on. I also have a view which has all the columns from the table plus some additional columns. The problem is when i run a select statement and I want to filter on the primary key:
select * from Table where Request_ID = 123456 - selecting directly from the table works fine
select * from vw_Table where Request_ID = 123456 - selecting the same from the view fails with 'error converting varchar to bigint
on the other hand though:
select * from vw_Table where Request_ID like 123456 - selecting the same from the view and using 'like' instead of '=' works fine
I am not doing any conversion on the primary key in the view and it comes as a bigint out of the view itself. Even explicitly converting both to varchar gives me the same error on the view:
select * from vw_Table where cast(Request_ID as varchar(max)) = cast('123456' as varchar(max)) - same error on this too.
Am I missing something here? Using 'like' seems to solve the problem but i feel it is a dirty fix.
Thanks
Sorry for the confusion guys. The view had a join on the Request_ID field to an another table where this field was a varchar type. I did not convert explicitly while writing this join and that seemed to cause the issue. When converting bigint explicitly to varchar on the join in the view, the issue gets fixed.
Sorry & thanks.
July 26, 2012 at 6:20 am
Can you post the DDL of the view and tables in question?
Along with the data for row 123456
Does seem strange the it only works on a like and not =
July 26, 2012 at 6:24 am
marek_polko (7/26/2012)
So I have this table where I have a bigint column being the primary key with autoincrement on. I also have a view which has all the columns from the table plus some additional columns. The problem is when i run a select statement and I want to filter on the primary key:select * from Table where Request_ID = 123456 - selecting directly from the table works fine
select * from vw_Table where Request_ID = 123456 - selecting the same from the view fails with 'error converting varchar to bigint
on the other hand though:
select * from vw_Table where Request_ID like 123456 - selecting the same from the view and using 'like' instead of '=' works fine
I am not doing any conversion on the primary key in the view and it comes as a bigint out of the view itself. Even explicitly converting both to varchar gives me the same error on the view:
select * from vw_Table where cast(Request_ID as varchar(max)) = cast('123456' as varchar(max)) - same error on this too.
Am I missing something here? Using 'like' seems to solve the problem but i feel it is a dirty fix.
Thanks
Please provide the DDL for table and view and if possible the record as well which is having that request id.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 26, 2012 at 6:40 am
Sorry for the confusion guys. The view had a join on the Request_ID field to an another table where this field was a varchar type. I did not convert explicitly while writing this join and that seemed to cause the issue. When converting bigint explicitly to varchar on the join in the view, the issue gets fixed.
Sorry & thanks.
July 26, 2012 at 6:51 am
marek_polko (7/26/2012)
Sorry for the confusion guys. The view had a join on the Request_ID field to an another table where this field was a varchar type. I did not convert explicitly while writing this join and that seemed to cause the issue. When converting bigint explicitly to varchar on the join in the view, the issue gets fixed.Sorry & thanks.
🙂
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 30, 2012 at 8:03 pm
marek_polko (7/26/2012)
Sorry for the confusion guys. The view had a join on the Request_ID field to an another table where this field was a varchar type. I did not convert explicitly while writing this join and that seemed to cause the issue. When converting bigint explicitly to varchar on the join in the view, the issue gets fixed.Sorry & thanks.
A VIEW that must join BIGINT and VARCHAR columns indicates a potential problem in your data model. Are you sure that is a proper approach?
Data Type Precedence - SQL Server 2005
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply