Hello,

I am trying to use a hold table (HOLDTOP10CLIENTS) which will pull the top 10 JH_LOCALCLIENTCODE by revenue.

Then I want to join that hold table to a synonym to pull the detail of only the top 10 JH_LOCALCLIENTCODE.

When I run my hold file, it pulls the 10 clients and revenue, but I am having issues with the join and pulling the rest of the detail fields.

Any suggestions would help.

Thanks!


TABLE FILE dm_global/vwjobtransactionsummary
SUM VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.REVAMOUNT
BY TOTAL HIGHEST 10 VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.REVAMOUNT NOPRINT
BY VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.JH_LOCALCLIENTCODE
WHERE VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.FISCALYEAR EQ '2018'
ON TABLE HOLD AS HOLDTOP10CLIENTS FORMAT FOCUS INDEX JH_LOCALCLIENTCODE
END


JOIN JH_LOCALCLIENTCODE IN HOLDTOP10CLIENTS
TO UNIQUE JH_LOCALCLIENTCODE IN dm_global/vwjobtransactionsummary AS J1
END


TABLE FILE dm_global/vwjobtransactionsummary
SUM VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.REVAMOUNT
VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.AL_LINEAMOUNT
VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.TEU
BY VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.JH_LOCALCLIENTCODE
BY VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.INVOICEYEAR
BY VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.INVOICEMONTH
END

This message has been edited. Last edited by: FP Mod Chuck,

What dbms is vwjobtransactionsummary in? Is it a FOCUS file or some other database? What specific issues are you having? Are you getting an error message?

I just had a co-worker figure out the issue. vwjobtransactionsummary is in a SQL DB. The issue is that the report would run, just not do a join and show only the top 10. But below is the code a co-worker sent me that worked.

Thanks for the help.


TABLE FILE dm_global/vwjobtransactionsummary
SUM
VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.REVAMOUNT AS 'CL_REV'
COMPUTE CLCODE/A100=VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.JH_LOCALCLIENTCODE;
BY TOTAL HIGHEST 10 VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.REVAMOUNT NOPRINT
BY VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.JH_LOCALCLIENTCODE
WHERE VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.FISCALYEAR EQ '2018'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HOLD_SUMMARY FORMAT FOCUS INDEX CLCODE
-RUN
-*? HOLD HOLD_SUMMARY
-*TABLE FILE HOLD_SUMMARY
-*PRINT *
-*END
-*-EXIT

JOIN LEFT_OUTER JH_LOCALCLIENTCODE IN dm_global/vwjobtransactionsummary
TO UNIQUE CLCODE IN HOLD_SUMMARY AS J1
END

TABLE FILE dm_global/vwjobtransactionsummary
SUM
VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.REVAMOUNT
VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.AL_LINEAMOUNT
VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.TEU
BY VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.JH_LOCALCLIENTCODE
BY VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.INVOICEYEAR
BY VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.INVOICEMONTH
WHERE VWJOBTRANSACTIONSUMMARY.VWJOBTRANSACTIONSUMMARY.FISCALYEAR EQ '2018'
WHERE CL_REV NE '';