Original Code sent to me from one of our FOCUS programmers. This code generates 71 rows of output. It's wrong....it should be producing 66 rows of output. See next EXAMPLE B.

******EXAMPLE A*****************
********************************
-DEFAULT &AID_YEAR = '0607'
SET HOLDLIST = PRINTONLY
SET ASNAMES = ON

TABLE FILE RRRAREQ
BY RRRAREQ_PIDM AS 'TEMP_PIDM'
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'ADMIT';
WHERE RRRAREQ_TRST_CODE NE 'C';
ON TABLE HOLD AS MYLOOKUP FORMAT FOCUS INDEX RRRAREQ_PIDM
END
-RUN

JOIN RRRAREQ_PIDM IN RRRAREQ TO TEMP_PIDM IN MYLOOKUP AS J1

TABLE FILE RRRAREQ
BY RRRAREQ_PIDM
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'SELSER';
WHERE NOT RRRAREQ_TRST_CODE IN ('S','W');
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END
***********************************


Example B generates 66 rows. It's just like Example A, with 2 exceptions - I reversed the Join to make the hold file the host file, and then reported from the hold file, and now it generates the correct amount of rows. But it works just a little slower (by about 5 - 10 secs or so). See Example C.

*****EXAMPLE B****************
******************************
-DEFAULT &AID_YEAR = '0607'
SET HOLDLIST = PRINTONLY
SET ASNAMES = 0N

TABLE FILE RRRAREQ
BY RRRAREQ_PIDM AS 'TEMP_PIDM'
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'ADMIT';
WHERE RRRAREQ_TRST_CODE NE 'C';
ON TABLE HOLD AS MYLOOKUP FORMAT FOCUS INDEX RRRAREQ_PIDM
END
-RUN

JOIN TEMP_PIDM IN MYLOOKUP TO RRRAREQ_PIDM IN RRRAREQ AS J1 <<<<<<<<<------------------

TABLE FILE MYLOOKUP <<<<<<<<-------------------
BY RRRAREQ_PIDM
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'SELSER';
WHERE NOT RRRAREQ_TRST_CODE IN ('S','W');
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END
***********************************


Example C is just like the Example A (which was wrong by 5 rows), with one minor addition: I set RRRAREQ_PIDM EQ TEMP_PIDM in the last report and now it produces 66 rows, which is correct - and works fast as well.

*******EXAMPLE C*******************
***********************************
-DEFAULT &AID_YEAR = '0607'
SET HOLDLIST = PRINTONLY
SET ASNAMES = ON

TABLE FILE RRRAREQ
BY RRRAREQ_PIDM AS 'TEMP_PIDM'
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'ADMIT';
WHERE RRRAREQ_TRST_CODE NE 'C';
ON TABLE HOLD AS MYLOOKUP FORMAT FOCUS INDEX RRRAREQ_PIDM
END
-RUN

JOIN RRRAREQ_PIDM IN RRRAREQ TO TEMP_PIDM IN MYLOOKUP AS J1

TABLE FILE RRRAREQ
BY RRRAREQ_PIDM
WHERE RRRAREQ_PIDM EQ TEMP_PIDM; <<<<<<<<<-------------------
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'SELSER';
WHERE NOT RRRAREQ_TRST_CODE IN ('S','W');
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END
***********************************

So, the big question is this: Why didn't EXAMPLE A produce 66 rows?
To me, it sounds like the Join doesn't work properly, for some reason. In Example C, I added another join (basically) within the report, and it fixed it.

If this isn't clear, please say so and I'll try and explain it better.

Brian

Brian,

Make the following the first line of A:

SET ALL=OFF

I'd be REAL interested if that doesn't fix it.

I suppose I should have offered an explanation. I think you are getting RRRAREQ records for which there is no MYLOOKUP record.

exactly the way it is supposed to work; exactly.
as Gerry says, 'WYCIWYG'

quote:

SET ALL=OFF

That didn't work. I put it at the top on the first line.

But I think you are correct in your assumption, that it is getting records that don't exist in Mylookup. For instance, 2 of the records we found DID have a C with Admit...and we are looking for NE to C.

Brian

Didn't work.... Hmmmm... That's puzzling. Ok, Susannah, what are we missing here?

I agree with Susannah. If there are more records in one table than the other, then you need to ensure you are selecting the second table data some way which your equality check on id did. As to why all=off doesn't work, try doing an ON TABLE SET ALL OFF within the table request and see what happens.

WebFOCUS does not handle some things the same way the mainframe version does I've discovered over time as we went from mainframe MVS accessing DB2 to WebFOCUS accessing DB2. Ascii conversion killed us on some of the SCT packed data buried in alpha defined areas.

ON TABLE SET ALL OFF doesn't work either. It's funny though, when the smaller hold file is the host file, there is no need for equality check.

Brian

bingo!
leaving aside that we would probably suggest that you make an alpha hold file, not focus
and then
IF MYFIELD IN FILE (MYHOLD)
or IF MYFIELD IS (MYHOLD) (depending on your version and your style)
instead of joining, anyway
but this is an excellent join lesson example.

go to this thread and read T's example at the end; that's the way to handle this situation.

This message has been edited. Last edited by: susannah,

All your evidence points to ALL being ON. Joining smaller to larger works because there in no way (not matter how ALL is set) to get more records than you have in the host file. But joining RRRAREQ to MYLOOKUP, it looks like you are picking up records that are in RRRAREQ, but not in MYLOOKUP. With ALL OFF, this should not happen. My suspicions are focused on the BY statements, but I haven't come up with a rational explanation. I know if I were working this problem, I'd PRINT in that second step (instead of BY), just looking for a hint in the results. Sounds weird, I know, but this is FOCUS, after all.

quote:

Originally posted by susannah:
bingo!
leaving aside that we would probably suggest that you make an alpha hold file, not focus
and then
IF MYFIELD IN FILE (MYHOLD)
or IF MYFIELD IS (MYHOLD) (depending on your version and your style)
instead of joining, anyway
but this is an excellent join lesson example.

I am not sure understand what you are explaining, but let me say that this code is also listed under my other post (looking in a hold file). It produces too large a hold file to use it in an IF IN FILE or WHERE IN FILE statement. So it must be used with Joins.

Could you be a little more specific about why you think EXAMPLE A should work this way?

Brian

Wait a minute. I just noticed something. DOn't know that it's related to this problem, but...

In your A code you have:

ON TABLE HOLD AS MYLOOKUP FORMAT FOCUS INDEX RRRAREQ_PIDM

But there is no RRRAREQ_PIDM in MYLOOKUP (You changed it to TEMP_PIDM).

I'm not sure I see a connection between this and your problem, but you might want to fix it and try again, anyway. I suppose it's possible that the join is failing and therefore no filtering is taking place, but if so it ought to fail in the C code as well.

DWF, it's just an alias, and it's like that in all of the examples. You can't index a file using the alias - you must use the actual field name. And the code runs without errors...

An alias. Ok, you must be in territory I've not been in (Gerry knows, there's a lot of that). You have ASNAMES = ON and you have this:

BY RRRAREQ_PIDM AS 'TEMP_PIDM'

I would expect MYLOOKUP to have a field named TEMP_PIDM and no field named RRRAREQ_PIDM. The fact that your other examples did not blow up doesn't persuade me. But.... if it works, it works.

quote:

(Gerry knows, there's a lot of that).

ROFL
that's great!

Brian,

The reason Exmaple A doesn't produce the 66 rows that correspond to the MYHOLD file is that you do not reference any field in MYHOLD, therefore the JOIN isn't used (I think, someone correct me if I'm wrong).

The driver table is the first table so your results from Example B are controlled because the implied WHERE of TEMP_PIDM EQ RRRAREQ_PIDM ensures a full inner join and 66 rows result.

Likewise, in Example C you reference the TEMP_PIDM field in a WHERE and therefore activate the JOIN, et voila, you have the recordset you expect.

Just one of the many quirks of FOCUS that we have grown to love and enjoy over many years

T

Tony,

When you say reference, what do you mean? Do yo mean referencing in the report section - ie. adding temp_pidm to the report output?

Brian

Tony's right I think. If you do not print, sum, sort or do something with any fields in the cross referenced file, FOCUS doesn't issue the join. Are these relational tables? Turning on SQL trace for the code could prove or disprove the theory. If you need me to send you the code to turn on SQL trace let me know.

Brian,

Reference, as in use the field in the table request. Whether it be in a sort, predicate, with a verb (e.g. PRINT).

So if you had one of these, the result set should give 66 rows -

WHERE TEMP_PIDM EQ RRRAREQ_PIDM
PRINT TEMP_PIDM NOPRINT (as you might not want it displayed)
BY TEMP_PIDM NOPRINT

etc.

Because one (or the only?) field in the child file within the join is referenced, the complete joined file structure is used.

T

Tony,

Well, in practicality, it works almost as you say it does, with one exception - it returned 67 ROWS, and the first row was a 0. Instead of using BY RRRAREQ_PIDM, I used BY TEMP_PIDM.

I also tried these together:
BY TEMP_PIDM NOPRINT
BY RRRAREQ_PIDM

and we were back to square one with 71 ROWS.


Codermonkey, I'd be interested in trying your suggestion, as long as it can be done within my report. I only have developer access to our webfocus system. Could you send me the code?

Brian

Brian,

With either of the two alternatives that you used I would have expected the same result as your Example C, as the method is basically the same (well similar idea). In preference I would always use the WHERE as your Example C as this forces the equality test and therefore the join structure.

However, I would be fairly concerned that you are receiving different results for these and would be inclined to check various settings such TEMPERASE, APP HOLD, APP PATH to rule out the fact that there may be different versions of the same HOLD file lurking in your system somewhere.

The code for capturing the SQL is -

-*-- Activate tracing ---
SET TRACEOFF = ALL
SET TRACEON = SQLTRANS
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 132
SET TRACEUSER = ON

T

Tony,

Just for the heck of it, I changed the name of the hold file to something new & unique, and it produced the same results with the 0 as the first record.

Also, where does the SQL trace output to? Can it output to the screen?

Brian

Sorry Brian, the output will be displayed in the output stream so you will have to temporarily change your output to HTML and then view source. The trace is contained in the FOCUS output towards the bottom of the source.

Strange about the results you're getting. I'd have to see the source data and interim files to be able to comment more though so you might want to raise a case with Support? For my money I wouldn't bother and just stick to the Example C version out of choice.

T

Ok, here's the trace code for the fex:

SET MSG=ON
-* Turn retrieval OFF if you have a pull
-* that doesn't use a HOLD file -- it's faster
SET XRETRIEVAL=ON
SET TRACEOFF=ALL
-* This shows what didn't translate and why
SET TRACEON=SQLAGGR
-* This generates the SQL and sends it back
-* to the screen
SET TRACEON=STMTRACE//CLIENT
SET TRACEUSER=ON

-* FOCUS code here

SET TRACEUSER=OFF
-EXIT

Let me know if you need more info.

Good Luck!

OK, here is the SQL trace for each of the above examples:

*****EXAMPLE A*************************
AGGREGATION DONE ...
SELECT T1."RRRAREQ_PIDM", SUM(T1."RRRAREQ_PIDM")
FROM FAISMGR.RRRAREQ T1
WHERE (T1."RRRAREQ_TRST_CODE" <> 'C')
AND (T1."RRRAREQ_TREQ_CODE" = 'ADMIT')
AND (T1."RRRAREQ_AIDY_CODE" = '0607')
GROUP BY T1."RRRAREQ_PIDM"
ORDER BY T1."RRRAREQ_PIDM";

0 NUMBER OF RECORDS IN TABLE= 16749 LINES= 16749

AGGREGATION DONE ...
SELECT T1."RRRAREQ_PIDM", SUM(T1."RRRAREQ_PIDM")
FROM FAISMGR.RRRAREQ T1
WHERE (T1."RRRAREQ_TREQ_CODE" = 'SELSER')
AND (T1."RRRAREQ_AIDY_CODE" = '0607')
AND NOT((T1."RRRAREQ_TRST_CODE" IN('S', 'W')))
GROUP BY T1."RRRAREQ_PIDM" ORDER BY T1."RRRAREQ_PIDM";

0 NUMBER OF RECORDS IN TABLE= 71 LINES= 71
*******************************************


*****EXAMPLE B*************************
SELECT T1."RRRAREQ_PIDM", SUM(T1."RRRAREQ_PIDM")
FROM FAISMGR.RRRAREQ T1
WHERE (T1."RRRAREQ_TRST_CODE" <> 'C')
AND (T1."RRRAREQ_TREQ_CODE" = 'ADMIT')
AND (T1."RRRAREQ_AIDY_CODE" = '0607')
GROUP BY T1."RRRAREQ_PIDM"
ORDER BY T1."RRRAREQ_PIDM";

0 NUMBER OF RECORDS IN TABLE= 16749 LINES= 16749

(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2599) NON-SQL SEGMENT IN HIERARCHY (OTHER INTERFACE PRESENT)

SELECT T1."RRRAREQ_PIDM",T1."RRRAREQ_AIDY_CODE", T1."RRRAREQ_TREQ_CODE",T1."RRRAREQ_TRST_CODE"
FROM FAISMGR.RRRAREQ T1
WHERE (T1."RRRAREQ_TREQ_CODE" = 'SELSER')
AND (T1."RRRAREQ_AIDY_CODE" = '0607')
AND NOT((T1."RRRAREQ_TRST_CODE" IN('S', 'W')))
ORDER BY T1."RRRAREQ_PIDM";

0 NUMBER OF RECORDS IN TABLE= 66 LINES= 66
************************************************


*****EXAMPLE C*************************
AGGREGATION DONE ...
SELECT T1."RRRAREQ_PIDM", SUM(T1."RRRAREQ_PIDM")
FROM FAISMGR.RRRAREQ T1
WHERE (T1."RRRAREQ_TRST_CODE" <> 'C')
AND (T1."RRRAREQ_TREQ_CODE" = 'ADMIT')
AND (T1."RRRAREQ_AIDY_CODE" = '0607')
GROUP BY T1."RRRAREQ_PIDM"
ORDER BY T1."RRRAREQ_PIDM";

0 NUMBER OF RECORDS IN TABLE= 16749 LINES= 16749

(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2599) NON-SQL SEGMENT IN HIERARCHY (OTHER INTERFACE PRESENT)

SELECT T2."RRRAREQ_PIDM",T2."RRRAREQ_AIDY_CODE", T2."RRRAREQ_TREQ_CODE",T2."RRRAREQ_TRST_CODE"
FROM FAISMGR.RRRAREQ T2
WHERE (T2."RRRAREQ_PIDM" = :0001)
AND (T2."RRRAREQ_TREQ_CODE" = 'SELSER')
AND (T2."RRRAREQ_AIDY_CODE" = '0607')
AND NOT((T2."RRRAREQ_TRST_CODE" IN('S', 'W')));

0 NUMBER OF RECORDS IN TABLE= 66 LINES= 66
************************************************

Brian,

That proves that in the Example A the JOIN was ignored, otherwise you would have had the AGGREGATION NOT DONE .... message as in the other two examples.

It's a shame that you cannot use SQL passthru or even creation of a temporary SQL Table to make things more efficient.

One other alternative to try and keep the whole thing within SQL, and therefore force SQL to do the work, is to use the tag from a join statement -

-DEFAULT &AID_YEAR = '0607'
SET HOLDLIST = PRINTONLY
SET ASNAMES = ON

JOIN CLEAR *
JOIN RRRAREQ_PIDM IN RRRAREQ TO RRRAREQ_PIDM IN RRRAREQ AS J1.

TABLE FILE RRRAREQ
BY RRRAREQ_PIDM
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'SELSER';
WHERE NOT RRRAREQ_TRST_CODE IN ('S','W');
WHERE J1.RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE J1.RRRAREQ_TREQ_CODE EQ 'ADMIT';
WHERE J1.RRRAREQ_TRST_CODE NE 'C';
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN

This should work OK as the TAG (J1.) is used internally when the table name is identical within a join to uniquely identify each components columns. The parent table's columns have no prefix operator and the child table has the prefix equal to the tag value (e.g. J1.)

I have tested this on an MS SQL table to check it functions OK.

T

Tony,

Ok, that is kinda what I suspected. So we'll just call this a 'quirk', and not a 'bug'.

And your code works great -- and fast. 66 rows, and the row data is accurate. I may have to try that one in the future, as I occasionally have the need for subselects. Great Example.


Brian

Brian,

You're most welcome, I'm glad it worked for you!

T

quick note about optimization... depending on the database release and type, joining a hold file to a relational table can work and be optimized. Typically the hold file must be format ALPHA and used as the host file not the cross-referenced file.

I realize you don't need it for this report but might prove useful with others...