MySQL Forums
Forum List  »  SQLite

this query in sqlite
Posted by: rachmat febfauza
Date: December 19, 2008 07:34PM

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?

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
this query in sqlite
9587
December 19, 2008 07:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.