Has anyone here ever had trouble understanding MISSING recor...

[Guest]

Hi Evan

Before we go too far, I think it will help you to know that the SET ALL=PASS has more to do with WHERE tests inside the TABLE FILE (or GRAPH or whatever) than the IF tests (like the one you have your your DEFINE).

So dont worry about the SET ALL=PASS too much for right now. We can talk more about that if we get to the SQL side of things.

It might also help to remember that WebFOCUS still thinks much like its hierarchical roots. In your case, WF will tend to view your data structure as a Parent Segment (your FOCUSFILE) and there are 2 Child Segments hanging off that top segment.

Even though were very much in a relational world, FOCUS still kind of thinks of everything has a hierarchy.

You can see a picture of how it thinks your data is arranged by coding the following after you JOIN statements:

CHECK FILE FOCUSFILE PICTURE

Itll give you that single parent with one to many relationships of 2 child segments. The 1 to many relationships will show up with double boxes on the child segments.

When youre doing a DEFINE, a good way to make sure things are working like you want is to simplify your TABLE request to PRINT the fields you are using so you can watch everything be evaluated line by line.

In your example, Id put in a

TABLE FILE FOCUSFILE

PRINT

FIELDNAME1

FIELDNAME2

FIELDA

FIELDNAME3

FIELDB

RECORD

END

When you run this, youll see how the RECORD comes to have whatever value it has.

If you can show us a few sample records, we can help you understand it more.

Also - since it appears your JOINing from a FOCUSFILE to Oracle tables, you can imagine how FOCUS has to process that. Itll first pick up a record from the FOCUS file and then send a request to each Oracle table with a host variable in the SQL. This will result in 2 individual SELECTs being fired off for each record you retrieve in the FOCUSFILE. So its not going to be great on performance but it should work.

If youd like to capture your SQL, you can add these lines at the top of your code:

SET TRACEOFF=ALL

SET TRACEUSER=CLIENT

SET TRACEON=STMTRACE//CLIENT

(thats from memory but I think thats right).

The SQL will help you see how WF is getting the data behind the scenes. In particular, you might watch to see if your DEFINE statement is making it into the SELECT statements. I personally dont think it will be, but its worth a look.

Lets start with you running the TABLE FILE I showed that just populates a few rows and then let us see what youre getting. Or experiment from there to see what makes things change.

Im a little surprised your RECORD is showing you the NODATA . too. But maybe Im MISSING something

Questions so far

[Evan Brown 2]

Has anyone here ever had trouble understanding MISSING records and how it related to JOINs (LEFT_OUTER, ALL) and ALL PASS settings Ive read the missing records chapter of the manual many, many times, yet Im still struggling to understand. Have you seen something or been to a training that helped in your understanding

Most of the times I have an issue, it is in one particular scenario. Even if I can have some help in wrapping my mind around this scenario would be a great place to start:

Heres what the MASTER files would look like so you can follow along in my DEFINE and JOIN.

FOCUSFILE

FIELDNAME1-Key

ORACLETABLE1

FIELDNAME2-Key

FIELDA

ORACLETABLE2

FIELDNAME3-Key

FIELDB

 

Blockquote

JOIN LEFT_OUTER FIELDNAME1 IN FOCUSFILE TO ALL FIELDNAME2 IN ORACLETABLE1 AS A.

JOIN LEFT_OUTER FIELDNAME2 IN FOCUSFILE TO ALL FIELDNAME3 IN ORACLETABLE2 AS B.

Blockquote

 

I know I have some missing records in ORACLETABLE2, and I dont have any selection criteria for either ORACLETABLE. I do have something in a DEFINE where it says something like this:

 

Blockquote

RECORD/A1=IF FIELDA EQ 123 AND FIELDB EQ 123 THEN Y ELSE

IF FIELDA EQ 123 THEN N ELSE ';

Blockquote

 

If it encounters a missing record from ORACLETABLE2 even if FIELDA equals 123, RECORD comes back as MISSING if there is no instance in ORACLETABLE2. If anyone can help me understand WHY this is happens, I would greatly appreciate it. I think if I understand why it will help me decide what code to use and when. Thanks

[Evan Brown 2]

Great explanation, thank you. I do generally print all of my fields when Im developing something, but those missing records are sneaky

So its not going to be great on performance but it should work. This is what my coworker whos been doing this since the early 80s keeps trying to drill into my head. I tend to use a TABLE request at the beginning to get the population of whatever, then using this file to JOIN to the Oracle table to work more like my select statement. I hear things about performance hits but I just really cant tell much difference in how fast things output to my screen. I know if I multiply this by our 2000 users it adds up and I can see how back in the day it was critical due to computing power. Ive only been developing 5 years or so so Ive only worked with modern computing power. Maybe Im just hard headed and need to get bitten by something to scare me into letting the RDMBS do more of the lifting.

I forget about CHECKFILE PICTURE. I need to get into the habit of using this along with SQL traces when Im developing. Thanks again for the help!