On 12/20/08, Simon Davies <simon.james.dav...@googlemail.com> wrote:
> 2008/12/20 Rachmat Febfauza <mathi...@yahoo.com>:
> >
>
> > 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)
> >
> > A1221 SMALL FOOD CAKE HOMS 1 2007-5-6 11:31:57 2007-5-6
> 11:31:57 0 (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
