October 25, 2012 at 11:48 am
I have seen some code somewhere long ago but cant find it anywhere now.
something like
create type boolean
from bit not null
'True' = 1
'False' = 0
i don't remember the syntax but the purpose was to reference a bit column as True or False
select * from tbl1 where my_bit_column = True
instead of
Select * from tbl1 where my_bit_column = 'True' -- {or = 1}
October 25, 2012 at 12:19 pm
IS this what you mean,
Select Case when bit = 1 then 'True' Else 'False' END
October 25, 2012 at 1:24 pm
no the code created a data type that the code could reference it as True or False
it allowed sql syntax to work
select * from tbl1 where bit_column = true
I have a delphi app(old program from 2000) that pushes a select statement like that and it is failing. been trying to find either the UDF for sql (old sql server long gone and can't get the UDF back) or the code for that delphi program so i can change it if i cant get the UDF in place.
October 25, 2012 at 2:21 pm
roy.tollison (10/25/2012)
no the code created a data type that the code could reference it as True or Falseit allowed sql syntax to work
select * from tbl1 where bit_column = true
I have a delphi app(old program from 2000) that pushes a select statement like that and it is failing. been trying to find either the UDF for sql (old sql server long gone and can't get the UDF back) or the code for that delphi program so i can change it if i cant get the UDF in place.
What datatype is bit_column?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 25, 2012 at 3:15 pm
It is a bit
October 25, 2012 at 3:22 pm
roy.tollison (10/25/2012)
It is a bit
If it is a bit then this custom type is not being used. Can you post the table definition and the query?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 25, 2012 at 3:26 pm
I am relaying this from memory but I thought it was a UDF that allowed the sql server to recognize True/False as a valid variable in the sql statement but i could be wrong.
The delphi app basically creates the database, adds some tables and columns. Gets some data and puts it into those tables. several of the columns are bit data types and my program bombs out on several of the queries saying sql syntax True is invalid. So i did some testing thru T-SQL and saw that bit_column = 'True' worked but bit_column = True errors out.
This delphi app was written back in MSSQL 7 and I couldn't find any version related changes to sql syntax in regards to True/False.
I then remembered that we had a script that the original developer had and it had some create type statements in it but I don't remember any of the details. Just that we had the some kind of problem back then and he ran that script and the problem was gone, so I am assuming the same issue is happening now as back then.
October 26, 2012 at 7:44 am
roy.tollison (10/25/2012)
I am relaying this from memory but I thought it was a UDF that allowed the sql server to recognize True/False as a valid variable in the sql statement but i could be wrong.The delphi app basically creates the database, adds some tables and columns. Gets some data and puts it into those tables. several of the columns are bit data types and my program bombs out on several of the queries saying sql syntax True is invalid. So i did some testing thru T-SQL and saw that bit_column = 'True' worked but bit_column = True errors out.
This delphi app was written back in MSSQL 7 and I couldn't find any version related changes to sql syntax in regards to True/False.
I then remembered that we had a script that the original developer had and it had some create type statements in it but I don't remember any of the details. Just that we had the some kind of problem back then and he ran that script and the problem was gone, so I am assuming the same issue is happening now as back then.
Maybe it was creating an enumeration in Delphi?
Like this...
type
SQLBoolean = (True = 1, False = 0);
The strings 'True' and 'False' will work with bit datatypes. If your column is defined as a bit then it is NOT a custom sql type or it would be defined as your custom boolean.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 26, 2012 at 8:47 am
you are on to something there because i have the database and some of the tables but not all of them. the tables missing are the ones with the bit column, so presumable the script created the boolean datatype based upon bit and then some other option allowed the True/False.
Do you happen to know this might be accomplished?
October 26, 2012 at 8:54 am
roy.tollison (10/26/2012)
you are on to something there because i have the database and some of the tables but not all of them. the tables missing are the ones with the bit column, so presumable the script created the boolean datatype based upon bit and then some other option allowed the True/False.Do you happen to know this might be accomplished?
Again a bit column is not a user defined type. If the column is bit then whatever shortcut that was being used is not in sql, it would have to be in your delphi app. I don't know of anyway that you can make True and False be constants in sql without making them strings 'True' and 'False'.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply