Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to perform minus (-) operation in a dynamic query

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

From
"Anoop G"
Date:
<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 />

Re: how to perform minus (-) operation in a dynamic query

From
"A. Kretschmer"
Date:
am  Mon, dem 14.07.2008, um 11:21:17 +0530 mailte Anoop G folgendes:
> SELECT mf,sf,(mf   mf * comm /100)   (sf   sf * comm/100) as flt_claim;                 ^^^               ^^^   ^^^

That's not valid SQL.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: how to perform minus (-) operation in a dynamic query

From
Markus Wanner
Date:
Hi,

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

Simply use a real minus sign '-', and not a hyphen '–'. (Try 
copy'n'pasting from this email, if nothing else works ;-) )

Regards

Markus

Re: how to perform minus (-) operation in a dynamic query

From
Tom Lane
Date:
"Anoop G" <anoopmadavoor@gmail.com> writes:
> ERROR:  syntax error at or near "\226" at character 18
> QUERY:  SELECT mf,sf,(mf \226 mf * comm /100) \226 (sf \226 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 \226 mf * comm /100) \226 (sf \226 sf * comm/100) as...

I'm not sure what character \226 is, but it's not a minus sign ...
        regards, tom lane

Re: how to perform minus (-) operation in a dynamic query

From
"Anoop G"
Date:
<div dir="ltr">Hai all,<br /><br /> Thanks to all , I got the answer,  actualy I am write the function in <a
href="http://openoffice.org">openoffice.org</a>and paste it to a .sql file that is the reason for the error.<br /><br
/>thanks<br />Anoop<br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><div class="gmail_quote">On Mon, Jul 14,
2008at 7:32 PM, Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> wrote:<br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
"AnoopG" <<a href="mailto:anoopmadavoor@gmail.com">anoopmadavoor@gmail.com</a>> writes:<br /> > ERROR:  syntax
errorat or near "\226" at character 18<br /> > QUERY:  SELECT mf,sf,(mf \226 mf * comm /100) \226 (sf \226 sf *
comm/100)as<br /><div class="Ih2E3d">> flt_claim<br /> > CONTEXT:  PL/pgSQL function "test_perc" line 7 at for
overexecute statement<br /></div>> LINE 1: SELECT mf,sf,(mf \226 mf * comm /100) \226 (sf \226 sf * comm/100)
as...<br/><br /> I'm not sure what character \226 is, but it's not a minus sign ...<br /><br />                      
 regards,tom lane<br /></blockquote></div><br /></div>