How to define a field based on number of records in the table?

ITEM_MASTER
-----------
ITEM_ID ;     NUMBER(10)
MASTER_ID   NUMBER(10)
UPC             VARCHAR2(20)
DESCRIPTION VARCHAR2(50)
UNIT_PRICE   NUMBER(10,2)

SET ASNAMES=ON
TABLE FILE ITEM_MASTER
PRINT UPC             AS 'MAS_UPC'
        DESCRIPTION   AS 'MAS_DESC'
        UNIT_PRICE    AS 'MAS_PRICE'
BY     ITEM_ID        AS 'MAS_ITEMID'
WHERE (ITEM_ID EQ MASTER_ID AND ITEM_ID EQ 100);
ON TABLE HOLD AS MASITEMS
END

-* Get all associated items
JOIN MAS_ITEMID IN MASITEMS TO ALL MASTER_ID IN ITEM_MASTER AS MC1
END

-* I need to define a field based on the number of associated items in the hold file
DEFINE FILE MASITEMS
ITEM_TYPE/A15 = IF ITEM_ID EQ MASTER_ID AND (if associated items exists (total number of records > 1)) THEN 'MASTER ITEM' ELSE '';
END

How do I find out the total number of items in the hold file and make use of it in the IF condition?
ITEM_TYPE/A15 = IF ITEM_ID EQ MASTER_ID AND &LINES GT 1 THEN 'MASTER ITEM' ELSE '';
This doesn't work...

k.lane

Platinum Member

posted

Hide Post

The problem is with the use of &LINES in this context. It's value has been reset to 0.

Do the following:

SET ASNAMES=ON
TABLE FILE ITEM_MASTER
PRINT UPC AS 'MAS_UPC'
DESCRIPTION AS 'MAS_DESC'
UNIT_PRICE AS 'MAS_PRICE'
BY ITEM_ID AS 'MAS_ITEMID'
WHERE (ITEM_ID EQ MASTER_ID AND ITEM_ID EQ 100);
ON TABLE HOLD AS MASITEMS
END
-RUN
-SET &M_LINES = &LINES ;

-* Get all associated items
JOIN MAS_ITEMID IN MASITEMS TO ALL MASTER_ID IN ITEM_MASTER AS MC1
END

ITEM_TYPE/A15 = IF ITEM_ID EQ MASTER_ID AND &M_LINES GT 1 THEN 'MASTER ITEM' ELSE '';

If at first things don't work, remember to insert:

-SET &ECHO = ALL ;

at the top of your program. This will give you a lot of information. If you do it in this case, you will see a zero (0) substituted for &LINES (in your existing logic).

Ken

Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005

Ignored post by k.lane posted

Show Post

susannah

Expert

posted

Hide Post

how about just counting your item elements per id when you make your first extract.
SUM CNT.DST.UPC AS kITEMS
BY ITEM_ID AS MAS_ITEMID
PRINT UPC AS 'MAS_UPC'
DESCRIPTION AS 'MAS_DESC'
UNIT_PRICE AS 'MAS_PRICE'
BY ITEM_ID AS 'MAS_ITEMID'
WHERE (ITEM_ID EQ MASTER_ID AND ITEM_ID EQ 100);
then you'll have your number of different flavors of each item right in your record.

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Show Post

posted

I forgot to mention one thing in my post. The first extract will always return only one item. This item can be a master reference item (other items use this as their pricing reference) or it can be an independent item (without any other items associated with it). That�s why I am joining it with the item_master table to see if it has any other items associated with it or not. If it has any then the join will have more than one record as the result. I have to mark all the master items with prefix �MASTER ITEM� on the report. This proc will be included inside a repeat loop from the main procedure.

posted

Could anyone know how to find the number of records in a join? In my case how do I find out the total number of records after I run the join?

Please Wait. Your request is being processed...