You're thinking of the SQLCODE, Kayza, which tells you if there is
another row of data that meets the WHERE clause.
Indicator vars tell you if there is data in the columns of an existing row.
This was from Oma Cox many years ago:
From my passed experience the SQLCODE does the following
= 0 - the action (edit,select,etc with/without where clause) has a
result (at least one row of data meet your request)
= 100 - the action (edit,select,etc with/without where clause) has no
results (there is no rows that meet your request)
Hope this helps,
Oma
e.g.
FETCH...(assuming there is at least 1 row of data, SQLCODE = 0)
WHILE SQLCODE <> 100 THEN
code...
FETCH...(After last row has been fetched, a subsequent fetch will
result in SQLCODE = 100)
ENDWHILE
There have been many discussions on this list about valid alternate
methods of determining whether there are more rows of data.
I'm only citing the WHILE SQLCODE to answer the question.
Doug
On 2/18/2013 5:02 PM, Kayza Kleinman wrote:
I had always thought that the indicator variable was to let me know if
there were any rows left in the cursor.
*From:*[email protected] [mailto:[email protected]] *On Behalf Of
*Doug Hamilton
*Sent:* Monday, February 18, 2013 5:02 AM
*To:* RBASE-L Mailing List
*Subject:* [RBASE-L] - RE: Variable in Cursor
You can use the same indicator var for all variables; the results
would be useless but it would prevent error messages when reading null
values into a variable. You could then test specific variables for
nullness.
SELECT vvar1 INDICATOR vind1, ...vvarn INDICATOR vind1
However, you can assign a specific indicator variable to a (or more,
or all) variable(s) that you want to test for being null. Then test
the specific ind var for -1.
SELECT vvar1 INDICATOR vind1, ... vvarx INDICATOR vindx,... vvarn
INDICATOR vind1
I'm not sure (at this time in the morning) of the pros & cons of
testing the variable for null vs testing the ind var for -1.
I s'pose testing the ind var for -1 is more definitive and foolproof
than testing the variable for null because of SET NULL ON/OFF and SET
NULL commands.
And, at some time in the future, use of indicator vars might be
required, so there'd be less code to crawl through and fix.
Doug
On 2/18/2013 1:09 AM, Bruce Chitiea wrote:
Shouldn't INDICATOR variables be assigned on a per-variable basis?
SELECT vvar1 INDICATOR vind1, ... vvarn INDICATOR vindn
-------- Original Message --------
Subject: [RBASE-L] - RE: Variable in Cursor
From: Kayza Kleinman <[email protected]
<mailto:[email protected]>>
Date: Sun, February 17, 2013 3:59 pm
To: [email protected] <mailto:[email protected]> (RBASE-L
Mailing List)
This is the relevant code:
declare inRows cursor for +
select Clientid,LASTNAME,FSTNAME,m_i_,MaidenName from
CCClientsStage2
OPEN inRows
FETCH inRows into vClientid, vLastname, vFstname, vMI,
vMaidenName +
indicator vIndicator
.
.
.
set var vFullName = .vLASTNAME+ ',' & .vFSTNAME& .vMI
set var vMainID = clientid from maininfo where +
tot_name = .vFullname
Tot_Name is a field that concatenates Lastname, Fstname and
MI. I had originally tried the comparison using each variable
to each field. Lastname and Fstaname always have a value. MI
is often blank. In the concatenated field, I just see nothing
when there is a blank in MI, but in the variable I see -0-
-----Original Message-----
From: [email protected] <mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Buddy
Sent: Sunday, February 17, 2013 4:28 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Variable in Cursor
Kayza
How are you comparing the null value. Also make sure you are
using the indicator when selecting values from the column
FETCH c1 INTO varName INDIC ivarName ....
IF varName IS NULL THEN
Do something here
ENDIF
You could also test for NULL
IF ivarName = -1 THEN
Do something here because varName IS NULL
ENDIF
Hope this helps.
Buddy
-----Original Message-----
From: [email protected] <mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Kayza Kleinman
Sent: Sunday, February 17, 2013 3:33 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Variable in Cursor
I have a cursor set up and I'm comparing the values in the
cursor to values in a different table. The problem is that
when I pull the values in the cursor into variables, if the
value in the column is null, the value of the variable shows
up as a literal -0-. This is a problem because the when I do a
comparison to the main table, it doesn't work. When there is a
value it does work.
Any ideas?
Kayza Kleinman
CIO &
Director, Nonprofit Helpdesk
Jewish Community Council of Greater Coney Island, Inc
3001 West 37th Street
Brooklyn NY 11224
718 449-5000 x 2266
fax 718 946-8240
http://kayzasblog.nphd.org/
www.jccgci.org <http://www.jccgci.org><http://www.jccgci.org/>
www.nphd.org <http://www.nphd.org><http://www.nphd.org/>
Helping you do good - better.
