Re: Embedded SQL For Screen Load -- RPG400-L

SQL's wildcard search can do both, more or less.

If one searched for "J%" one would get all records that started with "J" - in effect that would be a position-to, at least for the J's.
If one searched for %toy% one would select all records where "toy" was anywhere in the search field - a filter, if you will.

After reading the original post this afternoon I decided to write a simple SQL search/filter program <http://www.martinvt.com/SQL_-simple-/SQL_Filtered_Subfile/sql_filtered_subfile.html> that would show this feature. I'd be interested in what folks think of it. The screen shot shows the records selected when the search is %nte%. Included were records with international, center, enterprises, or Hunter. This is a 140-line program. Simple, fast, and intuitive to users.

Also, it uses a scroll bar.

On 12/14/2016 9:59 PM, midrange wrote:

Jerry,

Even though sql may not seem adapted to position & read, and I know if can
be worked out as Denis indicated, another suggestion - first of all expand
what the users can select by as Charles indicated (or not you can still do
the sql.. but this can be a big improvement to the user experience):
If this is a "starts with" name and since everyone is also used to google
searching why not offer both methods
Search for names starting with ____________ or contains ___________________
(if this was a html page it could be an autocomplete)

So if user selects starting with JOHNSON they see
JOHNSON & ACKERMAN
JOHNSON AT THE PARK
JOHNSON BROTHERS
... more

Or if contains JOHNSON
They would see all the others above, plus ABLE JOHNSON, THE JOHNSON CO, MARY
JOHNSON, etc.

What I usually do is take the sql out of the subfile part (and this makes
the retrofit of an old program easier, by putting the SQL code in a separate
called program, and returning a large number of records - say 1000 for a
name search, and return in the parms the key to the 1000 records, put into
an array, and run the sfl paging by increment or decrement the index of the
array or data structure. The selection may return 2, 5, 500, or the full
1000 records, & I usually don't have to worry about them rolling thru the
whole set.

By making the selection screen more flexible to the user, dynamically
building the search string, the user can combine things in ways a setll/read
never handled:
So if user wants customer names in states FL, GA, AL that have the name
JOHNSON somewhere in the name, and had an order in last year
In the rpgle, build the sql stmt in a large character field
SELECT custkey from customer where state in('FL','GA','AL' ) and name like
'%JOHNSON%' and lastorddt>='2015-12-14'
Then execute the sql, writing the custkey into an array
Return to SFL program
Use the array to drive your sfl...
If this makes sense, let me know - I can't share the code unless I heavily
sanitize it. The code I have is not that modern - late 90's to mid 2000's
(not there anymore) but it's my code for sampling.
Just to give ideas of search screens, off the top of my head, (name, cities
or states, balance ranges, address searches to street name or even ranges of
address #'s of a street, phone#, fax#, email address, order statuses,
whatever... None of this has to be in a single file.
Indexing can be important for performance - but quick searching can be made
over millions of records with right indexing, joins, and sql construction.

Jim Franz

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Denis
Robitaille
Sent: Wednesday, December 14, 2016 3:10 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: RE: Embedded SQL For Screen Load

Hello,

I have a template program that does just that. It does position to and
allows page up or down. It also have a simple filter option. I re wrote it
last year to be pure SQL. It works but, as said, SQL is not very well suited
for this.
If you are interested, I can email you privately the source code. But please
note that all comments are in French.

Denis Robitaille
Chef de service TI - Solution d'entreprise Infrastructure et opérations

CASCADES CENTRE DES TECHNOLOGIES
412 Marie Victorin
Kingsey falls(Québec) Canada J0A 1B0
Tél : 819 363 6100 Poste :52130
Cell : 819 352 9362

-----Message d'origine-----
De : RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Charles
Wilt
Envoyé : 14 décembre 2016 14:39
À : RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxx>
Objet : Re: Embedded SQL For Screen Load

My advice...

Don't do it. SQL doesn't really support "Position To", instead it's more
"Search/Subset By"

Lot's of posts over the years detailing the hoops needed in SQL to do a
position to (and provide the exact functionality you have now). Not really
worth the effort IMO.

On the other hand, it can be very worthwhile to switch from position to to
an SQL based "Search/Subset by".

Charles

On Wed, Dec 14, 2016 at 10:52 AM, (WalzCraft) Jerry Forss <
JForss@xxxxxxxxxxxxx> wrote:

Hi All,

I have to make a change to a pgm written in the 1920's (ok really 1988).

Enter name (partial), setll and read 10 records loading SFL.

User can page back so does setLL again and does READP loading array
then loads SFL again.

I want to make all this happen using SQL to modernize my skills.

The name is ONLY a starting point, not a match.

Anyone have some code they are willing to share about using SQL in
this fashion?

Jerry Forss
Information Technology Team
WalzCraft
608-781-6355 EXT 2530
jforss@xxxxxxxxxxxxx

Subject to Change Notice:

WalzCraft reserves the right to improve designs, and to change
specifications without notice.

Confidentiality Notice:

This message and any attachments may contain confidential and
privileged information that is protected by law. The information
contained herein is transmitted for the sole use of the intended

recipient(s) and should "only"

pertain to "WalzCraft" company matters. If you are not the intended
recipient or designated agent of the recipient of such information,
you are hereby notified that any use, dissemination, copying or
retention of this email or the information contained herein is
strictly prohibited and may subject you to penalties under federal
and/or state law. If you received this email in error, please notify
the sender immediately and permanently delete this email. Thank You;

WalzCraft PO Box 1748 La Crosse, WI. 54602-1748

www.walzcraft.com<http://www.walzcraft.com/> Phone... 800-237-1326
--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD