Re: Non-normalized Join Problem

Dear John,
I'd like to supplement my yesterday's first suggested solution for your problem:

(1) If you do the query in a SQL-plus - Script you may use the NEW_VALUE variable and a preceeding SELECt to find out the record# (key). The script may look like this:

COL v_diagnosis NEW_VALUE v_record

SELECT DT.record# v_diagnosis
FROM diagnosis_table DT
WHERE DT.disgnosis = ' <what you choose - or parameter &1> '

/* here: the variable v_record is (newly) created and filled wich the result of the first SELECT */

SELECT <what you want>
FROM Invoice_Table IT, Diagnosis_Table DT WHERE INSTR (CONCAT (IT.Dx1,IT.Dx2,IT.Dx3, ...), &v_record) > 0

(2) If you use REPORTS 2.5 you create a list of values in the parameter form showing the diagnosis and filling a user defined parameter with the key value record#.

Sincerely
H. WILD

+==========================================================================+
| Hartlieb  WILD        Systemanalyse    Programmierung     EDV-Training   |
|                     allgemein beeideter gerichtlicher Sachverständiger   |
|                                                                          |
| A-6073 SISTRANS 280   e-mail:  whsysana_at_ping.at     Tel.: (++43) (0)512  |
| Austria               CompuServe:   100131,1552                 378 110  |
+==========================================================================+

> I've got a database that is coming from a Mumps design that, for
> simplicity, let's say has two tables:
>
> Invoice Table Diagnosis Table
> ----------------------------- -------------------------------
> Inv# Dx1 Dx2 Dx3 Record# Diagnosis
> 123 1 2 1 Influenza
> 234 2 3 4 2 Bad Karma
> 345 3 1 3 Hypertension
> etc etc
>
> The invoice table has one to three diagnosis codes associated with each
> invoice. The Dx1..Dx3 fields in the invoice table are foreign keys
> on the record# field in the Diagnosis table.
>
> Although the Invoice table could obviously be normalized, it's not.
>
> Now my question:
>
> How do I define a join so I can run a query asking the question "show me
> all invoices that have had (Dx1 = Hypertension or Dx2 = Hypertension or
> Dx3 = Hypertension)"?
>
> If I use "Where invoice.dx1 = diagnosis.record# or invoice.dx2 =
> diagnosis.record# or invoice.dx3 = diagnosis.record#" then I just get the
> first matching record. I want ALL matches, so the report would look like:
> Invoice
> ---------
> 234
> 345
>
> Is there any way around this problem short of normalizing the database? I
> appreciate any ideas!
>
> TIA,
>
> John
Received on Thu Mar 28 1996 - 00:00:00 CET