My understanding is that the indicator var will NOT be set if no rows are returned. i.e...
R> CLEAR VAR vI R> SELECT column INTO vTest INDICATOR vI FROM table WHERE 1=2 R> SHO VAR vI -ERROR- Variable vI is not defined. ( 20) The indicator var is set to zero if a non-null value is assigned to vTest or it is set to -1 if a null value is SELECTed. Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: [email protected] [mailto:[email protected]] On Behalf Of Walker, Buddy Sent: Wednesday, May 16, 2012 8:59 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Difference between "select into" and "set var" Karen If you look at it like this The sum is actually trying to add something so it starts with 0. If nothing is there then it is 0. The other is retrieving a value from the column if nothing is there then it cannot set it to something different so whatever it was set to before the select into it retains that value. I always use an indicator as but I never check it. I believe if it doesn't find anything the indicator will be -1. I find it easier just to assign a value or null to the variable before each select into Buddy From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Wednesday, May 16, 2012 8:25 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Difference between "select into" and "set var" Good idea, to use the indicator variable. I never use the "set var" myself, always prefer the "select into", but it's good to know that if you're re-doing old code you have to account for the differences. And a reminder of how important it is if you use "select into" you need to either initialize or use an indicator. I know Mike said it makes sense that it works this way, but if in my example I said something like this: select sum(number) into vsum from hours where client = 'zzz' and the where clause fails, it does correctly return a '0' rather than erroring out and retaining a previous sum. But a simple select number into vsum from hours where client = 'zzz' will throw an error and retain a previous number. It doesn't make sense to me that the 2 act differently. Karen In a message dated 5/15/2012 6:57:51 PM Central Daylight Time, [email protected]<mailto:[email protected]> writes: I too like the SELECT ... INTO ... option but I would include INDICATOR variables to test for NULL values.
