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