I'm trying to create a Parameter datadump tool off a 50mill record transaction table

How do you pass multiple value in my situation below where the transaction table don't have a Product field?

CATEGORY TABLE
CATEGORY PRODUCT
AA AA1
AA AA2

TABLE FILE CATEGORY
PRINT
PRODUCT
ON TABLE HOLD HCAT FORMAT ALPHA

-RUN

-READ HCAT &PRODUCT.A12.


TRANSACTION TABLE

DEFINE FILE TXN
-*products created in the define
AA1/D15.2 = IF LEVEL6 EQ 'AA1' AND LEVEL5 EQ 'AA' THEN REVENUE ELSE 0;
AA2/D15.2 = IF LEVEL6 EQ 'AA2' AND LEVEL5 EQ 'AA' THEN REVENUE ELSE 0;
END

TABLE FILE TXN
SUM
&PRODUCT
BY MONTH
BY LEVEL1
BY LEVEL2
BY LEVEL3
BY LEVEL4
BY LEVEL5
BY LEVEL6
WHERE &PRODUCT NE 0
END


If there are any other suggestion beside multi value pass please pass along.

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

If you are going to DEFINE the PRODUCTs in table TXN, do you even need the CATEGORY table?

DEFINE FILE TXN
PRODUCT/A3 = IF (LEVEL6 EQ 'AA1' AND LEVEL5 EQ 'AA') THEN 'AA1' ELSE
             IF (LEVEL6 EQ 'AA2' AND LEVEL5 EQ 'AA') THEN 'AA2' ELSE
             IF ..... ;
END

TABLE FILE TXN
SUM REVENUE
BY PRODUCT
BY MONTH
BY LEVEL1
BY LEVEL2
BY LEVEL3
BY LEVEL4
BY LEVEL5
BY LEVEL6
WHERE (PRODUCT EQ '&PRODUCT')
  AND (REVENUE NE 0);
END

This message has been edited. Last edited by: Dan Satchell,

Thanks Dan.

I got 175 products and its not as simple as level6=aa and level5 eq aa to define a product.

Any alternative solutions??

quote:

CATEGORY TABLE
CATEGORY PRODUCT
AA AA1
AA AA2

TABLE FILE CATEGORY
PRINT
PRODUCT
ON TABLE HOLD HCAT FORMAT ALPHA

-RUN

-READ HCAT &PRODUCT.A12.


TRANSACTION TABLE

DEFINE FILE TXN
-*products created in the define
AA1/D15.2 = IF LEVEL6 EQ 'AA1' AND LEVEL5 EQ 'AA' THEN REVENUE ELSE 0;
AA2/D15.2 = IF LEVEL6 EQ 'AA2' AND LEVEL5 EQ 'AA' THEN REVENUE ELSE 0;
END

TABLE FILE TXN
SUM
&PRODUCT
BY MONTH
BY LEVEL1
BY LEVEL2
BY LEVEL3
BY LEVEL4
BY LEVEL5
BY LEVEL6
WHERE &PRODUCT NE 0
END

Ok solved 1 half of the issue by using filedef
FILEDEF HCAT DISK APPS\HCAT.TXT
TABLE FILE CATEGORY
PRINT
PRODUCT
ON TABLE HOLD HCAT FORMAT ALPHA
END

TABLE FILE TXN
SUM
-*-------------
-*RESOLVED WITH INCLUDE FILE
-*-------------
-MRNOEDIT -INCLUDE HCAT
BY MONTH
BY LEVEL1
BY LEVEL2
BY LEVEL3
BY LEVEL4
BY LEVEL5
BY LEVEL6
-*-------------
-*ISSUE REMAINING
-*HOW WOULD I PASS THE WHERE STATEMENT BELOW ???
-*FOR MORE THEN 2 PRODUCTS AS I NEED THE OR
-*COUNT # OF PROUDCTS AND LOOP??
-*-------------
WHERE AA1 NE 0 OR AA2 NE 0
END

Maybe something like this:

FILEDEF WCAT DISK APPS\WCAT.TXT
-*
DEFINE FILE CATEGORY
 WHERE_TXT/A15 = IF (LAST PRODUCT EQ '') THEN (PRODUCT | ' NE 0')
                 ELSE ('OR ' | PRODUCT | ' NE 0');
END
-*
TABLE FILE CATEGORY
 PRINT WHERE_TXT
 WHERE (PRODUCT NE '');
 ON TABLE HOLD WCAT FORMAT ALPHA
END
-*
TABLE FILE TXN
SUM
-*-------------
-*RESOLVED WITH INCLUDE FILE
-*-------------
-MRNOEDIT -INCLUDE HCAT
BY MONTH
BY LEVEL1
BY LEVEL2
BY LEVEL3
BY LEVEL4
BY LEVEL5
BY LEVEL6
WHERE
-MRNOEDIT -INCLUDE WCAT
;
END

Thanks Dan!!!

Thats exactly what I needed it to do!!

Cheers,