Plex 2E

 View Only
Expand all | Collapse all

CheckRow or SingleFetch for only a part of the key

  • 1.  CheckRow or SingleFetch for only a part of the key

    Posted Feb 25, 2015 01:35 PM

    Hello everyone,

     

    i have the following:

     

    Table with Primary Key Field (Code) char8 Field and has Field (Description) char100

    Example for Data Content:

    Code               Desc

    12345678       Row 1

    12312378       Row 2

    12345611       Row 3

     

    I need to check the first 6 chars of the key field... so if i "check" 123456 the returned Status has to be SUC

     

    How is this possible?

    The checkrow and/or singlefetch return INF because they always want to check the whole key...

     

    Check only a part of the Primary key... i hope my Problem is clearly described?!

     

    Thanks for tips

     

    best regards, tobias



  • 2.  Re: CheckRow or SingleFetch for only a part of the key

    Posted Feb 25, 2015 02:57 PM

    SQL LIKE Operator

     

    For example

     

    SELECT * FROM MyTable

    WHERE Code LIKE '123456%';


    So depends on your variant how you will invoke an SQL command? Most likely you are as400? then look at My BlockFetch dynamically using SQL - The CA Plex Wiki



    Old school would be because Keys are not normally updated then an extra field is added to the entity consiting of the first 6 characters of the key...yuk



  • 3.  Re: CheckRow or SingleFetch for only a part of the key

    Posted Mar 04, 2015 04:14 PM

    select where and like is known, but it doesnt work on iseries i suppose?!

     

    i think i have to write an External rpg program for iseries. for SQL i can use the SQL Statement...



  • 4.  Re: CheckRow or SingleFetch for only a part of the key

    Posted Feb 25, 2015 03:05 PM

    or if you are java, winc, .net etc look at   Filter.Filter.BlockFetch  and

    The Select Where Statement (SQL only) in plex help



  • 5.  Re: CheckRow or SingleFetch for only a part of the key

    Posted Mar 04, 2015 04:13 PM

    hi thank you but i have to implement both.... SQL and iseries.



  • 6.  Re: CheckRow or SingleFetch for only a part of the key

    Posted Feb 26, 2015 11:27 AM

    Why not just create a process group function and position to '12345600'?  If you get a hit return a success.



  • 7.  Re: CheckRow or SingleFetch for only a part of the key

    Posted Mar 04, 2015 03:21 AM

    i was doing this DB2 INTERVIEW QUESTIONS FAQS - MAINFRAME    and seeing how little I really know.....but came across the following which reminded me of this thread

     

     

    5) How do you retrieve the first 5 characters of FIRSTNAME column of DB2 table EMP ?

     

    SQL Query : SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP;

     

    But Rob process group is an ace idea as I was busy thinking char but 'code' i guess is number.

     



  • 8.  Re: CheckRow or SingleFetch for only a part of the key

    Posted Mar 04, 2015 04:20 PM

    assumed the data in the table are as follows:

     

    01051111  Weibliche Zucht- und Vermehrungsküken, von Hühnern, mit einem Gewicht von 185g oder weniger, Legerassen                                                                                                                                                 St              61

    01051119  Weibliche Zucht- und Vermehrungsküken, von Hühnern, mit einem Gewicht von 185g oder weniger (ausg. Legerassen)                                                                                                                                          St              61

    01051191  Hühner, lebend, mit einem Gewicht von 185g oder weniger, Legerassen (ausg. weibliche Zucht- und Vermehrungs-  küken)                                                                                                                                    St              61

    01051199  Hühner, lebend, mit einem Gewicht von 185g oder weniger (ausg. Legerassen sowie weibliche Zucht- und          Vermehrungsküken)                                                                                                                         St              61

    01051200  Truthühner, lebend, mit einem Gewicht von 185g oder weniger                                                                                                                                                                                             St              61

    01051300  Enten, lebend, mit einem Gewicht von 185g oder weniger                                                                                                                                                                                                  St              61

    01051400  Gänse, lebend, mit einem Gewicht von 185g oder weniger          


    interesting is the 8 Digit code...

     

    now i want to check if the partial key "010511" is included in the data.

     

    in this case the first 4 rows are "valid"; so i want that my check function Returns SUCCESS...

     

    can i reach this Goal with a processgroup and Position? really?

     

    i developed a processgroup that checks the first 6 Digits and breaks and Returns after first succesful comparsion. but the table has about 10000 rows. Performance is very bad...



  • 9.  Re: CheckRow or SingleFetch for only a part of the key

    Posted Mar 04, 2015 05:31 PM

    Just to make sure...

     

    Are you passing in "01051100" as an input to the process group function or are you just checking each row in the process row edit point for "010511"?  With only 10000 records I would think this would perform rather quickly on iSeries.



  • 10.  Re: CheckRow or SingleFetch for only a part of the key
    Best Answer

    Posted Mar 10, 2015 12:19 PM

    What about writing the function from scratch ?

     

    Accept the full key as input parm (01051100) replace the last 3 digits with zeros.

     

    Use Postion GE and Fetch next , replace the last 3 digits with zeros in the fetched data, compare , set Returning Status accordingly.

     

    Best

    Lorenz



  • 11.  Re: CheckRow or SingleFetch for only a part of the key

    Posted Mar 10, 2015 02:19 PM

    Thanks a lot... this works!!! best regards, tobias