Re: Error in crosstab using date_trunc

Alban Hertroys escribió:
> On 6 Dec 2009, at 4:13, Scott Marlowe wrote:
>
>
>> On Sat, Dec 5, 2009 at 12:11 PM, Jose Maria Terry Jimenez
>> <jtj@tssystems.net> wrote:
>>
>>> Hello all,
>>>
>>> I'm trying to do a crosstab from data that row names are times.
>>>
>>> These times are timestamps and  i  want to  use they truncating to minutes
>>>  this works for me:
>>>
>>> select distinct date_trunc('minute',"timestamp") as "timestamp" from
>>> historico order by "timestamp";
>>>
>>> Getting times "normalized" without seconds.
>>>
>>> If i do a crosstab using that date_trunc function i get errors. If i do:
>>>
>>> select *
>>> from crosstab
>>> (
>>> 'select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
>>> historico order by 1,2'
>>> )
>>> as
>>> (anet timestamp without time zone,
>>> re1 numeric,
>>> re2 numeric,
>>> re3 numeric
>>> )
>>> ;
>>>
>> Looks like an escaping issue. Try replacing your outer ' with $outer$
>> or something like that:
>>
>
>
> It _is_ an escaping issue. The part inside the crosstab consists of 3 literals, namely:
> 'select date_trunc('
> minute
> ',"timestamp") as "timestamp",remota,valor from historico order by 1,2'
>
> You need to escape the quotes around 'minute', either by typing ''minute'' or by using a different quote method for
theouter literal as Scott suggested. Your last option is the non-standard \' escaping. 
>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:737,4b1b914911734630115167!
>
>
>
>
Thank you very much. This worked for me.

Best,
Jose Maria

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiVirus: ClamAV 0.95.2/10115 - Sun Dec  6 09:37:09 2009
by Markus Madlener @ http://www.copfilter.org