• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu
Menu

Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Show posts Menu

Messages - Andrew Lindsay

#1
Hello, I have a situation now where the amount of data generated is getting quite large and I would like to be able to spilt the data from a large table into smaller tables.
My database looks like the following

Table1
ID1 as INTEGER
FixedID as STRING *12
FreeID as STRING * 12
Data1 as REAL
Data2 as REAL
.
.
.
Data86 as REAL

For each FixedID, I have lots of FreeID elements, so I would like to create tables where each table would have the FreeID and subsequent data as the elements of the table.

So if I had 36 rows of records with three (3) unique FixedIDs, I'd like to put the associated FreeID records into the three tables.

I hope this makes sense.

Best regards

Andrew
#2
Hello,
I have two tables, one table has the following structure-


Table 1
ID
Text1
Text2
Text3
Data1
Data2
Data3
Data4 - Currently NULL
Data5 - Currently NULL
Table 2
ID
Text1
Text2
Text3
Data4
Data5

I would like to update the data in Table 1 (update the NULL data) where there is data for the matching ID rows.

Ideally I would like to know if there is a single SQL statement that would perform this operation, or do I need to do it manually/programatically?

Any assistance would be appreciated.
#3
Rolf,

That's perfect!!!!!

Thanks for your help.

Regards

Andrew
#4
Hello,

I have an extremely large table (over 19,000,000 entries).  I would like to have a quick way to keep tally of the numbers of records.  I have tried 'Select Count(*) from tTable;' but this takes nearly 5 minutes to recover the number of records.

I would like to set up a second table called 'Status', with a single entry called Records, that would be incremented when a record is added, and decremented when a record is deleted.

I have read that triggers can be set up to modify a figure in another table, but I am not sure how to do this for inserts or deletes.  Any assistance would be appreciated.

Best regards

Andrew Lindsay
#5
Greetings again,

I have a table that has the following set-up

RecordTime DATETIME,
extTens1 INTEGER,
Pitch1 REAL,
Pitch2 REAL,
Pitch3 REAL,
Roll1 REAL,....

The RecordTime is set as a unique key and index, and the values are stored every second, from some time in February until the beginning of October.

I would like to create a table say for Pitch 1, that would extract all the Pitch1 results per month, so I would have a table with newRecordTime, FebPitch1, MarPitch1, ...

Any assistance would be appreciated.

Regards

Andrew
#6
I have a table that has a DateTime field and an Integer field.  I want to I have imported (approximately) 4 GB of CSV text into the database, but now find that there are a considerable (maybe 30%) of duplicates.  I would like to know if there is a way to find and delete duplicate data (based on the DateTime record).

Best regards

Andrew Lindsay
#7
If I have a table with RecordTime set as a date time format, how do I add say 10 minutes to every record in Record time?

Regards

Andrew
#8
Fred,

Do you have an example of a prepared SQLite statement, using 'placeholders'?

Regards

Andrew
#9
OK, I need some help here, and I can't quite grasp what the manual is saying.

I have 4 different CSV files, all in the format of

DateTime, Real.

They contain data every second for Tension, Pitch Roll and Heave, so each idividual file would be a series of entries as such-


Tension.CSV

RecordTime   Tension
2009-01-01 00:00:0073
2009-01-01 00:00:0167
2009-01-01 00:00:0353
2009-01-01 00:00:0472
2009-01-01 00:00:0590
2009-01-01 00:00:0877
2009-01-01 00:00:0947
2009-01-01 00:00:1019
2009-01-01 00:00:1190
2009-01-01 00:00:1378
2009-01-01 00:00:1482
2009-01-01 00:00:1550
2009-01-01 00:00:1721
2009-01-01 00:00:1884
   

Pitch.CSV

RecordTimePitch
39814.00001157410.635480334372697
39814.00003472220.651912825162737
39814.00004629630.960518554952992
39814.00005787040.0918474916201155
39814.00006944450.319918163823353
39814.00008101850.941540053614541
39814.00009259260.746957004452512
39814.00011574080.907714781997423
39814.00012731480.825096233881558
39814.00013888890.623002920037419
39814.0001504630.0988242513928097
39814.00018518520.423528689520101
39814.00019675930.164773031247083


Roll.CSV

RecordTimeRoll
398140.226449782983676
39814.00003472220.508779557441737
39814.00005787040.0975702516256709
39814.00008101850.695856147544947
39814.00010416670.554725874769032
39814.00011574080.0694166052752716
39814.00013888890.0500894191504688
39814.00017361120.686395584500251
39814.00018518520.106952609077364
39814.00019675930.7232638244345
39814.00020833340.874211940270543



Heave.CSV

RecordTimeHeave
398140.533813121206404
39814.00001157410.550086162941062
39814.00002314820.824738836166552
39814.00003472220.328173923893668
39814.00004629630.390095363264371
39814.00008101850.752438962289054
39814.00009259260.192342174956707
39814.00010416670.601769796791728
39814.00011574080.444666057148828
39814.00012731480.310802052318191
39814.00013888890.0131939787769038
39814.00016203710.285008667883267
39814.00017361120.526707486478498
39814.00019675930.887415995622519
39814.00020833340.432657835526106


For a combined table which would look like


RecordTimeTensionPitchRollHeave
3981473$NULL0.2264497829836760.533813121206404
39814.0000115741670.635480334372697$NULL0.550086162941062
39814.0000231482$NULL$NULL$NULL0.824738836166552
39814.0000347222530.6519128251627370.5087795574417370.328173923893668
39814.0000462963720.960518554952992$NULL0.390095363264371
39814.0000578704900.09184749162011550.0975702516256709$NULL
39814.0000694445$NULL0.319918163823353$NULL$NULL
39814.0000810185$NULL0.9415400536145410.6958561475449470.752438962289054
39814.0000925926770.746957004452512$NULL0.192342174956707
39814.000104166747$NULL0.5547258747690320.601769796791728
39814.0001157408190.9077147819974230.06941660527527160.444666057148828
39814.0001273148900.825096233881558$NULL0.310802052318191
39814.0001388889$NULL0.6230029200374190.05008941915046880.0131939787769038
39814.000150463780.0988242513928097$NULL$NULL
39814.000162037182$NULL$NULL0.285008667883267
39814.000173611250$NULL0.6863955845002510.526707486478498
39814.0001851852$NULL0.4235286895201010.106952609077364$NULL
39814.0001967593210.1647730312470830.72326382443450.887415995622519
39814.000208333484$NULL0.8742119402705430.432657835526106


Any assistance would be appreciated.

Regards

Andrew
#10
Yes,

Thanks guys.  I've got that one sorted out.  I thought that my problem was that I was using single quotes and not double quotes, however, the reason for my error was that I was using a date format of YYYY/MM/DD instead of YYYY-MM-DD.  i,e.

  sqlQuery = "SELECT COUNT(*) As RowCount FROM Data Where RecordTime >= '2009/04/01T00:00:00' AND RecordTime < '2009/04/01T01:00:00'"
 
This should read

  sqlQuery = "SELECT COUNT(*) As RowCount FROM Data Where RecordTime >= '2009-04-01T00:00:00' AND RecordTime < '2009-04-01T01:00:00'"

Then it all works out fine...




Now I have moved onto the second part of the question where I am trying to use the criteria from one table as a basis for a query on another table.

Regards

Andrew
#11
OK,

I stumbled across the answer to above, it is a force of habit to format dates with a '/' character, but it needs to be a '-' character...

Now...

I am about to head offshore tonight so I will get my question in now, because I know I am not going to be able to solve it myself.

I have two tables.

Table one contains the monitoring data at minute intervals.  The table looks like this.






Time    Hs        Vw        Tw   
2009-04-01 00:00:00    0.4        20        45   
2009-04-01 00:01:00    0.5        30        20   
2009-04-01 00:02:00    0.4        30        45   
...    ...        ...        ...   

Table two contains my reporting criteria








Start Timeend Time    Hs_crit        Vw_crit        Tw_crit   
2009-04-01 00:00:002009-04-01 01:00:00    0.5        30        40   
2009-04-01 01:00:002009-04-01 02:00:00    0.25        30        20   
2009-04-01 02:00:002009-04-01 03:00:00    0.3        30        30   
...............

For each Criteria Row, I want to count the number of entries that exceed the criteria, i.e. the results would be something like...







Start TimeEnd TimeCount
2009-04-01 00:00:002009-04-01 01:00:0035
2009-04-01 01:00:002009-04-01 02:00:0060
2009-04-01 02:00:002009-04-01 03:00:000
.........

Any assistance in this would be greatly appreciated.

Regards

Andrew
#12
I am having trouble with the following Query.

  sqlQuery = "SELECT COUNT(*) As RowCount FROM Data Where RecordTime >= '2009/04/01T00:00:00' AND RecordTime < '2009/04/01T01:00:00'"
  Replace "'" With $Dq In sqlQuery
  slSEL(sqlQuery,1)
  slGetRow(1)
  NumberOfRows = Val(slFN("RowCount",1))
  slCloseSet(1)


I should get an answer of 60 (I have data for every minute), however, I get an answer of 0.  Any assistance would be appreciated.

Regards


Andrew
#13
Hopefully the last question for tonight.

How do I insert the following value into a table with a field that is labeled as TIME.

The Value is "30/04/2009 0:00".

I do not get any errors when I insert the record, but when I look at the value in SQLite Maestro, it seems to have stored the value as "20:09:00".  Any asistance would be appreciated.

Regards

Andrew
#14
Thanks all kindly.

That has solved it perfectly.

Regards

Andrew
#15
I am connecting to a database and I do not know if there is a table present, how do I check if a table is present, and if not, create it?

I tried "If Not Exists FooBar Create FooBar ( Column1 TEXT, Column2 TEXT );"

Any asistance would be appreciated.

Regards

Andrew