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] 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
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
2008/12/23 Rachmat Febfauza: > = > > 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). No. "Begin" is part of the 'group by' clause. Each distinct value of "Begin" will be represented in the result set. > 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. Not sure. Can see no reason for there to be a significant performance difference. > 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. It does not give the same result with your originally posted data set. The like operator matches similar text values; '%' is a wildcard. See http://www.sqlite.org/lang_expr.html#like > > once again, i would say thank you for ur solution. sory for my bad english. > You're welcome, Rgds, Simon ___ 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
2008/12/21 Rachmat Febfauza: > > 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 > 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: select awal1.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 where awal1.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 ___ 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
On 12/20/08, Simon Davieswrote: > 2008/12/20 Rachmat Febfauza : > > > > > 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? > > > It looks like you are using the sqlite3 shell, so experiment with .separator > > Have you used .help? > seems like Rachmat is having a problem with the time difference being calculated with strftime("%s",akhir1."End")-strftime("%s",awal1."Begin") as Difference from awal1 MySQL is giving 0 for A1221 while SQLite is giving 649. Rachmat, you are specifying akhir1."End" >= awal1."Begin" in your WHERE clause, so why are you surprised at getting the following row? A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649 The above row satisfies your constraint. Change the constraint to akhir1."End" = awal1."Begin" and you will get only the following row in your result A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:31:57|0 just like in MySQL > Rgds, > > Simon > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ 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
2008/12/20 Rachmat Febfauza: > > 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? It looks like you are using the sqlite3 shell, so experiment with .separator Have you used .help? Rgds, Simon ___ 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
Re: [sqlite] confusing with how to to this in sqlite
On Dec 19, 2008, at 9:06 PM, Simon Davies wrote: >> >> how to make result like i hope? > > 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. >> Nice work. Thanks for the analysis, Simon! 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 D. Richard Hipp d...@hwaci.com ___ 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
2008/12/20 Rachmat Febfauza: > 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:46 > 2007-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? 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. > > 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
[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