Обсуждение: what's wrong in this procedure?

This error is returned Erro de SQL:

ERROR:  column "Aguardando Pagto" does not exist at character 352  >>>


create or replace function get_historico()   RETURNS SETOF twiste.type_cur__historico AS '
  SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS transacoes  FROM ofertas o  JOIN transacao t ON
o.ofertas_id= t.ofertas_id  JOIN municipio m ON o.municipio_id = m.municipio_id WHERE  o.data_fim <= now() AND t.status
IN("AguardandoPagto", "Em análise", "Aprovado", "Completo") GROUP BY o.data_fim; 
'
language 'sql';



>> the column is t.status and not "Aguardando Pagto";
>> what's wrong ?  thanks

On 02/25/2011 09:46 AM, Camaleon wrote:
> This error is returned Erro de SQL:
>
> ERROR:  column "Aguardando Pagto" does not exist at character 352>>>
>
>
> create or replace function get_historico()   RETURNS SETOF twiste.type_cur__historico AS '
>
>     SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS transacoes
>     FROM ofertas o
>     JOIN transacao t ON o.ofertas_id = t.ofertas_id
>     JOIN municipio m ON o.municipio_id = m.municipio_id
>    WHERE  o.data_fim<= now() AND t.status IN("Aguardando Pagto", "Em análise", "Aprovado", "Completo")
>    GROUP BY o.data_fim;
> '
> language 'sql';
>
>
>
>>> the column is t.status and not "Aguardando Pagto";
>>> what's wrong ?  thanks
>

Try single quotes, 'Aguardando Pagto'

-- 
Adrian Klaver
adrian.klaver@gmail.com

On 2011-02-25 19:33, Adrian Klaver wrote:
> On 02/25/2011 09:46 AM, Camaleon wrote:
>> This error is returned Erro de SQL:
>>
>> ERROR:  column "Aguardando Pagto" does not exist at character 352>>>
>>
>>
>> create or replace function get_historico()   RETURNS SETOF 
>> twiste.type_cur__historico AS '
>>
>>     SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, 
>> count(t.*) AS transacoes
>>     FROM ofertas o
>>     JOIN transacao t ON o.ofertas_id = t.ofertas_id
>>     JOIN municipio m ON o.municipio_id = m.municipio_id
>>    WHERE  o.data_fim<= now() AND t.status IN("Aguardando Pagto", "Em 
>> análise", "Aprovado", "Completo")
>>    GROUP BY o.data_fim;
>> '
>> language 'sql';
>>
>>
>>
>>>> the column is t.status and not "Aguardando Pagto";
>>>> what's wrong ?  thanks
>>
>
> Try single quotes, 'Aguardando Pagto'
and also
create or replace function get_historico()   RETURNS SETOF 
twiste.type_cur__historico AS $$
instead of
create or replace function get_historico()   RETURNS SETOF 
twiste.type_cur__historico AS '

together with the corresponding
$$
language 'sql';
instead of
'
language 'sql';

at the end

regards,
Yeb Havinga

On 02/25/2011 10:46 AM, Camaleon wrote:
> This error is returned Erro de SQL:
> 
> ERROR:  column "Aguardando Pagto" does not exist at character 352  >>>
> 
> 
> create or replace function get_historico()   RETURNS SETOF twiste.type_cur__historico AS '
> 
>    SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS transacoes
>    FROM ofertas o
>    JOIN transacao t ON o.ofertas_id = t.ofertas_id
>    JOIN municipio m ON o.municipio_id = m.municipio_id
>   WHERE  o.data_fim <= now() AND t.status IN("Aguardando Pagto", "Em análise", "Aprovado", "Completo")
>   GROUP BY o.data_fim;
> '
> language 'sql';
> 
> 
> 
>>> the column is t.status and not "Aguardando Pagto";
>>> what's wrong ?  thanks
> 

Single quotes (') not double quotes (")

On Feb 25, 2011, at 11:16 PM, Camaleon wrote:

> This error is returned Erro de SQL:
>
> ERROR:  column "Aguardando Pagto" does not exist at character 352  >>>
>
>
> create or replace function get_historico()   RETURNS SETOF twiste.type_cur__historico AS '
>
>   SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS transacoes
>   FROM ofertas o
>   JOIN transacao t ON o.ofertas_id = t.ofertas_id
>   JOIN municipio m ON o.municipio_id = m.municipio_id
>  WHERE  o.data_fim <= now() AND t.status IN("Aguardando Pagto", "Em análise", "Aprovado", "Completo")
>  GROUP BY o.data_fim;
> '
> language 'sql';


try Following:
create or replace function get_historico()   RETURNS SETOF twiste.type_cur__historico AS $$
 SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS transacoes FROM ofertas o JOIN transacao t ON
o.ofertas_id= t.ofertas_id JOIN municipio m ON o.municipio_id = m.municipio_idWHERE  o.data_fim <= now() AND t.status
IN('AguardandoPagto', 'Em análise', 'Aprovado', 'Completo')GROUP BY o.data_fim; 
$$
language 'sql';

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com