Re: Allow null in data structure

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

Yeah, I've struggled with nulls a bit, Jeff.  I'm still not exactly a 
fan, but I've gotten used to them. A couple of things:

1. The syntax is strange.  Don't separate the field variable and the 
null indicator variable with a comma:

into :dmitmmstDS :dmitmmstNull, :wmsupcDS :wmsupcNull;


2. You can use an arbitrary-sized array, as long as it's at least big 
enough.  I don't have any files with more than 1000 fields, so I can 
just create an array of 1000 indicators and it will work for 
everything.  Not a perfect solution by any means, but it does help.



On 2/14/2019 6:47 AM, Jeff Crosby wrote:
> Eeewww.  You have to know how many fields.
>
> I'm using a cursor, so I've got this
>
>       D wmsupcNull      S              5I 0 Dim(10)
>
>       D wmsupcDS      E DS                  ExtName(WMSUPC)
>       D                                     Inz
>       D                                     Prefix(u)
>
>       D dmitmmstNull    S              5I 0 Dim(143)
>
>       D dmitmmstDS    E DS                  ExtName(DMITMMST)
>       D                                     Inz
>       D                                     Prefix(i)
>
> the declare:
>
>            Exec SQL
>              Declare MyCursor cursor for
>                Select i.*,
>                       dmitmmstNull,
>                       u.*,
>                       wmsupcNull
>                  From WMSUPC u Full Outer
>                  Join DMITMMST i ON u.UPITEM = i.ITNBR
>                  Order By i.WHSLC,
>                           i.ITNBR;
>
> which barfs with
> "SQL0206: Position 22 Column or global variable WMSUPCNULL not found."
>
> and the fetch:
>
>            Exec SQL
>              Fetch Next from MyCursor
>                into :dmitmmstDS, :dmitmmstNull, :wmsupcDS, :wmsupcNull;
>
> which also barfs with
> "SQL0312: Position 34 Variable DMITMMSTNULL not defined or not usable.
> Reason: The variable is a dimensioned array."
> "SQL0312: Position 60 Variable WMSUPCNULL not defined or not usable.
> Reason: The variable is a dimensioned array."
>
> The "Database Embedded SQL Programming" does not have any RPG null example
> that I can find.
>
>
>
> On Wed, Feb 13, 2019 at 4:43 PM Alan Campin <alan0307d@xxxxxxxxx> wrote:
>
> > No, SQL reference.
> >
> > D CustomerDS      DS
> >   D  CustID                        7P 0
> >   D  CustName                     30A
> >   D  CustSales                     7P 2
> >
> >   D CustomerInds    S              5I 0 Dim(3)
> >
> >   D NullValue       C                   -1
> >
> >    /Free
> >
> >      Exec SQL
> >        Select CustID, CustName, CustSales
> >          Into :CustomerDS :CustomerInds
> >          From Customer
> >          Where CustID = :InputCust;
> >
> >      If SQLState = '00000';
> >
> >        If CustomerInds(3) = NullValue;
> >            PrintSales = 'No Sales';
> >        Else;
> >            PrintSales = ('Sales were $' + %Char(CustSales));
> >        EndIf;
> >      EndIf;
> >
> >
> > On Wed, Feb 13, 2019 at 1:37 PM Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>
> > wrote:
> >
> > > The RPG reference?  OK, I'll look.
> > >
> > > On Wed, Feb 13, 2019 at 4:34 PM Alan Campin <alan0307d@xxxxxxxxx> wrote:
> > >
> > > > Allow Null and %NullInd are for file I/O, not SQL.
> > > >
> > > > In SQL you create array of small int, one for each field in the data
> > > > structure. If null, array element is set to non-zero. All described in
> > > the
> > > > manuals.
> > > >
> > > > On Wed, Feb 13, 2019 at 1:24 PM Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx
> > > > wrote:
> > > >
> > > > > I'm doing an SQL full outer join of 2 files looking for exceptions
> > both
> > > > > ways.  The select is into data structures.
> > > > >
> > > > > Obviously there will be instances where one or the other of the files
> > > has
> > > > > nothing so the DS fields will be null.  I've got a  ALWNULL(*USRCTL)
> > > > > statement.  I've got an  If  %Nullind (iITNBR) in the code.
> > > > >
> > > > > The compiler barfs trying to put ALWNULL on the DS definition.  How
> > do
> > > I
> > > > > specify ALWNULL on a DS?  My googling skills are lacking today.
> > > > >
> > > > > Thanks.