<big><tt><font size="-1"><big>I'm also stuck on 7.4 for at least a few more months, but that's not part of the
problem.<br/> I've spent hours on this, cutting things out, etc., now I have to give it and me a break.<br /> Anything
standout to anyone?<br /><br /> ===================================<br /> FIRST the pgAdmin error message:<br />
===================================<br/><br /> ERROR: syntax error at or near "loop"<br /> CONTEXT: compile of
PL/pgSQLfunction "fill_advert_n_coupon" near line 92<br /><br /> ********** Error **********<br /><br /> ERROR: syntax
errorat or near "loop"<br /> SQL state: 42601<br /> Context: compile of PL/pgSQL function "fill_advert_n_coupon" near
line92<br /><br /> ==================================<br /> SECOND the code:<br />
==================================<br/> /*<br /><br /> Sales' info contains the fields { offer_title, _text, start,
stop,fineprint }.<br /> This function will take data from temp_salesimport and insert appropriately into<br /> tables
advert,advertdetail, and coupon.<br /><br /> -> The commented out command below was needed only once to assert a
referencecommerce transaction.<br /> -> The content of the insert must be matched by a query in the function
below:<br/> -> INSERT INTO commercetransaction (descrip) VALUES('Entry of Sales-gathered info while free');<br /><br
/>*/<br /> -------------------<br /> CREATE OR REPLACE FUNCTION fill_advert_n_coupon(varchar) RETURNS VOID AS '<br />
<br/> DECLARE daRec RECORD ;<br /> vCommTransID INT ;<br />
vAdvertTypeID INT ;<br /> vAdvertDetailTypeID INT ;<br /> vFieldName
VARCHAR(50);<br /> vBusID BIGINT ;<br /> vBusOwnerID BIGINT ;<br />
vAdvertID INT ;<br /> vValueText VARCHAR(256) ;<br />
vMaxSeq INT ;<br /> vNextSeq INT ;<br /> vValidFrom DATE ;<br
/> vValidTo DATE ;<br /> vRestriction VARCHAR(200) ;<br /> <br /> BEGIN<br
/> <br /> select into vCommTransID id from commercetransaction where descrip=''Entry of Sales-gathered info
whilefree''; -- =4510<br /> <br /> select into vAdvertTypeID id from adverttype where shortname=''CouponOffer''
;-- =1<br /><br /> select into vAdvertDetailTypeID id from advertdetailtype where shortname=''$1'' ; -- =2<br />
<br/> -- Options are title, text, start, stop, fineprint <br /> vFieldName= ''offer_'' || ''$1''<br /> <br />
-- =====================================================================<br /> <br /> FOR daRec IN SELECT *
FROMtemp_salesimport WHERE offer_title<>'''' LOOP<br /><br /> vBusID=daRec.bus_id<br /> <br />
selectinto vBusOwnerID businessownerid from business where id=vBusID ;<br /> <br /> <br /> IF
vFieldName=''offer_title''THEN<br /> select into vValueText offer_title from temp_salesimport where
bus_id=vBusID;<br /> ELSIF vFieldName=''offer_text'' THEN<br /> select into vValueText offer_text
fromtemp_salesimport where bus_id=vBusID ;<br /> ELSIF vFieldName=''offer_start'' THEN<br /> select into
vValueTextoffer_start from temp_salesimport where bus_id=vBusID ;<br /> ELSIF vFieldName=''offer_stop''
THEN<br/> select into vValueText offer_stop from temp_salesimport where bus_id=vBusID ;<br /> ELSIF
vFieldName=''offer_fineprint''THEN<br /> select into vValueText offer_fineprint from temp_salesimport where
bus_id=vBusID;<br /> END IF ;<br /> <br /> <br /> <br /> -- Begin inserting into the
destinationtables advertdetail, advertdetailline, and coupon<br /> --
======================================================================================<br/> if $1=''title'' or
$1=''text''then -- Advert stuff<br /> <br /><br /> insert into advert (adverttypeid, businessid,
businessownerid,<br/> isactive, isenabled, active_date, expire_date, commercetransactionid)<br
/> VALUES (vAdvertTypeID, vBusID, vBusOwnerID,<br /> TRUE, TRUE,
''2010-11-03'',''2011-02-03'',vCommTransID);<br /> <br /><br /> select into vAdvertID MAX(id) from
advert<br/> where adverttypeid=vAdvertTypeID <br /> and businessid=vBusID<br />
and businessownerid=vBusOwnerID <br /> and isactive=TRUE and isenabled=TRUE ;<br />
<br /><br /> select into vMaxSeq seq from advertdetail<br /> where advertid=vAdvertID and
advertdetailtype=vAdvertDetailTypeID;<br /> <br /> vNextSeq:=vMaxSeq+1 ;<br /> <br /><br />
insertinto advertdetail values(vAdvertID, vAdvertDetailTypeID, vValueText, vNextSeq) ;<br /> <br /> else
--Coupon stuff<br /> <br /><br /> select into vValidFrom, vValidTo, vRestriction<br />
offer_start, offer_stop, offer_fineprint<br /> from temp_salesimport<br /> where
bus_id=vBusID;<br /><br /><br /> insert into coupon (businessid, validfrom, validto, restriction)<br />
values(vBusID, vValidFrom, vValidTo, vRestriction) ;<br /> <br /> end if ; -- title
ortext -> advert + advertdetail, else coupon<br /> <br /> end loop ;<br /> <br /> RETURN ;<br
/><br/> -- END ; -- The Fantom one for BEGIN above. <br /><br /> END ; ' LANGUAGE plpgsql<br />
-------------------<br/> select fill_advert_n_coupon('title') ;<br /></big></font></tt></big> <pre
class="moz-signature"cols="72"><big><tt>--
Ralph
_________________________
</tt></big></pre>
