Stored Procedure Error

Hi, I've written a procedure to return a concatenated version of multiple comments records for each distinct account reference. When I execute it I get an "Invalid Day in date" error message. The two fields used in the procedure are character fields (23 & 70) are the size of the fields in the table. The note field is user inputted comments and some included dates input. We are on Informix 11.50.0000 FC7 CREATE procedure BSC_DocumentNotes () RETURNING CHAR(23),LVARCHAR (32739) ;DEFINE fmtacc CHAR(23); --used to store the unique id for the account DEFINE comtxt LVARCHAR (32739); --used to store my concatenated text DEFINE note CHAR(70); --used to store text from source table -- ============================================ -- Document note information - Create temporary table to store and return concatenated file note information from audmnote. -- ============================================ FOREACH accountcursor for SELECT distinct fil_num into fmtacc from audmnote LET comtxt = ''; FOREACH filenotecursor FOR select fil_not into note from audmnote where fil_num = fmtacc order by seq_num LET comtxt = comtxt + note; END FOREACH; --INSERT into dmnote (fmt_acc,com_text) values (fmtacc,comtxt); RETURN fmtacc,comtxt WITH RESUME; END FOREACH; END PROCEDURE;

What are the actual SQLCode and ISAM Codes that you are getting back? Meanwhile, try this one change to the concatenation line: CREATE procedure BSC_DocumentNotes () RETURNING CHAR(23),LVARCHAR (32739) ;DEFINE fmtacc CHAR(23); --used to store the unique id for the account DEFINE comtxt LVARCHAR (32739); --used to store my concatenated text DEFINE note CHAR(70); --used to store text from source table -- ============================================ -- Document note information - Create temporary table to store and return concatenated file note information from audmnote. -- ============================================ FOREACH accountcursor for SELECT distinct fil_num into fmtacc from audmnote LET comtxt = ''; FOREACH filenotecursor FOR select fil_not into note from audmnote where fil_num = fmtacc order by seq_num LET comtxt = RTRIM(comtxt) + RTRIM(note); END FOREACH; --INSERT into dmnote (fmt_acc,com_text) values (fmtacc,comtxt); RETURN fmtacc,comtxt WITH RESUME; END FOREACH; END PROCEDURE; Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Oct 21, 2013 at 9:02 PM, ADAM WOOD <adam.m.wood@outlook.com> wrote: > Hi, > I've written a procedure to return a concatenated version of multiple > comments > records for each distinct account reference. > > When I execute it I get an "Invalid Day in date" error message. The two > fields > used in the procedure are character fields (23 & 70) are the size of the > fields in the table. The note field is user inputted comments and some > included dates input. > > We are on Informix 11.50.0000 FC7 > > CREATE procedure BSC_DocumentNotes () > RETURNING CHAR(23),LVARCHAR (32739) ;> DEFINE fmtacc CHAR(23); --used to store the unique id for the account > DEFINE comtxt LVARCHAR (32739); --used to store my concatenated text > DEFINE note CHAR(70); --used to store text from source table > > -- ============================================ > > -- Document note information - Create temporary table to store and return > concatenated file note information from audmnote. > > -- ============================================ > > FOREACH accountcursor for > SELECT distinct fil_num into fmtacc from audmnote> > LET comtxt = ''; > > FOREACH filenotecursor FOR > > select fil_not into note from audmnote where fil_num = fmtacc order by > seq_num > > LET comtxt = comtxt + note; > > END FOREACH; > --INSERT into dmnote (fmt_acc,com_text) values (fmtacc,comtxt); > RETURN fmtacc,comtxt WITH RESUME; > END FOREACH; > > END PROCEDURE; > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11345d84b1e1f804e94a1af5

Hi Art, Thanks for your response. I tried the rtrim on both variables but still get <eb1>Invalid day in date State:S1000,Native:-1206,Origin:[Informix][Informix ODBC Driver][Informix]</eb1> I'm using QTODBC as a query tool.

Oh, DUH! I spotted it originally and ignored it. You are using the wrong operator (+) for string concatenation. It should be double pipes (||). So, the engine is trying to add up the strings algebraically. In order to do that it first tried to cast the strings to numerics, dates, datetimes, etc. as appropriate to the contents. That's why you are getting an invalid data error! The correct LET statement should be: LET comtxt = comtxt || note; SInce both comtxt and note are lvarchar, you probably don't need the TRIMs. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Oct 21, 2013 at 9:24 PM, ADAM WOOD <adam.m.wood@outlook.com> wrote: > Hi Art, > Thanks for your response. I tried the rtrim on both variables but still get > > <eb1>Invalid day in date > State:S1000,Native:-1206,Origin:[Informix][Informix ODBC > Driver][Informix]</eb1> > > I'm using QTODBC as a query tool. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e011827aed6d61504e94a857f

Art, You are a legend. That's what I get for working in a company with Informix and sql databases. Rookie mistake that one. Thanks. Adam