See if this helps:
https://enskill.com/embedded-sql-and-nulls/
It looks like you can use a second DS to contain the null flags.
-----Original Message-----
From: Rob Berendt [mailto:rob@xxxxxxxxx]
Sent: Tuesday, July 30, 2019 1:17 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL selecting into a data structure with null variables
I am not interested in converting the null values into something else. For example I do not want to change from Select thisvalue To Select ifnull(thisvalue, '') Now that I have that out of the way let me explain the situation.
I am reading from a UDTF. I am trying to read into a data structure.
It is not an externally defined data structure.
This data structure has two null capable subfields. I added the two null indicator subfields to the end of the data structure.
This is now causing
DCL-DS Security_journal QUALIFIED;
entry_timestamp timestamp;
sequence_number packed(21 : 0);
system_sequence_number packed(21 : 0);
object_type varchar(10);
user_profile_changed varchar(10);
command_type char(3);
password_was_changed char(1);
no_password char(1);
password_expired char(1);
currently_allobj char(1);
currently_secadm char(1);
previously_allobj_je char(1);
previously_allobj_j5 char(1);
previously_secadm_je char(1);
previously_secadm_j5 char(1);
group_profile_je varchar(10);
group_profile_j5 varchar(10);
remote_address varchar(46) nullind(remote_address_nullind);
remote_port int(10) nullind(remote_port_nullind);
current_user varchar(10);
job_name varchar(10);
job_user varchar(10);
job_number varchar(6);
program_name varchar(10);
remote_address_nullind ind;
remote_port_nullind ind;
END-DS;
The select statement itself only selects 24 columns.
exec sql fetch c1 into :security_journal; Selecting 24 columns into a data structure with 26 subfields results in
SQL0326 -
Message . . . . : Too many variables specified.
Cause . . . . . : 26 variables were specified on the FETCH, embedded SELECT,
SET, or VALUES INTO statement, but only 24 columns were returned from the
query. Extra global variables will not be changed. Extra host variables
will be filled with the appropriate value for the specified type:
SQLSTATE = 01557 Too many variables have been specified on SELECT INTO or FETCH.
Before I added the NULLIND and the additional columns I was getting errors when those two columns were null SQLSTATE = 22002 A null value, or the absence of an indicator parameter was detected; for example, the null value cannot be assigned to a variable, because no indicator variable is specified.
SQL0305
Message . . . . : Indicator variable required.
Cause . . . . . : A FETCH, embedded SELECT, CALL, GET DESCRIPTOR, or a SET
or VALUES INTO statement has resulted in a null value, but an indicator
variable was not specified for host variable REMOTE_ADDRESS. The relative
position of the host variable in the INTO clause or parameter list is 18. If
the host variable name is *N, a descriptor area was specified.
If this error occurs on a GET DESCRIPTOR statement, the null value is
being returned but the INDICATOR item was not specified on the GET
DESCRIPTOR statement.
Recovery . . . : Specify an indicator variable, and precompile the program
again.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
midrange.com
