Report grouping with 2 identical tables 2

[foxrainer]

I am not sure which way to go onthis:
I have to generate a report from another programs two tables: (different names) with identical fields (med_name, frequency, etc...)
Each table is used for, obviously, different purposes and, changing any structure on these two tables is out of the question (as the other program is dependent on them).

The report is simple: List medications according to specific patient.
The problem: All Records from table 'a' need to be grouped on top of the report, all records from table 'b' grouped underneath.

I could do a SELECT SQL into two different cursors (BTW, I only do reports from cursors) but don't know how to get a grouping in one report on two cursors.

Or, I could combine the two tables into one cursor, with an added field markning the 'a' or 'b', but can't get that to work either.

Could really use help on this!

Thank you very much in advance

Rainer

 

[DSummZZZ]

How about:

USE aTable ORDER whatever
COPY TO cTable FOR UPPER(PatientNum) = 'S123'
SELECT 0
USE cTable
SELECT * FROM bTable ;
WHERE UPPER(PatientNum) = 'S123';
ORDER BY whatever;
INTO TABLE bTmpTable

SELECT cTable
APPEND FROM bTmpTable

Dave S.

 

[foxrainer]

Dave,

thanks very much: sounds like a great way to get all fields into one table/ or cursor.

The question, though: how to get the report grouping set up so it will be able to differentiate? I.e.: Table a needs to have grouping on top, table b on bottom group.

Thanks in advance, every step helps

Rainer

 

[wgcs]

SELECT 'A' as SourceTable, * FROM aTable ;
WHERE UPPER(PatientNum) = 'S123';
ORDER BY Whatever;
INTO TABLE aTmpTable
SELECT 'B' as SourceTable, * FROM bTable ;
WHERE UPPER(PatientNum) = 'S123';
ORDER BY whatever;
INTO TABLE bTmpTable
SELECT * FROM aTmpTable ;
UNION ALL SELECT * FROM bTmpTable ;
ORDER BY Whatever,SourceTable

(Order by must be a list of fields or a list of field indexes)

 

[DSummZZZ]

Well, I guess you could do something like:

USE aTable ORDER whatever
COPY TO cTable FOR UPPER(PatientNum) = 'S123'
SELECT 0
USE cTable EXCLUSIVE
ALTER TABLE cTable ADD AorB C(1)
REPLACE ALL AorB WITH 'A'

SELECT * FROM bTable ;
WHERE UPPER(PatientNum) = 'S123';
ORDER BY whatever;
INTO TABLE bTmpTable

SELECT cTable
APPEND FROM bTmpTable
REPLACE ALL AorB WITH 'B' FOR EMPTY(AorB)

Then group your report on AorB.

Dave S.

 

[foxrainer]

Wow, great ideas!

I've been trying out a few sample codes, and this is what I came up with:

SELE med_name,frequency,(med_name=&quot;a&quot AS ab FROM meds WHERE case_no=155 INTO CURSOR trial UNION SELE order_name, instruct,(order_name<&quot;a&quot AS ab FROM treatmnt WHERE case_no=155 ORDER BY ab


It does work, is it acceptable?

Rainer

 

[wgcs]

If it works, of course!

I like how you integrated it all into a single SELECT statement, eliminating the meta-tables.

As a suggestion, formatting code more readably tends to make it more acceptable... even though it doesn't affect its functionality, it makes the next developer less likely to say &quot;Too complicated. Let's redo it....&quot;

eg:

SELECT med_name,  ;
       frequency, ;
       (med_name="a") AS ab ;
  FROM meds ;
  WHERE case_no=155 ;
  INTO CURSOR trial ;
UNION ;
  SELECT order_name, ;
         instruct,   ;
         (order_name<&quot;a&quot;) AS ab  ;
  FROM treatmnt ;
  WHERE case_no=155 ;
ORDER BY ab

 

[foxrainer]

Suggestion well taken, thank you!

I have been getting much better in leaving '&&notes' next to code and making things easier to read, ever since I have to decipher old FP2.0 code from someone else. It makes me appreciate your suggestion,,
thanks all
Rainer

 

[FoxRookie]

Union your queries -- make sure the first query defines all possible field lengths etc of the second table, (i use nvl()) when in doubt or the eqivalent thereof.

select nvl(field1,space(20)) as one, field2 as two ;
from mytable1 ;
union all
(select field1 as one, field2 as two ;
from mytable2) ;
order by 2,1) ;
into cursor thecurs

all fields that meet must match by type and the second query can not have a field longer than the first query. &quot;Order by clause&quot; must be number in accordance with the arrangement of the fields - trying to reference a table.field will not work;;;;with some careful thinging this works everytime.

addendum: nvl(mydatefield, {}) etc AND you will find that when table1(parent) has multiple table2(child) records, a regular SQL joun will suffice, but an outer join will increase speed by up to 60%. I am not sure why (Rushmore?), but that has been my experience. Input from the experts????

 

[FoxRookie]

Addendum: to my prior exhaustion, if you order your query correctly, the fox report engine will group correctly...

 

[foxrainer]

FoxRookie,

thank you for the input - these are good suggestions.

I've been using the union I listed above and it's working rahter well, this has been a helpful threat,

Rainer