Re: [sqlite] confusing with how to to this in sqlite

[Rachmat Febfauza]

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

[Griggs, Donald]

 

-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

[Rachmat Febfauza]

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

[Simon Davies]

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

[Rachmat Febfauza]

=
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

[Simon Davies]

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

[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


  
___
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

[P Kishor]

On 12/20/08, Simon Davies  wrote:
> 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

[Simon Davies]

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

[Rachmat Febfauza]

> 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

[D. Richard Hipp]

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

[Simon Davies]

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

[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: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