I have a database that hold several fields like this:

C018: '[21000..23000],34567,56555,[57000..58000]'
C401: '01.02'

I have to translate this field as follows into a conditional where clause.

DEFINE FILE XXX
VER_C018/A10=IF C018 FROM '21000' TO '23000' OR  C018 EQ '34567' OR C018 EQ '56555' OR 
C018 FROM '57000' TO '58000' THEN '01.02' ;
END

It looks rather easy, but the real challange is even a bit more complex.
The database holds about 200 records and may also chane so coding this by hand is not an option.

I was thinking of first read the string and do something with a replace characters.
The "[" needs to be "FROM " and so on.

This message has been edited. Last edited by: Kerry,

Your best best is probably to read the record, create an alpha hold file, and then read the file line by line as an &var.

First, EDIT out the characters to the left of the first : - that's the field namWith careful use of a few iterations using the STRREP you shoule be able to replace the necessary strings:

: '[ = FROM
,[ = OR (|columname|' FROM '
.. = TO
], = ) OR
, = OR
]' = space

You'll also need some logic (probably including a sum and a LAST) to combing the first record with the second record which becomes the THEN. And don't forget the ; at the end.

Whats the relationship between C018 and C401 ?

I think this could be done with a couple of TABLE FILE's, which would produce a WHERE clause.

there is no real relation between the two fileds Waz
it is just a condition to code a general ledger datafield, to report to the banking authorities

and yes I am thinking in that direction too
creating some define rules

Give this a spin

 
DEFINE FILE CAR
C018/A100 WITH COUNTRY='[21000..23000],34567,56555,[57000..58000]';
C401/A5= '01.02';
WHERE0/A400V=STRREP (100, C018, 2, ',[', 14,' OR C018 FROM ', 400, WHERE0);
WHERE1/A400V=STRREP (400, WHERE0, 1, '[', 10,'C018 FROM ', 400, WHERE1);
WHERE2/A400V=STRREP (400, WHERE1, 2, '..', 4,' TO ', 400, WHERE2);
WHERE3/A400V=STRREP (400, WHERE2, 1, ',', 12,' OR C018 EQ ', 400, WHERE2);
WHERE4/A400V=STRIP(400, WHERE3, ']', WHERE4);
MYDEFINE/A500V= 'IF '| WHERE4 | ' THEN C401 ELSE '| ''' '''|';';
END
TABLE FILE CAR
SUM COUNTRY NOPRINT
C018 OVER
WHERE0 OVER
WHERE1 OVER
WHERE2 OVER
WHERE3 OVER
MYDEFINE
END
 

Thanks Frank, just wondering if there is more the to values than the two lines.

Here is my suggestion, a bit long winded, but relatively dynamic.

EX -LINES 5 EDAPUT MASTER,tmp_gl,CV,FILE
FILENAME=tmp_gl, SUFFIX=FIX,$
SEGNAME=tmp_gl, $
  FIELD=GL_CODE ,ALIAS=  ,A4 ,A5 ,$
  FIELD=GL_DATA ,ALIAS=  ,A250 ,A250 ,$

EX -LINES 3 EDAPUT FOCTEMP,tmp_gl,CV,FILE
C018 [21000..23000],34567,56555,[57000..58000]
C401 01.02

-RUN
FILEDEF TMP_GL DISK tmp_gl.ftm

-RUN

TABLE   FILE TMP_GL
 PRINT  
        COMPUTE
        SET_CMD/A266 = '-SET &|C401 = ''' || GL_DATA || ''';' ;
 WHERE  GL_CODE EQ 'C401'
 ON     TABLE SET HOLDLIST PRINTONLY
 ON     TABLE HOLD AS TMP_C401 FORMAT ALPHA
END

-RUN

-INCLUDE TMP_C401

-RUN

DEFINE  FILE TMP_GL
-* change the ' ' | HEXBYT(10,'A1') to match the EOL for the OS.
 CR_LINE/A300 = STRREP(250,GL_DATA,1,',',2,' ' | HEXBYT(10,'A1'),300,'A300') ;
END

TABLE   FILE TMP_GL
 PRINT  
        CR_LINE
 WHERE  GL_CODE EQ 'C018'
 ON     TABLE HOLD AS TMP_C018 FORMAT ALPHA
END

TABLE   FILE TMP_C018
 PRINT  
        COMPUTE
        CNTR/I9     = LAST CNTR + 1 ; NOPRINT
        COMPUTE
        VALUES/A250 = STRREP(250,CTRAN(250,CTRAN(250,CR_LINE,91,39,'A250'),93,39,'A250'),2,'..',6,''' TO ''',250,'A250') ;
                                      NOPRINT
        COMPUTE
        TEST/A262   = IF VALUES CONTAINS ' TO '
                     THEN '(C018 FROM ' | VALUES || ')'
                     ELSE '(C018 EQ ' | VALUES || ')' ;
                                      NOPRINT
        COMPUTE
        FEX_LINE/A278 = IF CNTR EQ 1
                       THEN 'VER_C018/A10=IF ' | TEST
                       ELSE 'OR ' | TEST ;
 ON     TABLE SET HOLDLIST PRINTONLY
 ON     TABLE HOLD AS FEX_C018 FORMAT ALPHA
END

-RUN

DEFINE FILE XXX
-INCLUDE FEX_C018
THEN '&C401' ELSE ' ' ;
END

Thanks all for your suggestions.
I think the solution as presented by JG is the most transparant one (for me) so I have build this in my report already and it works.
The real world is even more complex, but also that will work