What is ABL equivalent for SELECT IN statement

Question

Anyone please help me for ABL equivalent for SELECT IN statement For e.g.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

Answer 1

OR is obviously simplest but since that has been declared as unacceptable perhaps this would meet the need?

define variable i as integer no-undo.
define variable n as integer no-undo.
define variable cityList as character no-undo.

cityList = "boston,new york,chicago,...".

n = num-entries( cityList ).
do for i = 1 to n:
  for each customer no-lock where city = entry( i, cityList ):
    display customer.name customer.city.
  end.
end.

This will avoid table scans and be about as efficient as the OR since the field comparison is an equality match.

Answer 2

CAN-DO Function

* Returns TRUE if a character value exists in a comma-delimited list; otherwise returns FALSE
* Use the CAN-DO function instead of stringing many OR conditions together
* Example

      IF CAN-DO(“A,B,C,D",x) THEN MESSAGE “Value is in set”.

Answer 3

There is no in-built IN operator in Progress 4GL/ABL. You can use OR operator for this purpose. An example:

FOR EACH customer WHERE city = 'boston' OR city = 'salo' OR city = 'paris':
        DISP customer.NAME.
END.

Answer 4

As Austin says: you can use OR.

I will try to elaborate this a bit: using a dynamic query you could create a more flexible approach than a simple "FOR EACH" - like if you have a varying number of OR statements each time you run the query.

A quick and dirty example:

/* Definitions */
DEFINE VARIABLE cQuery       AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cInString    AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cOrStatement AS CHARACTER   NO-UNDO.
DEFINE VARIABLE iEntry       AS INTEGER     NO-UNDO.

/* Defining a temp-table to query */
DEFINE TEMP-TABLE client
    FIELD clientId AS INTEGER
    FIELD city     AS CHARACTER.

/* And a query */
DEFINE QUERY qClient FOR client.

/* Create some bogus data */
CREATE client.
ASSIGN 
    client.clientId = 1
    client.city     = "Rome".

CREATE client.
ASSIGN 
    client.clientId = 2
    client.city     = "Barcelona".

CREATE client.
ASSIGN 
    client.clientId = 3
    client.city     = "Paris".

CREATE client.
ASSIGN 
    client.clientId = 4
    client.city     = "Prague".

/* These are the cities we are searching for */
cInString = "Rome,Stockholm,Prague".

/* Convert the comma-separated list of cities to an "OR-statement" */
DO iEntry = 1 TO NUM-ENTRIES(cInString):
    cOrStatement = cOrStatement + (IF cOrStatement = "" THEN "" ELSE " OR ") + "client.city = " + QUOTER(ENTRY(iEntry,cInString)).
END.

/* Add () around the or-statement just to be sure */
cOrStatement = "(" + cOrStatement + ")".

/* Put together the query */
cQuery = "FOR EACH client WHERE " + cOrStatement.

/* Attach the query-string to the query */
QUERY qClient:QUERY-PREPARE(cQuery).

/* Open the query ...*/
QUERY qClient:QUERY-OPEN().

/* And get the first result */
GET FIRST qClient.

/* Iterate through results as long as there are any... */
DO WHILE AVAILABLE client:
    DISP client WITH FRAME x1 10 DOWN.

    DOWN WITH FRAME x1.
    GET NEXT qClient.
END.

/* Close query */
QUERY qClient:QUERY-CLOSE().

Answer 5

You can use LOOKUP function like this:

DEFINE VARIABLE wc-liste AS CHARACTER  NO-UNDO.
DEFINE VARIABLE wc-sep   AS CHARACTER  NO-UNDO.

wc-sep = ",".

wc-liste = value1 + wc-sep + value2 + wc-sep + value3...

FOR EACH table_name
    WHERE LOOKUP(table_name.column_name, wc-liste, wc-sep) > 0
    NO-LOCK:
    ...
END.

But it's not a good idea for perfomances.

you can also try this:

bcle:
FOR EACH table_name
    NO-LOCK:
    IF LOOKUP(table_name.column_name, wc-liste, wc-sep) = 0 
    THEN 
        NEXT bcle.
    ...
END.