Hi all,

SET ALL = PASS "Includes missing segment instances in a report, regardless of WHERE or IF criteria in the request”

We have always had problems using SET ALL=PASS and today I have found a support document that states that SET ALL=PASS is not supported using DB2

So if I want to do a left outer join, but need to specify a WHERE clause on the child (if a child is found), WebFocus drops the parent when no child is found. Not good.

We have come up with a few workarounds, such as first building a hold file over the child file, just containing the data we would be interested in. We then do a SET ALL=ON and can do a normal left outer join as desired. However this can take quite a bit of time when dealing with a large file.

In my current challenge I need to join to a 2nd file, but only when an indicator on that file is set to ‘S’.

SET ALL=PASS

JOIN
ORDER_NO IN ORDER_FILE TO
ORDER_NO IN DESPATCH_FILE AS J0
END

TABLE FILE
PRINT
ORDER_FILE. ORDER_NO
DESPATCH_FILE. SHIPPED_IND.
WHERE DESPATCH_FILE.SHIPPED_IND EQ ‘S’;
END
-RUN

I have two orders; 123 and 456, but only 123 is present on the 2nd file DESPATCH_FILE. As I am specifying a WHERE statement on DESPATCH_FILE and as SET ALL=PASS is not working, Webfocus drops 456 and only reports 123. If I remove the WHERE statement, I get both orders, despite 456 not being present on the 2nd file.

As stated before, I would usually build a ‘cut down’ version of the DESPATCH_FILE, only containing my ‘S’ records, but in my current case it is a very large volume of data and is not feasible.

I have therefore tried a few other things, neither of which are working and so I wondered if anyone could help spot the problems with them:


1, I tried adding a new defined field to the .MAS file of ORDER_FILE:

DEFINE D_S_IND/A1 = 'S’;

I then added this new field to my join in hope that it would pass the ‘S’ to the relative field and thereby negate the need for the WHERE clause:

JOIN
ORDER_NO AND D_S_IND IN ORDER_FILE TO
ORDER_NO AND SHIPPED_IND IN DESPATCH_FILE AS J0
END

However WebFocus tells me it cannot find the field D_S_IND. I then remembered that perhaps I have to use a WITH statement, but apparently WITH cannot be used in conjunction with AND!



2, I have also tried creating a one row hold file (HOLD1) containing 2 fields; a constant (Company) that is in every ORDER_FILE record and a computed field (D_S_IND) containing the ‘S’.

I then join ORDER_FILE to this new file, so every record has an ‘S’ field and then joined that to my DESPATCH_FILE. WebFocus does not error with this, but it does not work. It’s as if WebFocus doesn’t see the newly computed ‘S’ field.


JOIN
COMPANY IN ORDER_FILE TO
COMPANY IN HOLD1 AS J0
END

JOIN
ORDER_NO AND D_S_IND IN ORDER_FILE TO
ORDER_NO AND SHIPPED_IND IN DESPATCH_FILE AS J0
END


So sorry to waffle on, but I really hope someone can understand my predicament and perhaps suggest a way to fix it or even spot what I can do to make one of my attempted fixes work!

Many thanks as always!!

Mark,

SET ALL was originially designed to work with FOCUS files, not relational databases. When RDBMS's became popular, the syntax was extended to work with them but often turned off optimization.

I do not recommend joining a non-DB2 table to a DB2 table. This is a heterogeneous join and WebFOCUS winds up doing all the work anyway.

I would recommend first that you look in your Data Adminstration manual for your platform and DB2 and read up on the optimization section and JOIN. Somewhere in my memory I had thought that left outer joins were passed in DB2 but maybe not in your release.

Also, use tracing in your program to look at the generated SQL to see if it is doing what you expect:

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON
SET XRETRIEVAL=OFF  

Also, check on what you can do with the MISSING option.

And lastly, you might have to look into SQL Passthru as a solution.

Sorry I can't be more help.

As a DB2 reporting person, I try to pull most of the information with the left outer in place, do a print or what ever on the possibly missing field and do final selection in a hold file. All other selection is usually in the first table of the join. Takes creative join structures sometimes.

Try changing your where statement to:
WHERE DESPATCH_FILE.SHIPPED_IND EQ ‘S’
OR ORDER_FILE.ORDER_NO NE DESPATCH_FILE.ORDER_NO;