Re: [MonetDB-users] Querying date type column in MonetDB
I see --- if you consider it a bug, please file a detailed bug report including information & instructions to reporduce the problem as well as infornmation about the exact MonetDB version and OS you're using via http://bugs.MonetDB.org/
Stefan
----- Oorspronkelijk bericht ----- Van: Freddy Priyatna freddy.priyatna@gmail.com Verzonden: maandag 25 oktober 2010 16:32 Aan: Communication channel for MonetDB users monetdb-users@lists.sourceforge.net Onderwerp: Re: [MonetDB-users] Querying date type column in MonetDB
Hi Stefan,
Nope, the name of the column is "deliveryDays" :)
Freddy
2010/10/25 Stefan Manegold Stefan.Manegold@cwi.nl
Hi Freddy,
I don't think that the error you experience is triggered by the column type, but rather by the fact that your date column ha? a name - say "date" - that is a reserved key word in SQL an hence needs to be double-quoted according to the SQL standard (this is not a MonetDB specific choice, but a "feature" of the SQL standard) --- not knowing your table & column names, I can only guess, though ...
Stefan
----- Oorspronkelijk bericht ----- Van: Freddy Priyatna freddy.priyatna@gmail.com Verzonden: maandag 25 oktober 2010 16:03 Aan: MonetDB-users monetdb-users@lists.sourceforge.net Onderwerp: [MonetDB-users] Querying date type column in MonetDB
Dear All,
I am new to MonetDB, so most likely my question will look obvious.
I would like to ask, how can columns with date type is queried in monetdb. Based on my experience, it seems that date type columns need special treatment. It seems that the only way to query them is with the syntax "column_name" while other columns seems to work with the format table_name.column_name without the need of double quote. When I tried to query date column with the syntax table_name.column_name, it gave me an error "no such column"
So my question is, is it true that the only way to query date columns is using "column_name" format? Another question will be, what happen if I need to query same name columns coming from different table?
Thanks in advance, Freddy
Nokia and AT&T present the 2010 Calling All Innovators-North America contest Create new apps & games for the Nokia N8 for consumers in U.S. and Canada $10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store http://p.sf.net/sfu/nokia-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Dear Stefan,
OK, thanks for your help.
Freddy
2010/10/25 Stefan Manegold Stefan.Manegold@cwi.nl
I see --- if you consider it a bug, please file a detailed bug report including information & instructions to reporduce the problem as well as infornmation about the exact MonetDB version and OS you're using via http://bugs.MonetDB.org/
Stefan
----- Oorspronkelijk bericht ----- Van: Freddy Priyatna freddy.priyatna@gmail.com Verzonden: maandag 25 oktober 2010 16:32 Aan: Communication channel for MonetDB users < monetdb-users@lists.sourceforge.net> Onderwerp: Re: [MonetDB-users] Querying date type column in MonetDB
Hi Stefan,
Nope, the name of the column is "deliveryDays" :)
Freddy
2010/10/25 Stefan Manegold Stefan.Manegold@cwi.nl
Hi Freddy,
I don't think that the error you experience is triggered by the column type, but rather by the fact that your date column ha? a name - say
"date" -
that is a reserved key word in SQL an hence needs to be double-quoted according to the SQL standard (this is not a MonetDB specific choice, but
a
"feature" of the SQL standard) --- not knowing your table & column names,
I
can only guess, though ...
Stefan
----- Oorspronkelijk bericht ----- Van: Freddy Priyatna freddy.priyatna@gmail.com Verzonden: maandag 25 oktober 2010 16:03 Aan: MonetDB-users monetdb-users@lists.sourceforge.net Onderwerp: [MonetDB-users] Querying date type column in MonetDB
Dear All,
I am new to MonetDB, so most likely my question will look obvious.
I would like to ask, how can columns with date type is queried in
monetdb.
Based on my experience, it seems that date type columns need special treatment. It seems that the only way to query them is with the syntax "column_name" while other columns seems to work with the format table_name.column_name without the need of double quote. When I tried to query date column with the syntax table_name.column_name, it gave me an error "no such column"
So my question is, is it true that the only way to query date columns is using "column_name" format? Another question will be, what happen if I
need
to query same name columns coming from different table?
Thanks in advance, Freddy
Nokia and AT&T present the 2010 Calling All Innovators-North America contest Create new apps & games for the Nokia N8 for consumers in U.S. and
Canada
$10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store http://p.sf.net/sfu/nokia-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Nokia and AT&T present the 2010 Calling All Innovators-North America contest Create new apps & games for the Nokia N8 for consumers in U.S. and Canada $10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store http://p.sf.net/sfu/nokia-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Stefan Manegold Stefan.Manegold@cwi.nl writes:
I see --- if you consider it a bug, please file a detailed bug report including information & instructions to reporduce the problem as well as infornmation about the exact MonetDB version and OS you're using via http://bugs.MonetDB.org/
I would not consider it a bug. The catch is the uppercase letter in deliveryDays. The default handling of upper/lowercase characters is different than in other RDMBS. So in some cases the quotes are necessary.
Arjen de Rijke
Stefan
----- Oorspronkelijk bericht ----- Van: Freddy Priyatna freddy.priyatna@gmail.com Verzonden: maandag 25 oktober 2010 16:32 Aan: Communication channel for MonetDB users monetdb-users@lists.sourceforge.net Onderwerp: Re: [MonetDB-users] Querying date type column in MonetDB
Hi Stefan,
Nope, the name of the column is "deliveryDays" :)
Freddy
2010/10/25 Stefan Manegold Stefan.Manegold@cwi.nl
Hi Freddy,
I don't think that the error you experience is triggered by the column type, but rather by the fact that your date column ha? a name - say "date" - that is a reserved key word in SQL an hence needs to be double-quoted according to the SQL standard (this is not a MonetDB specific choice, but a "feature" of the SQL standard) --- not knowing your table & column names, I can only guess, though ...
Stefan
----- Oorspronkelijk bericht ----- Van: Freddy Priyatna freddy.priyatna@gmail.com Verzonden: maandag 25 oktober 2010 16:03 Aan: MonetDB-users monetdb-users@lists.sourceforge.net Onderwerp: [MonetDB-users] Querying date type column in MonetDB
Dear All,
I am new to MonetDB, so most likely my question will look obvious.
I would like to ask, how can columns with date type is queried in monetdb. Based on my experience, it seems that date type columns need special treatment. It seems that the only way to query them is with the syntax "column_name" while other columns seems to work with the format table_name.column_name without the need of double quote. When I tried to query date column with the syntax table_name.column_name, it gave me an error "no such column"
So my question is, is it true that the only way to query date columns is using "column_name" format? Another question will be, what happen if I need to query same name columns coming from different table?
Thanks in advance, Freddy
Nokia and AT&T present the 2010 Calling All Innovators-North America contest Create new apps & games for the Nokia N8 for consumers in U.S. and Canada $10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store http://p.sf.net/sfu/nokia-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Nokia and AT&T present the 2010 Calling All Innovators-North America contest Create new apps & games for the Nokia N8 for consumers in U.S. and Canada $10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store http://p.sf.net/sfu/nokia-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Dear Arjen,
Thanks. I did check the uppercase letter. With a date-type column "deliveryDays" in a table "bsbm.offer", here is the result - SELECT "deliveryDays" FROM bsbm.offer --> WORKS - SELECT "deliverydays" FROM bsbm.offer --> FAILS - SELECT "offer.deliveryDays" FROM bsbm.offer --> FAILS - SELECT deliveryDays FROM bsbm.offer --> FAILS - SELECT offer.deliveryDays FROM bsbm.offer --> FAILS - SELECT offer."deliveryDays" FROM bsbm.offer ---> WORKS
The last query is the one I need :)
Again, thanks for the help.
Regards Freddy
2010/10/25 Arjen de Rijke Arjen.de.Rijke@cwi.nl
Stefan Manegold Stefan.Manegold@cwi.nl writes:
I see --- if you consider it a bug, please file a detailed bug report
including information & instructions to reporduce the problem as well as infornmation about the exact MonetDB version and OS you're using via http://bugs.MonetDB.org/
I would not consider it a bug. The catch is the uppercase letter in deliveryDays. The default handling of upper/lowercase characters is different than in other RDMBS. So in some cases the quotes are necessary.
Arjen de Rijke
Stefan
----- Oorspronkelijk bericht ----- Van: Freddy Priyatna freddy.priyatna@gmail.com Verzonden: maandag 25 oktober 2010 16:32 Aan: Communication channel for MonetDB users <
monetdb-users@lists.sourceforge.net>
Onderwerp: Re: [MonetDB-users] Querying date type column in MonetDB
Hi Stefan,
Nope, the name of the column is "deliveryDays" :)
Freddy
2010/10/25 Stefan Manegold Stefan.Manegold@cwi.nl
Hi Freddy,
I don't think that the error you experience is triggered by the column type, but rather by the fact that your date column ha? a name - say
"date" -
that is a reserved key word in SQL an hence needs to be double-quoted according to the SQL standard (this is not a MonetDB specific choice,
but a
"feature" of the SQL standard) --- not knowing your table & column
names, I
can only guess, though ...
Stefan
----- Oorspronkelijk bericht ----- Van: Freddy Priyatna freddy.priyatna@gmail.com Verzonden: maandag 25 oktober 2010 16:03 Aan: MonetDB-users monetdb-users@lists.sourceforge.net Onderwerp: [MonetDB-users] Querying date type column in MonetDB
Dear All,
I am new to MonetDB, so most likely my question will look obvious.
I would like to ask, how can columns with date type is queried in
monetdb.
Based on my experience, it seems that date type columns need special treatment. It seems that the only way to query them is with the syntax "column_name" while other columns seems to work with the format table_name.column_name without the need of double quote. When I tried to query date column with the syntax table_name.column_name, it gave me an error "no such column"
So my question is, is it true that the only way to query date columns is using "column_name" format? Another question will be, what happen if I
need
to query same name columns coming from different table?
Thanks in advance, Freddy
Nokia and AT&T present the 2010 Calling All Innovators-North America contest Create new apps & games for the Nokia N8 for consumers in U.S. and
Canada
$10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store http://p.sf.net/sfu/nokia-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Nokia and AT&T present the 2010 Calling All Innovators-North America
contest
Create new apps & games for the Nokia N8 for consumers in U.S. and
Canada
$10 million total in prizes - $4M cash, 500 devices, nearly $6M in
marketing
Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store http://p.sf.net/sfu/nokia-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
--
CWI, Kamer L330 Centrum voor Wiskunde en Informatica Science Park 123 Email: arjen.de.rijke@cwi.nl 1098 XG Amsterdam tel: +31-(0)20-5924305 Nederland +31-(0)6-51899284 fax: +31-(0)20-5924312 ===================== http://www.cwi.nl/~rijke/ ====================
Nokia and AT&T present the 2010 Calling All Innovators-North America contest Create new apps & games for the Nokia N8 for consumers in U.S. and Canada $10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store http://p.sf.net/sfu/nokia-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On 25-10-2010 17:09:27 +0200, Freddy Priyatna wrote:
Dear Arjen,
Thanks. I did check the uppercase letter.
With a date-type column "deliveryDays" in a table "bsbm.offer", here is the result
- SELECT "deliveryDays" FROM bsbm.offer --> WORKS
case matches
- SELECT "deliverydays" FROM bsbm.offer --> FAILS
case mismatches
- SELECT "offer.deliveryDays" FROM bsbm.offer --> FAILS
column named 'offer.deliveryDays' doesn't exist, you probably mean "offer"."deliveryDays"
- SELECT deliveryDays FROM bsbm.offer --> FAILS
this is weird, I'd expect it to match (as everything not quoted is lowercased, as per the standard)
- SELECT offer.deliveryDays FROM bsbm.offer --> FAILS
idem
- SELECT offer."deliveryDays" FROM bsbm.offer ---> WORKS
sort of the same as the first one.
offer.deliverydays should work as well.
Thanks Fabian
But - SELECT offer.deliverydays FROM bsbm.offer doesnt work.
So the best way for me is - SELECT "offer"."deliveryDays" FROM bsbm.offer
Regards Freddy
2010/10/25 Fabian Groffen Fabian.Groffen@cwi.nl
On 25-10-2010 17:09:27 +0200, Freddy Priyatna wrote:
Dear Arjen,
Thanks. I did check the uppercase letter.
With a date-type column "deliveryDays" in a table "bsbm.offer", here is
the
result
- SELECT "deliveryDays" FROM bsbm.offer --> WORKS
case matches
- SELECT "deliverydays" FROM bsbm.offer --> FAILS
case mismatches
- SELECT "offer.deliveryDays" FROM bsbm.offer --> FAILS
column named 'offer.deliveryDays' doesn't exist, you probably mean "offer"."deliveryDays"
- SELECT deliveryDays FROM bsbm.offer --> FAILS
this is weird, I'd expect it to match (as everything not quoted is lowercased, as per the standard)
- SELECT offer.deliveryDays FROM bsbm.offer --> FAILS
idem
- SELECT offer."deliveryDays" FROM bsbm.offer ---> WORKS
sort of the same as the first one.
offer.deliverydays should work as well.
Nokia and AT&T present the 2010 Calling All Innovators-North America contest Create new apps & games for the Nokia N8 for consumers in U.S. and Canada $10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store http://p.sf.net/sfu/nokia-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On 25-10-2010 17:27:39 +0200, Freddy Priyatna wrote:
Thanks Fabian
But
- SELECT offer.deliverydays FROM bsbm.offer
doesnt work.
I was confused last time. Everything that is typed is lowercased, hence also offer.deliveryDays, and does not match the "deliveryDays" one.
So the best way for me is
- SELECT "offer"."deliveryDays" FROM bsbm.offer
which matches the original creation, and is the best way in any case.
Sorry for the long wait.
participants (4)
-
Arjen de Rijke -
Fabian Groffen -
Freddy Priyatna -
Stefan Manegold