March 2013 - users-list

Extract quarter from date
by Diethard Steiner 17 Mar '13

17 Mar '13

Hi, When I run this: SELECT DISTINCT(EXTRACT(QUARTER FROM o_orderdate)) FROM orders; I get following error: Error: syntax error, unexpected IDENT in: "select distinct(extract(quarter" SQLState: 42000 ErrorCode: 0 Is Quarter not supported or is this a bug? Extract Year, month, day are all working fine. Environment: RHEL (Amazon Linux), Latest version of MonetDB compiled from source code. Thanks, Diethard

4 6

Connecting Tableau to MonetDB
by Pedro Salgueiro 15 Mar '13

15 Mar '13

Hi, I am trying to connect Tableau to a MonetDB instance which is running in a linux machine, using the ODBC MonetDB driver. Tableau manages to connect to MonetDB and is able to list the tables, but then it fails with the following error while performing any Analysis: [MonetDB][ODBC Driver 11.13.9] Invalid identifier 'none:product:nk' > I tried two versions of the ODBC, the latest and Oct2012-SP, but the problem is present in both versions. It's worth pointing that I am able to use the same MonetDB with the JDBC with no problems. <http://dev.monetdb.org/downloads/Windows/Oct2012-SP3/> While googling for this particular problem, I found the following mail in the mailing list: http://mail.monetdb.org/pipermail/users-list/2011-November/005248.html Has there been any developments in this field? Best regards, Pedro Salgueiro

2 1

standard deviation in SQL
by Anshuman Kumar 15 Mar '13

15 Mar '13

Hi, I'm trying to find an aggregate function to find standard deviation. Does monetDB support such a function? For example:- to find the average of the selected values we can use- Select AVG(age) from students where grade = 4; I was taking the help of google and found this link- http://old.nabble.com/users-list-Digest,-Vol-3,-Issue-8-td34660082.html Then I tried the solution given by Percy Wegmann in the above link. After that when I run this query- Select STDEV(age) from students where grade = 4; [Age being a column of type double AND I get similar kind of error for int] I get this error - TypeException:user.s1_1[21]:'aggr.stddev' undefined in: _33:any := aggr.stddev(_ 32:bat[:oid,:dbl]) program contains errors I need to have this function working in order to be able to use MonetDB. Kindly help. Regards, Anshuman

3 4

stddev function disappeared?
by Anthony Damico 14 Mar '13

14 Mar '13

Hello, I'm trying to run some old code with a recent upgrade of MonetDB and the standard deviation function appears to have disappeared. I'm running MonetDB on Windows. This issue was previously discussed and resolved in this thread: http://old.nabble.com/users-list-Digest%2C-Vol-3%2C-Issue-8-tt34660082.html… I could re-open this bug report if appropriate? http://bugs.monetdb.org/show_bug.cgi?id=2454#c18 My server specs are below, and the actual commands below that. Thank you!!! # MonetDB 5 server v11.15.1 "Feb2013" # Serving database 'bsapuf', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50003/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded > c:\Program Files\MonetDB\MonetDB5>mclient -p 50003 "bsapuf" user(win32):monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013) Database: MonetDB v11.15.1 (Feb2013), 'bsapuf' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select sum( hha_pmt_amt ) from hha08; +-----------+ | L1 | +===========+ | 675676300 | +-----------+ 1 tuple (2.311ms) sql>select sum( hha_pmt_amt ), avg( hha_pmt_amt ) from hha08; +-----------+--------------------------+ | L1 | L2 | +===========+==========================+ | 675676300 | 5412.8151310993435 | +-----------+--------------------------+ 1 tuple (63.559ms) sql>select sum( hha_pmt_amt ), median( hha_pmt_amt ) from hha08; +-----------+------+ | L1 | L2 | +===========+======+ | 675676300 | 3500 | +-----------+------+ 1 tuple (9.601ms) sql>select sum( hha_pmt_amt ), stddev( hha_pmt_amt ) from hha08; SELECT: no such unary operator 'stddev(int)' sql>select sum( hha_pmt_amt ), sd( hha_pmt_amt ) from hha08; SELECT: no such unary operator 'sd(int)' sql>select sum( hha_pmt_amt ), sddev( hha_pmt_amt ) from hha08; SELECT: no such unary operator 'sddev(int)' sql>select sum( hha_pmt_amt ), var( hha_pmt_amt ) from hha08; SELECT: no such unary operator 'var(int)' sql>select sum( hha_pmt_amt ), variance( hha_pmt_amt ) from hha08; SELECT: no such unary operator 'variance(int)' sql>select sum( hha_pmt_amt ), stdevp( hha_pmt_amt ) from hha08; SELECT: no such unary operator 'stdevp(int)' sql>

3 4

SQL function for getting the last inserted ID
by Gijs Molenaar 12 Mar '13

12 Mar '13

Hi, A typical question that is probably asked before, but I can't find an answer in the archives or in the doc. Is there a SQL function for getting the last inserted ID for a auto increment or serial field? PostgreSQL has lastval(). I know it is returned in the query result, but I need to have the value inside a SQL function. Also I know that if you manually create a sequence for ID generation you can get the next value with: SELECT NEXT VALUE FOR seq_freq INTO freqid; But we are trying to keep our queries as generic as possible (close to impossible, but still) hence we want to use the SERIAL type. Or is there a way to find out what is the name of the sequence made when giving a SERIAL type to a column? thanks, -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

3 3

Insertion problem for large number of columns
by Shajon 12 Mar '13

12 Mar '13

Dear Monet Team, Today I was trying to insert data in MonetDB using " insert into ." command through jdbc from my application but the server is crashing & no data is inserting. That table has 25 columns and I am passing all values. Thanks Shajon

5 6

Questions
by Bouslah Ahmed 10 Mar '13

10 Mar '13

Hi, I am a university student and I use MonetDB. I have two questions to ask you regarding loading data using mclient: 1 - Is it possible to load multiple csv files into multiple tables in one query? For exemple i have five csv files and five tables and I want to use a single command line instead of five mclient -u us1 -d demo -s " COPY INTO DWDATE FROM STDIN USING DELIMITERS '|','\n'" - <F:\2Go\DATE.tbl mclient -u us1 -d demo -s " COPY INTO CUSTOMER FROM STDIN USING DELIMITERS '|','\n'" - <F:\2Go\CUSTOMER.tbl mclient -u us1 -d demo -s " COPY INTO SUPPLIER FROM STDIN USING DELIMITERS '|','\n'" - <F:\2Go\SUPPLIER.tbl mclient -u us1 -d demo -s " COPY INTO PART FROM STDIN USING DELIMITERS '|','\n'" - <F:\2Go\PART.tbl mclient -u us1 -d demo -s " COPY INTO LINEORDER FROM STDIN USING DELIMITERS '|','\n'" -<F:\2Go\LINEORDER.tbl 2 - Is it possible to enter the password in the request as well as user and database name? This will save me introduce each time the password For exemple mclient -u us1 -d demo -s " COPY INTO DWDATE FROM STDIN USING DELIMITERS '|','\n'" - <F:\2Go\DATE.tbl <PassWord> thank you.

1 0

Parse dates in CSV file, CSV vault
by Aubrey Barnard 10 Mar '13

10 Mar '13

MonetDB Users, Is YYYY-MM-DD the only date format that can be parsed and read from a CSV with the COPY command? Or can the format be specified? On a related note, is the CSV vault feature ready for use (Feb2013 version)? The mailing list implies it is not ready and there is nothing in the release notes. If it is ready, how do I set it up? Thanks much for helping me get my data loaded. Aubrey Barnard

2 1

RE: Installation on Ubuntu 12.10 Quantal
by Duncan Garmonsway 10 Mar '13

10 Mar '13

Thanks, that worked. Didn't realise I was still such a noob... Cheers, Duncan On 10 March 2013 00:00, <users-list-request(a)monetdb.org> wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://mail.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. INSERT INTO ... SELECT * FROM ... OFFSET X seems to leave > destination table in an inconsistent state (Percy Wegmann) > 2. Re: INSERT INTO ... SELECT * FROM ... OFFSET X seems to leave > destination table in an inconsistent state (Martin Kersten) > 3. Installation on Ubuntu 12.10 Quantal (Duncan Garmonsway) > 4. Re: Installation on Ubuntu 12.10 Quantal (Brandon Jackson) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Fri, 8 Mar 2013 10:22:53 -0600 > From: Percy Wegmann <percy(a)clicksecurity.com> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: INSERT INTO ... SELECT * FROM ... OFFSET X seems to leave > destination table in an inconsistent state > Message-ID: > < > CADpZG+Scab3V8u_isKziJobUgCYO4BEmkCWMB8iOD-pjggTWQQ(a)mail.gmail.com> > Content-Type: text/plain; charset="iso-8859-1" > > Dear Monet Team, > > Today I ran into an issue with copying data between tables. I've tried it > on 11.13.7 and 11.13.9 with the same results. What I find is that when > including an OFFSET clause in my source query, the destination table ends > up in a weird state. > > I've logged a bug for it - http://bugs.monetdb.org/show_bug.cgi?id=3249 > > I'm using the workaround of ordering my data and using a LIMIT instead of > an OFFSET, which seems to work. Just wanted to let you know. > > Thanks, > Percy >

1 0

Installation on Ubuntu 12.10 Quantal
by Duncan Garmonsway 09 Mar '13

09 Mar '13

Hello, After installation I am getting the error: $ monetdbd create ~/monetdb/ monetdbd: error while loading shared libraries: libstream.so.3: cannot open shared object file: No such file or directory But the library (and the symlink to it) are right here: $ ls -l /usr/local/lib | grep libstream -rwxr-xr-x 1 root root 943 Mar 9 14:23 libstream.la lrwxrwxrwx 1 root root 18 Mar 9 14:23 libstream.so -> libstream.so.3.1.5 lrwxrwxrwx 1 root root 18 Mar 9 14:23 libstream.so.3 -> libstream.so.3.1.5 -rwxr-xr-x 1 root root 107879 Mar 9 14:23 libstream.so.3.1.5 And there were no errors reported during any of ./configure sudo make sudo make install My installation script was: sudo apt-get install libxml2-dev bison ant flex unixODBC-dev # download and unpack source wget http://dev.monetdb.org/downloads/sources/Latest/MonetDB-11.15.3.tar.xz tar -xvf MonetDB-11.15.3.tar.xz # install cd MonetDB-11.15.3 ./configure sudo make sudo make install Help much appreciated, Duncan

2 1