Conversion failed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conversion failed

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-10-07 : 07:06:16

Hi,

The below SQL query I receive the error message
Conversion failed when converting the varchar value 'InActive' to data type bit.. Please help..

SELECT     dbo.tblKeyfob.KeyfobNo, dbo.tblKeyfob.DatePurchased, dbo.tblKeyfob.Dateactivated, dbo.tblKeyfob.ExpiryDate, dbo.tblKeyfob.Status AS KeyfobStatus, 
                      dbo.tblCustomer.CitrixUsername, dbo.tblCustomer.DisabledDate, 
                      CASE WHEN dbo.tblCustomer.ActiveFlag = 'False' THEN 'InActive' ELSE COALESCE (dbo.tblCustomer.ActiveFlag, 'Active') END AS UserStatus
FROM         dbo.tblKeyfob INNER JOIN
                      dbo.tblCustomer ON dbo.tblKeyfob.KeyfobID = dbo.tblCustomer.KeyfobID

Note: ActiveFlag datatype is a bit.

I want when its set to false then it states InActive otherwise it states Active.
Thank you

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2013-10-07 : 07:26:39

A BIT data type can be a 0 or 1 and nothing else.

try the below:

SELECT     dbo.tblKeyfob.KeyfobNo, dbo.tblKeyfob.DatePurchased, dbo.tblKeyfob.Dateactivated, dbo.tblKeyfob.ExpiryDate, dbo.tblKeyfob.Status AS KeyfobStatus, 
                      dbo.tblCustomer.CitrixUsername, dbo.tblCustomer.DisabledDate, 
                      CASE WHEN dbo.tblCustomer.ActiveFlag = 0 THEN 'InActive' ELSE 'Active' END AS UserStatus
FROM         dbo.tblKeyfob INNER JOIN
                      dbo.tblCustomer ON dbo.tblKeyfob.KeyfobID = dbo.tblCustomer.KeyfobID

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-10-07 : 07:53:23

Ok - this worked and added th eorder by as shown below;

SELECT     TOP (100) PERCENT dbo.tblKeyfob.KeyfobNo, dbo.tblKeyfob.DatePurchased, dbo.tblKeyfob.Dateactivated, dbo.tblKeyfob.ExpiryDate, 
                      dbo.tblKeyfob.Status AS KeyfobStatus, dbo.tblCustomer.CitrixUsername, dbo.tblCustomer.DisabledDate, 
                      CASE WHEN dbo.tblCustomer.ActiveFlag = 0 THEN 'InActive' ELSE 'Active' END AS UserStatus
FROM         dbo.tblKeyfob INNER JOIN
                      dbo.tblCustomer ON dbo.tblKeyfob.KeyfobID = dbo.tblCustomer.KeyfobID
ORDER BY dbo.tblKeyfob.KeyfobNo

The problem is when I run the query in design view it orderd by KeyfobNo, but when I open View it doesnt order by Keyfob No.

Why dooes it behave like so?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 08:09:11

quote:

Originally posted by dr223

Ok - this worked and added th eorder by as shown below;

SELECT     TOP (100) PERCENT dbo.tblKeyfob.KeyfobNo, dbo.tblKeyfob.DatePurchased, dbo.tblKeyfob.Dateactivated, dbo.tblKeyfob.ExpiryDate, 
                      dbo.tblKeyfob.Status AS KeyfobStatus, dbo.tblCustomer.CitrixUsername, dbo.tblCustomer.DisabledDate, 
                      CASE WHEN dbo.tblCustomer.ActiveFlag = 0 THEN 'InActive' ELSE 'Active' END AS UserStatus
FROM         dbo.tblKeyfob INNER JOIN
                      dbo.tblCustomer ON dbo.tblKeyfob.KeyfobID = dbo.tblCustomer.KeyfobID
ORDER BY dbo.tblKeyfob.KeyfobNo

The problem is when I run the query in design view it orderd by KeyfobNo, but when I open View it doesnt order by Keyfob No.

Why dooes it behave like so?

Thank you

Its by design
You cant guarantee the order of results from a view by just including ORDER BY in the definition from sql 2005 onwards
For that you need to add ORDER BY in the select statement which uses the view

ie


SELECT columns...
FROM View
ORDER BY ...

see

http://visakhm.blogspot.com/2010/01/behaviour-of-order-by-inside-view.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-10-07 : 09:11:41

Still same problem

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 09:35:55

quote:
Originally posted by dr223

Still same problem

what do you mean by that?
do you mean even after using ORDER BY in SELECT statement containing the view?
also whats the datatype of KeyfobNo?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-10-07 : 12:22:14

Keyfob no datatype is numeric..

An dyes even if am reday it from the view and and order by - it still doesnt sort it out...

Why?

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 12:58:32

If you have the order by query in the final select, it should/will order. However, if it is not ordering as you expected it to, see what the data type of the column is. The most common thing that you see is the case where a column is of character type, and the data in the column is all numeric. In such cases, it would do a string sort - which means 9 would be considered greater than 10 etc.

If that indeed is the case, you might try this:

ORDER BY CAST(KeyfobNo AS INT)

Be sure to put the order by clause in the outermost query - i.e., where you are selecting from the view. Also, this assumes that everything in the KeyfobNo column can be converted to an int. Otherwise this will fail.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 23:00:44

quote:
Originally posted by dr223

Keyfob no datatype is numeric..

An dyes even if am reday it from the view and and order by - it still doesnt sort it out...

Why?

Thanks

show us some sample data and explain what you mean by not sorting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources