Dennis, In addition "SET EQNULL" is non-standard SQL syntax. I try as much as possible to adhere to standard SQL standards.
I can thus minimized problems should RBase tighten it syntax rules or shouild I have to move programs to another database implementation. Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 >________________________________ > From: Dennis McGrath <[email protected]> >To: RBASE-L Mailing List <[email protected]> >Sent: Thursday, May 24, 2012 9:47 AM >Subject: [RBASE-L] - Re: Difference between "select into" and "setvar" > > >Jim, > >I can see the value of using the indicator variables in stored procedures >to make value change testing robust without resorting to >SET EQNULL ON before testing the before and after variable values. > >One certainly would not want to be flipping environment settings an a stored >procedure! > >Dennis McGrath >Software Developer >QMI Security Solutions >1661 Glenlake Ave >Itasca IL 60143 >630-980-8461 >[email protected] >From:[email protected] [mailto:[email protected]] On Behalf Of James Bentley >Sent: Friday, May 18, 2012 9:40 AM >To: RBASE-L Mailing List >Subject: [RBASE-L] - Re: Difference between "select into" and "setvar" > >Dennis, > >I use then in my stored procedures that check if a column has changed as a >result of an update. The following is an example. Try to write the IF >statement in paragraph 4 in order to test if a say > the HExtraAdrLn has changed. The difficult part is that >Case 1: the old may have been null and the new is not >Case 2: the old has value and new is null >Case 3: both are null. >Using the Indicator variable values the test is easy just do the <> test: >TBU048PIv03 <> .TBU048NIv03 >Case 1: -1 <> 0 - Data changed >Case 2: 0 <> -1 - Data changed >Case 3: -1 <> -1 - Data unchanged > >Try encapsulating of of those tests together along with the TBU048PHExtraAdrLn ><> .TBU048NHExtraAdrLn > > >SELECT MbrNumber, HAdrSource, HMainAdrLine, HExtraAdrLine, HCity,+ >HStateUSPSCode, HUSPSZipCode, HCountry, HPhoneNbr, HCellNbr, HomeChgDate + >INTO TBU048PMbrNumber, TBU048PHAdrSource IND TBU048PIv01,+ >TBU048PHMainAdrLn IND TBU048PIv02, TBU048PHExtraAdrLn IND TBU048PIv03,+ >TBU048PHCity IND TBU048PIv04, TBU048PHStateUSPS IND TBU048PIv05,+ >TBU048PHUSPSZip IND TBU048PIv06, TBU048PHCountry IND TBU048PIv09,+ >TBU048PHPhoneNbr IND TBU048PIv10, TBU048PHCellNbr IND TBU048PIv11,+ >TBU048PPrvHOrgDate IND TBU048PIv13, FROM MbrCurHomeAdr + >WHERE CURRENT OF SYS_OLD >-- >SELECT MbrNumber, HAdrSource, HMainAdrLine, HExtraAdrLine, HCity,+ >HStateUSPSCode, HUSPSZipCode, HCountry, HPhoneNbr, HCellNbr, HomeChgDate + >INTO TBU048NMbrNumber, TBU048NHAdrSource IND TBU048NIv01,+ >TBU048NHMainAdrLn IND TBU048NIv02, TBU048NHExtraAdrLn IND TBU048NIv03,+ >TBU048NHCity IND TBU048NIv04, TBU048NHStateUSPS IND TBU048NIv05,+ >TBU048NHUSPSZip IND TBU048NIv06, TBU048NHCountry IND TBU048NIv09,+ >TBU048NHPhoneNbr IND TBU048NIv10, TBU048NHCellNbr IND TBU048NIv11,+ >TBU048NPrvHOrgDate IND TBU048NIv13, FROM MbrCurHomeAdr + >WHERE CURRENT OF SYS_NEW >-- >SELECT CurHomeAdrCnt,CurHomeAudAction,CurHomeAudDate,CurHomeAudTime,+ >PrvHomeAdrCnt + >INTO TBU048RCntCHomeAdr IND TBU048RIv01, TBU048RCurHAudAct IND TBU048RIv02,+ >TBU048RCurHAudDate IND TBU048RIv03, TBU048RCurHAudTime IND TBU048RIv04,+ >TBU048RCntPHomeAdr IND TBU048RIv05 FROM RegisterOfMbrs + >WHERE MbrNumber = .TBU048PMbrNumber > >IF TBU048PHMainAdrLn <> .TBU048NHMainAdrLn + >OR + >OR TBU048PHCity <> .TBU048NHCity OR TBU048PHStateUSPS <> .TBU048NHStateUSPS + >OR TBU048PHPhoneNbr <> .TBU048NHPhoneNbr + >OR TBU048PHCellNbr <> .TBU048NHCellNbr OR TBU048PIv02 <> .TBU048NIv02 + >OR TBU048PIv03 <> .TBU048NIv03 OR TBU048PIv04 <> .TBU048NIv04 + >OR TBU048PIv05 <> .TBU048NIv05 OR TBU048PIv06 <> .TBU048NIv06 + >OR TBU048PIv09 <> .TBU048NIv09 OR TBU048PIv10 <> .TBU048NIv10 + >OR TBU048PIv11 <> .TBU048NIv11 THEN > SET VAR TBI053FromOtherTrg INTEGER = 1 > INSERT INTO MbrPrvHomeAdr (MbrNumber, HAdrSource, HMainAdrLine,+ > HExtraAdrLine, HCity, HStateUSPSCode, HUSPSZipCode, HCountry, HPhoneNbr,+ > HCellNbr, PrvHomeOrgDate, PrvHomeAudAction, PrvHomeAudDate) + > VALUES (.TBU048PMbrNumber, .TBU048PHAdrSource, .TBU048PHMainAdrLn,+ > .TBU048PHExtraAdrLn, .TBU048PHCity, .TBU048PHStateUSPS, .TBU048PHUSPSZip,+ > .TBU048PHCountry, .TBU048PHPhoneNbr, .TBU048PHCellNbr, .TBU048PPrvHOrgDate,+ > 'Chg ', .#DATE) > SET VAR TBU048SwInsPrv = 1 > SET VAR TBU048RCntPHomeAdr = (1 + .TBU048RCntPHomeAdr) >ENDIF >Jim Bentley >American Celiac Society >[email protected] >tel: 1-504-737-3293 > >________________________________ > >From:Dennis McGrath <[email protected]> >To: RBASE-L Mailing List <[email protected]> >Sent: Friday, May 18, 2012 3:59 AM >Subject: [RBASE-L] - Re: Difference between "select into" and "setvar" > > > >It seems that some users of this list find my statements below to be offensive. >I appoligize for any upset I may have caused. > >Does anyone out there have an example of how they use use the indicator >variables after the data has been retrieved? > >I'm always interested in making my code more efficient, and would welcome any >examples where this is acheived with indicator variables. > >Dennis McGrath >[email protected] >[email protected] > > >On Thu, May 17, 2012 at 10:57 AM, Dennis McGrath <[email protected]> wrote: >Now that we can easily turn off the message about a null value being returned, >I find the indicator variables totally unnecessary code bloat. > >In the rbase environment, at least, If I want to know if a null is returned I >can test the actual value returned. In over 25 years of progamming rbase, the >only use I ever had for the indicator variables was to avoid throwing the >error to make tracing easier. > >Dennis McGrath >[email protected] >[email protected] > > > > > > >
