Sunday, July 13, 2008

[SQL] how to perform minus (-) operation in a dynamic query


Hai all,

I am new to plpgsql ,I have a  table  structure:

 Column |       Type       | Modifiers
--------+------------------+-----------
 mf     | double precision |
 sf     | double precision |
 comm   | integer          |

I create a  the following funtion

create or replace function test_perc() returns setof record as $body$

declare

vchr_query VARCHAR(100);

r record;

begin

vchr_query:= 'SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim';

FOR r in EXECUTE vchr_query LOOP

RETURN NEXT r;

END LOOP;

RETURN;

end$body$
language 'plpgsql'


function created

but when I am traing to run this function I got the following error

ERROR:  syntax error at or near "–" at character 18
QUERY:  SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim
CONTEXT:  PL/pgSQL function "test_perc" line 7 at for over execute statement
LINE 1: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as...


How I can solve this ,pls help me


thanks in advance:
Anoop



No comments: