[sqlite] temporay file
my query take hundreds of MB temporary file if i execute this query. why sqlite make enourmous big temporary table. my table in only 9 MB and have 12000 row. then i compare with mysql again, it's not make big temp table too much. my query is in topic "[sqlite] confusing with how to to this in sqlite" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confusing with how to to this in sqlite
later i consider that my query take hundreds of MB temporary file if i execute this query. why sqlite make big temporary table is enormous big. my table in only 9 MB and have 12000 row. then i compare with mysql again, it's not make big temp table too much - Original Message From: "Griggs, Donald" <donald.gri...@allscripts.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Tuesday, December 30, 2008 2:52:11 AM Subject: Re: [sqlite] confusing with how to to this in sqlite -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rachmat Febfauza Sent: Sunday, December 28, 2008 9:13 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] confusing with how to to this in sqlite thanks simon for the explanation. after holiday, i works on how to optimize my query. actually awal1 table consist 12000 rows and akhir1 too. how to improve performance? i added index on table awal1 and akhir1 with following syntax : create index awal1i1 on awal1(Code,Category,Product,Location,"Begin"); create index akhir1i1 on akhir1(Code,Category,Product,Location,"End"); is this create index syntax right? or i must specify each column with individual index?? like create index awal1i1 on awal1(Code); create index awal1i2 on awal1(Product); etc and i want to know to to improve performance of my query? some hint? i have one question again, is sqlite suitable for large database file? coz my apps may grow up to 1 giga database file. thanks again = = Regarding syntax: If you don't get an error, the syntax is acceptible.;-) Sqlite *does* support compound indicies. However: -- You may want to use "EXPLAIN QUERY PLAN" as a prefix to your SELECT (just running as a test) to ensure than your index is used. -- You can quickly experiment with using a simple index on "BEGIN" or "PRODUCT" instead and measure times. -- As you measure times, be aware of possible "caching effects" -- i.e. the first run may be slower than subsequent runs of the a query on the same tables. -- Make sure you see the link on performance, below. -- Make sure you include many INCLUDES within a single TRANSACTION (if appropriate to your application). This can make a dramatic difference. -- You want to be familiar with the PRAGMA's that can affect performance. http://www.sqlite.org/pragma.html (but note that some of these can be used to trade data safety for performance -- make sure you're making an informed choice) Regarding: " is sqlite suitable for large database file? coz my apps may grow up to 1 giga database file." Have you read http://www.sqlite.org/whentouse.html And http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations ? If not, you'll want to. Many folks successfully run sqlite on multi-gigabyte databases, BUT -- in those cases, the simplicity and small footprint of sqlite may be less compelling, -- Are there any features in http://www.sqlite.org/omitted.html that you will grieve in their absence? You might go over the detailed feature lists for postgres, Mysql, etc. with the same question in mind. -- How much concurant access do you anticipate? -- Will you control the queries (so as to optimize them and the indicies) or will the database be subjected frequently to ad hoc queries (which *might* benefit from a sophisticated query optimizer)? Hope this helps, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cancel query in sqlite
I don't know What that is in the Delphi wrapper you use. === i use zeos ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] cancel query in sqlite
how to cancel query in sqlite specially in delphi, coz i execute query from delphi code. thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confusing with how to to this in sqlite
thanks simon for the explanation. after holiday, i works on how to optimize my query. actually awal1 table consist 12000 rows and akhir1 too. how to improve performance? i added index on table awal1 and akhir1 with following syntax : create index awal1i1 on awal1(Code,Category,Product,Location,"Begin"); create index akhir1i1 on akhir1(Code,Category,Product,Location,"End"); is this create index syntax right? or i must specify each column with individual index?? like create index awal1i1 on awal1(Code); create index awal1i2 on awal1(Product); etc and i want to know to to improve performance of my query? some hint? i have one question again, is sqlite suitable for large database file? coz my apps may grow up to 1 giga database file. thanks again ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confusing with how to to this in sqlite
=
Without the group by clause the result set is:
sqlite> select awal1.Code, awal1.Level, awal1.Category, awal1.Product,
awal1.Location, awal1."Begin",akhir1."End", strftime("%s",akh
ir1."End")-strftime("%s",awal1."Begin") as Difference from awal1,
akhir1 where awal1.Code = akhir1.Code and akhir1.Category like awa
l1.Category || '%' and awal1.Product = akhir1.Product and
awal1.Location = akhir1.Location and akhir1."End" >= awal1."Begin";
A1220SMALLFOODMARGARINEHOMS 12007-05-06 11:42:46
2007-05-06 11:42:460
A1221SMALLFOODCAKEHOMS 22007-05-06 11:31:57
2007-05-06 11:31:570
A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57
2007-05-06 11:31:570
A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57
2007-05-06 11:42:46649
A1221SMALLFOODCAKEHOMS 12007-05-06 11:42:46
2007-05-06 11:42:460
A1222SMALLFOODWAFERHOMS 22007-05-06 11:20:34
2007-05-06 11:31:57683
A1222SMALLFOODWAFERHOMS 12007-05-06 11:20:34
2007-05-06 11:31:57683
A1222SMALLFOODWAFERHOMS 12007-05-06 11:20:34
2007-05-06 11:42:461332
A1222SMALLFOODWAFERHOMS 12007-05-06 11:42:46
2007-05-06 11:42:460
A1236MEDIUMFOODSNACKHOMS 22007-05-06 10:48:57
2007-05-06 11:19:211824
A1236MEDIUMFOODSNACKHOMS 12007-05-06 10:48:57
2007-05-06 11:19:251828
A1269SMALLCLOTHESBELTHOMS 32007-05-07 17:28:25
2007-05-07 17:28:272
The group by clause combines rows
A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57
2007-05-06 11:31:570
A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57
2007-05-06 11:42:46649
into 1 row. The values in the columns not included in the group by
clause ("Begin", "End" and Difference) could be from any of the
combined rows (which rows is not, I believe, specified in any
standard). MySql and Sqlite seem to result in different selections. If
you want specific rows, then you need to modify the query to control
the data selection. In this case it appears that:
selectawal1.Code,
awal1.Level,
awal1.Category,
awal1.Product,
awal1.Location,
awal1."Begin",
min( akhir1."End" ),
min( strftime("%s",akhir1."End")-strftime("%s",awal1."Begin") ) as
Difference
fromawal1, akhir1
whereawal1.Code = akhir1.Code and
akhir1.Category like awal1.Category || '%' and
awal1.Product = akhir1.Product and
awal1.Location = akhir1.Location and
akhir1."End" >= awal1."Begin"
group byawal1."Begin",
awal1.Code,
awal1.Category,
awal1.Product,
awal1.Location;
gives the result you want.
Rgds,
Simon
=
thanks to Simon. it works. but i have some question. i hope u can help me
to explain these :
1. is it necessary or not to specify min(awal1.begin).
2. is "min(strftime("%s",akhir1."End")-strftime("%s",awal1."Begin")) as
Difference" is more time consuming to compute than
"strftime("%s",min(akhir1."End"))-strftime("%s",awal1."Begin") as Difference".
i think these give the same result.
3. akhir1.Category like awal1.Category || '%'. what does it mean? why is use ||
'%'???. why not to write "awal1.category = akhir1.category". it also give the
same result.
once again, i would say thank you for ur solution. sory for my bad english.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confusing with how to to this in sqlite
It looks like you are using the sqlite3 shell, so experiment with .separator Have you used .help? Rgds, Simon yes i am using sqlite3 shell, i mean not to make display like mysql does, but the difference column that i want like mysql does. take a look at this different A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649 (this is done with sqlite) A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6 11:31:570 (this is done with mysql) why in sqlite we got (649) : 2007-05-06 11:31:57|2007-05-06 11:42:46|649 and in mysql we got (0) : 2007-5-6 11:31:572007-5-6 11:31:570 or i think the reason is like i found in mysql documentation like this : "If you use LEFT JOIN to find rows that do not exist in some table and you have the following test: col_name IS NULL in the WHERE part, where col_name is a column that is declared as NOT NULL, MySQL stops searching for more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition. " i also have test with left join but the result don't like mysql does. sory for my less knowledge thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confusing with how to to this in sqlite
> The result should not be as you are expecting because column category > in table awal1 contain values 'FOOD' and column category in table > akhir1 contains values 'FOOD ' (an additional space character). If the > category columns (containijng 'FOOD') contain exactly the same text > then you would get your expected result. >> i have to change 'FOOD ' to 'FOOD', but the result is not that i hope. Rachmat: If what Simon says is correct (and I have no reason to doubt it) you might also get your query to work by specifying a collating sequence of RTRIM on the category column. http://www.sqlite.org/datatype3.html#collation i also spesifiy collate in create table awal1 and akhir1 like this : CREATE TABLE awal1(Code char(5) collate RTRIM,Level varchar(8) collate RTRIM, Category varchar(50) collate RTRIM, Product varchar(60) collate RTRIM, Location varchar(50) collate RTRIM, "Begin" datetime); CREATE TABLE akhir1(Code char(5) collate RTRIM,Level varchar(8) collate RTRIM,Category varchar(50) collate RTRIM,Product varchar(60) collate RTRIM, Location varchar(50) collate RTRIM,"End" datetime); but the result is not my hope. the results are : A1236|MEDIUM|FOOD|SNACK|HOMS 1|2007-05-06 10:48:57|2007-05-06 11:19:25|1828 A1236|MEDIUM|FOOD|SNACK|HOMS 2|2007-05-06 10:48:57|2007-05-06 11:19:21|1824 A1222|SMALL|FOOD|WAFER|HOMS 1|2007-05-06 11:20:34|2007-05-06 11:42:46|1332 A1222|SMALL|FOOD|WAFER|HOMS 2|2007-05-06 11:20:34|2007-05-06 11:31:57|683 A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649 A1221|SMALL|FOOD|CAKE|HOMS 2|2007-05-06 11:31:57|2007-05-06 11:31:57|0 A1220|SMALL|FOOD|MARGARINE|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0 A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0 A1222|SMALL|FOOD|WAFER|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0 A1269|SMALL|CLOTHES|BELT|HOMS 3|2007-05-07 17:28:25|2007-05-07 17:28:27|2 take a look at this different A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649 (this is done with sqlite) A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6 11:31:570 (this is done with mysql) how to solve this? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] confusing with how to to this in sqlite
I have problem with executing this query in sqlite. to reconstruct problem
please follow the following steps.
1. create table awal1, akhir1 and hasil1 first.
CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), Product
varchar(60), Location varchar(50), "Begin" datetime);
INSERT INTO `awal1` VALUES ('A1220', 'SMALL', 'FOOD', 'MARGARINE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 2',
'2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1',
'2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 2',
'2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1',
'2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 2',
'2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 1',
'2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3',
'2007-05-07 17:28:25');
CREATE TABLE akhir1(Code char(5),Level varchar(8),Category varchar(50),Product
varchar(60), Location varchar(50),"End" datetime);
INSERT INTO `akhir1` VALUES ('A1220', 'SMALL', 'FOOD ', 'MARGARINE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 2',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 2',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 2',
'2007-05-06 11:19:21');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 1',
'2007-05-06 11:19:25');
INSERT INTO `akhir1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3',
'2007-05-07 17:28:27');
CREATE TABLE hasil1 (Code char(5), Level vachar(8), Category varchar (50),
Product varchar(60), Location varchar(50), "Begin" datetime, "End" datetime,
Difference integer, PRIMARY KEY
(Code,Level,Category,Product,Location,"Begin","End"));
2. then execute this query
insert or ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category,
awal1.Product, awal1.Location, awal1."Begin",akhir1."End",
strftime("%s",akhir1."End")-strftime("%s",awal1."Begin") as Difference from
awal1, akhir1 where awal1.Code = akhir1.Code and awal1.Category =
akhir1.Category and awal1.Product = akhir1.Product and awal1.Location =
akhir1.Location and akhir1."End" >= awal1."Begin" group by awal1."Begin",
awal1.Code, awal1.Category, awal1.Product, awal1.Location;
3. check the result
the result that i hope is like this
A1236MEDIUMFOODSNACKHOMS 12007-5-6 10:48:572007-5-6
11:19:251828
A1236MEDIUMFOODSNACKHOMS 22007-5-6 10:48:572007-5-6
11:19:211824
A1222SMALLFOODWAFERHOMS 12007-5-6 11:20:342007-5-6
11:31:57683
A1222SMALLFOODWAFERHOMS 22007-5-6 11:20:342007-5-6
11:31:57683
A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6
11:31:570
A1221SMALLFOODCAKEHOMS 22007-5-6 11:31:572007-5-6
11:31:570
A1220SMALLFOODMARGARINEHOMS 12007-5-6 11:42:462007-5-6
11:42:460
A1221SMALLFOODCAKEHOMS 12007-5-6 11:42:462007-5-6
11:42:460
A1222SMALLFOODWAFERHOMS 12007-5-6 11:42:462007-5-6
11:42:460
A1269SMALLCLOTHESBELTHOMS 32007-5-7 17:28:252007-5-7
17:28:272
how to make result like i hope?
thanks for advanced
note : the result that i hope is mysql result's, so i confuse how to do this in
sqlite. Why i prefer sqlite other than mysql, coz if i execute about 12000 rows
in mysql server, my computer starts not responding temporary, it tooks 100% cpu
usage but less PF or memory usage.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
I have problem with executing this query in sqlite. to reconstruct problem
please follow the following steps.
1. create table awal1, akhir1 and hasil1 first.
CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), Product
varchar(60), Location varchar(50), "Begin" datetime);
INSERT INTO `awal1` VALUES ('A1220', 'SMALL', 'FOOD', 'MARGARINE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 2',
'2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1',
'2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 2',
'2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1',
'2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 2',
'2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 1',
'2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3',
'2007-05-07 17:28:25');
CREATE TABLE akhir1(Code char(5),Level varchar(8),Category varchar(50),Product
varchar(60), Location varchar(50),"End" datetime);
INSERT INTO `akhir1` VALUES ('A1220', 'SMALL', 'FOOD ', 'MARGARINE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 2',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 2',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 2',
'2007-05-06 11:19:21');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 1',
'2007-05-06 11:19:25');
INSERT INTO `akhir1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3',
'2007-05-07 17:28:27');
CREATE TABLE hasil1 (Code char(5), Level vachar(8), Category varchar (50),
Product varchar(60), Location varchar(50), "Begin" datetime, "End" datetime,
Difference integer, PRIMARY KEY
(Code,Level,Category,Product,Location,"Begin","End"));
2. then execute this query
insert or ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category,
awal1.Product, awal1.Location, awal1."Begin",akhir1."End",
strftime("%s",akhir1."End")-strftime("%s",awal1."Begin") as Difference from
awal1, akhir1 where awal1.Code = akhir1.Code and awal1.Category =
akhir1.Category and awal1.Product = akhir1.Product and awal1.Location =
akhir1.Location and akhir1."End" >= awal1."Begin" group by awal1."Begin",
awal1.Code, awal1.Category, awal1.Product, awal1.Location;
3. check the result
the result that i hope is like this
A1236MEDIUMFOODSNACKHOMS 12007-5-6 10:48:572007-5-6
11:19:251828
A1236MEDIUMFOODSNACKHOMS 22007-5-6 10:48:572007-5-6
11:19:211824
A1222SMALLFOODWAFERHOMS 12007-5-6 11:20:342007-5-6
11:31:57683
A1222SMALLFOODWAFERHOMS 22007-5-6 11:20:342007-5-6
11:31:57683
A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6
11:31:570
A1221SMALLFOODCAKEHOMS 22007-5-6 11:31:572007-5-6
11:31:570
A1220SMALLFOODMARGARINEHOMS 12007-5-6 11:42:462007-5-6
11:42:460
A1221SMALLFOODCAKEHOMS 12007-5-6 11:42:462007-5-6
11:42:460
A1222SMALLFOODWAFERHOMS 12007-5-6 11:42:462007-5-6
11:42:460
A1269SMALLCLOTHESBELTHOMS 32007-5-7 17:28:252007-5-7
17:28:272
how to make result like i hope?
thanks for advanced
note : the result that i hope is mysql result's, so i confuse how to do this in
sqlite. Why i prefer sqlite other than mysql, coz if i execute about 12000 rows
in mysql server, my computer starts not responding temporary, it tooks 100% cpu
usage but less PF or memory usage.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to load in file and outfile
how to do load infile and into outfile like mysql does in sqlite? thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how sqlite use index
how to make sqlite use index? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how do this stuff in sqlite
anyone can help me? - Original Message From: Igor Tandetnik <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, November 27, 2008 12:07:31 PM Subject: Re: [sqlite] how do this stuff in sqlite "Rachmat Febfauza" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > i have query that don't work in sqlite but in mysql work and make > good result. Define "don't work". Do you get an error? What's the error text? > CREATE TABLE hasil1 (Code char(5), Level vachar(8), Category varchar > (50), Product varchar(60), Location varchar(50), Begin datetime, End > datetime, Difference integer, PRIMARY KEY > (Code,Level,Category,Product,Location,Begin,End)); Begin and End are keywords in SQLite (SQL is case insensitive). If you insist on naming your columns this way, you have to enclose the names in double quotes, just as you did when creating awal1 and akhir1. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how do this stuff in sqlite
Define "don't work". Do you get an error? What's the error text? i mean it can be run but the result doesn't like mysql do. whats wrong with my query? Begin and End are keywords in SQLite (SQL is case insensitive). If you insist on naming your columns this way, you have to enclose the names in double quotes, just as you did when creating awal1 and akhir1. thanks but, i think we don't need to quote begin or end coz this is some exceprtion. like mysql does. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how do this stuff in sqlite
i have query that don't work in sqlite but in mysql work and make good result.
sqlite :
table definition
CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), Product
varchar(60), Location varchar(50), "begin" datetime);
CREATE TABLE akhir1(Code char(5),Level varchar(8),Category varchar(50),Product
varchar(60), Location varchar(50),"end" datetime);
CREATE TABLE hasil1 (Code char(5), Level vachar(8), Category varchar (50),
Product varchar(60), Location varchar(50), Begin datetime, End datetime,
Difference integer, PRIMARY KEY
(Code,Level,Category,Product,Location,Begin,End));
query :
insert or ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category,
awal1.Product, awal1.Location, awal1.Begin, akhir1.End,
strftime("%s",akhir1.End)-strftime("%s",awal1.Begin) as Difference from awal1,
akhir1 where awal1.Code = akhir1.Code and awal1.Category = akhir1.Category and
awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and
akhir1.End >= awal1.Begin group by awal1.Begin, awal1.Code, awal1.Category,
awal1.Product, awal1.Location
data i attached in zip file.
mysql
table definition
CREATE TABLE `awal1` (
`Code` char(5) NOT NULL default '',
`Level` enum('SMALL','MEDIUM','BIG') NOT NULL default 'SMALL',
`Category` varchar(50) NOT NULL default '',
`Product` varchar(60) NOT NULL default '',
`Location` varchar(50) NOT NULL default '',
`Begin` datetime NOT NULL default '-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `akhir1` (
`Code` char(5) NOT NULL default '',
`Level` enum('SMALL','MEDIUM','BIG') NOT NULL default 'SMALL',
`Category` varchar(50) NOT NULL default '',
`Product` varchar(60) NOT NULL default '',
`Location` varchar(50) NOT NULL default '',
`End` datetime NOT NULL default '-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `hasil1` (
`Code` char(5) NOT NULL default '',
`Level` enum('SMALL','MEDIUM','BIG') NOT NULL default 'SMALL',
`Category` varchar(50) NOT NULL default '',
`Product` varchar(60) NOT NULL default '',
`Location` varchar(50) NOT NULL default '',
`Begin` datetime NOT NULL default '-00-00 00:00:00',
`End` datetime NOT NULL default '-00-00 00:00:00',
`Difference` int(8) NOT NULL,
PRIMARY KEY (`Code`,`Level`,`Category`,`Product`,`Location`,`Begin`,`End`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
query
insert ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category,
awal1.Product, awal1.Location, awal1.Begin, akhir1.End,
time_to_sec(timediff(akhir1.End, awal1.Begin)) as Difference from awal1, akhir1
where awal1.Code = akhir1.Code and awal1.Category = akhir1.Category and
awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and
akhir1.End >= awal1.Begin group by awal1.Begin, awal1.Code, awal1.Category,
awal1.Product, awal1.Location;
sory if my post messed up, sory for my bad english.
i attached data and this file or u can download at
http://rapidshare.com/files/167784565/up.zip.html
thanks 4 ur advanced
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
