how to perform minus (-) operation in a dynamic query
От | Anoop G |
---|---|
Тема | how to perform minus (-) operation in a dynamic query |
Дата | |
Msg-id | 1a027d210807132251l3cc6780eq8edde7c70469b1f5@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: how to perform minus (-) operation in a dynamic query
("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Re: how to perform minus (-) operation in a dynamic query (Markus Wanner <markus@bluegap.ch>) Re: how to perform minus (-) operation in a dynamic query (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-sql |
<br />Hai all,<br /><br />I am new to plpgsql ,I have a table structure:<br /><br /> Column | Type | Modifiers<br/>--------+------------------+-----------<br /> mf | double precision |<br /> sf | double precision|<br /> comm | integer |<br /><br />I create a the following funtion <br /><br /><p style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">create or replace function test_perc()returns setof record as $body$</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed,serif"><font size="3">declare </font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed,serif"><font size="3">vchr_query VARCHAR(100);</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVuSerif Condensed, serif"><font size="3">r record;</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVuSerif Condensed, serif"><font size="3">begin</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVuSerif Condensed, serif"><font size="3">vchr_query:= 'SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100)as flt_claim';</font></font><div class="Ih2E3d"><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed,serif"><font size="3">FOR r in EXECUTE vchr_query LOOP</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVuSerif Condensed, serif"><font size="3">RETURN NEXT r;</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVuSerif Condensed, serif"><font size="3">END LOOP;</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVuSerif Condensed, serif"><font size="3">RETURN;</font></font></div><p style="margin-bottom: 0.2in;"><font face="DejaVuSerif Condensed, serif"><font size="3">end$body$<br />language 'plpgsql'</font></font><p style="margin-bottom:0.2in;"><br /><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">functioncreated</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">butwhen I am traing to run this function I got the following error</font></font><p style="margin-bottom: 0.2in;">ERROR: syntax error at or near "–" at character 18<br /> QUERY: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf *comm/100) as flt_claim<br />CONTEXT: PL/pgSQL function "test_perc" line 7 at for over execute statement<br />LINE 1: SELECTmf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as...<br /><p style="margin-bottom: 0.2in;">How I can solve this,pls help me<p style="margin-bottom: 0.2in;"><br />thanks in advance:<br />Anoop<br /><p style="margin-bottom: 0.2in;"><br/><br />