Another BaseStation SQLite Question

[rikgale]

Is there a way of checking for this case and changing/forcing the "1" to be the correct format for the boolean (TBH - I am not sure if it should be a string or a number)

[jakems]

Hi

Interested is a boolean and is defined in Basestation as:

Interested boolean not null default 0,

So it should never have a value of null. As a boolean the test can be = true or = false. As Dave says SQLite does not enforce strong typing so depending on the app you are using you may find you get more consistent results using them. Please note I did say may!

[Pete]

Hi Dave

Apologies for the delayed reply as I've been away from my computer.  I've tried your suggested query and unfortunately, it returns a third row with 66 entries in it.

Is  there anyway of developing the sql script to produce a list of those 66 entries?

Regards

Pete

[DaveReid]

Probably.

What are the 3 values for Interested in your results table ?

[Pete]

Hi

The three rows have returned the following:

Rec No   Interested   count(*)
  1            (a Tick)     34439
  2            (blank)     41254
  3            (a Tick)     66

It seems (to me anyway) that the 66 entries in row 3 have had something other than a '1' entered into them.

I hope this helps.

Pete

[DaveReid]

SQLite Personal is telling us that there are two different non-zero values in the Interested column, both being interpreted as True - but it won't tell us what those values are.

Try the following (back up your SQB first):

Update Aircraft Set Interested = 1 where Interested <> 0

Then re-run the original query and report how many rows/values you get.

[Anmer]

If you use DB Browser it shows the actual values in the Interested column whereas SQLite Expert doesn't.

Using DB Browser you can sort on the Interested column which should show the records containing values other than 0 and 1.

[Pete]

Thanks to you both.  The third row has now gone completely and rows one and two display numbers I would expect to see.  The database has changed since I gave you the original figures so that's to be expected.  Thanks again.

Pete