× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.