I have two DB2 tables. One is
a fact table (COMM_CUST_REPTG_F)
with amounts.
The second is a lookup table
(AMOUNT_BAND_D) that groups the
amounts into bands or ranges.
The report summarizes the amounts
in the fact table grouped by the ranges in the lookup table.
Is this possible with WebFOCUS code (not SQL pass-through)? As you can see, there's no join field.
The SQL:

SELECT
 T1.BAND_DS,
SUM(T2.TOT_AUTH_AMT)
FROM  BSLC.AMOUNT_BAND_D T1,
BSLC.COMM_CUST_REPTG_F T2
WHERET2.TIME_DIM_KEY = 37488 AND
(T2.TOT_AUTH_AMT >= 
T1.BAND_FROM_AMT AND
T2.TOT_AUTH_AMT <=T1.BAND_TO_AMT)
GROUP BY T1.BAND_DS
ORDER BY  T1.BAND_DS
FOR FETCH ONLY

Contents
of the lookup table:

BAND_CD  
BAND_FROM_AMT  BAND_TO_AMT  BAND_DS
 ---------------------- 
1 0 25000        
0 - 25000 
2 25001 50000  25001 - 50000 
3 50001  100000 50001 - 100000
4 100001 250000 100001 - 250000 
5  250001  500000  250001 - 500000
6   500001  1000000 500001 - 1000000 
7   1000001   1500000 1000001 - 1500000 
8 1500001  25000001500001 - 2500000  
9 2500001  5000000 2500001 - 5000000 
10 5000001 7500000 5000001 - 
7500000 1
1   7500001  10000000     
7500001 - 
10000000 
12  10000001 15000000   10000001 - 
15000000
13  15000001 20000000 15000001 - 
20000000 
14   20000001 
- 25000000 15  25000001       30000000   
25000001 - 30000000 
16  30000001  40000000     
30000001 - 40000000 17 40000001  
50000000  40000001 - 50000000 
18  50000001 
99999999999  50000001 - 
99999999999

Thank you.

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

Perhaps the IN-GROUPS-OF might work for you or a define, I'm not sure as to your look-up table

This code:
TABLE FILE HOLDD
HEADING CENTER
"FISAPP &YEARSDESC PART II SECTION F #26 - #39"
"UNDERGRADUATE DEPENDENT WITH AND WITHOUT DEGREES ELEGIBLE FOR AID"
"REPORT RUN &DATE"
SUM CNT.STUID AS 'RECIPIENT'
BY INCOME IN-GROUPS-OF 3000 AS 'BOTTOM,BRACKET'
ACROSS DEGREE AS ''
ACROSS ENROLL AS ''
IF SA1W1 EQ 'D'
IF SGRADE EQ 'U'
IF AUTOEFC NE 'Y'
ON TABLE COLUMN-TOTAL
ON TABLE SET ONLINE-FMT PDF
ON TABLE SET STYLE *
TYPE=REPORT, ORIENTATION = LANDSCAPE, FONT = COURIER, SIZE = 9, $
ENDSTYLE
END
Gives this report:
PAGE 3
FISAPP 2004=05 PART II SECTION F #26 - #39
UNDERGRADUATE DEPENDENT WITH AND WITHOUT DEGREES ELEGIBLE FOR AID
REPORT RUN 07/12/05
NO BACC YES BACC
FULL PART FULL PART
BOTTOM
BRACKET
375000 1 0 0 0
381000 0 1 0 0
384000 0 1 0 0
423000 0 1 0 0
426000 0 1 0 0
429000 1 0 0 0
504000 1 0 0 0
690000 1 1 0 0
1002000 1 0 0 0
TOTAL 3603 2283 360 79

This was last page and pdf doesn't cut paste well

I'd like to use the llokup table and not in-groups-of, the groupings are not consistent.

I am mainly curious about how to refer to another table without a JOIN, perhaps some McGyver technique involving dummy fields.

Hi Francis,

Have you considered using conditional joins like in the example below using the sample Employee DB

SET ALL=ON
JOIN FILE EMPLOYEE AT EMP_ID TAG T1 TO ALL
FILE JOBLIST AT JOBCLASS TAG T2 AS JC1
WHERE T1.CURR_SAL GE T2.LOWSAL;
WHERE T1.CURR_SAL LE T2.HIGHSAL;
END
TABLE FILE EMPLOYEE
SUM
T1.CURR_SAL
BY
T2.JOBDESC
END


M

Wow, I've never used this before. Thanks for the tip.

quote:

Using conditional JOIN syntax, you can establish joins based on conditions other than
equality between fields. In addition, the host and cross-referenced join fields do not have to
contain matching formats, and the cross-referenced field does not have to be indexed.
The conditional join is supported for FOCUS and for VSAM, ADABAS, IMS, and all relational
data sources. Because each data source differs in its ability to handle complex WHERE
criteria, the optimization of the conditional JOIN syntax differs depending on the specific
data sources involved in the join and the complexity of the WHERE criteria.

Mickey,

Thanks a million for pointing me to the Conditional Join, I've never used it and I have over 15 years of FOCUS experience! You can always learn something new.

In at least WebFOCUS 5.2.3, there appears to be what I'd call a bug: The host Master file name cannot be longer than 8 characters. The cross-referenced Master file name can be longer than 8 characters.

This is a problem as, in our DB2 data-mart, table names are very long. I have temporarily solved it by creating a copy of the master and access file with smaller names.

The error I get when using a host master file name of longer than 8 characters is:

(FOC1822) WARNING. INVALID SYMBOL: v

A symbol in the master file is not recognized. Please consult
the documentation for a description of all the valid master
file attributes.

UW Francis.

This seems to be a bug in 52 and is supposed to have been fixed in version 526.

http://techsupport.informationbuilders.com/known_problems/82731029.html

M

UW? I'm not familiar with that abbreviation.

Thanks for the Known Problem link.

Oddly, the following disclaimer is at the end of the document,

quote:

If possible, shorten the length of the master names. However, this may change the behavior so that it does not do a non-equijoin. It may retrieves all records from each table in the sql, then do the join locally with the subset data. This is not efficient with large amounts of data.

I did not know that the name of the Master has an effect on the generated SQL!