2008/12/20 Rachmat Febfauza <mathi...@yahoo.com>:
> 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
>
> A1236 MEDIUM FOOD SNACK HOMS 1 2007-5-6 10:48:57 2007-5-6
> 11:19:25 1828
> A1236 MEDIUM FOOD SNACK HOMS 2 2007-5-6 10:48:57 2007-5-6
> 11:19:21 1824
> A1222 SMALL FOOD WAFER HOMS 1 2007-5-6 11:20:34 2007-5-6
> 11:31:57 683
> A1222 SMALL FOOD WAFER HOMS 2 2007-5-6 11:20:34 2007-5-6
> 11:31:57 683
> A1221 SMALL FOOD CAKE HOMS 1 2007-5-6 11:31:57 2007-5-6
> 11:31:57 0
> A1221 SMALL FOOD CAKE HOMS 2 2007-5-6 11:31:57 2007-5-6
> 11:31:57 0
> A1220 SMALL FOOD MARGARINE HOMS 1 2007-5-6 11:42:46
> 2007-5-6 11:42:46 0
> A1221 SMALL FOOD CAKE HOMS 1 2007-5-6 11:42:46 2007-5-6
> 11:42:46 0
> A1222 SMALL FOOD WAFER HOMS 1 2007-5-6 11:42:46 2007-5-6
> 11:42:46 0
> A1269 SMALL CLOTHES BELT HOMS 3 2007-5-7 17:28:25 2007-5-7
> 17:28:27 2
>
>
> 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 temporary, it tooks
> 100% cpu usage but less PF or memory usage.
>
Rgds,
Simon
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
