I'm trying to create a Smart Note that will list the accounts that exist in the GMDISTRIB that do not have a Record_Type attribute set up on the GL16.2. Not all accounts should have their Record_Type attribute value assigned, only the accounts in the GMDISTRIB.
I've broken it down into two queries:
1. The first infoset JDBC query pulls a listing of all of the unique accounts that exist in the GMDISTRIB.
SELECT GMDISTRIB.ACCOUNT FROM GMDISTRIB GROUP BY GMDISTRIB.ACCOUNT
2. The second infoset JDBC query pulls all of the Accounts from GLCHARTDTL with a link to GLACCMXVAL for attribute value data.
SELECT GLCHARTDTL.CHART_NAME, GLCHARTDTL.SUMRY_ACCT_ID, GLCHARTDTL.CHART_SECTION, GLCHARTDTL.ACCOUNT, GLCHARTDTL.ACCOUNT_DESC, GLACCMXVAL.MATRIX_CAT, GLACCMXVAL.MX_VALUE FROM GLCHARTDTL LEFT JOIN GLACCMXVAL ON GLCHARTDTL.OBJ_ID = GLACCMXVAL.OBJ_ID WHERE GLCHARTDTL.CHART_NAME='FINANCIALS' ORDER BY GLCHARTDTL.ACCOUNT
My question is, how do I create a Smart Note that will show only the accounts from the first query that have a Record_Type=null from the second query? Any suggestions would greatly be appreciated.
Thanks for you time!
Ken