Indicator in SELECT Statement
Hello everyone,
Could anyone describe to me the usage of the INDICATOR keyword in the
INTO clause of a SELECT statement.
I'm writing an ESQL/C program for an OUTER join select statement. Since
the nature of OUTER join will returns a NULL value for the subservient
table which has no rows satisfying the join condition, I need to have to
code to check for its NULL value.
The following is the code snippet for this checking. Hope you can
provide me pointers and comments for the following outlined code.
$DECLARE q_curs CURSOR FOR
SELECT a.key1, a.name, b.key2
FROM tabA a, OUTER tabB b
WHERE a.key1 = b.key1
AND b.key2 != 0;
$OPEN q_curs;
$int var_key1, var_key2, var_name;
$short nullInd;
for (;;) {
$FETCH q_curs INTO $var_key1, $var_name, $var_key2:nullInd;
if (sqlca.salcode == SQLNOTFOUND)
break;
if (nullInd >= 0) {
// This portion of code for var_key2 is NOT NULL???
}
else
{
// This portion of code for var_key2 is = NULL???
}
}
Thanks and have a nice day,
kitming
The coding is right on! Just a style comment I prefer to use the ANSI ESQL
syntax (EXEC SQL ...) rather than the Informix specific syntax ($ ...)
since it is more portable should (heaven forbid!) I have to port the code
to another database. I encourage everyone to do the same, descpite the
extra keystrokes.
Art S. Kagel
Kit Ming wrote:
>
> Hello everyone,
>
> Could anyone describe to me the usage of the INDICATOR keyword in the
> INTO clause of a SELECT statement.
>
> I'm writing an ESQL/C program for an OUTER join select statement. Since
> the nature of OUTER join will returns a NULL value for the subservient
> table which has no rows satisfying the join condition, I need to have to
> code to check for its NULL value.
> The following is the code snippet for this checking. Hope you can
> provide me pointers and comments for the following outlined code.
>
> $DECLARE q_curs CURSOR FOR
> SELECT a.key1, a.name, b.key2
> FROM tabA a, OUTER tabB b
> WHERE a.key1 = b.key1
> AND b.key2 != 0;>
> $OPEN q_curs;
>
> $int var_key1, var_key2, var_name;
> $short nullInd;
>
> for (;;) {
> $FETCH q_curs INTO $var_key1, $var_name, $var_key2:nullInd;
> if (sqlca.salcode == SQLNOTFOUND)
> break;
>
> if (nullInd >= 0) {
> // This portion of code for var_key2 is NOT NULL???
> }
> else
> {
> // This portion of code for var_key2 is = NULL???
> }
> }
>
> Thanks and have a nice day,
> kitming