Re: SQL join 2 files

[CRPence <crp@xxxxxxxxxxxxxxxxxxxx>]

  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.