Mike,
Another possibility is that the numbers in your macro-substitution variable,
with hyphens in them, look like expressions requiring subtraction. It never
hurts to put single-quotes around each value in an IN list. And it might
help.
SET VAR vTRP_Melt TEXT = +
( '(' & cval('quote') + '435-10' + cval('quote') + ',' + cval('quote') +
'436-11' + cval('quote') & ')')
Bill
On Wed, Dec 16, 2009 at 10:06 PM, James Bentley
<[email protected]>wrote:
> Mike,
> TBOM syntax analyzer does not like the syntax "IN ( &VTRP_MELT )"
> try SET VAR VTRP_MELT TEXT = '(435-10,436-11)'
> THEN
> TALLY ITEM_NO FROM SHIPMAST WHERE SHIPDATE BETWEEN 1/1/2009 AND 12/15/2009
> AND MELTCODE IN &VTPR_MELT AND SX_FLAG = 'SHIP'
>
> Apparently the syntax analyzer tequires the parenthesis to be included in
> the &variable
> Jim Bentley
> American Celiac Society
> [email protected]
> tel: 1-504-737-3293
>
>
> ------------------------------
> *From:* Ramsour Mike <[email protected]>
> *To:* RBASE-L Mailing List <[email protected]>
> *Sent:* Wed, December 16, 2009 8:37:06 PM
> *Subject:* [RBASE-L] - TALLY behavior
>
> Good evening:
>
> Is this the correct behavior for the TALLY command?
>
> ============================
>
> Parameters:
>
> ITEM_NO column data type: INTEGER
>
> Variable = Value Type
> ------------------ ------------------------------ -------
> VTPR_MELT = 435-10,436-11 TEXT
>
> ============================
>
> This commands gives this result:
>
> TALLY ITEM_NO FROM SHIPMAST WHERE SHIPDATE BETWEEN 1/1/2009 AND 12/15/2009
> AND MELTCODE IN ( &VTPR_MELT ) AND SX_FLAG = 'SHIP'
> <WARNING> No rows exist or satisfy the specified clause. (2059)
>
> Whereas only changing "MELTCODE IN ( &VTPR_MELT )" to "MELTCODE IN
> (435-10,436-11)", i.e. replacing the variable with hard-coded values gives
> the expected result:
>
> R>TALLY ITEM_NO FROM SHIPMAST WHERE SHIPDATE BETWEEN 1/1/2009 AND
> 12/15/2009 AND MELTCODE IN (435-10,436-11) AND SX_FLAG = 'SHIP'
> ITEM_NO Number of Occurrences
> ----------------- ---------------------
> 141758
> 5
> 143077
> 6
> 143206
> 4
> 143272
> 2
> 143341
> 3
> 144400 66
> blah
> blah
> blah
>
> ============================
>
> Both examples copied and pasted directly from the R>.
>
> CROSSTAB works correctly with or without the use of the variable. Am I
> missing something?
>
> Mike Ramsour
>
> Confidentiality Notice
> This message is intended exclusively for the individual or entity to which it
> is
> addressed and may contain privileged, proprietary, or otherwise private
> information.
> If you are not the named addressee, you are not authorized to read, print,
> retain,
> copy or disseminate this message or any part of it. If you have received this
> message in error, please notify the sender immediately by e-mail and delete
> all
> copies of the message.
>
>
>
