Re: SQL to validate data from multiple files

[Michael Schutte <mschutte369@xxxxxxxxx>]

<correction>
You ------ could also do this.

SELECT IBMREQD FROM sysibm/sysdummy1
where itemisvalid('30962980') = '1'
  and itemlocationisvalid('30962980','32AA00002') = '1'


Change the itemlocationisvalid to not equal to '1' for the reverse.
</correction>


On Fri, Aug 9, 2013 at 1:02 PM, Michael Schutte <mschutte369@xxxxxxxxx>wrote:

> Sometimes is better to take the complexity out and write it separately.
>
> Personally, I'd put each scenario in it's own SQL UDF.
>
> this is an rpgle solution.  but you'll get the point.
>
> clear valid;
> exec sql
> Select '1'
> into :valid
> from itempf
> where item = '30962980'
>  and itemlocationisvalid(item, '32AA00002') = '1'
>
> If either the item is invalid or the location, then '1' will not be placed
> into host variable valid.
>
>
> You should also do this.
>
> SELECT IBMREQD FROM sysibm/sysdummy1
> where itemisvalid('30962980') = '1'
>   and itemlocationisvalid('30962980','32AA00002') = '1'
>
>
> Change the itemlocationisvalid to not equal to '1' for the reverse.
>
>
>
>
> On Fri, Aug 9, 2013 at 9:21 AM, Gqcy <gmufasa01@xxxxxxxxx> 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...
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.