>
> At 04:00 PM 21-11-01 +0200, Gerhardus Geldenhuis wrote:
> >Hi have two tables:
> >CREATE TABLE REPLICATION_LOG
> >(
> > LOGID DOM_INCFIELD NOT NULL,
> > LOGTIMESTAMP DOM_TIMESTAMP,
> > TABLENAME DOM_VARCHAR25 NOT NULL,
> > UID DOM_VARCHAR25 NOT NULL,
> > DBACTION DOM_VARCHAR25 NOT NULL,
> > BRANCH DOM_VARCHAR25 NOT NULL,
> > REPLICATED DOM_BOOLEAN,
> > DATEREPLICATED DATE,
> > CONSTRAINT PK_REPLICATION_LOG PRIMARY KEY (LOGID)
> >)
> >
> >CREATE TABLE REPLICATION_ORDER
> >(
> > TABLENAME DOM_VARCHAR25 NOT NULL,
> > WEIGHT INTEGER NOT NULL,
> > CONSTRAINT PK_REPLICATION_ORDER PRIMARY KEY (TABLENAME, WEIGHT)
> >)
> >
> >I want to do the following select from replication_log:
> >"select distinct(tablename) from replication_log"
> >then I want to do a union between that selection and
> >replication_order and sort by weight. This is to achieve
> >a specific order of sorting for the replication. I think
> >this is the best way to do this.
> >
> >How do I do this. I have never used union before and the embedded-sql
> >guide did not explain it in a understandable way. It assumes some
> >basic knowledge which I learn as I go.
>
> I suspect you won't be doing a union this time, either. :) A
> union combines congruent sets from each of the contributing
> queries and "stacks" their output one on top of the other. So a
> union between these two tables would be a set of tablenames
> followed by another set of tablenames, with nothing to order on
> but ...well....1.
>
> What you seem to be asking for is a set of distinct table names
> in a certain order. If so, it's a join you need. Without
> knowing about the data in REPLICATION_ORDER, I can't tell if it
> is doable in a single SQL statement, since the primary key
> (TABLENAME modified by WEIGHT) seems to suggest that you wouldn't
> be able to get a DISTINCT tablename.
>
> If that's the case, a dataset output from a stored procedure or
> requerying a view might be the only way - and you would have to
> decide which WEIGHT you were interested in.
>
> Helen
>

What I basically need is a distinct select from "replication_log" giving me
all the table names and then I have a "lookup" table("replication_order")
that assigns a weight to the tablename to sort them because they must not be
sorted alphbetically. I also thought I needed a join but the manual referred
me to the union page.
If a join would solve the problem an example would be apprecaited.

Here is typical data for the two tables
Replication_log
~~~~~~~~~~~~~~~
LOGID TABLENAME UID DBACTION BRANCH
===== ================= ================== =========== ============
8 MTRL_FRAMES 882 UPDATE GLEN
9 MTRL_FRAMES 882 UPDATE BOKSBURG
10 MTRL_FRAMES 882 UPDATE GLEN
11 MTRL_FRAMES 882 UPDATE BOKSBURG
12 MTRL_FRAMES 890 INSERT GLEN
13 MTRL_FRAMES 890 INSERT BOKSBURG
14 MTRL_FRAMES 890 UPDATE GLEN
15 MTRL_FRAMES 890 UPDATE BOKSBURG
16 SUPPLIERS 34 UPDATE GLEN
17 SUPPLIERS 34 UPDATE BOKSBURG

Replication_order(would be constant most of the time)
TABLENAME WEIGHT
==============================
CUSTOMERS 1
MTRL_FRAMES 2
MTRL_GENERAL 3
MTRL_GLASSPRODUCTS 4
MTRL_MISCELLANEOUS 5
MTRL_MOUNTS 6
MTRL_PRINTS 7
MSFRAMES 8
MSMOUNTS 9
MSPRINTS 10
MULTIPLESUPPLIERS 11
POS_QISC 12
POS_QISC_ITEMS 13
POS_PAYMENTDETAIL 14
POS_MISC_STOCK 15
POS_WORKSORDER 16
POS_WORKSORDER_BOOKING 17
POS_BILLOFMATERIAL 18

Groete and many thanks
Gerhardus