Re: SQL is amazing was ( Avoid Level checks )

On Wed, Jun 29, 2022 at 7:49 AM Rob Berendt <rob@xxxxxxxxx> wrote:

Do you still do that S/34 RPG stuff where every field name has a 2 character prefix which indicate which table it came from?
Because if you don't and ITEMNUMBER is called the same name in every table it appears it's extremely simple to get a cross reference of every table it's used in by simply doing the following
select SYSTEM_COLUMN_NAME, SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME,
DATA_TYPE, LENGTH, NUMERIC_SCALE, COLUMN_HEADING, COLUMN_TEXT
from qsys2.syscolumns
where system_column_name = 'ITEMNUMBER'
;

And it's not even that much harder to handle the case where you *do*
use a prefix. If it's always exactly two characters, there's
SUBSTRING(SYSTEM_COLUMN_NAME, 3) = whatever. Or the slightly more
verbose but more self-documenting SUBSTRING(SYSTEM_COLUMN_NAME FROM
3). Even if the prefix isn't always a fixed number of characters,
there are plenty of other string-handling functions at your disposal,
including regular expressions if it gets really intricate. And you can
include other properties in the WHERE clause if they will help (I can
easily see data type and length being handy in some situations).

So yeah, just amplifying the point that SQL and the system catalogs
and services provide an impressive combination of functionality and
ease of use.

I find field reference files rather obsolete.

Some people have *never* found FRFs to be very beneficial in practice.
Most people can understand the theory behind why they might be cool,
but it is SO, SO easy to deviate from the rigorous practices necessary
for them to provide significant value (whether through laziness, time
pressure, ignorance, or simple accident). Frank Kolmann himself
expressed that opinion on this list just last month.

John Y.