Re: SQL join 2 files

Yes, it is true; your logical file cannot be created with DDL. Your logcial
file is not a join logical, but a multi-format logical. SQL has no
substitute contstruct for a multi-format logical file.

Another DDS concept that is foreign to SQL is a object that specifies
selection and ordering. DDS supports logical files that specified selection
(PFILE and S, O specs) and ordering (K specs). There is no such thing in
SQL. You can create a view, but the ordering is specified on the query of
that view.


On 6/19/07, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx> 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.

--
Jeff Crosby
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531

The opinions expressed are my own and not necessarily the opinion of my
company. Unless I say so.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.