I need to create a master-detail relationship PDF report based on three database tables. After querying a selection of data from the primary table PARENT, it should display the results for each row returned in PARENT along with a listing of all the history records from the HISTORY table that has a FK relationship to PARENT and a listing of all the comments records from the COMMENTS table that also has a FK relationship to the parent.

I am relatively new to Focus. I've done a few reports but don't have a complete understanding of how to do something this complicated. I do not want a drill-down - this needs to all show up in the report, sort of looking like:

PARENT: Key Date Entered: XX/xx/xxxx
Parent Information: owjro wofjowejf owjf owijfow
woefjoewjf owj owfjoewjif w

HISTORY:
03/12/2007 Asked how to do it
03/09/2007 Ran into a roadblock
03/06/2007 Assigned new project

COMMENTS:
George: Please complete this report
Sam: I have requested the report
Joseph: Finish this report

Hi Snoop,

Could you provide a few more details?
HISTORY: is it 2 fields, date & comment?
COMMENTS: name & text?
PARENT INFO: how many fields?

quote:

Could you provide a few more details?
HISTORY: is it 2 fields, date & comment?
COMMENTS: name & text?
PARENT INFO: how many fields?

Certainly, sorry about that. PARENT is actually a view resulting from a number of tables joined together, with 25 fields. It has a unique ID field that is used to join the other two tables to it for this report. The information from PARENT is displayed laid out in a PDF report, not in table form.

HISTORY contains a date/time stamp, ID of person who did that action, the STATUS change that happened with that action to the PARENT table (it's basically an audit of actions occurring to the PARENT recording the resulting status change, so that you could see over time that the PARENT record started out in one status level and then moved to the next four days later, etc.

COMMENTS -- it's actually MATERIALS, I was just trying to keep this general and not get too focused on the specifics of what I'm trying to accomplish. It contains 10 fields and a date/time stamp. Each row represents basically one line of materials included in the PARENT record. So each PARENT record can have 1 to many MATERIALS, and 1 to many HISTORY items.

Snoop,

Here is an example of what you could achieve. I used the CAR file. The files HOLD, HOLD1 and HOLD2 represent your 3 tables. It should give you a direction.
You will have a bit of work to fine tune your program.
Good luck!

-* File snoop.fex

TABLE FILE CAR
SUM SALES RCOST DCOST
BY CAR
ON TABLE HOLD
END

TABLE FILE CAR
PRINT STANDARD BY CAR
ON TABLE HOLD AS HOLD1
END

TABLE FILE CAR
PRINT WARRANTY BY CAR
ON TABLE HOLD AS HOLD2
END

DEFINE FILE HOLD
ALINE/A60='SALES: ' | FTOA(SALES,'(D7)','A9') |
' RETAIL: ' | FTOA(RETAIL,'(D7)','A9') |
' DEALER: ' | FTOA(DEALER,'(D7)','A9') ;
LEVEL/I1=1;
END

DEFINE FILE HOLD1
ALINE/A60=STANDARD;
LEVEL/I1=2;
END

DEFINE FILE HOLD2
ALINE/A60=WARRANTY;
LEVEL/I1=3;
END

TABLE FILE HOLD
PRINT ALINE AS ''
BY CAR NOPRINT NOSPLIT
BY LEVEL NOPRINT
ON CAR SUBHEAD

"<CAR "

ON CAR SUBFOOT
" "
ON LEVEL SUBHEAD
"STANDARD"
WHEN LEVEL EQ 2;
SUBHEAD
"WARRANTY"
WHEN LEVEL EQ 3;
ON TABLE PCHOLD FORMAT PDF
MORE
FILE HOLD1
MORE
FILE HOLD2
END

This message has been edited. Last edited by: Danny-SRL,

Thanks for the response, but I'm still a bit confused. The two other tables are not included in that first table file -- I have a large view that returns all the rows into PARENT (my first table for the report) and then I will need to, for each row's ID as I generate the report, get the accompanying files from the other two tables HISTORY and MATERIALS. I'm not getting all that data from the same source. What am I missing?

I would create this with the PDF layout painter.

Create the 3 reports on the page, in the layout you want. Each of the reports should be sorted by the ID field you mention, and also PAGE-BREAk on the ID field. So for the first report:

  
TABLE FILE Fn
PRINT....
BY ID
ON ID PAGE_BREAK

For the second and third:

  
TABLE FILE Fn
PRINT....
BY ID NOPRINT
ON ID PAGE_BREAK

The big question is to get the data on each page correlating. So I would do an initial SQLOUT file of all 3 tables with the data you need, and then use that for the reports within the PDF painter.

I think it would be better to create the 3 report focexecs first, all running the the SQLOUT file, and then after you have completed the PDF layout, you should be able to remove the code that creates the SQLOUT from parts 2 and 3.

Hope that makes sense.

Apparently this is my newness talking... but I am still not understanding this. I have three separate and distinct queries. One query is used to pull the bulk of the data in for the "master" report. It has a lot of selection criteria to determine which rows to pull from the PARENT view in the first place. The other two queries are to pull out all the rows from another table for a given ONE row of the master report. How do I get all three extracts of data from the same "TABLE FILE fn"?

I have this report lay out completed through the PDF layout printer for the PRIMARY data (the PARENT or master report section), but I couldn't see how I would be able to specify an entirely separate data source linked by ID for another section of the report.

It sounds like I'm being dense because you guys all seem very matter-of-fact about this. I appreciate the assistance, but i'm not following what you're saying yet.

Maybe someone else can help out here, my SQL is hopeless.

What I was saying in the previous post is that there will be 3 seperate reports for the PDF output. Each of these will be a focexec with a TABLE request from an initial SQL request, producing an SQLOUT file which will contain all the data required in the report from the 3 tables. Then in PDF painter, create 3 report areas, referencing the focexecs. Using something like this for the first report on the PDF page:

  
.
.
SELECT ID,......
FROM TABLE1, TABLE2, TABLE3
WHERE ......
ORDER BY 1;
END
.
.
TABLE FILE SQLOUT
PRINT Field1 Field2
BY ID
ON ID PAGE-BREAK
.
.

Then for the second and third part on the page:

  

TABLE FILE SQLOUT
PRINT Field4 Field5
BY ID NOPRINT
ON ID PAGE-BREAK
.
.

It will be easier for you to create 3 seperate focexecs for each report, make sure they work. Then in PDF layout painter, paint in the areas you want for each of these reports on one page, referencing the correct focexec.

The page break on ID for each of the reports will cause a new page in the PDF output.

So basically you are creating a temporary file first, from which you can run the 3 reports.

I hope this os getting clearer, sometimes I get a bit tied up in my explanations.

Don't worry about it - we all start somewhere and this is exactly what the forum is for. Trying to be simple:

TABLE FILE PARENT
PRINT ID
(other data fields)
(selection criteria)
ON TABLE HOLD
END
JOIN ID IN HOLD TO ID IN TABLE2 AS JOIN0
TABLE FILE HOLD
PRINT ID
(other data fields for history)
ON TABLE HOLD AS HOLD1
END
JOIN CLEAR *
JOIN ID IN HOLD TO ID IN TABLE3 AS JOIN1
PRINT ID
(other data fields for materials)
ON TABLE HOLD AS HOLD2
END

Then you jump back into Daniel's code at DEFINE FILE HOLD. It's going to look at all the data you just saved as if it were all the same thing, but sorting with subheadings for the 3 different sections of PARENT, HISTORY, and MATERIALS.

Is that any clearer?

For most of the problems there is more than one
solution. Users become creative and use the solution that fits best. So you will see here different ways to the result and that makes it sometimes a bit confusing for a new starter.
But in the end you will be a guru too.

That's the beauty of the FOCUS language. I would say "there's always more than one way to skin a cat" but I don't know if that translates very well across cultures. But I agree that the flexibility is both wonderful and sometimes confusing.

quote:

Originally posted by Darin Lee:
Don't worry about it - we all start somewhere and this is exactly what the forum is for. Trying to be simple:

This is much clearer. But here's what I'm not understanding. To Alan -- hey, I will gladly swap off SQL knowledge. I can SQL code my way around anything!

So do I have to pull ALL data that's going to be used in a given report into one TABLE, and then pull parts of it into various defined HOLD areas? I'm already joining a ton of tables to get the complex query that's the basis of the parent section of this report. I can certainly join the HISTORY and MATERIALS tables with the PARENT table. But let's say my query is retrieving 100 rows of data for the PARENT table, with each row containing those 25 fields. Then we join in all the rows from HISTORY and MATERIALS as well. That means that instead of one row for a PARENT record which happens to have 10 HISTORY rows and 4 MATERIALS rows, I will be retrieving 1 + 10 + 4 rows = 15 rows which contain the fields involved in PARENT and HISTORY and MATERIALS, with a lot of repetitive data, that I will then have to plow through. So I'm rebelling against that because it just seems so incredibly inefficient.

It sounds like from Alan B's comment that the only option is to join all of this data into one huge table of multiple rows per PARENT record? That looks like what you are recommending too, Darin. If this is the case I can certainly rewrite my view to just do this from the beginning and I won't have to worry about the rest of it. I've played around with your version and attempted to simplify what I currently have to get it to work with that. In your example, where you're showing "JOIN ID IN HOLD TO ID IN TABLE2 AS JOIN0" -- so this is looking again like we're just joining all the data into one large table of data for the entire report, details and all, right? I'm trying to get my assumptions straight here.

I guess I am thinking from the perspective of a sql person where you want to try to limit the amount of massive repetitious data you're pulling back for performance. Is that not something I need to address in Focus?

Thanks again so much for everyone's patience.

Getting closer, but not quite.
All of the data doesn't need to be in one table. What I was demonstrating is first, getting all the data you need for the parent record including that id that will relate to the history and materials records and holding that in HOLD.
Second, you can join the ID in hold to the data source that contains the history and hold the history records in HOLD1. This way, you are only getting the additional data for the record that met your first criteria (They are the only ones that exists in HOLD.)
Third, you clear that join, rejoin the first HOLD to the data source that contains the materials and hold those records in HOLD2, again getting data for only the records that met the first selection criteria.
Last, you're going to redefine each of those hold files so they will look similar and read all of them together as if they were a single data set, sorting them by id, then by record type (parent, history, material). That's what the MORE does.
This will come out looking like
Parent record A
History 1
.
.
History n
Materials 1
.
.
Materials n

Parent record B
and so on.

Hi Snoop,

As far as I understand your problem, you're trying to tie the data for The Parent, History and the Comment part together. What Alan tried to explain is the following.
First you join the 3 tables (or views) and create one extract file containing all the columns you're need for the 3 different parts in your report. Then from that extract file you create the 3 different parts. Now for every master record or key (whatever), you need to combine those 3 parts on one page. Now Alan proposed to use the PDF layout painter, which is ok if you know how to use is. I don't know which WF Dev. Studio version you are running. In the 7.6 version you can do great stuff with it.
Daniels' solution is also good. He uses the MORE function to concatenate the 3 different parts togeter.
What also could work for you is what is called a PDF compound report. Here is an example on the car DB:

This report is in fact the envelope that will contain the result of the 3 parts. I created a loop that executes the 3 different reports for every country. In you case it will Partent, history and commend for every master.

 
-*Create a list of all the countries needed in the report
TABLE FILE CAR
PRINT COUNTRY
BY COUNTRY NOPRINT
ON TABLE SAVE AS TEST FORMAT ALPHA
END
-RUN
-SET &RECS = &LINES;
-SET &TEL  = 1;
-* Open the compound document
SET COMPOUND = OPEN NOBREAK
-GOTO VOLGEND
-START
SET COMPOUND = NOBREAK
-VOLGEND
-READ TEST &LAND.10.

EX car1_test COUNTRY=&LAND
SET COMPOUND = NOBREAK
EX car2_test COUNTRY=&LAND
-IF &TEL EQ &RECS GOTO EINDE;
EX car3_test COUNTRY=&LAND

-SET &TEL = &TEL + 1;
-IF &TEL LE &RECS GOTO START;
-EINDE
-* Close the compound document
SET COMPOUND = CLOSE
EX car3_test COUNTRY=&LAND

Here is the code for car1_test.fex , car2_test.fex and car3_test.fex

-* File car1_test.fex
TABLE FILE CAR
SUM
     RETAIL_COST
BY COUNTRY
BY CAR
HEADING
"Master  "
FOOTING
"end of the master part"
WHERE COUNTRY EQ '&COUNTRY';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
ENDSTYLE
END

-* File car2_test.fex
TABLE FILE CAR
SUM
     RETAIL_COST
     RETAIL_COST
BY COUNTRY
ACROSS CAR AS ''
HEADING
"History  "
FOOTING
"End of the history part"
WHERE COUNTRY EQ '&COUNTRY';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=ON,
     FONT='ARIAL',
     SIZE=9,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     GRID=OFF,
     SIZE=12,
     STYLE=BOLD,
$

ENDSTYLE
END


-* File car3_test.fex
TABLE FILE CAR
SUM
     RETAIL_COST
     RETAIL_COST
     SALES
BY COUNTRY
BY CAR
HEADING
"COMMENT"
FOOTING
"END OF THE COMMENT PART"
WHERE COUNTRY EQ '&COUNTRY';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$

ENDSTYLE
END
  

Just try to run the different examples the others and I gave you and use the one that fits you best. The result of the different examples will make clear what everyone tries to explain.

Hope this help.

This message has been edited. Last edited by: <JJI>,

Dirk/Snoop

The PDF layout painter is just another way of creating a PDF compound report.

The basic scenario is roughly the same as Dirk described, but instead of running a loop for the 3 focexecs, all 3 focexecs are described on one page, and the page break is used to have each ID on a seperate page, ensuring each page is linked to one ID.

Whilst there is a concern about data sizes, it is often better to hold one larger file than run a request multiple times, and if you are producing a report, I expect the number of records is not (hopefully) enormous.

The resultant code from the painter looks like this, and I have added an extract phase at the top:

-* File TEST70.fex
-* Get extract
TABLE FILE CAR
PRINT 
MODEL
MODEL
LENGTH
WIDTH
HEIGHT
WEIGHT
WHEELBASE
STANDARD
BY COUNTRY
BY CAR
ON TABLE HOLD
END
-RUN
-* Default Mode: ResourceLayout
-* Layout each report on the page
COMPOUND LAYOUT
UNITS=IN, $
SECTION=section1, LAYOUT=ON, MERGE=OFF, ORIENTATION=PORTRAIT, PAGESIZE=Letter, $
pagelayout=1, name='Layout page 1', $
component='report1', type=report, position=(1.125 0.500), dimension=(6.542 3.625), $
component='report2', type=report, position=(1.125 4.438), dimension=(5.917 2.458), $
component='report3', type=report, position=(1.125 7.083), dimension=(5.938 1.979), $
END
-* Run each report....
SET COMPONENT='report1'
-*component_type report
TABLE FILE HOLD
PRINT MODEL
BY COUNTRY
BY CAR
ON CAR PAGE-BREAK
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
.
.
ENDSTYLE
END
SET COMPONENT='report2'
-*component_type report
TABLE FILE HOLD
PRINT
STANDARD
BY COUNTRY NOPRINT
BY CAR NOPRINT
ON CAR PAGE-BREAK
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
.
.
ENDSTYLE
END
SET COMPONENT='report3'
-*component_type report
TABLE FILE HOLD
PRINT
MODEL
LENGTH
WIDTH
HEIGHT
WEIGHT
WHEELBASE
BY COUNTRY NOPRINT
BY CAR NOPRINT
ON CAR PAGE-BREAK
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
.
.

ENDSTYLE
END
COMPOUND END

The PDF layout painter is easy to use, and once you have one set up, you can play with the code if you wish.

And Snoop, the way you want to produce a report is purely personal, each person has their own way of achieving a result. There is always more than one way, sometimes many ways, of achieving the results you want, just be comfortable with the language.

Snoop,

I gave you a small example using the CAR file. Did you run it? Does it look like what you wanted? You have one row for eache value of CAR, after which all the rows of STANDARD and after that all the rows of WARRANTY. If this is the general idea, we can work towards adding more data.

Danny, and everyone -- yes -- I have been trying out the examples. Yours basically works and does what I want it to do, as does JJI's. I'm leaning more towards JJI's because my reports are so huge that I'm thinking it would be more straightforward to split it into separate files.

I'm still getting errors on '(FOC3209) UNKNOWN KEYWORD IN STYLESHEET FILE AT LINE 8: . ' on Alan's but I'm trying it as well.

I'm in the process of trying to slowly adapt JJI's solution to my particulars. I do appreciate everyone's assistance and will let you know when I get this one completed. Thanks again!

Snoop,

I'm glad my solution works for you . However if you are using an earlier WF version then WF 7.1.3 my solution will not work like that. In that case you'll need to replace the "EX fex ...." by a "-INCLUDE FEXname".

Snoop
I omitted the style sheet for brevity, I should have explained. In place of the dots, use this code:

UNITS=IN,
PAGESIZE='Letter',
LEFTMARGIN=0.250000,
RIGHTMARGIN=0.250000,
TOPMARGIN=0.250000,
BOTTOMMARGIN=0.250000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
FONT='ARIAL',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
$
TYPE=DATA,
SIZE=8,
BACKCOLOR=( RGB(234 234 255) 'WHITE' ),
$
TYPE=TITLE,
BORDER=LIGHT,
BORDER-STYLE=RIDGE,
BORDER-COLOR=RGB(51 51 153),
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(147 172 219),
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=9,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=9,
STYLE=BOLD,
$
TYPE=HEADING,
BORDER=LIGHT,
BORDER-STYLE=RIDGE,
BORDER-COLOR=RGB(51 51 153),
SIZE=12,
COLOR='WHITE',
BACKCOLOR=RGB(147 172 219),
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
SIZE=14,
$
TYPE=FOOTING,
BORDER=LIGHT,
BORDER-STYLE=RIDGE,
BORDER-COLOR=RGB(51 51 153),
COLOR='WHITE',
BACKCOLOR=RGB(147 172 219),
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=9,
STYLE=BOLD,
$
TYPE=SUBFOOT,
BORDER=LIGHT,
BORDER-STYLE=RIDGE,
BORDER-COLOR=RGB(51 51 153),
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(147 172 219),
STYLE=BOLD,
$
TYPE=SUBTOTAL,
SIZE=8,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=8,
STYLE=BOLD,
$
TYPE=ACROSSTITLE,
SIZE=8,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
SIZE=9,
STYLE=BOLD,
$

not having this code in will cause that error you get.

Well duh. that was pretty clueless of me, I should have seen that! thanks, sorry for bugging you.

Well I have to say I am certainly learning a lot.

I got the HISTORY and MATERIALS reports working as separate FOCEXECs per JJI's example, and had the PARENT portion working as its own separate FOCEXEC as well, then when I got to the point of wrapping them all together I came upon what I saw as a fatal flaw:

I'm actually performing a rather complicated selection criteria, as well as sorting order, in the primary report, based on about a dozen variables that are passed into the initial FOCEXEC. Upon re-reading JJI's solution, I realized that this would require me to basically perform the query and determine the keys for all PARENT records that might be included, and then iterating through those keys and executing all three FOCEXECs on each row.

I would love to have feedback on this -- my thinking is that this will become very inefficient. Won't I be basically querying the same data four times using this solution, basically determining a list of keys in the main driving routines, then passing the key to all three focexec's in turn so that they can do their own query and display and then going onto the next key?

So I am back revisiting Danny's suggestion. I have basically implemented it as he suggests, and for now I'm just hard-coding in a KEY value to run this report for just one PARENT record for which I know I have a lot of HISTORY and MATERIALS records. Then I combined Darin Lee's stuff with adding in the additional JOINS to the other two tables for the additional information needed for these secondary report areas.

It runs successfully, but I get only ONE row (the first one) printed out for HISTORY.

I get an error when I attempt to do the JOIN CLEAR -- is this because my main FOCEXEC does have four joins already for the PARENT reporting? This error is preventing me from successfully joining the MATERIALS records so I never can see what happens there.

QUESTIONS:
1) My master file contains the information for the PARENT records, not the additional HISTORY and MATERIALS records. My assumption from Darin Lee's JOIN suggestions was that this would basically do a query against the HISTORY table for all records with key matching the key in PARENT. But it doesn't seem to be doing this. Where am I going wrong?

2) Will the JOIN CLEAR * have an impact on the initial four joins I did to get the entire PARENT record, or (as I am assuming) have they already served their purpose and okay to clear all joins? If this is the case, why am I getting an error whenever i include that join? I'm assuming that's the cause of the error -- I'm actually getting an error UNRECOGNIZED COMMAND PRINT
UNRECOGNIZED COMMAND ON TABLE HOLD AS HOLD_MATERIAL

so it fails because it can't successfulloy complete the definition of my third hold table.

3) Assuming I get this working, how can I apply layout stuff to the report? I am using alignment and the like to lay out my fields on the original PDF report, and I'm not clear on what I do instead of the "ALINE/A100" definitions to get this to format properly.

4) Feedback on my first attempt doing it as JJI suggested -- am I missing something thinking that if I get this working it will be incredibly inefficient? If I do go this route, how can i modify the main program section so that instead of just looping through lines it loops through the KEY values to use for calling each of the three smaller focexecs for the compound report?

Thanks again. I'm learning so much from this so I appreciate your patience.

WebFOCUS 7.1.5 on Win2K using SQL2000/SQL2005/ORACLE10.4

Sorry - missed a couple of important things!!
in the JOIN you need to use JOIN field1 in table1 to ALL field2 in table2 - that explains why you're only getting one record. You need to join to ALL (one to many).
I don't know why the JOIN CLEAR * gives you an error message. it would have to be something else. make sure they are in proper sequence

JOIN CLEAR *
JOIN field1.......
DEFINE
.
END
TABLE
.
END

it might also help to use a -RUN after each section of your procedure. This is an "execute immediately" basically and clears all the commands from the processing stack. It helps isolate the problem a little better.

quote:

Originally posted by Darin Lee:
Sorry - missed a couple of important things!!
in the JOIN you need to use JOIN field1 in table1 to ALL field2 in table2 - that explains why you're only getting one record. You need to join to ALL (one to many).
I don't know why the JOIN CLEAR * gives you an error message. it would have to be something else. make .

SWEET! that got me running with multiple records as expected.

Apparently it's not the JOIN clear. Something strange is happening -- i am using a master file for the third table that's working fine for other FOCEXECs and whenever I do the join to it here, it is now returning NO RECORDS FOUND for the entire report. So I have another problem going on, but I'm one step closer. Thanks a bunch. Continuing to work on it...

Anothe couple things to be aware of --
First, the no records found may be due to your join field not matching EXACTLY. try this:

SET ALL=PASS
JOIN field1 IN table1 TO ALL field2 in table2 as JOIN0
TABLE FILE table1
PRINT table1.field1
table2.field2
END

If you're getting data in column 1 but not column 2, either your joined fields aren't matching or there is no corresponding record in table2.

Also, I don't know if it's valid to have no matching records in History or Materials, but the default is an inner join meaning that if there is no value in table2, the records for table1 are also excluded. I would use SET ALL=ON (or SET ALL=PASS) at the beginning of your procedure to resolve this issue. It changes to a left outer join meaning include everything in PARENT whether or not there is anything in History or Material, and then also pull in anything that may be available in those tables.

Alan

quote:

SELECT ID,......
FROM TABLE1, TABLE2, TABLE3
WHERE ......
ORDER BY 1;
END

do you mean creating an union select?

Select fields
from table aaa
union
select fields
from table bbb
union
select fields
from table ccc;

Is this what you want?

Frank

It probably is Frank. The last time I wrote any SQL was 10 years ago! I need to get myself back up to speed.

Luckily this was just an example, and snoop can outcode me in SQL by a long mile, so hopefully he understood what I was trying to achieve.

Now where's my SQL for dummies?

I am happy to report that I'm on the downhill run on this one, and so far it's slowly coming together and working.

However... I'm not sure how to make it print all the data in the main section (parent). It needs to be several lines long and displaying all 25 fields nicely formatted. I am afraid I don't completely understand what Danny's solution is doing regarding the "ALINE" print. Do I have to use the same defined field ALINE for each level entry so that I can print out everything using this method?

And can I just voice a frustration? It is SO hard to find the real source of an error in your FOCEXEC. I just spent a couple of hours on one issue before finding out that actually it was the format string in my .mas file, but no error ever came up and informed me of that. Do most of you develop in the report painter exclusively or do something else to prevent this type of issue?

probably the best way would be to create a subheading like

ON LEVEL SUBHEAD
"Some Text <PARENTFLD1> <15> <PARENTFLD2> <PARENTFLD3> "
"<PARENTFLD4> <PARENTFLD5> <PARENTFLD6> "
WHEN LEVEL EQ 1;

putting your text and spot markers wherever you need. You can use the stylesheet to style and also align columns as needed.

Snoop,
My solution rests on the MORE command which allows creating a report (or a HOLD file) using data from different files, as long as the data assembled from each file has the same names. Hence the use, in my example of the names ALINE and LEVEL. For your problem, it would demand a deeper knowledge of the quantity of data to out on the PDF page and the desired layout. It has the advantage of not having to use many JOINs which, in your case, seem to complicate.
If you need more info about the method, please ask.

quote:

Originally posted by Darin Lee:
probably the best way would be to create a subheading like

ON LEVEL SUBHEAD
"Some Text <15> "
" "
WHEN LEVEL EQ 1;

putting your text and spot markers wherever you need. You can use the stylesheet to style and also align columns as needed.

I am having trouble getting that to work because you don't have access to those fields at that point. Basically, I need to be formatting and printing out fields from the HISTORY table on one SUBHEAD when level is 2, and from MATERIALS table on one SUBHEAD when level is 3. How can you address those fields at that point? I've tried

WebFOCUS 7.1.5 on Win2K using SQL2000/SQL2005/ORACLE10.4