SQL Left Join unmatched null handling.

10 pts.
I am using SQL Left Join. My null values in unmatched returns are producing the following message when I Fetch them into my host variables: (SQL0305)"A FETCH, an embedded SELECT, a CALL or a SET or VALUES INTO statement has resulted in a null value, but an indicator variable was not specified for host variable xxx..." Question: How can I make my host variables capable of accepting null values... or is there another solution?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi Stoogenut,

Generally, not only in the case of NULLS resulting from
outer joins, but also for NULLS fetched from the database,
when using a tool that implements EMBEDDED SELECT, then
you should use indicator variables to detect the NULLS
For example, many years ago, when I used Oracle Pro*Cobol,
then the technique was as follows:
1. use a set of host variables for collecting the fetched
2. use another set of host NUMERIC variables as indicator
variables (same number as the above host variables)
3. before performing the FETCH (after opening the SELECT
cursor, a.s.o.), we always initialized all the host
variables above (numeric host variables to ZERO, string
host variables to SPACES).
Usually, the host variables were always ARRAYS of size
1000 for better performance (less FETCH-es performed).

The FETCH itself looked like this:

FETCH my_cursor
INTO :my_var1:my_indicator_var1,
:my_var2:my_indicator_var2, …

( with the proper number of INTO variables ).

That is, each host return variable is followed immediately
by an indicator variable (separated by a second colon).

After the FETCH, if the fetched value was a NULL value,
then the host return variable remained as it was initialized before the FETCH, but the indicator host
variable contained -1.
This was the ONLY indication that the fetched value was
in fact a NULL and not a ZERO or a SPACES value.

In the specific case of Oracle Pro*Cobol, using the host
indicator variables was NOT MANDATORY, except for the case
that you wanted to be able to recognize the NULLS.
If this was not required by the program’s logic, then the
host indicator variables could been left off, without
causing any error.
Maybe this is not the case with other tools using embedded

The usage of the indicator variables is the same for
INSERT and UPDATE statements, when you intend to INSERT
a NULL into the database, for example:

INSERT INTO my_table (col1)
VALUES (:my_var1:my_indicator_var1);

If my_indicator_var1 is set to -1, then a NULL will be
inserted into the database.

Hope this information could help.

Best regards,

Discuss This Question: 5  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Stoogenut
    Thank you, ludith, for your information. I find that the indicator variables have to be SMALLINT type. How are these defined, and where? Do they have to exist in the same table as the potential null values? I am using embedded SQL in RPGLE and trying to left join several files in a Declare Cursor statement. The potential null files contain no SMALLINT variables, so these must be otherwise defined (if possible)
    10 pointsBadges:
  • BigKat
    You can supply a default value with COALESCE select f1.fld, COALESCE(f2.fld,0.00) from file1 f1 left outer join file2 f2 on f1.jfld = f2.jfld
    9,460 pointsBadges:
  • TheQuigs
    Here's the title page of the 'SQL Programming with Host Languages' from the documentation in the Information Center: http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzajp/rzajpmst02.htm#ToC Scroll down and you'll find two sections of interest: 'Using host variables in ILE RPG for iSeries applications that use SQL' and 'Using indicator variables in ILE RPG for iSeries applications that use SQL' Here's the link: http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/rzajp/rzajpmst144.htm#HDREXAMPINDICATORIRPG They're both under "Coding SQL Statements in ILE RPG for iSeries Applications"
    0 pointsBadges:
  • Eidolonlynx
    I'd side with BigKat on handling the nulls - in T-SQL, it's commonplace to use ISNULL (which translates into the IFNULL and COALESCE constructs of the RPGLE world) for explicit variable control in outter joins. You just have to look at the business rules for the variable to determine a proxy value for a NULL that is outside the valid range.... As far as variable definitions, SQL Server employs the following integer definitions: tinyint - unsigned, single byte smallint - signed, 2 byte integer (or int) - signed, 4 byte bigint - signed, 8 byte Hope that helps!
    0 pointsBadges:
  • Welcome
    Hi Stoogenut, I understand from another post of yours that this is now working. Anyway, please note that the indicator variables are HOST numberic variables, which have nothing to do with your database columns. Therefore, the documentation for your tool indicates that they should be SMALLINT variables. Other tools may indicate another numeric type, specific for that tool. You define these variables in your program, in addition to the variables that will store your fetched values, with the single difference that, while the regular return variables can be any datatype (string, numeric, a.s.o.), the indicator variables are always numeric. Their only use is to be compared to the value "-1", to detect that the real fetched value was a NULL. Hope everything is clear now. Best rgds, Iudith
    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: