[RBG7-L] - Re: In clause of where statement

[A. Razzak Memon]

At 09:32 AM 6/7/2005, Ted Wolfley wrote:

I would like to have multiple text values entered in
one dialog box with the text values separated by commas.
The variable from the dialog box would then be used in
the IN clause of a SELECT statement.  The statement works
fine with one value but not more than one.  To use more
than one text value in the IN clause, the text values
must have single quotes around each value.

Ted,

Try the following example and see what you get:

01. Start RBG7, RBG75 or RBG8

02. CONNECT ConComp

03. Create a command file with the following:

    -- start
    LABEL Start
    CLEAR VAR iv%,vYears,vEndKey,vRows,vCaption,vInClause
    SET VAR vYears TEXT = NULL
    SET VAR vEndKey TEXT = NULL
    SET VAR vRows INTEGER = 0
    SET VAR vCaption TEXT = 'Understanding WHERE Clause ...'
    SET VAR vInClause TEXT = NULL
    CLS
    DIALOG 'Enter Years Separated by Comma (e.g: 2000,2002,2004)' +
    vYears=34 vEndKey 1 +
    CAPTION .vCaption ICON APPS +
    OPTION TITLE_FONT_COLOR GREEN +
    |TITLE_BACK_COLOR WHITE +
    |TITLE_FONT_NAME ARIAL +
    |TITLE_BOLD OFF +
    |WINDOW_BACK_COLOR WHITE +
    |BUTTON_OK_CAPTION &Continue +
    |BUTTON_CANCEL_CAPTION C&ancel +
    |BUTTONS_BACK_COLOR WHITE +
    |BUTTON_FONT_COLOR GREEN

    IF vYears IS NULL OR vEndKey = '[Esc]' THEN
       GOTO Done
    ENDIF

    SET VAR vInClause = ('('+.vYears+')')
    SELECT COUNT(*) INTO vRows INDIC ivRows FROM TransMaster +
    WHERE (IYR4(TransDate)) IN &vInClause

    IF vRows = 0 THEN
       CLS
       PAUSE 2 USING 'No Record(s) Found!' +
       CAPTION .vCaption +
       ICON INFO
       GOTO Start
    ENDIF

    CLS
    BROWSE * FROM TransMaster +
    WHERE (IYR4(TransDate)) IN &vInClause +
    ORDER BY TransID +
    OPTION WINDOW_STATE MAXIMIZED|CAPTION 'Sales Data' +
    |ROWVIEW ON

    LABEL Done
    CLS
    CLEAR VAR iv%,vYears,vEndKey,vRows,vCaption,vInClause
    RETURN
    -- end

04. RUN commandfile.ext

Have fun!

Very Best R:egards,

Razzak