[RBASE-L] - RE: SELECT not allowed within IF statement: Solved

[Bruce A. Chitiea]

Here's what works, LISTOF and Ampersanding being the keys:

SELECT LISTOF(calmonthabbr) INTO vCMAList INDIC vInd +
FROM zMonth

SET VAR vDumpty TEXT = ('(' + .vCMAList + ')')

IF vHumpty NOT IN &vDumpty THEN ...

Perfect.

Thanks everyone, for your help

Bruce

-----Original Message-----
From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] On Behalf Of Dennis
McGrath
Sent: Friday, July 26, 2013 10:35 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: SELECT not allowed within IF statement

I don't think that will work, this should:

SET VAR vck TEXT = NULL
--get comma delimited list
SELECT (listof(colname)) into vck INDIC ivck FROM wherever --Add parenthesis
set var vCk = ('(' + .vCk + ')')

--use in ampersand
IF vhumpty NOT IN &vck THEN


if any of your values are likely to have embedded spaces then this is
better:
those are four single quotes, which evaluate to one
SELECT (listof('''' + colname + '''' )) into vck INDIC ivck FROM wherever



Dennis McGrath
Software Developer
QMI Security Solutions
1661 Glenlake Ave
Itasca IL 60143
630-980-8461
dmcgr...@qmiusa.com
-----Original Message-----
From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] On Behalf Of Jim Belisle
Sent: Friday, July 26, 2013 12:22 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: SELECT not allowed within IF statement

Are you setting the items you want to check against to a variable first?
SET VAR vck TEXT = NULL
SELECT listof(your list) into vck INDIC ivck FROM wherever
IF vhumpty NOT IN .vck THEN

-----Original Message-----
From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] On Behalf Of Bruce A.
Chitiea
Sent: Friday, July 26, 2013 12:15 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - SELECT not allowed within IF statement

All:

What I need to do is test a variable against a list of values within an
IF statement.

This works:

      IF vHumpty NOT IN +
      ('JAN','FEB','MAR', ... 'DEC') THEN

I'd rather avoid hard-coding the values; preferring to pull values
dynamically.

All the following attempts fail, producing this error message:

      '-ERROR- Sub-SELECTs are not supported by this command. (2310)'

This fails:

      IF vHumpty NOT IN +
      (SELECT CalMonthAbbr FROM zMonth) THEN

As does this:

      SET VAR vDumpty TEXT =  +
      '(SELECT CalmonthAbbr FROM zMonth)'

      IF vHumpty NOT IN + 
      &vDumpty THEN

As does this:

      SET VAR vGrimmBros TEXT = +
      '(SELECT CalMonthAbbr FROM zMonth)

      SET VAR vDumpty TEXT = .vGrimmBros

      IF vHumpty NOT IN +
      &vDumpty THEN

It must be real simple. I'm just not seeing it.

TIA

Bruce