Re: LENGTH() in stored procedure

On Wed, 17 Dec 1997, Nagesh Daliparthy wrote: > I want to retrieve some data from a table , convert all into string, > concatinate with a delimiter and return only one CHAR variable c_2000 of > declared size of 2000. > > I new when I store one character the c_2000 will be padded with spaces upto > 2000. So I tried as follows > > LET c_2000 = c_2000[1, LENGTH(c_2000)] || "|" || "some text" ; > [...] > > While creating the procedure I got error 201 at character position > pointing to LENGTH or p_len as the case may be. Can any one explain > why the error 201 is coming? Because the syntax is erroneous! You can only use literal numbers in substrings in SPL, as in [1,10]. You cannot have any variables, let alone complex expressions like a length function. If you really need 2000 characters, you're pretty much stuck; if you don't really need more than 255, you can probably use VARCHAR to get around the problem. LVARCHAR might work if you have IUS (or is that IDS-UDO now?). This is one of the biggest limitations in SPL. The 7.3 release will have a set of SUBSTR() functions and the like which will presumably be usable in SPL, thereby getting around the problem. In the interim, you can play with horribly long-winded SPs to play with substrings, as in the UPPER and LOWER stored procedures that periodically show up on the list, but they are excruciating to write and slow to execute. Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>