Re: [MonetDB-users] Querying date type column in MonetDB

newer
[MonetDB-users] MonetDB Debain and...

older
[MonetDB-users] Copy into issues...

Stefan Manegold

25 Oct 2010 25 Oct '10

2:45 p.m.

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 Verzonden: maandag 25 oktober 2010 16:32 Aan: Communication channel for MonetDB users 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

...

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 Verzonden: maandag 25 oktober 2010 16:03 Aan: MonetDB-users 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

Show replies by date

Freddy Priyatna

25 Oct 25 Oct

2:52 p.m.

New subject: [MonetDB-users] Querying date type column in MonetDB

Dear Stefan, OK, thanks for your help. Freddy 2010/10/25 Stefan Manegold

...

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

...
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 Verzonden: maandag 25 oktober 2010 16:03 Aan: MonetDB-users 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

Arjen de Rijke

2:58 p.m.

New subject: [MonetDB-users] Querying date type column in MonetDB

Stefan Manegold 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 Verzonden: maandag 25 oktober 2010 16:32 Aan: Communication channel for MonetDB users 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

...
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 Verzonden: maandag 25 oktober 2010 16:03 Aan: MonetDB-users 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/ ====================

Freddy Priyatna

3:09 p.m.

New subject: [MonetDB-users] Querying date type column in MonetDB

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

...

Stefan Manegold 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 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

...
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 Verzonden: maandag 25 oktober 2010 16:03 Aan: MonetDB-users 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

Fabian Groffen

3:17 p.m.

New subject: [MonetDB-users] Querying date type column in MonetDB

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.

Freddy Priyatna

3:27 p.m.

New subject: [MonetDB-users] Querying date type column in MonetDB

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

...

...
Dear Arjen,

Thanks. I did check the uppercase letter.

With a date-type column "deliveryDays" in a table "bsbm.offer", here is

On 25-10-2010 17:09:27 +0200, Freddy Priyatna wrote: 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

Fabian Groffen

23 Nov 23 Nov

12:34 p.m.

New subject: [MonetDB-users] Querying date type column in MonetDB

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.

5148

Age (days ago)

5177

Last active (days ago)

List overview

Download

6 comments

4 participants

participants (4)

Arjen de Rijke
Fabian Groffen
Freddy Priyatna
Stefan Manegold