HOWEVER, you have to be real careful if you're modifying old code to replace those "set var" with "select into" because they work differently in case of where no values are found. Issue #1: Let's say you have a variable that has a value already. Let's say that vBillID = 100 In the old "set var" days, if you did this expression without re-initializing first (which will have a "no rows" result): SET VAR vBillID = BillID FROM billmatrix WHERE shipper = 999999 The result of it is vBillID is NULL, which is what you would expect
But if you had that vBillID = 100, and then did this: SELECT billid INTO vBillID ind iv1 FROM billmatrix WHERE shipper = 999999 it retains the previous vBillID = 100. Issue #2: In the 'set var' days, you did not have to initialize the variable. So if vBillID doesn't exist, the 'set var' would create the variable with a null value. However, the 'select into' sort of fails and does not create the variable at all. There is no vBillID, which might trip up subsequent code. The fix to both the issues is to obviously make sure you initialize the variable to null before you do the 'select into'. I'm sure all of us do that when writing new code, but if you're converting OLD code you might have to remember to add that in! Karen -----Original Message----- From: jan johansen <[email protected]> To: rbase-l <[email protected]> Sent: Wed, Feb 6, 2019 1:28 pm Subject: RE: [RBASE-L] - SQL tutorial Javier, I normally use the "SELECT Colname INTO...INDICATOR" as well.Works great. The only time I have an issue is when it could return more than one row. Thanks, Jan From: <[email protected]> To: <[email protected]> Date: Wed, 6 Feb 2019 12:31:56 -0600 Subject: RE: [RBASE-L] - SQL tutorial Jan, A while back I stopped using the “SET VAR =…” syntax and replaced (when possible) with the “SELECT ColName INTO…INDICATOR …” construct as R:azzak example shows. As I understand, it reflects the more standard SQL syntax and works great. Javier, Javier Valencia, PE O: 913-829-0888 C: 913-915-3137 From: [email protected] [mailto:[email protected]] On Behalf Of jan johansen Sent: Wednesday, February 06, 2019 12:06 PM To: [email protected] Subject: Re: [RBASE-L] - SQL tutorial Razzak, Thank you. I was actually replacing the existence check with this when I posted the question. I think elsewhere in the system when I look at WHILE SQLCODE = 0 or WHILE SQLCODE <> 100 my brain got really tied in knots. Thx again, Jan From: "A. Razzak Memon" <[email protected]> To: [email protected], [email protected] Date: Tue, 05 Feb 2019 20:28:00 -0500 Subject: Re: [RBASE-L] - SQL tutorial Jan, Here is one approach to validate the existence of a row ... SET VAR vRows INTEGER = 0 SELECT COUNT(*) INTO vRows INDICATOR iv1 FROM PODetail WHERE PO_ID = .vPOID AND ReceiptStatus = 'P' IF vRows = 0 THEN CLS PAUSE 2 USING 'No record found!' CAPTION 'Receipt Status' ICON WARNING + BUTTON 'Press any key to continue ...' + OPTION BACK_COLOR WHITE + |MESSAGE_FONT_NAME Tahoma + |MESSAGE_FONT_COLOR RED + |MESSAGE_FONT_SIZE 12 GOTO Done ENDIF -- Do what you have to do here ... -- Example CLS EDIT USING ReceiptStatus WHERE PO_ID = .vPOID AND ReceiptStatus = 'P' LABEL Done CLEAR VARIABLES iv%,vRows RETURN Hope it helps! Razzak At 06:41 PM 2/5/2019, jan johansen wrote: >All, > >I think I need a SQL refresher. >I need to update old code that looks like this. > >SET ERROR VAR hold >SET VAR vchk = ReceiptStatus IN PODetail WHERE PO_ID = .vPOID AND >ReceiptStatus = 'P' >IF hold = 0 THEN > >So is >IF hold = 0 the same as >IF SQLCODE = 0? > >Sorry such a dumb question. > >Jan > >-- >For group guidelines, visit ><http://www.rbase.com/support/usersgroup_guidelines.php>http://www.rbase.com/support/usersgroup_guidelines.php >--- >You received this message because you are subscribed to the Google >Groups "RBASE-L" group. >To unsubscribe from this group and stop receiving emails from it, >send an email to ><mailto:[email protected]>[email protected]. >For more options, visit ><https://groups.google.com/d/optout>https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.-- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout. #yiv2071345570 #yiv2071345570 -- _filtered #yiv2071345570 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv2071345570 {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} #yiv2071345570 #yiv2071345570 p.yiv2071345570MsoNormal, #yiv2071345570 li.yiv2071345570MsoNormal, #yiv2071345570 div.yiv2071345570MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:New;} #yiv2071345570 a:link, #yiv2071345570 span.yiv2071345570MsoHyperlink {color:blue;text-decoration:underline;} #yiv2071345570 a:visited, #yiv2071345570 span.yiv2071345570MsoHyperlinkFollowed {color:purple;text-decoration:underline;} #yiv2071345570 p {margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:New;} #yiv2071345570 span.yiv2071345570EmailStyle18 {color:#1F497D;} #yiv2071345570 span.yiv2071345570SpellE {} #yiv2071345570 .yiv2071345570MsoChpDefault {font-size:10.0pt;} _filtered #yiv2071345570 {margin:1.0in 1.0in 1.0in 1.0in;} #yiv2071345570 div.yiv2071345570WordSection1 {} #yiv2071345570 -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
