RE: SQL Existence Check

see comments inline.

From: "Joe Pluta" <joepluta@PlutaBrothers.com>
>> The industry standard is becoming SQL, (obsolescence looms)
>> but I will try to give a reasoned response.

>SQL is not an industry standard for business applications.

Pray tell what is the standard for business apps , I mean
not simply for AS400s, but all systems.
I mean relational or relational like databases that can
be accessed by using 'SQL like' systax.

>It's a standard for inquiries.  Inquiries are only a small,
>rather trivial subset of business applications.
>Try to code a price lookup in SQL, and you'll be
>using cursors and fetch loops, which are basically just
>poor SQL equivalents of SETLL/READE loops.

I have written complete business apps with just using SQL.
Whats wrong with cursors and fetch loops, why poor.
They work and I have not measured performance but with
good indexes performance is satisfactory.

>>The concept of a scrollable cursor is basically just a mimic of an ISAM
index,
>>with poorer performance.  If SQL were meant for transaction processing,
>>it would be called STPL, not SQL.  Try to process
>>heterogenous sets of hierarchical data in SQL - you either need multiple
>>cursors and FETCH/READE loops or massive JOINs that return duplicate data
in
>>all but the lowest order columns.

'heterogenous sets of hierarchical data' I stopped setting up such
databases when the S3/15D died. Not being personal but who in their
right mind would set up an AS400 application in this manner.
I still come accross the stuff with EDI but I avoid it using
EDI packaged software,.... and thats exactly what I set up for
EDI output ie 'duplicate data in all but the lowest order columns'

>> As I used SQL more I found I could write programs that
>> with the same code handled different sort sequences,
>> multiple selection criteria EASILY.

>Again, this is the class of programs known as "query", hence the name
>"structured query language".  Try to write an MRP generation or a BOM
>explosion in SQL, and get back to me.

You know as well as I do that SQL is more than 'query'. To get
stuck on semantics is not what I want to discuss. Anyhow whats
BOM got to do with coding powerful query applications.

Dont have time to write an MRP.
I have not used SQL for BOM explosions but I could easily
replace the CHAIN operation with an SQL SELECT. Do you mean
that performance is not so hot, perhaps so, but setting up
appropriate indexes helps. Ditto MRP gen which is basically
multilevel BOM explosion then summarised and a bit of date calcs.
There is more than one way to use SQL to achieve this, but I
would probably use intermediate files and multiple passes rather than
set up a complex SQL to do this in one pass.

>> Sure I can whack in an F spec for a simple
>> CHAIN or SETLL but why should I as I have not other F SPECS
>> and it makes the code look silly.
>
>What's silly is five or ten lines of SQL where a single CHAIN would do.
>THAT'S silly.

FRCFRCM02  IF   E           K DISK
C     RCFKEY        PLIST
C                   PARM                    KCFCUS
C                   PARM                    KCFRUM
C                   Eval      KCFCUS  = RCFCUS
C                   Eval      KCFRUM  = RCFRUMA
C     RCFKEY        CHAIN     RCFRCM02                           91

C
C/Exec SQL  SELECT  DISTINCT RCFCUS  INTO :WCFCUS  from RCFRCM02
C+ WHERE RCFCUS =  :RCFCUS   and RCFRUM = :RCFRUMA
C/End-Exec

7 vs 3


>> I am not writing code for cross platform applications
>> but I sure know how to , do you.

><laughing>  Yes, Frank, I believe I have a bit of knowledge in
>cross-platform development.

<kow towing>
I seen some of your stuff Joe, I am impressed and you know more
than I do, I was trying to be general in my reply. Your weight
can put some people off from even trying SQL by a simple
comment one way or the other.


Frank