>Hello,
>
>I need help with the following stored procedure that I need to change:
>
>Here is a part of it like it is today:
>-----------------------------------------------
>BEGIN
> SELECT PRODUCTIONLINENR FROM TBLPRODUCTIONLINE WHERE ESXKEY =

:ESXPRODUCTIONLINE into :PRODUCTIONLINENR;

> END
>----------------------------------------------
>
>If :ESXPRODUCTIONLINE = 'L1' and one record in field ESXKEY = 'L1'

then this is no problem.

>
>But now I want to change the data in ESXKEY so it contains several "L

values" in same record separated by semicolon, like this:

>Record 1, ESXKEY = 'L1;L2'
>Record 2, ESXKEY = 'L11;L12'
>Record 3....
>
>:ESXPRODUCTIONLINE will still have only one "L value" eg 'L1'
>
>How can I change my SELECT string to get result from record 1 only, if

I use LIKE %ESXPRODUCTIONLINE%

>I will get match in both record 1 and 2 I believe.
>
>Hope someone can help me.

Hei Tor Jørgen!

The good news is that what you say you want is quite easily doable (I
used execute block since I wanted to check my result without creating a
stored procedure - it's basically the same thing):

EXECUTE BLOCK (ESXPRODUCTIONLINE VARCHAR(10) = :TorJorgen) RETURNS(ID
INTEGER, ESXKEY VARCHAR(50))
AS
BEGIN
FOR SELECT ID, ESXKEY
FROM TBLPRODUCTIONLINE
WHERE ESXKEY CONTAINING :ESXPRODUCTIONLINE
AND SUBSTRING(ESXKEY FROM POSITION(:ESXPRODUCTIONLINE IN
ESXKEY)+CHARACTER_LENGTH(:ESXPRODUCTIONLINE) FOR 1) NOT BETWEEN '0' AND '9'
--The substring just checks that the first letter after the match isn't
a continuation, I take it that all begin with a letter and continue with
only numbers...
INTO :ID, :ESXKEY DO
SUSPEND;
END

The bad news is that this is unlikely to be what you want (like Mark and
Scott said). It should work fine if you only have 1000 records in
TblProductionLine, but since no index can be used for CONTAINING, things
will get slower and slower as TblProductionLine grows and if
TblProductionLine has millions of records and the query is executed
every few seconds, users will get rather annoyed by the system. It is by
far preferable to have have a scalable solution with several normalised
tables and join them whenever there's a need. In your case, that could be:

CREATE TABLE TBLPRODUCTIONLINEESXKEY
(ID INTEGER NOT NULL,
ID_TBLPRODUCTIONLINE INTEGER NOT NULL,
ESXKEY VARCHAR(10) CHARACTER SET ISO8859_1 COLLATE NO_NO,
CONSTRAINT (PK_TBLPRODUCTIONLINEESXKEY PRIMARY KEY(ID));

and then e.g. (there are several alternatives)

SELECT PL.ID, LIST(DISTINCT PLE.ESXKEY)
FROM TBLPRODUCTIONLINE PL
JOIN TBLPRODUCTIONLINEESXKEY PLE ON PL.ID = PLE.ID_TBLPRODUCTIONLINE
JOIN TBLPRODUCTIONLINEESXKEY PLE2 ON PL.ID = PLE2.ID_TBLPRODUCTIONLINE
WHERE PLE2.ESXKEY = :ESXPRODUCTIONLINE
GROUP BY 1

if you want the list.

HTH,
Set (Svein Erling)