on 10/3/02 10:42 AM, Jeff Mason at jeffm.ma.ultranet@r... wrote:

> I don't mean to be a pain (well, maybe a little ;-), but are you converting
> this Omnis (whatever that is) application to an SQL database?  If so, then
> you have already designed the SQL tables, haven't you?.  How do these tables
> relate to one another, i.e. what referential constraints are there?  From
> what you have posted I cannot tell what entities these tables represent, nor
> how those entities relate to one another.

Omnis (www.omnis.net) is a 20 year old 4GL "front-ending tool" which runs on
Windows, Macs and Linux. It can talk/connect to most major backends -
Oracle, Sybase, MySQl, etc and of course FrontBase which is what I'm using.

I'm converting an old app done in an early version of Omnis (which uses its
own native datafile) to run in the current version (Studio) and also move to
SQL. I've recreated the tables in FrontBase to look pretty much like the old
ones, save a little naming change. This app is in need of MAJOR MAJOR
redesign, but the owners want no part of that - just a straight move-over.

So.

All of that may be extraneous - here is restatement of the problem:

The requirement is to build a display of ALL current transactions for a
given member. This could be:

select * from subs_trans where sub_mem_no = member_no;

When a transaction has subs_invtype = 'PAY' a lookup is
done in the Payments table:

(effectively) select fpr_chqno,fpr_receipts from Payments where
cast(fpr_number as char) = subs_reference_paid;

to pull in fpr_chqno and fpr_receipt for that transaction.
(check # and some receipt #)

What I THINK is needed here is a correlated subquery where
the main query can pass in subs_invtype and subs_reference_paid
to the subquery. The subquery can then return either two nulls (no such
record) or 
fpayments.cfprchqno and fpayments.cfprreceipt.

It also seems like a join would fit: all the members transactions joined
with the two columns from the members payment records. These would either be
null or have data if they exit. I can get neither to work and my brain is
fried!! 

Condensed tables and code follow.

Regards and thanks for all efforts!

=Alan R.
-------------------------


From Subscriber Table:
MEMBER_NO            Character            10 <-|
                                               |
From Transactions Table:                       |
SUB_MEM_NO             Character       10 <----|
SUBS_REFERENCE_PAID    Character       20      |
SUBS_INVTYPE           Character       3       |
                                               |
From Payments Table:                           |
FPR_MEMNO          Character       10 <--------|
FPR_NUMBER         Number          0 dp
FPR_CHQNO          Character       12
FPR_RECEIPT        Character       20
---

Omnis native DML code:

Define list (Store long data) {SUBS_TRANS,LV_SORT}
Set main file {SUBS_TRANS}
Find on SUB_MEM_NO (Exact match) {MEMBER_NO}
While flag true
   If SUBS_INVTYPE='PAY'
      Single file find on FPR_NUMBER (Exact match) {SUBS_REFERENCE_PAID}
      If flag true
         Calculate SUBS_PREFIX as FPR_CHQNO
         Calculate SUBS_INVNO as FPR_RECEIPT
      End If
   End If
   Add line to list
   Next on SUB_MEM_NO (Exact match)
End While