from:"Giuseppe Costanzi"

Re: [sqlite] Student's t-test table

2014-11-16 Thread Giuseppe Costanzi

 2.011, 2.407,
2.682, 2.943, 3.269, 3.505);
INSERT INTO "distribuitions" VALUES('50', 1.299, 1.676, 2.009, 2.403,
2.678, 2.937, 3.261, 3.496);
INSERT INTO "distribuitions" VALUES('60', 1.296, 1.671, 2.0, 2.39,
2.66, 2.915, 3.232, 3.46);
INSERT INTO "distribuitions" VALUES('70', 1.294, 1.667, 1.994, 2.381,
2.648, 2.899, 3.211, 3.435);
INSERT INTO "distribuitions" VALUES('80', 1.292, 1.664, 1.99, 2.374,
2.639, 2.887, 3.195, 3.416);
INSERT INTO "distribuitions" VALUES('90', 1.291, 1.662, 1.987, 2.369,
2.632, 2.878, 3.183, 3.402);
INSERT INTO "distribuitions" VALUES('100', 1.29, 1.66, 1.984, 2.364,
2.626, 2.871, 3.174, 3.391);
INSERT INTO "distribuitions" VALUES('120', 1.289, 1.658, 1.98, 2.358,
2.617, 2.86, 3.16, 3.373);
INSERT INTO "distribuitions" VALUES('infinity', 1.282, 1.645, 1.96,
2.326, 2.576, 2.807, 3.09, 3.291);
COMMIT;

regards
1966bc

On Sun, Nov 16, 2014 at 9:37 AM, jungle Boogie <jungleboog...@gmail.com> wrote:
> Hi Giuseppe,
> On 16 November 2014 00:28, Giuseppe Costanzi <giuseppecosta...@gmail.com> 
> wrote:
>> if it serves to someone I have attached it
>>
>
> Attachments don't come through, post a link to the file.
>
>> regards
>> 1966bc
>>
>
> Best,
> jb
>
>
> --
> ---
> inum: 883510009027723
> sip: jungleboo...@sip2sip.info
> xmpp: jungle-boo...@jit.si
> ___
> 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] Student's t-test table

2014-11-16 Thread Giuseppe Costanzi

if it serves to someone I have attached it

regards
1966bc

On Sun, Nov 16, 2014 at 8:56 AM, Giuseppe Costanzi
<giuseppecosta...@gmail.com> wrote:
> Thanks Simon, I know how to perform the importation.
> However thanks of the idea.
> I start looking for it.
>
> On Sun, Nov 16, 2014 at 8:46 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>>
>> On 16 Nov 2014, at 7:39am, Giuseppe Costanzi <giuseppecosta...@gmail.com> 
>> wrote:
>>
>>> do you know if a database exists in sqlite with the values, degrees of
>>> fredom vs  probability (alfa) of the t Student?
>>
>> If you can find one in text or .csv format, we can tell you how to import it.
>>
>> Simon.
>> ___
>> 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] Student's t-test table

2014-11-15 Thread Giuseppe Costanzi

Thanks Simon, I know how to perform the importation.
However thanks of the idea.
I start looking for it.

On Sun, Nov 16, 2014 at 8:46 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 16 Nov 2014, at 7:39am, Giuseppe Costanzi <giuseppecosta...@gmail.com> 
> wrote:
>
>> do you know if a database exists in sqlite with the values, degrees of
>> fredom vs  probability (alfa) of the t Student?
>
> If you can find one in text or .csv format, we can tell you how to import it.
>
> Simon.
> ___
> 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

[sqlite] Student's t-test table

2014-11-15 Thread Giuseppe Costanzi

Hi to everybody,

do you know if a database exists in sqlite with the values, degrees of
fredom vs  probability (alfa) of the t Student?

I need it to calculate confidence intervals to compare two means

regards

bc1966
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] -init file

2014-08-23 Thread Giuseppe Costanzi

thanks Simon,
stupidly I have used this,

 sqlite  can  be initialized using resource files. These can be combined
   with command line arguments to set up sqlite exactly the way  you  want
   it.  Initialization proceeds as follows:

   o The defaults of


   mode= LIST
   separator   = "|"
   main prompt = "sqlite> "
   continue prompt = "   ...> "



 found on
http://www.linuxcommand.org/man_pages/sqlite31.html

regards
beppe

On Sat, Aug 23, 2014 at 6:35 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 23 Aug 2014, at 5:28pm, Giuseppe Costanzi <giuseppecosta...@gmail.com> 
> wrote:
>
>> my file, setconsole
>>
>> headers = ON
>> mode= COLUMN
>> timer   = ON
>
> Those commands are dot commands, not proper SQLite commands.  They need to 
> have dots in front of them.  Also, having an equals sign there is incorrect.  
> Those lines should look more like
>
> .headers ON
> .mode COLUMN
> .timer ON
>
> If you are in doubt about a command try typing it into the SQLite shell tool 
> and see if it does what you want or gives you an error message.
>
> Simon.
> ___
> 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

[sqlite] -init file

2014-08-23 Thread Giuseppe Costanzi

HI all,
I am trying to open an sqlite3  shell session passing a configuration
file with the option -init  but the console it doesn't shape

my file, setconsole

headers = ON
mode= COLUMN
timer   = ON

pep@hal9000:~/test/server$ sqlite3 -init setconsole test.db
SQLite version 3.7.13
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from halflives;
1|421|530|519|37.8|37.8|6|518|Nessuna annotazione|1
2|420|530|519|35.0|37.0|2|517|Nessuna annotazione|1
3|264|530|519|4.0|8.0|7|522|Nessuna annotazione|1
4|264|526|519|-20.0|-20.0|1|517|Nessuna annotazione|1
5|264|526|519|20.0|25.0|1|514|Nessuna annotazione|1
6|105|529|520|-20.0|-20.0|2|522|Acidificare le urine|1
7|105|529|520|4.0|8.0|2|522|Acidificare le urine|1
8|105|529|520|20.0|25.0|2|521|Acidificare le urine|1
9|418|530|520|25.0|25.0|7|522|Nessuna annotazione|1
10|418|530|520|-20.0|-20.0|6|516|Nessuna annotazione|1
11|418|530|520|4.0|8.0|7|522|Nessuna annotazione|1
12|418|530|520|20.0|25.0|3|522|Nessuna annotazione|1
13|385|526|519|37.0|37.0|15|521|L'emivita biologiaca in realta' varia
tra le 8 e le 15 ore|1
14|385|526|520|20.0|25.0|2|522|Nessuna annotazione|1
15|385|526|520|-20.0|-20.0|3|524|Nessuna annotazione|1
16|385|526|520|4.0|8.0|7|522|Nessuna annotazione|1
17|385|526|520|20.0|25.0|2|522|Nessuna annotazione|1


suggests?

p.s.
I'm on debian 6

regards
beppe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] After ANALYZE all has become slow

2014-08-08 Thread Giuseppe Costanzi

well,
after an intense session of debug, we have come to understand the why
of these decreases of performance.
In practice in two tables on 40 they missed two indexes on a total of 50.
Now adversity has wanted these two indexes to be used more or less by
a lot of interrogations.
Restored these the problem it is practically resolved.
sqlite and python never disappoint us.
It stays us the doubt however of how come such problem is amplified
after having performed ANALYZE.

p.s.
I arrange with Keith that is difficult to change versions on you linux
OS, we use debian 5 as server.
I believe even that the panic doesn't often, help to resolve the problems...

you excuse for the trouble

regars
beppe

On Fri, Aug 8, 2014 at 2:09 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
>
> The default installation on my VPS running the current release of CentOS is 
> pretty old as well.  Unfortunately it is kind of difficult to upgrade the 
> version of SQLite (and/or Python) on some Linux distributions because there 
> are other system dependencies on the versions of Python.  For example on 
> CentOS there are dependencies between Python version and the package manager 
> (yum in the case of CentOS) making such upgrades sometimes very problematic.
>
> Linux X 3.15.4-x86_64 #1 SMP Mon Jul 7 08:42:36 EDT 2014 x86_64 x86_64 
> x86_64 GNU/Linux
>
> # python
> Python 2.6.6 (r266:84292, Jan 22 2014, 09:42:36)
> [GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2
> Type "help", "copyright", "credits" or "license" for more information.
>>>> import sqlite3
>>>> cn = sqlite3.connect(':memory:')
>>>> sqlite3.version
> '2.4.1'
>>>> sqlite3.sqlite_version
> '3.6.20'
>>>> for i in range(20):
> ...  cn.cursor().execute('select 
> sqlite_compileoption_get(?);',(i,)).fetchall()
> ...
> Traceback (most recent call last):
>   File "", line 2, in 
> sqlite3.OperationalError: no such function: sqlite_compileoption_get
>>>>
>
> Also, the version of the interface module (sqlite3.py / _sqlite3.so) between 
> python and sqlite3 (sqlite3.so) is quite old as well (compared to the current 
> versions of python and sqlite3 -- though the interface has not significantly 
> changed in how it implements most connection/cursor operations).
>
> Is the command line shell of sqlite3 you are using in your testing the same 
> version of SQLite as the shared object being used by python?
>
> # sqlite3
> SQLite version 3.6.20
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite>
>
> If they are different, that might explain the different results between the 
> two depending on the versions.  There have been many changes in sqlite since 
> version 3.6.20 which might affect performance of actually running a query, 
> depending on the version of the command shell in use.
>
> If they are the same version then the time taken to execute a select at the 
> command line tool interface and retrieve the results should not be 
> significantly different than what you see using from python calls.  cursor() 
> methods .execute(...) corresponds to sqlite3_prepare / sqlite3_bind (if 
> parameters are given); .fetchone() corresponds to a single sqlite3_step and 
> the sqlite3_value calls necessary to retrieve a row; and, .fetchall() is 
> simply .fetchone() in a loop until no more rows are available and accumulates 
> the retrieved row tuples as a list (all executed in C code, not in python 
> code, so it should be relatively the same efficiency as the sqlite3 shell 
> tool is).
>
>
>
>>-Original Message-
>>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>>boun...@sqlite.org] On Behalf Of Giuseppe Costanzi
>>Sent: Friday, 8 August, 2014 03:14
>>To: General Discussion of SQLite Database
>>Subject: Re: [sqlite] After ANALYZE all has become slow
>>
>>using putty
>>
>>Linux SRVXXX 2.6.26-2-686 #1 SMP Mon Aug 30 07:01:57 UTC 2010 i686
>>
>>The programs included with the Debian GNU/Linux system are free software;
>>the exact distribution terms for each program are described in the
>>individual files in /usr/share/doc/*/copyright.
>>
>>Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
>>permitted by applicable law.
>>You have new mail.
>>Last login: Fri Aug  8 08:22:45 2014 from trantor.ia.lan
>>hal9000@SRVXXX :~$ clear
>>hal9000@SRVXXX :~$ python
>>Python 2.5.2 (r252:60911, Jan 24 2010, 14:53:14)
>>[GCC 4.3.2] on linux2
>>Type "help", "copyright", "credits" or "license" for more information.
>>>>> import

Re: [sqlite] After ANALYZE all has become slow

2014-08-08 Thread Giuseppe Costanzi

using putty

Linux SRVXXX 2.6.26-2-686 #1 SMP Mon Aug 30 07:01:57 UTC 2010 i686

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
You have new mail.
Last login: Fri Aug  8 08:22:45 2014 from trantor.ia.lan
hal9000@SRVXXX :~$ clear
hal9000@SRVXXX :~$ python
Python 2.5.2 (r252:60911, Jan 24 2010, 14:53:14)
[GCC 4.3.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> cn = sqlite3.connect(':memory:')
>>> sqlite3.version
'2.3.2'
>>> sqlite3.sqlite_version
'3.5.9'
>>> cn.cursor().execute("SELECT sqlite_version();").fetchall()
[(u'3.5.9',)]

>>> for i in range(20):
>>> print cn.cursor().execute('SELECT 
>>> sqlite_compileoption_get(?);',(i,)).fetchall()



>>> sqlite3.OperationalError: no such function: sqlite_compileoption_get
  File "", line 1
sqlite3.OperationalError: no such function: sqlite_compileoption_get

On Fri, Aug 8, 2014 at 3:47 AM, Keith Medcalf <kmedc...@dessus.com> wrote:
> Giuseppe,
>
> What version of the sqlite3 library is python using?
>
>>python
> Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)] on 
> win32
> Type "help", "copyright", "credits" or "license" for more information.
>>>> import sqlite3
>>>> cn = sqlite3.connect(':memory:')
>
>>>> sqlite3.version
> '2.6.0'
>
>>>> sqlite3.sqlite_version
> '3.8.6'
>
>>>> cn.cursor().execute('select sqlite_version();').fetchall()
> [(u'3.8.6',)]
>
>>>> for i in range(20):
> ...cn.cursor().execute('select 
> sqlite_compileoption_get(?);',(i,)).fetchall()
> ...
> [(u'DEFAULT_LOCKING_MODE=0',)]
> [(u'ENABLE_COLUMN_METADATA',)]
> [(u'ENABLE_FTS3',)]
> [(u'ENABLE_FTS3_PARENTHESIS',)]
> [(u'ENABLE_FTS4',)]
> [(u'ENABLE_LOAD_EXTENSION',)]
> [(u'ENABLE_MEMORY_MANAGEMENT',)]
> [(u'ENABLE_MEMSYS5',)]
> [(u'ENABLE_RTREE',)]
> [(u'ENABLE_STAT4',)]
> [(u'MAX_SCHEMA_RETRY=50',)]
> [(u'SOUNDEX',)]
> [(u'SYSTEM_MALLOC',)]
> [(u'TEMP_STORE=2',)]
> [(u'THREADSAFE=1',)]
> [(None,)]
> [(None,)]
> [(None,)]
> [(None,)]
> [(None,)]
>>>>
>
>>-Original Message-
>>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>>boun...@sqlite.org] On Behalf Of Simon Slavin
>>Sent: Thursday, 7 August, 2014 09:26
>>To: General Discussion of SQLite Database
>>Subject: Re: [sqlite] After ANALYZE all has become slow
>>
>>
>>On 7 Aug 2014, at 3:24pm, Giuseppe Costanzi <giuseppecosta...@gmail.com>
>>wrote:
>>
>>> I have finished running ANALYZE and SQLite commands are taking longer
>>> only when I use python db api
>>> such as
>>> [...]
>>>
>>> however I have done what you have pointed out me and in effects the
>>> speed of execution
>>> from the shell is best after having performed  ANALYZE
>>> as I would be me expected [snip]
>>
>>> I have also noticed that, using python db api2 if I perform a query
>>> recalling a view on the database
>>> this it is slower if I directly write the query in the script of python
>>> For example if I perform [...]
>>> and this independently from ANALTYZE but after run ANALYZER the
>>> situation worsens
>>
>>Very good.  If you had a situation where the Shell Tool got slower on
>>ANALYZE then it would definitely be an error in SQLite and we could
>>investigate.  But you are reporting a situation where the Shell Tool gets
>>faster but Python gets slower.
>>
>>This makes me think that the fault is with your Python library.  I don't
>>know enough about Python to help.  I am going to hope that someone
>>familiar with Python sees this and can help you.
>>
>>Simon.
>>___
>>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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] After ANALYZE all has become slow

2014-08-07 Thread Giuseppe Costanzi

On Thu, Aug 7, 2014 at 2:50 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
>
> On 7 Aug 2014, at 10:56am, Giuseppe Costanzi <giuseppecosta...@gmail.com> 
> wrote:
>
> > Hi to everybody,
> > I have the following problem, on a database, 20M on a server linux, when I
> > make ANALYZE all the interrogations become slow.
>
> I'm very sorry, but I'm having a little trouble with your English and want to 
> be sure I understand your report.
>
> While you are running ANALYZE, you can expect the system to run slowly.  
> ANALYZE does lots of work.  After you have finished running ANALYZE, the 
> system should not be slower than it was before you started running ANALYZE.
>
> If you have finished running ANALYZE and SQLite commands are taking longer 
> than they did before, please tell us which version of SQLite you're running.
>
> If you are executing commands in your own application can you please download 
> the SQLite shell tool and try the same commands in that ?  You can use the 
> following command
>
> .timer ON
>
> to make the shell tool show you timing for each command it runs.
>
> You should be able to reverse the results of ANALYZE using the following 
> commands:
>
> DROP TABLE sqlite_stat1;
> DROP TABLE sqlite_stat2;
> DROP TABLE sqlite_stat3;
> DROP TABLE sqlite_stat4;
>
> (Some of these will give errors because different versions of SQLite create 
> different tables.)  If you do this, and run your queries again, do they run 
> faster like they ran before you did ANALYZE ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




ok I will try to explain better
I have finished running ANALYZE and SQLite commands are taking longer
only when I use python db api
such as
def read(self, fetch, sql, args=()):

try:
cur = self.con.cursor()
cur.execute(sql,args)
if fetch == True:
rs =  cur.fetchall()
else:
rs =  cur.fetchone()
cur.close()
return rs
except Exception, e:
self.handle_exception(e)

however I have done what you have pointed out me and in effects the
speed of execution
from the shell is best after having performed  ANALYZE
as I would be me expected

V:\>sqlite3 lab.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .timer ON
sqlite> .schema package_unloaded
CREATE VIEW package_unloaded
AS
SELECT
dict_products.dict_product_id,
products.product,
products.code,
SUM(CASE WHEN events.category_id =  0  THEN 1 ELSE 0  END) AS download,
SUM(CASE WHEN packages.available =  1  THEN 1 ELSE 0 END) AS stock,
dict_products.department_id AS department_id,
strftime('%Y-%m-%d', events.log_time) AS log_time
FROM dict_products
INNER JOIN products ON (dict_products.product_id = products.product_id)
LEFT JOIN batchs ON (dict_products.dict_product_id = batchs.dict_product_id)
LEFT JOIN packages ON (batchs.batch_id = packages.batch_id)
LEFT JOIN events ON (packages.package_id = events.package_id)
WHERE events.enable =1
GROUP BY dict_products.dict_product_id
ORDER BY products.code;
sqlite> SELECT code,product,download,stock FROM package_unloaded WHERE
department_id = 1 AND downl
oad >0 AND log_time
   ...> BETWEEN '2014-04-01' AND '2014-06-30' ORDER BY code;

some rows as
15439|VB12 Diluente|5|2
15440|FOL DTT Releasing Agent|6|1
15443|CKMB DIL|3|1
CPU Time: user 0.375000 sys 0.203125
sqlite>

sqlite> ANALYZE;
CPU Time: user 0.203125 sys 0.140625
sqlite>
sqlite> SELECT code,product,download,stock FROM package_unloaded WHERE
department_id = 1 AND downl
oad >0 AND log_time
   ...> BETWEEN '2014-04-01' AND '2014-06-30' ORDER BY code;
some rows as
15439|VB12 Diluente|5|2
15440|FOL DTT Releasing Agent|6|1
15443|CKMB DIL|3|1
CPU Time: user 0.25 sys 0.00
sqlite>
I have also noticed that, using python db api2 if I perform a query
recalling a view on the database
this it is slower if I directly write the query in the script of python
For example if I perform
SELECT * FROM package_dowloaded WHERE department_id = ?  ORDER BY code;
the execution is slower if I perform
SELECT
dict_products.dict_product_id,
products.product,
products.code,
SUM(CASE WHEN events.category_id =  0  THEN 1 ELSE 0  END) AS download,
SUM(CASE WHEN packages.available =  1  THEN 1 ELSE 0 END) AS stock,
dict_products.department_id AS department_id,
strftime('%Y-%m-%d', events.log_time) AS log_time
FROM dict_products
INNER JOIN products ON (dict_products.product_id = products.product_id)
LEFT JOIN batchs ON (dict_products.dict_product_id = batchs.dict_product_id)
LEFT JOIN packages ON (batchs.batch_id = pac

[sqlite] After ANALYZE all has become slow

2014-08-07 Thread Giuseppe Costanzi

Hi to everybody,
I have the following problem, on a database, 20M on a server linux, when I
make ANALYZE all the interrogations become slow.
The database is constituted by 37 tables, 56 views and varied indexes.
I use python database API 2.0 to talking with clients.
Considering that the data base will quickly grow I had thought about using
ANALYZE for accellerare the queries.
Suggestions?
Regards, beppe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date in gridview visualized in YYYY-MM-DD format:

2014-03-24 Thread Giuseppe Costanzi

as others have suggested you

strftime('%d-%m-%Y', mytalbe.myfield)

regards

beppe


On Wed, Mar 19, 2014 at 10:39 PM, Stefano Ravagni
wrote:

> Hello, i'm new in SQLite develop...
>
> i'n a gridview (.NET) i visualize the data type in -MM-DD format
>
> Using others database i ever see data in format which follow culture
> setting (italian in this case)... but not in SQLite..
>
> How could i solve ?
>
> ___
> 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

[sqlite] explain me EXPLAIN and EXPLAIN QUERY PLAN

2014-01-01 Thread Giuseppe Costanzi

hi,
 where I can find a good documentation, of keywords in object?
I would want to learn well meaning and use of opcode, p1, p2, pn ,comment
and order,from,detail...

I don't succeed in understanding the output of these two keywordses.
wishes and good year to everybody.

beppe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP BY months and years using integer data

2013-12-21 Thread Giuseppe Costanzi

I don't know if I have understood well but the statment

SUM(stats.quantity * (stats.which_month = 1))
SUM(stats.quantity * (stats.which_month = 2))

should be interpreted

SUM stats.quantity IF stats.which_month = 1 is TRUE
SUM stats.quantity IF stats.which_month = 2 is TRUE

Am'I still in the darkness?


On Sun, Dec 22, 2013 at 4:35 AM, RSmith <rsm...@rsweb.co.za> wrote:

> Boolean Logic 101 - Feel free to skip if this is not your thread!
> 
> -
>
> In addition to other replies - Boolean logic is interesting in that it has
> no real arithmetic value and can have only true or false as a value. It
> doesn't fit too well as a high-level storage value of a system even though
> it permeates low-level storage in every way -  it can't be null for
> instance, but in an RDBMS it _can_ be Null and it _can_ be arithmetic by
> taking on the values 0 and 1 for false and true respectively. This has the
> added advantage that it can be used in arithmetic statements which is what
> Igor used to save considerable amounts of typing in your query.
>
> Whether or not it is faster in execution than a case statement is probably
> up to some testing, but I am willing to bet it is somewhat faster
> considering the relative complexity of a CASE statement.
>
> Any statement containing a comparison operator ends up being a Boolean
> statement because it can be found to either be so, or not be so. Month is
> not Boolean, but (Month = January) is definitely Boolean, because the
> comparison is either true or false depending on the value of "Month".
>
> Similarly any attribute can be Boolean if it is found to be an attribute
> of an object. Giuseppe is not Boolean, but he is human and likely male, so
> that:
> (Giuseppe = Human) is true or 1, and
> (Giuseppe = Female) is false or 0.
>
> For RDBMS and indeed most programming languages, any Boolean evaluated
> result can directly be inferred as the arithmetic values 0 and 1 so that I
> could make some programmatic assumptive statements based on it, such as:
>
> HumanX's fertility period recurs every (28 * (HumanX = Female)) days. This
> will be 0 days for men (28 * false) = (28 * 0) = 0, and 28 for ladies as
> (28 * true) = (28 * 1) = 28 in the same logic.
>
> Similarly your odds of balding is roughly 0.04 + (35/Age)*((HumanX = Male)
> * 10) since men have a roughly 40% chance of balding by age 35 which is
> about 10 times more likely than women and the odds increase with age[1].
>
>
> Hope this makes clear how Booleans are used mathematically in high-level
> code. Have a great day!
> Ryan
>
>
> [1] - http://www.statisticbrain.com/hair-loss-statistics/
>
>
>
> On 2013/12/21 22:24, Giuseppe Costanzi wrote:
>
>> ...but stats.which_month is not a boolean, it represent  a month...
>>
>>
>>
> ___
> 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] GROUP BY months and years using integer data

2013-12-21 Thread Giuseppe Costanzi

...but stats.which_month is not a boolean, it represent  a month...



On Sat, Dec 21, 2013 at 9:11 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 21 Dec 2013, at 7:45pm, Giuseppe Costanzi <giuseppecosta...@gmail.com>
> wrote:
>
> > I am not sure to have understood the  Igor's solution
> >
> > ...SUM(stats.quantity * (stats.which_month = 1)) AS gen
> >
> > which is the meaning of the operator * in the statement?
>
> Multiply.
>
> It works because "stats.which_month = 1" is a boolean and therefore
> evaluates to either 0 or 1.
>
> Simon.
> ___
> 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] GROUP BY months and years using integer data

2013-12-21 Thread Giuseppe Costanzi

thanks to all for the solutions.

I am not sure to have understood the  Igor's solution

...SUM(stats.quantity * (stats.which_month = 1)) AS gen

which is the meaning of the operator * in the statement?

regards and still thanks
beppe


On Sat, Dec 21, 2013 at 8:21 PM, James K. Lowden
wrote:

> On Sat, 21 Dec 2013 17:30:50 +0200
> RSmith  wrote:
>
> > Just move the scope of the grouping and use Nulls in stead of 0's,
> > like this:
> >
> > SELECT stats.which_year AS year,
> > SUM(CASE WHEN stats.which_month =  1  THEN stats.quantity ELSE NULL
> > END) AS gen,
>
> ELSE NULL is the default
>
> sqlite> create table T ( t int not null primary key );
> sqlite> insert into T values (1) ;
> sqlite> insert into T values (2) ;
> sqlite> .nullvalue NULL
> sqlite> select t, case t when 1 then 'one' end as tee from T;
> 1|one
> 2|NULL
>
> But I would recommend ELSE 0, on the principle that NULL arithmetic is
> dodgy at best.
>
> BTW, "which_month" means the same as "month"; you could tack
> "which" in front of any name without changing the meaning.
>
> --jkl
> ___
> 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

[sqlite] GROUP BY months and years using integer data

2013-12-21 Thread Giuseppe Costanzi

HI all,
I've a table such

CREATE TABLE 'stats' (
'stat_id' INTEGER PRIMARY KEY,
'test_id' INTEGER,
'quantity' INTEGER,
'which_month' INTEGER,
'which_year' INTEGER,
)

and I need to extract data with somenthing like

SELECT

stats.which_year AS year,

CASE WHEN stats.which_month =  1  THEN SUM(stats.quantity) ELSE 0 END AS
gen,
CASE WHEN stats.which_month =  2  THEN SUM(stats.quantity) ELSE 0 END AS
feb,
CASE WHEN stats.which_month =  3  THEN SUM(stats.quantity) ELSE 0 END AS
mar,
CASE WHEN stats.which_month =  4  THEN SUM(stats.quantity) ELSE 0 END AS
apr,
CASE WHEN stats.which_month =  5  THEN SUM(stats.quantity) ELSE 0 END AS
mag,
CASE WHEN stats.which_month =  6  THEN SUM(stats.quantity) ELSE 0 END AS
giu,
CASE WHEN stats.which_month =  7  THEN SUM(stats.quantity) ELSE 0 END AS
lug,
CASE WHEN stats.which_month =  8  THEN SUM(stats.quantity) ELSE 0 END AS
ago,
CASE WHEN stats.which_month =  9  THEN SUM(stats.quantity) ELSE 0 END AS
sett,
CASE WHEN stats.which_month =  10  THEN SUM(stats.quantity) ELSE 0 END AS
ott,
CASE WHEN stats.which_month =  11  THEN SUM(stats.quantity) ELSE 0 END AS
nov,
CASE WHEN stats.which_month =  12  THEN SUM(stats.quantity) ELSE 0 END AS
dic,

stats.test_id AS test_id

FROM stats

WHERE  stats.test_id = 420
GROUP BY which_year


that return this

"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "71769" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "68972" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "65075" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "50605" "420"

if I add which_month in th e GROUP BY clause I've


"2009" "5994" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "6112" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "7046" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "5947" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "6471" "0" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "6027" "0" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "5841" "0" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "3352" "0" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "6564" "0" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "7075" "0" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "6283" "0" "420"
"2009" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "5057" "420"
"2010" "6112" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "6201" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "6890" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "5907" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "6246" "0" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "5667" "0" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "5185" "0" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "3269" "0" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "5963" "0" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "6520" "0" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "6162" "0" "420"
"2010" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "4850" "420"
"2011" "5888" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "6027" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "6756" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "5889" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "6276" "0" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "5985" "0" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "4968" "0" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "3159" "0" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "0" "5562" "0" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "0" "0" "5957" "0" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "5131" "0" "420"
"2011" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "3477" "420"
"2012" "4949" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2012" "0" "4790" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2012" "0" "0" "5608" "0" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2012" "0" "0" "0" "4337" "0" "0" "0" "0" "0" "0" "0" "0" "420"
"2012" "0" "0" "0" "0" "4846" "0" "0" "0" "0" "0" "0" "0" "420"
"2012" "0" "0" "0" "0" "0" "4739" "0" "0" "0" "0" "0" "0" "420"
"2012" "0" "0" "0" "0" "0" "0" "3887" "0" "0" "0" "0" "0" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "2412" "0" "0" "0" "0" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "0" "4404" "0" "0" "0" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "0" "0" "4542" "0" "0" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "3622" "0" "420"
"2012" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "0" "2469" "420"

finally the question is:
How I can  keeping months and years in the same row?
what i mistake in my query?
thanks for any suggestions?


beppe
___
sqlite-users mailing list
sqlite-users@sqlite.org

Re: [sqlite] GROUP BY

2013-11-14 Thread Giuseppe Costanzi

Yeah, something like

SELECT order_id,issued,reference, company, department_id, SUM(quantity) AS
quantity, SUM(dispatch) AS dispatch, SUM(surplus) AS surplus

FROM orders_surplus

GROUP BY order_id

I have however to sum quantity, dispatch and surplus fields to force the
view to calculate the right values

to get that this

order_idissuedcompany   dep_id  qty,dispatch   surplus
> "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> "1""12-11-2013"  "Siemens"  "1" "2""2""0"

becomes this

order_idissuedcompany   dep_id  qty,dispatch   surplus
> "1""12-11-2013"  "Siemens"  "1" "8""6""2"

that is I want the total sum of quantity field (6+2=8), the total sum of
the dispatch field (4+2=2)
and the result of their subtraction (8-6=2)
thanks jim


On Thu, Nov 14, 2013 at 2:35 PM, Jim Callahan <
jim.callahan.orla...@gmail.com> wrote:

> How to query and/or group complex SQL?
>
> Add the language to create a SQL VIEW before your SELECT statement:
>
> CREATE VIEW viewname AS SELECT [your SQL]
> http://www.sqlite.org/lang_createview.html
>
> Then use the viewname in a second SELECT statement as you would a table.
>
> SELECT order_id, issued,  company,  dep_id,  qty, dispatch,  surplus
> FROM viewname
> WHERE order_id = 1;
>
> or, if necessary
>
> WHERE order_id = '1';
>
> How to query and/or group:
> ...complex SQL...
>
> order_idissuedcompany   dep_id  qty,dispatch   surplus
> > "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> > "1""12-11-2013"  "Siemens"  "1" "2""2""0"
> > "2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
> > "3""13-11-2013"  "Siemens"  "8" "3""3""0"
> > How I can group by order_id? In the example I'would return on order_id
>
> =1:
>
>
> I assume your SQL is already producing the output in your example.
> Hope I haven't missed your point and this helps.
>
> Jim
>
> On Thu, Nov 14, 2013 at 2:48 AM, Giuseppe Costanzi <
> giuseppecosta...@gmail.com> wrote:
>
> > hi to everybody,
> > you excuse for the preceding mails but I have had problems with this and
> I
> > have had to change provider.
> > However I propose my question.
> > I have this query, that you also see in attachment file.
> > SELECT
> > orders.order_id AS order_id,
> > strftime('%d-%m-%Y', orders.issued) AS issued,
> > suppliers.company AS company,
> > departments.department_id AS dep_id,
> > order_details.quantity AS qty,
> >
> > SUM(CASE WHEN transactions.category_id =  1  THEN 1  ELSE 0 END) AS
> > dispatch,
> >
> > order_details.quantity -  SUM(CASE WHEN transactions.category_id =  1
> > THEN 1  ELSE 0 END) AS surplus
> >
> > FROM orders
> > INNER JOIN departments ON (departments.department_id =
> > orders.department_id)
> >
> > INNER JOIN suppliers ON (suppliers.supplier_id = orders.supplier_id)
> > INNER JOIN order_details ON (orders.order_id = order_details.order_id)
> > INNER JOIN transactions ON order_details.order_detail_id =
> > transactions.order_detail_id
> >
> > WHERE  orders.state = 0 AND orders.enable =1
> > GROUP BY order_details.order_detail_id
> >
> > that return such as
> >
> > order_idissuedcompany   dep_id  qty,dispatch   surplus
> > "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> > "1""12-11-2013"  "Siemens"  "1" "2""2""0"
> > "2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
> > "3""13-11-2013"  "Siemens"  "8" "3""3""0"
> >
> > How I can group by order_id? In the example I'would return on order_id
> > =1:
> >
> > order_idissuedcompany   dep_id  qty,dispatch   surplus
> > "1""12-11-2013"  "Siemens"  "1" "8""6""2"
> >
> > any suggestions?
> >
> > regards beppe
> >
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP BY

2013-11-14 Thread Giuseppe Costanzi

HI clemens I've resolve with

SELECT order_id,issued,reference, company, dep_id, SUM(qty) AS quantity,
SUM(dispatch) AS dispatch, SUM(surplus) AS surplus

FROM(
SELECT
  orders.order_id AS order_id,
orders.reference AS reference,
  strftime('%d-%m-%Y', orders.issued) AS issued,
suppliers.company AS company,
orders.department_id AS dep_id,
order_details.qty ,
SUM(CASE WHEN transactions.category_id =  1  THEN 1  ELSE 0 END) AS
dispatch,
  order_details.quantity -  SUM(CASE WHEN transactions.category_id =  1
 THEN 1  ELSE 0 END) AS surplus

FROM orders
INNER JOIN suppliers ON (suppliers.supplier_id = orders.supplier_id)
INNER JOIN order_details ON (orders.order_id = order_details.order_id)
LEFT JOIN transactions ON order_details.order_detail_id =
transactions.order_detail_id

WHERE  orders.state = 0 AND orders.enable =1
GROUP BY order_details.order_detail_id)
GROUP BY order_id


On Thu, Nov 14, 2013 at 11:04 AM, Clemens Ladisch <clem...@ladisch.de>wrote:

> Giuseppe Costanzi wrote:
> > order_idissuedcompany   dep_id  qty,dispatch   surplus
> > "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> > "1""12-11-2013"  "Siemens"  "1" "2""2""0"
> > "2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
> > "3""13-11-2013"  "Siemens"  "8" "3""3""0"
> >
> > How I can group by order_id?
>
> By writing "group by order_id".
>
> > In the example I'would return on order_id =1:
> >
> > order_idissuedcompany   dep_id  qty,dispatch   surplus
> > "1""12-11-2013"  "Siemens"  "1" "8""6""2"
>
> Also add SUM(...) where needed.
>
>
> Regards,
> Clemens
> ___
> 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

[sqlite] GROUP BY

2013-11-13 Thread Giuseppe Costanzi

hi to everybody,
you excuse for the preceding mails but I have had problems with this and I
have had to change provider.
However I propose my question.
I have this query, that you also see in attachment file.
SELECT
orders.order_id AS order_id,
strftime('%d-%m-%Y', orders.issued) AS issued,
suppliers.company AS company,
departments.department_id AS dep_id,
order_details.quantity AS qty,

SUM(CASE WHEN transactions.category_id =  1  THEN 1  ELSE 0 END) AS
dispatch,

order_details.quantity -  SUM(CASE WHEN transactions.category_id =  1
THEN 1  ELSE 0 END) AS surplus

FROM orders
INNER JOIN departments ON (departments.department_id =
orders.department_id)

INNER JOIN suppliers ON (suppliers.supplier_id = orders.supplier_id)
INNER JOIN order_details ON (orders.order_id = order_details.order_id)
INNER JOIN transactions ON order_details.order_detail_id =
transactions.order_detail_id

WHERE  orders.state = 0 AND orders.enable =1
GROUP BY order_details.order_detail_id

that return such as

order_idissuedcompany   dep_id  qty,dispatch   surplus
"1""12-11-2013"  "Siemens"  "1" "6""4""2"
"1""12-11-2013"  "Siemens"  "1" "2""2""0"
"2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
"3""13-11-2013"  "Siemens"  "8" "3""3""0"

How I can group by order_id? In the example I'would return on order_id
=1:

order_idissuedcompany   dep_id  qty,dispatch   surplus
"1""12-11-2013"  "Siemens"  "1" "8""6""2"

any suggestions?

regards beppe
SELECT 
orders.order_id AS order_id,
strftime('%d-%m-%Y', orders.issued) AS issued,
suppliers.company AS company,
departments.department_id AS dep_id,
order_details.quantity AS qty,

SUM(CASE WHEN transactions.category_id =  1  THEN 1  ELSE 0 END) AS
dispatch,

order_details.quantity -  SUM(CASE WHEN transactions.category_id =  1
THEN 1  ELSE 0 END) AS surplus

FROM orders
INNER JOIN departments ON (departments.department_id =
orders.department_id) 

INNER JOIN suppliers ON (suppliers.supplier_id = orders.supplier_id) 
INNER JOIN order_details ON (orders.order_id = order_details.order_id)
INNER JOIN transactions ON order_details.order_detail_id =
transactions.order_detail_id

WHERE  orders.state = 0 AND orders.enable =1
GROUP BY order_details.order_detail_id

that return such as

order_idissuedcompany   dep_id  qty,dispatch   surplus
"1""12-11-2013"  "Siemens"  "1" "6""4""2"
"1""12-11-2013"  "Siemens"  "1" "2""2""0"
"2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
"3""13-11-2013"  "Siemens"  "8" "3""3""0"

How I can group by order_id? In the example I'would return on order_id
=1:

order_idissuedcompany   dep_id  qty,dispatch   surplus
"1""12-11-2013"  "Siemens"  "1" "8""6""2"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syntax control

2012-09-21 Thread Giuseppe Costanzi

On Fri, 2012-09-21 at 08:33 +0200, Giuseppe Costanzi wrote:
> hi all,
> could you check me the syntax of this statment?
> 
> UPDATE batchs
> SET
>   batchs.department_id = (SELECT 
> products.department_id 
>   FROM products
> WHERE  
>   products.product_id =  batchs.product_id)
>   
> WHERE
> EXISTS (
> SELECT *
> FROM products
> WHERE products.product_id = batchs.product_id
> )
> 
> I would update the field named department_id on batchs table
> with the values of the same field on products table.
> the statement return
> near ".": syntax error
> but I don't see it.
> Sql seems that  don't digest the call to batchs.department_id
> 
> regards
> beppe

resolved

UPDATE batchs 

SET department_id = (SELECT department_id FROM products WHERE
products.product_id = batchs.product_id)
> 
> ___
> 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

[sqlite] Syntax control

2012-09-21 Thread Giuseppe Costanzi

hi all,
could you check me the syntax of this statment?

UPDATE batchs
SET
  batchs.department_id = (SELECT 
  products.department_id 
  FROM products
WHERE  
  products.product_id =  batchs.product_id)
  
WHERE
EXISTS (
SELECT *
FROM products
WHERE products.product_id = batchs.product_id
)

I would update the field named department_id on batchs table
with the values of the same field on products table.
the statement return
near ".": syntax error
but I don't see it.
Sql seems that  don't digest the call to batchs.department_id

regards
beppe

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update case in a transaction

2012-03-16 Thread Giuseppe Costanzi

On Fri, 2012-03-16 at 12:28 -0400, Igor Tandetnik wrote:
> On 3/16/2012 11:59 AM, Giuseppe Costanzi wrote:
> > CREATE TRIGGER update_stocks AFTER INSERT ON transactions
> > BEGIN
> > UPDATE products
> > SET
> >   products.stock =(CASE
> >   WHEN (transactions.flow =1 )
> >   THEN SUM(products.stock + 
> > transactions.quantity)
> >   ELSE SUM(products.stock - 
> > transactions.quantity)
> >   END)
> >
> > WHERE products.product_id = transactions.product_id
> > AND transactions.registered =1;
> > END
> 
> CREATE TRIGGER update_stocks AFTER INSERT ON transactions
> BEGIN
> UPDATE products SET stock =
>(CASE WHEN new.flow = 1
> THEN stock + new.quantity
> ELSE stock - new.quantity
>END)
> WHERE product_id = new.product_id
> AND new.registered =1;
> END;
> 

thank you very much Igor.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Update case in a transaction

2012-03-16 Thread Giuseppe Costanzi

Hi all,
I would create a trigger to update a field in a table when in another
table a new record is insert

My scenario is

products table
product_id = INTEGER PK
product =  TEXT
stock = INTEGER

transactions table
transaction_id = INTEGER PK
product_id = INTEGER FK
flow = BOOLEAN (0,1)
quantitty = INTEGER
registered = BOOLEAN (0,1)

I've tried 

CREATE TRIGGER update_stocks AFTER INSERT ON transactions
BEGIN
UPDATE products
SET
 products.stock =(CASE
  WHEN (transactions.flow =1 )
  THEN SUM(products.stock + 
transactions.quantity)
  ELSE SUM(products.stock - 
transactions.quantity)
  END)

WHERE products.product_id = transactions.product_id
AND transactions.registered =1;
END

but I got a 
END [ near ".": syntax error ]
It's the right way?
what is wrong?
I've never done before

regards
giuseppe

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Between And statement too much slow

2008-09-19 Thread Giuseppe Costanzi


Hi,
I'm using this query on my aplication to retrive a recordset that shows
transactions between two date.
The problem is that it's too much slow.
I'm using python and the field TransactionDate is Datetime (e.g. 
2008-01-01) and i passed some such parameters

('1', datetime.date(2008, 7, 1), datetime.date(2008, 7, 31))

the query look on 16750 record on tblTansactions and return 85 rows


sSQL ="""SELECT 
tblProducts.ProductName,tblProducts.WarehouseCode,tblProducts.UnitsInStock,SUM(tblTransactions.Quantity)
 FROM tblProducts
 INNER JOIN tblTransactions ON 
tblProducts.ProductID=tblTransactions.ProductID
 WHERE tblTransactions.TransactionType = 0
 AND tblProducts.WarehouseID = ?
 AND tblTransactions.TransactionDate BETWEEN ? AND ?
 GROUP BY tblProducts.ProductID
 ORDER BY tblProducts.ProductName"""

How could improve my code?

regards

Giuseppe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Extract month and year from a datetime field

2008-07-22 Thread Giuseppe Costanzi

Igor Tandetnik wrote:
> Giuseppe Costanzi <[EMAIL PROTECTED]> wrote:
>   
>> How  extract the month and/or year from a datetime field?
>> For example:
>>
>> SELECT OrderID,OrderDate
>> FROM tblOrders
>> 
>
> select OrderID, strftime('%m', OrderDate) as Month, strftime('%Y', 
> OrderDate) as Year
> from tblOrders;
>
> http://sqlite.org/lang_datefunc.html
>
> Igor Tandetnik 
>   
Thanks a lot Igor,I will looking for this.
Giuseppe
>
>
> ___
> 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] Student's t-test table

Re: [sqlite] Student's t-test table

Re: [sqlite] Student's t-test table

[sqlite] Student's t-test table

Re: [sqlite] -init file

[sqlite] -init file

Re: [sqlite] After ANALYZE all has become slow

Re: [sqlite] After ANALYZE all has become slow

Re: [sqlite] After ANALYZE all has become slow

[sqlite] After ANALYZE all has become slow

Re: [sqlite] Date in gridview visualized in YYYY-MM-DD format:

[sqlite] explain me EXPLAIN and EXPLAIN QUERY PLAN

Re: [sqlite] GROUP BY months and years using integer data

Re: [sqlite] GROUP BY months and years using integer data

Re: [sqlite] GROUP BY months and years using integer data

[sqlite] GROUP BY months and years using integer data

Re: [sqlite] GROUP BY

Re: [sqlite] GROUP BY

[sqlite] GROUP BY

Re: [sqlite] Syntax control

[sqlite] Syntax control

Re: [sqlite] Update case in a transaction

[sqlite] Update case in a transaction

[sqlite] Between And statement too much slow

Re: [sqlite] Extract month and year from a datetime field

25 matches

Site Navigation

Mail list logo

Footer information