Re: SQL join 2 files

Expressions can be part of the select list, which will effectively "add fields" to the SELECT. See the following example:

create table qtemp.s (a char, b char, c char)
create table qtemp.t (a char, b char, c char, D date)
create view qtemp.v as (
select a.*,cast(null as time) as D from qtemp.s a
union all
select b.* from qtemp.t b )

Normally I would explicitly name the field list of the view, but it is optional in this case [the "as D" I believe makes that possible]. The correlation identifier allows naming generically all [i.e. "a.*"] fields for file S in QTEMP, and additionally the expression. I used NULL instead of the base-date; just my preference.

Of course ordering in a VIEW is not available, so creating an INDEX for the desired key field in the TABLE, and then when the SELECT FROM View is done, specify the ORDER BY on that key field referenced in the VIEW.

Regards, Chuck
-- All comments provided "as is" with no warranties of any kind whatsoever and may not represent positions, strategies, nor views of my employer

Jeff Crosby wrote:

The more I learn about SQL, the more I like it. The problem I have is
trying to figure out how to do certain things. Some thinga that seem simple
I just can't seem to figure out or find by searching. I must not have
crossed that line to where I can 'think' in SQL yet.

Here's what I'm trying to do. I have a join logical, ALITMMST, created via
DDS that I want created via SQL DML instead.

The DDS:

A R MBRECORD PFILE(DMITMMST OLITMMST)
A FORMAT(DMITMMST)
A K ITNBR

DMITMMST is active items. OLITMMST is items that have been deleted in the
past 24 months. The purpose of ALITMMST is twofold: 1) the buyers can make
sure they don't reuse an item number within 2 years, and 2) they can use
Query against sales history data using ALITMMST.

To create an equivalent in SQL I cannot say:

CREATE VIEW ALITMMST AS SELECT * FROM DMITMMST UNION SELECT * FROM OLITMMST

because there is 1 additional field in OLITMMST at the end of the format
that doesn't exist in DMITMMST. That field is an *ISO date data type
indicating the date the item was delete. I'm certain something like this:

CREATE VIEW ALITMMST AS SELECT *,DATE('0001-01-01') FROM DMITMMST UNION
SELECT * FROM OLITMMST

cannot be done because I couldn't make anything work nor could I find any
examples anywhere indicating one could add additional fields to a "SELECT *"
statement. There's over 100 fields so I don't really want to key them all
in. There is also the fact that the resulting union has no key.

I tried retrieving the SQL but it fails. I'm assuming I need some kid of
INDEX as opposed to a VIEW. I just can't find any examples on how to do
this. This didn't seem like an odd thing to do.

Is this an example of something that can be done in DDS, but not SQL? I
refuse to believe that to be true.