Re: SQL to validate data from multiple files

On 09 Aug 2013 06:21, Gqcy wrote:

I am trying to validate some diverse data in one pass, and am not
quite there... two elements, Part number, Inventory location.

the part number must be valid.
the location must be valid.
the combination must either not exist (when we put parts away),
or do exist (when we take parts out).

select
COALESCE(VLOCPF.LLOLOC, ' ') as LLLOC
,COALESCE(ITEMPF.ITEM, ' ') as IITEM
,COALESCE(LOCPF.LITILO, ' ') as LITILO
,COALESCE( LOCPF.LITITM, ' ') as LITITM
from datalib.LOCPF as LOCPF
cross join datalib.VLOCpf as VLOCPF
cross join datalib.ITEMPF as ITEMPF
where VLOCPF.LLOLOC = '32AA00002'
and ITEMPF.ITEM = '30962980'
and (LOCPF.LITILO = '32AA00002' and LOCPF.LITITM = '30962980')

This works for my first two files, but when I add the 3rd validation
(the combination of part location), I do not return ANY data...

Correctly returning no rows... hopefully.?

Using a cross join just to force the lookup into one statement is not a
good choice, unless the files will have almost no rows; i.e. if a key is
unavailable, the run could take extreme storage and run-time. Best to
avoid the Cartesian product except when the data is known to be very
small sets.

Presumably those are uniquely keyed across those selections, so there
can be only one row from each file that matches the selection. If so,
then the following fetched INTO :indicators should establish the
existence for the values as a logical true\false; which I infer is what
the above query, in effect, wants to accomplish.

select
COALESCE( (select '1' from datalib.VLOCPF as VLOCPF
where VLOCPF.LLOLOC = '32AA00002' /* :HVloc */ )
, '0' ) as LLLOCexists
, COALESCE( (select '1' from datalib.ITEMPF as ITEMPF
where ITEMPF.ITEM = '30962980' /* :HVitm */ )
, '0' ) as IITEMexists
, COALESCE( (select '1' from datalib.LOCPF as LOCPF
where LOCPF.LITILO = '32AA00002' /* :HVloc */
and LOCPF.LITITM = '30962980' /* :HVitm */ )
, '0' ) as "Itm+LocExists"
from sysibm.sysdummy1

If cursors are maintained over multiple /validations/ then each as its
own separate query should do just as well.... but they would no longer
be in just one SQL statement.