Professional Web Applications Themes

error : syntax error at or near $1 for over select rows - PostgreSQL / PGSQL

This is the error i am getting when calling select * from cas_reset_qi_changedate('CAS','2003-02-03' ERROR: syntax error at or near "$1" at character 25 CONTEXT: PL/pgSQL function "cas_reset_qi_changedate" line 15 at for over select rows pgsql function : CREATE or replace FUNCTION qe13.CAS_RESET_QI_CHANGEDATE (VARCHAR,TIMESTAMP) RETURNS INTEGER AS ' DECLARE AGR_UNID ALIAS for $1; M_COMM_CLOSE_DATE ALIAS for $2; DEFAULT_LOWEST_DATE timestamp default ''1900-01-01 00:00:00.0''; vQuotaInstanceGID varchar(34) default null; vQIStartDate timestamp; vQIEndDate timestamp; for1 record; BEGIN FOR for1 in select qi.tril_gid as vQuotaInstanceGID,qi.startdate as vQIStartDate,qi.enddate as vQIEndDate from cm_quotainstance as qi, cm_quota as q, fs_agr as a where a.fs_unid = AGR_UNID and a.fs_model = ...

  1. #1

    Default error : syntax error at or near $1 for over select rows


    This is the error i am getting when calling select * from
    cas_reset_qi_changedate('CAS','2003-02-03'

    ERROR: syntax error at or near "$1" at character 25
    CONTEXT: PL/pgSQL function "cas_reset_qi_changedate" line 15 at for over
    select rows

    pgsql function :
    CREATE or replace FUNCTION qe13.CAS_RESET_QI_CHANGEDATE (VARCHAR,TIMESTAMP)
    RETURNS INTEGER AS '


    DECLARE
    AGR_UNID ALIAS for $1;
    M_COMM_CLOSE_DATE ALIAS for $2;
    DEFAULT_LOWEST_DATE timestamp default ''1900-01-01 00:00:00.0'';
    vQuotaInstanceGID varchar(34) default null;
    vQIStartDate timestamp;
    vQIEndDate timestamp;
    for1 record;

    BEGIN


    FOR for1 in select qi.tril_gid as vQuotaInstanceGID,qi.startdate as
    vQIStartDate,qi.enddate as vQIEndDate from cm_quotainstance as qi, cm_quota
    as q, fs_agr as a where a.fs_unid = AGR_UNID and a.fs_model = q.model and
    qi.quota = q.tril_gid LOOP

    if (vQIStartDate > M_COMM_CLOSE_DATE OR vQIEndDate
    <= M_COMM_CLOSE_DATE) then
    update cm_quotainstance set changedate =
    DEFAULT_LOWEST_DATE where tril_gid = vQuotaInstanceGID;
    end if;
    END LOOP;

    RETURN 0;


    END;
    ' LANGUAGE 'plpgsql';

    I know the problem is that we cant use a variable name in a select query.
    But then how can I use this variable later in "if" statement (say
    vQIStartDate variable)
    Thanks and Regards,
    Vinita Bansal

    __________________________________________________ _______________
    NRIs send 10 photos FREE to India.
    [url]http://creative.mediaturf.net/creatives/icicibank/june/kodak/OTP.htm[/url] And win
    a FREE ticket to India.


    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    vinita bansal Guest

  2. #2

    Default Re: error : syntax error at or near $1 for over select rows


    On Dec 27, 2004, at 11:36 AM, vinita bansal wrote:
    > FOR for1 in select qi.tril_gid as
    > vQuotaInstanceGID,qi.startdate as vQIStartDate,qi.enddate as
    > vQIEndDate from cm_quotainstance as qi, cm_quota as q, fs_agr as a
    > where a.fs_unid = AGR_UNID and a.fs_model = q.model and qi.quota =
    > q.tril_gid LOOP
    >
    > if (vQIStartDate > M_COMM_CLOSE_DATE OR
    > vQIEndDate <= M_COMM_CLOSE_DATE) then
    > update cm_quotainstance set changedate =
    > DEFAULT_LOWEST_DATE where tril_gid = vQuotaInstanceGID;
    > end if;
    > END LOOP;
    >

    for1 is a record type from which you can access the other columns. So
    with something like

    FOR for1 in select * from cm_quotainstance where ... loop

    then you can access the columns in your subsequent if statements like:

    if (for1.startdate > > M_COMM_CLOSE_DATE OR for1.enddate <=
    M_COMM_CLOSE_DATE) ...

    Look in the plpgsql section of the doentation under "Looping Through
    Query Results" -- this is section 36.7.4 in the 8.0 doentation.


    Best,

    John DeSoi, Ph.D.
    [url]http://pgedit.com/[/url]
    Power Tools for PostgreSQL


    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    John DeSoi Guest

Similar Threads

  1. Replies: 6
    Last Post: September 2nd, 01:22 PM
  2. Syntax error
    By middletree in forum Macromedia ColdFusion
    Replies: 11
    Last Post: May 2nd, 03:10 AM
  3. SELECT: Syntax error. Please help
    By sam in forum PHP Development
    Replies: 14
    Last Post: September 29th, 10:32 PM
  4. Error Syntax Error
    By Ray at in forum ASP
    Replies: 1
    Last Post: August 13th, 03:40 PM
  5. Select rows where other related rows don't exist
    By Dmand in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 06:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139