Strange Question About Boolean Data Types In Sql... 1

[nikeloeleven]

Hi guys,

I was using SQL to create a field called "StatusofTarget" (status of target) .....

i want the data type for this field to be boolean so that the boolean values can be set as either "COMPLETED" or "PROGRESSING"... but i realised that SQL in interbase doesnt recognise BOOLEAN data type...
is there a way to set boolean data types for fields?

"StatusofTarget boolean Not Null,"

doesnt work in sql..

please can you tell me how to set BOOLEAN data types in sql ... and the two values to be "COMPLETED" or "PROGRESSING" instead of YES/NO or True/False

 

[willir]

COMPLETED and PROGRESSING are more of a status type, not a boolean. Use of a Boolean field is more like...
COMPLETED - yes / no

And SQL statement would be more like...
SELECT ... FROM ... WHERE Completed = True
(The definition of a boolean field is true/false, the yes/no thing is so that it is more easily understood by some)

vs...
SELECT ... FROM ... WHERE StatusofTarget = "Completed"

As for creating the boolean field in Interbase, can't help you there. My approach, it creating tables programmatically, with VBA for Access...

Function CreateATable() As Boolean

Dim dbs As DAO.Database, tdf As TableDef
Dim fld As Field, idx As Index

Set dbs = CurrentDb()

Set tdf = dbs.CreateTableDef("tblYourTable")

With tdf
    .Fields.Append .CreateField("YourPKTextField1", dbText, 10)
    .Fields.Append .CreateField("YourIntegerField1", dbInteger)
    .Fields.Append .CreateField("YourBooleanField1", dbBoolean)
    .Fields.Append .CreateField("YourLongIntField1", dbLong)
    .Fields.Append .CreateField("YourSingleField1", dbSingle)
    .Fields.Append .CreateField("YourDateField1", dbDate)
End With

[tt]
'Other date types:
' dbBigInt  dbBinary  dbByte   dbChar   dbCurrency    dbDouble
' dbFloat   dbGUID    dbMemo   dbTime   dbLongBinary  dbTimeStamp
' dbVarBinary
[/tt]
dbs.TableDefs.Append tdf
Set idx = tdf.CreateIndex("PrimaryKey")
'Set fld = idx.CreateField("YourPKTextField1")
idx.Fields.Append idx.CreateField("YourPKTextField1")
idx.Primary = True
idx.Unique = True
tdf.Indexes.Append idx

CreateATable = True

End Function

Richard

 

[alvechurchdata]

the boolean values can be set as either "COMPLETED" or "PROGRESSING"

Don't use boolean. Store them as CHAR(11). It will take more space but you'll be very glad you did when the user asks you to add a "CANCELLED" category.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[r937]

excellent advice, nicely presented

star for you, geoff

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[nikeloeleven]

thx a lot

but i dont want the user to be able to enter any more values other than progressing or completed so how will char do this?

 

[alvechurchdata]

but i dont want the user to be able to enter any more values other than progressing or completed so how will char do this?{/quote]
Char won't do this by itself. You'll have to add some sort of validation to the field to restrict its values.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[r937]

use a CHECK CONSTRAINT

i did a quick search on google and apparently interbase supports them

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[nikeloeleven]

what's a check constraint... and how does it work please....

 

[r937]

nikeloeleven, i've noticed you're asking a lot of questions which could easily be answered by a quick perusal of the manual

you do have a manual for interbase, right?

if not, why not?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[nikeloeleven]

im sorry for asking so many questions

i do not have an iterbase manual but im trying to get one

ive searched on the net for some of my problems but its hard to find what im looking for

thnx

 

[r937]

less than 10 seconds on google (my time, not google's time, google took like a few milliseconds) and i found one --

http://www.channelcrm.dk/DownloadFiles/SQLRef.html

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)