PS Query & SQL

Hello all,

For who attended and have additional questions, comments, and/or insights to this webinar, please add them here.  I will get my scramble expression out there today.

Hello all,

Here is the link to the Idea Labs that asks for way to move the fields around more efficiently in Composite Query: https://community.oracle.com/mosc/discussion/4581685/field-reordering-improvement-for-composite-query.

Hello all,

For who attended and have additional questions, comments, and/or insights to this webinar, please add them here.  I will get my scramble expression out there today.

Hello all,

As promised, I am giving you all the method by which I "scramble" PII data. Attached you will find a document on using STANDARD_HASH and DBMS.RANDOM to do so.

Hello all,

Here is the link to the Idea Labs that asks for way to move the fields around more efficiently in Composite Query: https://community.oracle.com/mosc/discussion/4581685/field-reordering-improvement-for-composite-query.

Hello all,

For who attended and have additional questions, comments, and/or insights to this webinar, please add them here.  I will get my scramble expression out there today.

Hello all,

For who attended and have additional questions, comments, and/or insights to this webinar, please add them here.  I will get my scramble expression out there today.

I just watched the recording of the webinar...thanks for providing this!

You mentioned the Q&A from the session would be available... if it is posted on HEUG I haven't been able to find it.

Thanks again!

Susan

Hello all,

For who attended and have additional questions, comments, and/or insights to this webinar, please add them here.  I will get my scramble expression out there today.

Hey Susan - I think in the end, we did not capture the individual questions from the Q&A, but I can check with Tesha about that.  You can review the PS Query / SQL library to see if there are resources there which might prove helpful.  That library can be found here - PS Query & SQL Library. 

Also, please feel free to post questions here - I know Dan and I will be happy to answer any questions we can, and there were several folks on the call who had experience with this tool.  

Take Care! 

I just watched the recording of the webinar...thanks for providing this!

You mentioned the Q&A from the session would be available... if it is posted on HEUG I haven't been able to find it.

Thanks again!

Susan

Hello all,

For who attended and have additional questions, comments, and/or insights to this webinar, please add them here.  I will get my scramble expression out there today.

Hello Susan,

Here is the out from the Q&A.  I tried to clean it up some from the CSV that was sent to me.

Hey Susan - I think in the end, we did not capture the individual questions from the Q&A, but I can check with Tesha about that.  You can review the PS Query / SQL library to see if there are resources there which might prove helpful.  That library can be found here - PS Query & SQL Library. 

Also, please feel free to post questions here - I know Dan and I will be happy to answer any questions we can, and there were several folks on the call who had experience with this tool.  

Take Care! 

I just watched the recording of the webinar...thanks for providing this!

You mentioned the Q&A from the session would be available... if it is posted on HEUG I haven't been able to find it.

Thanks again!

Susan

Hello all,

For who attended and have additional questions, comments, and/or insights to this webinar, please add them here.  I will get my scramble expression out there today.

Thanks to your wonderful webinar, I was able to create a composite query for our scholarship coordinator that has one row for each scholarship awarded with both Fall disbursement and Spring disbursement data in one row...yay!

One issue I haven't figured out is what to do with scholarships that have more than one disbursement per term. The output of my current composite query is producing 4 rows instead of one...one row for the first Fall disbursement with the first Spring disbursement data, a second row for the first Fall disbursement with the second Spring disbursement data, a third row for the second Fall disbursement with the first Spring disbursement data, and a fourth row for the second Fall disbursement with the second Spring disbursement data.

Ideally, there would be a column in the output for each of those second disbursements...all on one row.

Here is the SQL:

SELECT DISTINCT Q1."1EMPLID",
    Q1."1NAME",
    Q1."1EMAIL_ADDR",
    Q1."1TEM_TYPE",
    Q2."4IT_DESCR",
    Q2."4FUND_CODE",
    Q2."4DEPTID",
    Q3."2STRM",
    Q3."2DISB_ID",
    Q3."2OFF_BAL",
    Q3."2ACC_BAL",
    Q3."2DISB_BAL",
    Q3."2ACAD__PLAN",
    Q3."2ACAD_CAREER",
    Q4."3STRM",
    Q4."3DISB_ID",
    Q4."3OFF_BAL",
    Q4."3ACC_BAL",
    Q4."3DISB_BAL",
    Q4."3ACAD_PLAN",
    Q4."3ACAD_CAREER"
FROM
  (SELECT DISTINCT A.EMPLID AS "1EMPLID",
    B.NAME AS "1NAME",
    C.EMAIL_ADDR AS "1EMAIL_ADDR",
    A.ITEM_TYPE AS "1TEM_TYPE",
    A.ACAD_CAREER AS "1ACAD_CAREER",
    A.INSTITUTION AS "1INSTITUTION",
    A.AID_YEAR AS "1AID_YEAR"
  FROM PS_STDNT_AWARDS A,
    (PS_SCC_PERDATA_QVW B LEFT OUTER
JOIN
     PS_SCC_EMAIL_QVW C
ON
     B.EMPLID = C.EMPLID AND C.E_ADDR_TYPE = 'OCMP' )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.EMPLID = B.EMPLID
     AND A.OFFER_AMOUNT > 0) ) Q1
JOIN
    ( SELECT B.DESCR AS "4IT_DESCR",
    E.FUND_CODE AS "4FUND_CODE",
    E.DEPTID AS "4DEPTID",
    B.SETID AS "4SETID",
    B.ITEM_TYPE AS "4ITEM_TYPE",
    B.EFFDT AS "4EFFDT",
    B.EFF_STATUS AS "4EFF_STATUS",
    C.STRM AS "4STRM",
    C.EFFDT AS "4GL_EFFDT",
    C.EFF_STATUS AS "4GL_EFF_STATUS"
  FROM PS_ITEM_TYPE_1_TBL A,
    PS_ITEM_TYPE_TBL B,
    PS_GL_INT_DT_TBL C,
    PS_GL_INTERFACE E
  WHERE ( B.SETID = A.SETID
    AND B.ITEM_TYPE = A.ITEM_TYPE
    AND C.SETID = A.SETID
    AND C.ITEM_TYPE = A.ITEM_TYPE
    AND ( B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_TYPE_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.ITEM_TYPE = B_ED.ITEM_TYPE
          AND B_ED.EFFDT <= SYSDATE)
     AND A.SETID = 'SOCMP'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND B.EFF_STATUS = 'A'
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_GL_INT_DT_TBL C_ED
        WHERE C.SETID = C_ED.SETID
          AND C.ITEM_TYPE = C_ED.ITEM_TYPE
          AND C.STRM = C_ED.STRM
          AND C.SESSION_CODE = C_ED.SESSION_CODE
          AND C_ED.EFFDT <= SYSDATE)
     AND C.STRM = (SELECT MAX( D.STRM)
  FROM PS_GL_INT_DT_TBL D
  WHERE D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_GL_INT_DT_TBL D_ED
        WHERE D.SETID = D_ED.SETID
          AND D.ITEM_TYPE = D_ED.ITEM_TYPE
          AND D.STRM = D_ED.STRM
          AND D.SESSION_CODE = D_ED.SESSION_CODE
          AND D_ED.EFFDT <= SYSDATE)
     AND D.SETID = C.SETID
     AND D.ITEM_TYPE = C.ITEM_TYPE)
     AND A.SETID = E.SETID
     AND A.ITEM_TYPE = E.ITEM_TYPE
     AND E.EFFDT =
        (SELECT MAX(E_ED.EFFDT) FROM PS_GL_INTERFACE E_ED
        WHERE E.SETID = E_ED.SETID
          AND E.ITEM_TYPE = E_ED.ITEM_TYPE
          AND E.STRM = E_ED.STRM
          AND E.SESSION_CODE = E_ED.SESSION_CODE
          AND E_ED.EFFDT <= SYSDATE)
     AND E.DB_CR_IND = 'D'
     AND E.STRM = C.STRM )) ) Q2
ON
     Q1."1TEM_TYPE" = Q2."4ITEM_TYPE" LEFT OUTER
JOIN
    ( SELECT DISTINCT A.STRM AS "2STRM",
    A.ITEM_TYPE AS "2ITEM_TYPE",
    A.DISBURSEMENT_ID AS "2DISB_ID",
    A.OFFER_BALANCE AS "2OFF_BAL",
    A.ACCEPT_BALANCE AS "2ACC_BAL",
    A.DISBURSED_BALANCE AS "2DISB_BAL",
    B.DESCR AS "2ACAD__PLAN",
    A.EMPLID AS "2EMPLID",
    A.ACAD_CAREER AS "2ACAD_CAREER",
    A.AID_YEAR AS "2AID_YEAR",
    A.INSTITUTION AS "2INSTITUTION"
  FROM ((PS_STDNT_AWRD_DISB A LEFT OUTER
JOIN
     PS_STDNT_FA_TERM C
ON
     A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.AID_YEAR = C.AID_YEAR AND A.ACAD_CAREER = C.ACAD_CAREER AND C.STRM = A.STRM AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_FA_TERM C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.INSTITUTION = C_ED.INSTITUTION
          AND C.STRM = C_ED.STRM
          AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ =
        (SELECT MAX(C_ES.EFFSEQ) FROM PS_STDNT_FA_TERM C_ES
        WHERE C.EMPLID = C_ES.EMPLID
          AND C.INSTITUTION = C_ES.INSTITUTION
          AND C.STRM = C_ES.STRM
          AND C.EFFDT = C_ES.EFFDT) ) LEFT OUTER
JOIN
     PS_ACAD_PLAN_TB_VW B
ON
     C.INSTITUTION = B.INSTITUTION AND B.ACAD_PLAN = C.ACAD_PLAN )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.STRM = '2257'
     AND A.OFFER_BALANCE > 0) ) Q3
ON
     Q1."1EMPLID" = Q3."2EMPLID" AND  Q1."1ACAD_CAREER" = Q3."2ACAD_CAREER" AND  Q1."1INSTITUTION" = Q3."2INSTITUTION" AND  Q1."1AID_YEAR" = Q3."2AID_YEAR" AND  Q1."1TEM_TYPE" = Q3."2ITEM_TYPE" LEFT OUTER
JOIN
    ( SELECT DISTINCT A.STRM AS "3STRM",
    A.ITEM_TYPE AS "3ITEM_TYPE",
    A.DISBURSEMENT_ID AS "3DISB_ID",
    A.OFFER_BALANCE AS "3OFF_BAL",
    A.ACCEPT_BALANCE AS "3ACC_BAL",
    A.DISBURSED_BALANCE AS "3DISB_BAL",
    B.DESCR AS "3ACAD_PLAN",
    A.EMPLID AS "3EMPLID",
    A.ACAD_CAREER AS "3ACAD_CAREER",
    A.AID_YEAR AS "3AID_YEAR",
    A.INSTITUTION AS "3INSTITUTION"
  FROM ((PS_STDNT_AWRD_DISB A LEFT OUTER
JOIN
     PS_STDNT_FA_TERM C
ON
     A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.AID_YEAR = C.AID_YEAR AND A.ACAD_CAREER = C.ACAD_CAREER AND C.STRM = A.STRM AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_FA_TERM C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.INSTITUTION = C_ED.INSTITUTION
          AND C.STRM = C_ED.STRM
          AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ =
        (SELECT MAX(C_ES.EFFSEQ) FROM PS_STDNT_FA_TERM C_ES
        WHERE C.EMPLID = C_ES.EMPLID
          AND C.INSTITUTION = C_ES.INSTITUTION
          AND C.STRM = C_ES.STRM
          AND C.EFFDT = C_ES.EFFDT) ) LEFT OUTER
JOIN
     PS_ACAD_PLAN_TB_VW B
ON
     C.INSTITUTION = B.INSTITUTION AND B.ACAD_PLAN = C.ACAD_PLAN )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.STRM = '2263'
     AND A.OFFER_BALANCE > 0) ) Q4
ON
     Q1."1EMPLID" = Q4."3EMPLID" AND  Q1."1ACAD_CAREER" = Q4."3ACAD_CAREER" AND  Q1."1INSTITUTION" = Q4."3INSTITUTION" AND  Q1."1AID_YEAR" = Q4."3AID_YEAR" AND  Q1."1TEM_TYPE" = Q4."3ITEM_TYPE"
ORDER BY Q1."1NAME" ASC
Actions
Query Selected Collapsible section Query Selected
Query Fields      
       
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_1 Q1
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_4 Q2
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_2 Q3
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_3 Q4
Menu
 

Hello Susan,

Here is the out from the Q&A.  I tried to clean it up some from the CSV that was sent to me.

Hey Susan - I think in the end, we did not capture the individual questions from the Q&A, but I can check with Tesha about that.  You can review the PS Query / SQL library to see if there are resources there which might prove helpful.  That library can be found here - PS Query & SQL Library. 

Also, please feel free to post questions here - I know Dan and I will be happy to answer any questions we can, and there were several folks on the call who had experience with this tool.  

Take Care! 

I just watched the recording of the webinar...thanks for providing this!

You mentioned the Q&A from the session would be available... if it is posted on HEUG I haven't been able to find it.

Thanks again!

Susan

Hello all,

For who attended and have additional questions, comments, and/or insights to this webinar, please add them here.  I will get my scramble expression out there today.

Susan,

Does your Composite Query need to show each disbursement for a term or can you do a sum per term?

Thanks to your wonderful webinar, I was able to create a composite query for our scholarship coordinator that has one row for each scholarship awarded with both Fall disbursement and Spring disbursement data in one row...yay!

One issue I haven't figured out is what to do with scholarships that have more than one disbursement per term. The output of my current composite query is producing 4 rows instead of one...one row for the first Fall disbursement with the first Spring disbursement data, a second row for the first Fall disbursement with the second Spring disbursement data, a third row for the second Fall disbursement with the first Spring disbursement data, and a fourth row for the second Fall disbursement with the second Spring disbursement data.

Ideally, there would be a column in the output for each of those second disbursements...all on one row.

Here is the SQL:

SELECT DISTINCT Q1."1EMPLID",
    Q1."1NAME",
    Q1."1EMAIL_ADDR",
    Q1."1TEM_TYPE",
    Q2."4IT_DESCR",
    Q2."4FUND_CODE",
    Q2."4DEPTID",
    Q3."2STRM",
    Q3."2DISB_ID",
    Q3."2OFF_BAL",
    Q3."2ACC_BAL",
    Q3."2DISB_BAL",
    Q3."2ACAD__PLAN",
    Q3."2ACAD_CAREER",
    Q4."3STRM",
    Q4."3DISB_ID",
    Q4."3OFF_BAL",
    Q4."3ACC_BAL",
    Q4."3DISB_BAL",
    Q4."3ACAD_PLAN",
    Q4."3ACAD_CAREER"
FROM
  (SELECT DISTINCT A.EMPLID AS "1EMPLID",
    B.NAME AS "1NAME",
    C.EMAIL_ADDR AS "1EMAIL_ADDR",
    A.ITEM_TYPE AS "1TEM_TYPE",
    A.ACAD_CAREER AS "1ACAD_CAREER",
    A.INSTITUTION AS "1INSTITUTION",
    A.AID_YEAR AS "1AID_YEAR"
  FROM PS_STDNT_AWARDS A,
    (PS_SCC_PERDATA_QVW B LEFT OUTER
JOIN
     PS_SCC_EMAIL_QVW C
ON
     B.EMPLID = C.EMPLID AND C.E_ADDR_TYPE = 'OCMP' )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.EMPLID = B.EMPLID
     AND A.OFFER_AMOUNT > 0) ) Q1
JOIN
    ( SELECT B.DESCR AS "4IT_DESCR",
    E.FUND_CODE AS "4FUND_CODE",
    E.DEPTID AS "4DEPTID",
    B.SETID AS "4SETID",
    B.ITEM_TYPE AS "4ITEM_TYPE",
    B.EFFDT AS "4EFFDT",
    B.EFF_STATUS AS "4EFF_STATUS",
    C.STRM AS "4STRM",
    C.EFFDT AS "4GL_EFFDT",
    C.EFF_STATUS AS "4GL_EFF_STATUS"
  FROM PS_ITEM_TYPE_1_TBL A,
    PS_ITEM_TYPE_TBL B,
    PS_GL_INT_DT_TBL C,
    PS_GL_INTERFACE E
  WHERE ( B.SETID = A.SETID
    AND B.ITEM_TYPE = A.ITEM_TYPE
    AND C.SETID = A.SETID
    AND C.ITEM_TYPE = A.ITEM_TYPE
    AND ( B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_TYPE_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.ITEM_TYPE = B_ED.ITEM_TYPE
          AND B_ED.EFFDT <= SYSDATE)
     AND A.SETID = 'SOCMP'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND B.EFF_STATUS = 'A'
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_GL_INT_DT_TBL C_ED
        WHERE C.SETID = C_ED.SETID
          AND C.ITEM_TYPE = C_ED.ITEM_TYPE
          AND C.STRM = C_ED.STRM
          AND C.SESSION_CODE = C_ED.SESSION_CODE
          AND C_ED.EFFDT <= SYSDATE)
     AND C.STRM = (SELECT MAX( D.STRM)
  FROM PS_GL_INT_DT_TBL D
  WHERE D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_GL_INT_DT_TBL D_ED
        WHERE D.SETID = D_ED.SETID
          AND D.ITEM_TYPE = D_ED.ITEM_TYPE
          AND D.STRM = D_ED.STRM
          AND D.SESSION_CODE = D_ED.SESSION_CODE
          AND D_ED.EFFDT <= SYSDATE)
     AND D.SETID = C.SETID
     AND D.ITEM_TYPE = C.ITEM_TYPE)
     AND A.SETID = E.SETID
     AND A.ITEM_TYPE = E.ITEM_TYPE
     AND E.EFFDT =
        (SELECT MAX(E_ED.EFFDT) FROM PS_GL_INTERFACE E_ED
        WHERE E.SETID = E_ED.SETID
          AND E.ITEM_TYPE = E_ED.ITEM_TYPE
          AND E.STRM = E_ED.STRM
          AND E.SESSION_CODE = E_ED.SESSION_CODE
          AND E_ED.EFFDT <= SYSDATE)
     AND E.DB_CR_IND = 'D'
     AND E.STRM = C.STRM )) ) Q2
ON
     Q1."1TEM_TYPE" = Q2."4ITEM_TYPE" LEFT OUTER
JOIN
    ( SELECT DISTINCT A.STRM AS "2STRM",
    A.ITEM_TYPE AS "2ITEM_TYPE",
    A.DISBURSEMENT_ID AS "2DISB_ID",
    A.OFFER_BALANCE AS "2OFF_BAL",
    A.ACCEPT_BALANCE AS "2ACC_BAL",
    A.DISBURSED_BALANCE AS "2DISB_BAL",
    B.DESCR AS "2ACAD__PLAN",
    A.EMPLID AS "2EMPLID",
    A.ACAD_CAREER AS "2ACAD_CAREER",
    A.AID_YEAR AS "2AID_YEAR",
    A.INSTITUTION AS "2INSTITUTION"
  FROM ((PS_STDNT_AWRD_DISB A LEFT OUTER
JOIN
     PS_STDNT_FA_TERM C
ON
     A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.AID_YEAR = C.AID_YEAR AND A.ACAD_CAREER = C.ACAD_CAREER AND C.STRM = A.STRM AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_FA_TERM C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.INSTITUTION = C_ED.INSTITUTION
          AND C.STRM = C_ED.STRM
          AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ =
        (SELECT MAX(C_ES.EFFSEQ) FROM PS_STDNT_FA_TERM C_ES
        WHERE C.EMPLID = C_ES.EMPLID
          AND C.INSTITUTION = C_ES.INSTITUTION
          AND C.STRM = C_ES.STRM
          AND C.EFFDT = C_ES.EFFDT) ) LEFT OUTER
JOIN
     PS_ACAD_PLAN_TB_VW B
ON
     C.INSTITUTION = B.INSTITUTION AND B.ACAD_PLAN = C.ACAD_PLAN )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.STRM = '2257'
     AND A.OFFER_BALANCE > 0) ) Q3
ON
     Q1."1EMPLID" = Q3."2EMPLID" AND  Q1."1ACAD_CAREER" = Q3."2ACAD_CAREER" AND  Q1."1INSTITUTION" = Q3."2INSTITUTION" AND  Q1."1AID_YEAR" = Q3."2AID_YEAR" AND  Q1."1TEM_TYPE" = Q3."2ITEM_TYPE" LEFT OUTER
JOIN
    ( SELECT DISTINCT A.STRM AS "3STRM",
    A.ITEM_TYPE AS "3ITEM_TYPE",
    A.DISBURSEMENT_ID AS "3DISB_ID",
    A.OFFER_BALANCE AS "3OFF_BAL",
    A.ACCEPT_BALANCE AS "3ACC_BAL",
    A.DISBURSED_BALANCE AS "3DISB_BAL",
    B.DESCR AS "3ACAD_PLAN",
    A.EMPLID AS "3EMPLID",
    A.ACAD_CAREER AS "3ACAD_CAREER",
    A.AID_YEAR AS "3AID_YEAR",
    A.INSTITUTION AS "3INSTITUTION"
  FROM ((PS_STDNT_AWRD_DISB A LEFT OUTER
JOIN
     PS_STDNT_FA_TERM C
ON
     A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.AID_YEAR = C.AID_YEAR AND A.ACAD_CAREER = C.ACAD_CAREER AND C.STRM = A.STRM AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_FA_TERM C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.INSTITUTION = C_ED.INSTITUTION
          AND C.STRM = C_ED.STRM
          AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ =
        (SELECT MAX(C_ES.EFFSEQ) FROM PS_STDNT_FA_TERM C_ES
        WHERE C.EMPLID = C_ES.EMPLID
          AND C.INSTITUTION = C_ES.INSTITUTION
          AND C.STRM = C_ES.STRM
          AND C.EFFDT = C_ES.EFFDT) ) LEFT OUTER
JOIN
     PS_ACAD_PLAN_TB_VW B
ON
     C.INSTITUTION = B.INSTITUTION AND B.ACAD_PLAN = C.ACAD_PLAN )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.STRM = '2263'
     AND A.OFFER_BALANCE > 0) ) Q4
ON
     Q1."1EMPLID" = Q4."3EMPLID" AND  Q1."1ACAD_CAREER" = Q4."3ACAD_CAREER" AND  Q1."1INSTITUTION" = Q4."3INSTITUTION" AND  Q1."1AID_YEAR" = Q4."3AID_YEAR" AND  Q1."1TEM_TYPE" = Q4."3ITEM_TYPE"
ORDER BY Q1."1NAME" ASC
Actions
Query Selected Collapsible section Query Selected
Query Fields      
       
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_1 Q1
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_4 Q2
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_2 Q3
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_3 Q4
Menu
 

Hello Susan,

Here is the out from the Q&A.  I tried to clean it up some from the CSV that was sent to me.

Hey Susan - I think in the end, we did not capture the individual questions from the Q&A, but I can check with Tesha about that.  You can review the PS Query / SQL library to see if there are resources there which might prove helpful.  That library can be found here - PS Query & SQL Library. 

Also, please feel free to post questions here - I know Dan and I will be happy to answer any questions we can, and there were several folks on the call who had experience with this tool.  

Take Care! 

I just watched the recording of the webinar...thanks for providing this!

You mentioned the Q&A from the session would be available... if it is posted on HEUG I haven't been able to find it.

Thanks again!

Susan

Hello all,

For who attended and have additional questions, comments, and/or insights to this webinar, please add them here.  I will get my scramble expression out there today.

It would be fine to sum the disbursements for each term. So far, I have tried two methods...

Method #1: I tried creating an expression for each of the term amount fields (Offered, Accepted, Disbursed) and using those in the Fields instead of the term amount fields, which resulted in just one row for the student's scholarship (yay!) but the amounts are double what they should be.

The expression looks like this (and I had to choose Aggregate Sum in the field properties):

I didn't save the SQL from Method 1, but I could recreate that and include it in another post if that would help.

Method #2: Instead of using the expressions in the Fields, I tried choosing Aggregate Sum in the field properties for each of the term amount fields, and again I got one row for the student's scholarship but the term amounts were doubled.

(I also tried taking the fields from FA Term for Academic Plan and Career out of the Fields, but the term amounts still doubled.)

Here is the SQL for Method 2:

Susan,

Does your Composite Query need to show each disbursement for a term or can you do a sum per term?

Thanks to your wonderful webinar, I was able to create a composite query for our scholarship coordinator that has one row for each scholarship awarded with both Fall disbursement and Spring disbursement data in one row...yay!

One issue I haven't figured out is what to do with scholarships that have more than one disbursement per term. The output of my current composite query is producing 4 rows instead of one...one row for the first Fall disbursement with the first Spring disbursement data, a second row for the first Fall disbursement with the second Spring disbursement data, a third row for the second Fall disbursement with the first Spring disbursement data, and a fourth row for the second Fall disbursement with the second Spring disbursement data.

Ideally, there would be a column in the output for each of those second disbursements...all on one row.

Here is the SQL:

SELECT DISTINCT Q1."1EMPLID",
    Q1."1NAME",
    Q1."1EMAIL_ADDR",
    Q1."1TEM_TYPE",
    Q2."4IT_DESCR",
    Q2."4FUND_CODE",
    Q2."4DEPTID",
    Q3."2STRM",
    Q3."2DISB_ID",
    Q3."2OFF_BAL",
    Q3."2ACC_BAL",
    Q3."2DISB_BAL",
    Q3."2ACAD__PLAN",
    Q3."2ACAD_CAREER",
    Q4."3STRM",
    Q4."3DISB_ID",
    Q4."3OFF_BAL",
    Q4."3ACC_BAL",
    Q4."3DISB_BAL",
    Q4."3ACAD_PLAN",
    Q4."3ACAD_CAREER"
FROM
  (SELECT DISTINCT A.EMPLID AS "1EMPLID",
    B.NAME AS "1NAME",
    C.EMAIL_ADDR AS "1EMAIL_ADDR",
    A.ITEM_TYPE AS "1TEM_TYPE",
    A.ACAD_CAREER AS "1ACAD_CAREER",
    A.INSTITUTION AS "1INSTITUTION",
    A.AID_YEAR AS "1AID_YEAR"
  FROM PS_STDNT_AWARDS A,
    (PS_SCC_PERDATA_QVW B LEFT OUTER
JOIN
     PS_SCC_EMAIL_QVW C
ON
     B.EMPLID = C.EMPLID AND C.E_ADDR_TYPE = 'OCMP' )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.EMPLID = B.EMPLID
     AND A.OFFER_AMOUNT > 0) ) Q1
JOIN
    ( SELECT B.DESCR AS "4IT_DESCR",
    E.FUND_CODE AS "4FUND_CODE",
    E.DEPTID AS "4DEPTID",
    B.SETID AS "4SETID",
    B.ITEM_TYPE AS "4ITEM_TYPE",
    B.EFFDT AS "4EFFDT",
    B.EFF_STATUS AS "4EFF_STATUS",
    C.STRM AS "4STRM",
    C.EFFDT AS "4GL_EFFDT",
    C.EFF_STATUS AS "4GL_EFF_STATUS"
  FROM PS_ITEM_TYPE_1_TBL A,
    PS_ITEM_TYPE_TBL B,
    PS_GL_INT_DT_TBL C,
    PS_GL_INTERFACE E
  WHERE ( B.SETID = A.SETID
    AND B.ITEM_TYPE = A.ITEM_TYPE
    AND C.SETID = A.SETID
    AND C.ITEM_TYPE = A.ITEM_TYPE
    AND ( B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_TYPE_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.ITEM_TYPE = B_ED.ITEM_TYPE
          AND B_ED.EFFDT <= SYSDATE)
     AND A.SETID = 'SOCMP'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND B.EFF_STATUS = 'A'
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_GL_INT_DT_TBL C_ED
        WHERE C.SETID = C_ED.SETID
          AND C.ITEM_TYPE = C_ED.ITEM_TYPE
          AND C.STRM = C_ED.STRM
          AND C.SESSION_CODE = C_ED.SESSION_CODE
          AND C_ED.EFFDT <= SYSDATE)
     AND C.STRM = (SELECT MAX( D.STRM)
  FROM PS_GL_INT_DT_TBL D
  WHERE D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_GL_INT_DT_TBL D_ED
        WHERE D.SETID = D_ED.SETID
          AND D.ITEM_TYPE = D_ED.ITEM_TYPE
          AND D.STRM = D_ED.STRM
          AND D.SESSION_CODE = D_ED.SESSION_CODE
          AND D_ED.EFFDT <= SYSDATE)
     AND D.SETID = C.SETID
     AND D.ITEM_TYPE = C.ITEM_TYPE)
     AND A.SETID = E.SETID
     AND A.ITEM_TYPE = E.ITEM_TYPE
     AND E.EFFDT =
        (SELECT MAX(E_ED.EFFDT) FROM PS_GL_INTERFACE E_ED
        WHERE E.SETID = E_ED.SETID
          AND E.ITEM_TYPE = E_ED.ITEM_TYPE
          AND E.STRM = E_ED.STRM
          AND E.SESSION_CODE = E_ED.SESSION_CODE
          AND E_ED.EFFDT <= SYSDATE)
     AND E.DB_CR_IND = 'D'
     AND E.STRM = C.STRM )) ) Q2
ON
     Q1."1TEM_TYPE" = Q2."4ITEM_TYPE" LEFT OUTER
JOIN
    ( SELECT DISTINCT A.STRM AS "2STRM",
    A.ITEM_TYPE AS "2ITEM_TYPE",
    A.DISBURSEMENT_ID AS "2DISB_ID",
    A.OFFER_BALANCE AS "2OFF_BAL",
    A.ACCEPT_BALANCE AS "2ACC_BAL",
    A.DISBURSED_BALANCE AS "2DISB_BAL",
    B.DESCR AS "2ACAD__PLAN",
    A.EMPLID AS "2EMPLID",
    A.ACAD_CAREER AS "2ACAD_CAREER",
    A.AID_YEAR AS "2AID_YEAR",
    A.INSTITUTION AS "2INSTITUTION"
  FROM ((PS_STDNT_AWRD_DISB A LEFT OUTER
JOIN
     PS_STDNT_FA_TERM C
ON
     A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.AID_YEAR = C.AID_YEAR AND A.ACAD_CAREER = C.ACAD_CAREER AND C.STRM = A.STRM AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_FA_TERM C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.INSTITUTION = C_ED.INSTITUTION
          AND C.STRM = C_ED.STRM
          AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ =
        (SELECT MAX(C_ES.EFFSEQ) FROM PS_STDNT_FA_TERM C_ES
        WHERE C.EMPLID = C_ES.EMPLID
          AND C.INSTITUTION = C_ES.INSTITUTION
          AND C.STRM = C_ES.STRM
          AND C.EFFDT = C_ES.EFFDT) ) LEFT OUTER
JOIN
     PS_ACAD_PLAN_TB_VW B
ON
     C.INSTITUTION = B.INSTITUTION AND B.ACAD_PLAN = C.ACAD_PLAN )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.STRM = '2257'
     AND A.OFFER_BALANCE > 0) ) Q3
ON
     Q1."1EMPLID" = Q3."2EMPLID" AND  Q1."1ACAD_CAREER" = Q3."2ACAD_CAREER" AND  Q1."1INSTITUTION" = Q3."2INSTITUTION" AND  Q1."1AID_YEAR" = Q3."2AID_YEAR" AND  Q1."1TEM_TYPE" = Q3."2ITEM_TYPE" LEFT OUTER
JOIN
    ( SELECT DISTINCT A.STRM AS "3STRM",
    A.ITEM_TYPE AS "3ITEM_TYPE",
    A.DISBURSEMENT_ID AS "3DISB_ID",
    A.OFFER_BALANCE AS "3OFF_BAL",
    A.ACCEPT_BALANCE AS "3ACC_BAL",
    A.DISBURSED_BALANCE AS "3DISB_BAL",
    B.DESCR AS "3ACAD_PLAN",
    A.EMPLID AS "3EMPLID",
    A.ACAD_CAREER AS "3ACAD_CAREER",
    A.AID_YEAR AS "3AID_YEAR",
    A.INSTITUTION AS "3INSTITUTION"
  FROM ((PS_STDNT_AWRD_DISB A LEFT OUTER
JOIN
     PS_STDNT_FA_TERM C
ON
     A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.AID_YEAR = C.AID_YEAR AND A.ACAD_CAREER = C.ACAD_CAREER AND C.STRM = A.STRM AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_FA_TERM C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.INSTITUTION = C_ED.INSTITUTION
          AND C.STRM = C_ED.STRM
          AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ =
        (SELECT MAX(C_ES.EFFSEQ) FROM PS_STDNT_FA_TERM C_ES
        WHERE C.EMPLID = C_ES.EMPLID
          AND C.INSTITUTION = C_ES.INSTITUTION
          AND C.STRM = C_ES.STRM
          AND C.EFFDT = C_ES.EFFDT) ) LEFT OUTER
JOIN
     PS_ACAD_PLAN_TB_VW B
ON
     C.INSTITUTION = B.INSTITUTION AND B.ACAD_PLAN = C.ACAD_PLAN )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.STRM = '2263'
     AND A.OFFER_BALANCE > 0) ) Q4
ON
     Q1."1EMPLID" = Q4."3EMPLID" AND  Q1."1ACAD_CAREER" = Q4."3ACAD_CAREER" AND  Q1."1INSTITUTION" = Q4."3INSTITUTION" AND  Q1."1AID_YEAR" = Q4."3AID_YEAR" AND  Q1."1TEM_TYPE" = Q4."3ITEM_TYPE"
ORDER BY Q1."1NAME" ASC
Actions
Query Selected Collapsible section Query Selected
Query Fields      
       
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_1 Q1
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_4 Q2
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_2 Q3
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_3 Q4
Menu
 

Hello Susan,

Here is the out from the Q&A.  I tried to clean it up some from the CSV that was sent to me.

Hey Susan - I think in the end, we did not capture the individual questions from the Q&A, but I can check with Tesha about that.  You can review the PS Query / SQL library to see if there are resources there which might prove helpful.  That library can be found here - PS Query & SQL Library. 

Also, please feel free to post questions here - I know Dan and I will be happy to answer any questions we can, and there were several folks on the call who had experience with this tool.  

Take Care! 

I just watched the recording of the webinar...thanks for providing this!

You mentioned the Q&A from the session would be available... if it is posted on HEUG I haven't been able to find it.

Thanks again!

Susan

Hello all,

For who attended and have additional questions, comments, and/or insights to this webinar, please add them here.  I will get my scramble expression out there today.

I was able to avoid the duplicate rows by taking Disbursement ID out of the child queries and using an expression in the child queries to sum the disbursements per item type.

Thanks for the help!

Susan

It would be fine to sum the disbursements for each term. So far, I have tried two methods...

Method #1: I tried creating an expression for each of the term amount fields (Offered, Accepted, Disbursed) and using those in the Fields instead of the term amount fields, which resulted in just one row for the student's scholarship (yay!) but the amounts are double what they should be.

The expression looks like this (and I had to choose Aggregate Sum in the field properties):

I didn't save the SQL from Method 1, but I could recreate that and include it in another post if that would help.

Method #2: Instead of using the expressions in the Fields, I tried choosing Aggregate Sum in the field properties for each of the term amount fields, and again I got one row for the student's scholarship but the term amounts were doubled.

(I also tried taking the fields from FA Term for Academic Plan and Career out of the Fields, but the term amounts still doubled.)

Here is the SQL for Method 2:

Susan,

Does your Composite Query need to show each disbursement for a term or can you do a sum per term?

Thanks to your wonderful webinar, I was able to create a composite query for our scholarship coordinator that has one row for each scholarship awarded with both Fall disbursement and Spring disbursement data in one row...yay!

One issue I haven't figured out is what to do with scholarships that have more than one disbursement per term. The output of my current composite query is producing 4 rows instead of one...one row for the first Fall disbursement with the first Spring disbursement data, a second row for the first Fall disbursement with the second Spring disbursement data, a third row for the second Fall disbursement with the first Spring disbursement data, and a fourth row for the second Fall disbursement with the second Spring disbursement data.

Ideally, there would be a column in the output for each of those second disbursements...all on one row.

Here is the SQL:

SELECT DISTINCT Q1."1EMPLID",
    Q1."1NAME",
    Q1."1EMAIL_ADDR",
    Q1."1TEM_TYPE",
    Q2."4IT_DESCR",
    Q2."4FUND_CODE",
    Q2."4DEPTID",
    Q3."2STRM",
    Q3."2DISB_ID",
    Q3."2OFF_BAL",
    Q3."2ACC_BAL",
    Q3."2DISB_BAL",
    Q3."2ACAD__PLAN",
    Q3."2ACAD_CAREER",
    Q4."3STRM",
    Q4."3DISB_ID",
    Q4."3OFF_BAL",
    Q4."3ACC_BAL",
    Q4."3DISB_BAL",
    Q4."3ACAD_PLAN",
    Q4."3ACAD_CAREER"
FROM
  (SELECT DISTINCT A.EMPLID AS "1EMPLID",
    B.NAME AS "1NAME",
    C.EMAIL_ADDR AS "1EMAIL_ADDR",
    A.ITEM_TYPE AS "1TEM_TYPE",
    A.ACAD_CAREER AS "1ACAD_CAREER",
    A.INSTITUTION AS "1INSTITUTION",
    A.AID_YEAR AS "1AID_YEAR"
  FROM PS_STDNT_AWARDS A,
    (PS_SCC_PERDATA_QVW B LEFT OUTER
JOIN
     PS_SCC_EMAIL_QVW C
ON
     B.EMPLID = C.EMPLID AND C.E_ADDR_TYPE = 'OCMP' )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.EMPLID = B.EMPLID
     AND A.OFFER_AMOUNT > 0) ) Q1
JOIN
    ( SELECT B.DESCR AS "4IT_DESCR",
    E.FUND_CODE AS "4FUND_CODE",
    E.DEPTID AS "4DEPTID",
    B.SETID AS "4SETID",
    B.ITEM_TYPE AS "4ITEM_TYPE",
    B.EFFDT AS "4EFFDT",
    B.EFF_STATUS AS "4EFF_STATUS",
    C.STRM AS "4STRM",
    C.EFFDT AS "4GL_EFFDT",
    C.EFF_STATUS AS "4GL_EFF_STATUS"
  FROM PS_ITEM_TYPE_1_TBL A,
    PS_ITEM_TYPE_TBL B,
    PS_GL_INT_DT_TBL C,
    PS_GL_INTERFACE E
  WHERE ( B.SETID = A.SETID
    AND B.ITEM_TYPE = A.ITEM_TYPE
    AND C.SETID = A.SETID
    AND C.ITEM_TYPE = A.ITEM_TYPE
    AND ( B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_TYPE_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.ITEM_TYPE = B_ED.ITEM_TYPE
          AND B_ED.EFFDT <= SYSDATE)
     AND A.SETID = 'SOCMP'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND B.EFF_STATUS = 'A'
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_GL_INT_DT_TBL C_ED
        WHERE C.SETID = C_ED.SETID
          AND C.ITEM_TYPE = C_ED.ITEM_TYPE
          AND C.STRM = C_ED.STRM
          AND C.SESSION_CODE = C_ED.SESSION_CODE
          AND C_ED.EFFDT <= SYSDATE)
     AND C.STRM = (SELECT MAX( D.STRM)
  FROM PS_GL_INT_DT_TBL D
  WHERE D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_GL_INT_DT_TBL D_ED
        WHERE D.SETID = D_ED.SETID
          AND D.ITEM_TYPE = D_ED.ITEM_TYPE
          AND D.STRM = D_ED.STRM
          AND D.SESSION_CODE = D_ED.SESSION_CODE
          AND D_ED.EFFDT <= SYSDATE)
     AND D.SETID = C.SETID
     AND D.ITEM_TYPE = C.ITEM_TYPE)
     AND A.SETID = E.SETID
     AND A.ITEM_TYPE = E.ITEM_TYPE
     AND E.EFFDT =
        (SELECT MAX(E_ED.EFFDT) FROM PS_GL_INTERFACE E_ED
        WHERE E.SETID = E_ED.SETID
          AND E.ITEM_TYPE = E_ED.ITEM_TYPE
          AND E.STRM = E_ED.STRM
          AND E.SESSION_CODE = E_ED.SESSION_CODE
          AND E_ED.EFFDT <= SYSDATE)
     AND E.DB_CR_IND = 'D'
     AND E.STRM = C.STRM )) ) Q2
ON
     Q1."1TEM_TYPE" = Q2."4ITEM_TYPE" LEFT OUTER
JOIN
    ( SELECT DISTINCT A.STRM AS "2STRM",
    A.ITEM_TYPE AS "2ITEM_TYPE",
    A.DISBURSEMENT_ID AS "2DISB_ID",
    A.OFFER_BALANCE AS "2OFF_BAL",
    A.ACCEPT_BALANCE AS "2ACC_BAL",
    A.DISBURSED_BALANCE AS "2DISB_BAL",
    B.DESCR AS "2ACAD__PLAN",
    A.EMPLID AS "2EMPLID",
    A.ACAD_CAREER AS "2ACAD_CAREER",
    A.AID_YEAR AS "2AID_YEAR",
    A.INSTITUTION AS "2INSTITUTION"
  FROM ((PS_STDNT_AWRD_DISB A LEFT OUTER
JOIN
     PS_STDNT_FA_TERM C
ON
     A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.AID_YEAR = C.AID_YEAR AND A.ACAD_CAREER = C.ACAD_CAREER AND C.STRM = A.STRM AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_FA_TERM C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.INSTITUTION = C_ED.INSTITUTION
          AND C.STRM = C_ED.STRM
          AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ =
        (SELECT MAX(C_ES.EFFSEQ) FROM PS_STDNT_FA_TERM C_ES
        WHERE C.EMPLID = C_ES.EMPLID
          AND C.INSTITUTION = C_ES.INSTITUTION
          AND C.STRM = C_ES.STRM
          AND C.EFFDT = C_ES.EFFDT) ) LEFT OUTER
JOIN
     PS_ACAD_PLAN_TB_VW B
ON
     C.INSTITUTION = B.INSTITUTION AND B.ACAD_PLAN = C.ACAD_PLAN )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.STRM = '2257'
     AND A.OFFER_BALANCE > 0) ) Q3
ON
     Q1."1EMPLID" = Q3."2EMPLID" AND  Q1."1ACAD_CAREER" = Q3."2ACAD_CAREER" AND  Q1."1INSTITUTION" = Q3."2INSTITUTION" AND  Q1."1AID_YEAR" = Q3."2AID_YEAR" AND  Q1."1TEM_TYPE" = Q3."2ITEM_TYPE" LEFT OUTER
JOIN
    ( SELECT DISTINCT A.STRM AS "3STRM",
    A.ITEM_TYPE AS "3ITEM_TYPE",
    A.DISBURSEMENT_ID AS "3DISB_ID",
    A.OFFER_BALANCE AS "3OFF_BAL",
    A.ACCEPT_BALANCE AS "3ACC_BAL",
    A.DISBURSED_BALANCE AS "3DISB_BAL",
    B.DESCR AS "3ACAD_PLAN",
    A.EMPLID AS "3EMPLID",
    A.ACAD_CAREER AS "3ACAD_CAREER",
    A.AID_YEAR AS "3AID_YEAR",
    A.INSTITUTION AS "3INSTITUTION"
  FROM ((PS_STDNT_AWRD_DISB A LEFT OUTER
JOIN
     PS_STDNT_FA_TERM C
ON
     A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.AID_YEAR = C.AID_YEAR AND A.ACAD_CAREER = C.ACAD_CAREER AND C.STRM = A.STRM AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_FA_TERM C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.INSTITUTION = C_ED.INSTITUTION
          AND C.STRM = C_ED.STRM
          AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ =
        (SELECT MAX(C_ES.EFFSEQ) FROM PS_STDNT_FA_TERM C_ES
        WHERE C.EMPLID = C_ES.EMPLID
          AND C.INSTITUTION = C_ES.INSTITUTION
          AND C.STRM = C_ES.STRM
          AND C.EFFDT = C_ES.EFFDT) ) LEFT OUTER
JOIN
     PS_ACAD_PLAN_TB_VW B
ON
     C.INSTITUTION = B.INSTITUTION AND B.ACAD_PLAN = C.ACAD_PLAN )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.STRM = '2263'
     AND A.OFFER_BALANCE > 0) ) Q4
ON
     Q1."1EMPLID" = Q4."3EMPLID" AND  Q1."1ACAD_CAREER" = Q4."3ACAD_CAREER" AND  Q1."1INSTITUTION" = Q4."3INSTITUTION" AND  Q1."1AID_YEAR" = Q4."3AID_YEAR" AND  Q1."1TEM_TYPE" = Q4."3ITEM_TYPE"
ORDER BY Q1."1NAME" ASC
Actions
Query Selected Collapsible section Query Selected
Query Fields      
       
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_1 Q1
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_4 Q2
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_2 Q3
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_3 Q4
Menu
 

Hello Susan,

Here is the out from the Q&A.  I tried to clean it up some from the CSV that was sent to me.

Hey Susan - I think in the end, we did not capture the individual questions from the Q&A, but I can check with Tesha about that.  You can review the PS Query / SQL library to see if there are resources there which might prove helpful.  That library can be found here - PS Query & SQL Library. 

Also, please feel free to post questions here - I know Dan and I will be happy to answer any questions we can, and there were several folks on the call who had experience with this tool.  

Take Care! 

I just watched the recording of the webinar...thanks for providing this!

You mentioned the Q&A from the session would be available... if it is posted on HEUG I haven't been able to find it.

Thanks again!

Susan

Hello all,

For who attended and have additional questions, comments, and/or insights to this webinar, please add them here.  I will get my scramble expression out there today.

Susan,

I was about to message you today that the best bet is to take care of the multiplying values in the base queries rather than at the Composite Query level.  Seems you beat me to it! Kudos.  When joining to other tables that may have multiple rows per student, it can multiply those sums.

I too have an expression that @Jeffrie Brooks came up with to handle that.  I almost use it be default now.  I am attaching a small write up I have of it.

I was able to avoid the duplicate rows by taking Disbursement ID out of the child queries and using an expression in the child queries to sum the disbursements per item type.

Thanks for the help!

Susan

It would be fine to sum the disbursements for each term. So far, I have tried two methods...

Method #1: I tried creating an expression for each of the term amount fields (Offered, Accepted, Disbursed) and using those in the Fields instead of the term amount fields, which resulted in just one row for the student's scholarship (yay!) but the amounts are double what they should be.

The expression looks like this (and I had to choose Aggregate Sum in the field properties):

I didn't save the SQL from Method 1, but I could recreate that and include it in another post if that would help.

Method #2: Instead of using the expressions in the Fields, I tried choosing Aggregate Sum in the field properties for each of the term amount fields, and again I got one row for the student's scholarship but the term amounts were doubled.

(I also tried taking the fields from FA Term for Academic Plan and Career out of the Fields, but the term amounts still doubled.)

Here is the SQL for Method 2:

Susan,

Does your Composite Query need to show each disbursement for a term or can you do a sum per term?

Thanks to your wonderful webinar, I was able to create a composite query for our scholarship coordinator that has one row for each scholarship awarded with both Fall disbursement and Spring disbursement data in one row...yay!

One issue I haven't figured out is what to do with scholarships that have more than one disbursement per term. The output of my current composite query is producing 4 rows instead of one...one row for the first Fall disbursement with the first Spring disbursement data, a second row for the first Fall disbursement with the second Spring disbursement data, a third row for the second Fall disbursement with the first Spring disbursement data, and a fourth row for the second Fall disbursement with the second Spring disbursement data.

Ideally, there would be a column in the output for each of those second disbursements...all on one row.

Here is the SQL:

SELECT DISTINCT Q1."1EMPLID",
    Q1."1NAME",
    Q1."1EMAIL_ADDR",
    Q1."1TEM_TYPE",
    Q2."4IT_DESCR",
    Q2."4FUND_CODE",
    Q2."4DEPTID",
    Q3."2STRM",
    Q3."2DISB_ID",
    Q3."2OFF_BAL",
    Q3."2ACC_BAL",
    Q3."2DISB_BAL",
    Q3."2ACAD__PLAN",
    Q3."2ACAD_CAREER",
    Q4."3STRM",
    Q4."3DISB_ID",
    Q4."3OFF_BAL",
    Q4."3ACC_BAL",
    Q4."3DISB_BAL",
    Q4."3ACAD_PLAN",
    Q4."3ACAD_CAREER"
FROM
  (SELECT DISTINCT A.EMPLID AS "1EMPLID",
    B.NAME AS "1NAME",
    C.EMAIL_ADDR AS "1EMAIL_ADDR",
    A.ITEM_TYPE AS "1TEM_TYPE",
    A.ACAD_CAREER AS "1ACAD_CAREER",
    A.INSTITUTION AS "1INSTITUTION",
    A.AID_YEAR AS "1AID_YEAR"
  FROM PS_STDNT_AWARDS A,
    (PS_SCC_PERDATA_QVW B LEFT OUTER
JOIN
     PS_SCC_EMAIL_QVW C
ON
     B.EMPLID = C.EMPLID AND C.E_ADDR_TYPE = 'OCMP' )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.EMPLID = B.EMPLID
     AND A.OFFER_AMOUNT > 0) ) Q1
JOIN
    ( SELECT B.DESCR AS "4IT_DESCR",
    E.FUND_CODE AS "4FUND_CODE",
    E.DEPTID AS "4DEPTID",
    B.SETID AS "4SETID",
    B.ITEM_TYPE AS "4ITEM_TYPE",
    B.EFFDT AS "4EFFDT",
    B.EFF_STATUS AS "4EFF_STATUS",
    C.STRM AS "4STRM",
    C.EFFDT AS "4GL_EFFDT",
    C.EFF_STATUS AS "4GL_EFF_STATUS"
  FROM PS_ITEM_TYPE_1_TBL A,
    PS_ITEM_TYPE_TBL B,
    PS_GL_INT_DT_TBL C,
    PS_GL_INTERFACE E
  WHERE ( B.SETID = A.SETID
    AND B.ITEM_TYPE = A.ITEM_TYPE
    AND C.SETID = A.SETID
    AND C.ITEM_TYPE = A.ITEM_TYPE
    AND ( B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_TYPE_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.ITEM_TYPE = B_ED.ITEM_TYPE
          AND B_ED.EFFDT <= SYSDATE)
     AND A.SETID = 'SOCMP'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND B.EFF_STATUS = 'A'
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_GL_INT_DT_TBL C_ED
        WHERE C.SETID = C_ED.SETID
          AND C.ITEM_TYPE = C_ED.ITEM_TYPE
          AND C.STRM = C_ED.STRM
          AND C.SESSION_CODE = C_ED.SESSION_CODE
          AND C_ED.EFFDT <= SYSDATE)
     AND C.STRM = (SELECT MAX( D.STRM)
  FROM PS_GL_INT_DT_TBL D
  WHERE D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_GL_INT_DT_TBL D_ED
        WHERE D.SETID = D_ED.SETID
          AND D.ITEM_TYPE = D_ED.ITEM_TYPE
          AND D.STRM = D_ED.STRM
          AND D.SESSION_CODE = D_ED.SESSION_CODE
          AND D_ED.EFFDT <= SYSDATE)
     AND D.SETID = C.SETID
     AND D.ITEM_TYPE = C.ITEM_TYPE)
     AND A.SETID = E.SETID
     AND A.ITEM_TYPE = E.ITEM_TYPE
     AND E.EFFDT =
        (SELECT MAX(E_ED.EFFDT) FROM PS_GL_INTERFACE E_ED
        WHERE E.SETID = E_ED.SETID
          AND E.ITEM_TYPE = E_ED.ITEM_TYPE
          AND E.STRM = E_ED.STRM
          AND E.SESSION_CODE = E_ED.SESSION_CODE
          AND E_ED.EFFDT <= SYSDATE)
     AND E.DB_CR_IND = 'D'
     AND E.STRM = C.STRM )) ) Q2
ON
     Q1."1TEM_TYPE" = Q2."4ITEM_TYPE" LEFT OUTER
JOIN
    ( SELECT DISTINCT A.STRM AS "2STRM",
    A.ITEM_TYPE AS "2ITEM_TYPE",
    A.DISBURSEMENT_ID AS "2DISB_ID",
    A.OFFER_BALANCE AS "2OFF_BAL",
    A.ACCEPT_BALANCE AS "2ACC_BAL",
    A.DISBURSED_BALANCE AS "2DISB_BAL",
    B.DESCR AS "2ACAD__PLAN",
    A.EMPLID AS "2EMPLID",
    A.ACAD_CAREER AS "2ACAD_CAREER",
    A.AID_YEAR AS "2AID_YEAR",
    A.INSTITUTION AS "2INSTITUTION"
  FROM ((PS_STDNT_AWRD_DISB A LEFT OUTER
JOIN
     PS_STDNT_FA_TERM C
ON
     A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.AID_YEAR = C.AID_YEAR AND A.ACAD_CAREER = C.ACAD_CAREER AND C.STRM = A.STRM AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_FA_TERM C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.INSTITUTION = C_ED.INSTITUTION
          AND C.STRM = C_ED.STRM
          AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ =
        (SELECT MAX(C_ES.EFFSEQ) FROM PS_STDNT_FA_TERM C_ES
        WHERE C.EMPLID = C_ES.EMPLID
          AND C.INSTITUTION = C_ES.INSTITUTION
          AND C.STRM = C_ES.STRM
          AND C.EFFDT = C_ES.EFFDT) ) LEFT OUTER
JOIN
     PS_ACAD_PLAN_TB_VW B
ON
     C.INSTITUTION = B.INSTITUTION AND B.ACAD_PLAN = C.ACAD_PLAN )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.STRM = '2257'
     AND A.OFFER_BALANCE > 0) ) Q3
ON
     Q1."1EMPLID" = Q3."2EMPLID" AND  Q1."1ACAD_CAREER" = Q3."2ACAD_CAREER" AND  Q1."1INSTITUTION" = Q3."2INSTITUTION" AND  Q1."1AID_YEAR" = Q3."2AID_YEAR" AND  Q1."1TEM_TYPE" = Q3."2ITEM_TYPE" LEFT OUTER
JOIN
    ( SELECT DISTINCT A.STRM AS "3STRM",
    A.ITEM_TYPE AS "3ITEM_TYPE",
    A.DISBURSEMENT_ID AS "3DISB_ID",
    A.OFFER_BALANCE AS "3OFF_BAL",
    A.ACCEPT_BALANCE AS "3ACC_BAL",
    A.DISBURSED_BALANCE AS "3DISB_BAL",
    B.DESCR AS "3ACAD_PLAN",
    A.EMPLID AS "3EMPLID",
    A.ACAD_CAREER AS "3ACAD_CAREER",
    A.AID_YEAR AS "3AID_YEAR",
    A.INSTITUTION AS "3INSTITUTION"
  FROM ((PS_STDNT_AWRD_DISB A LEFT OUTER
JOIN
     PS_STDNT_FA_TERM C
ON
     A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.AID_YEAR = C.AID_YEAR AND A.ACAD_CAREER = C.ACAD_CAREER AND C.STRM = A.STRM AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_FA_TERM C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.INSTITUTION = C_ED.INSTITUTION
          AND C.STRM = C_ED.STRM
          AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ =
        (SELECT MAX(C_ES.EFFSEQ) FROM PS_STDNT_FA_TERM C_ES
        WHERE C.EMPLID = C_ES.EMPLID
          AND C.INSTITUTION = C_ES.INSTITUTION
          AND C.STRM = C_ES.STRM
          AND C.EFFDT = C_ES.EFFDT) ) LEFT OUTER
JOIN
     PS_ACAD_PLAN_TB_VW B
ON
     C.INSTITUTION = B.INSTITUTION AND B.ACAD_PLAN = C.ACAD_PLAN )
  WHERE ( A.INSTITUTION = 'SOCMP'
     AND A.AID_YEAR = '2026'
     AND ( A.ITEM_TYPE BETWEEN '800000000000' AND '800000099999'
     OR A.ITEM_TYPE IN ('840000004500','840000004501','840000005300'))
     AND A.STRM = '2263'
     AND A.OFFER_BALANCE > 0) ) Q4
ON
     Q1."1EMPLID" = Q4."3EMPLID" AND  Q1."1ACAD_CAREER" = Q4."3ACAD_CAREER" AND  Q1."1INSTITUTION" = Q4."3INSTITUTION" AND  Q1."1AID_YEAR" = Q4."3AID_YEAR" AND  Q1."1TEM_TYPE" = Q4."3ITEM_TYPE"
ORDER BY Q1."1NAME" ASC
Actions
Query Selected Collapsible section Query Selected
Query Fields      
       
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_1 Q1
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_4 Q2
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_2 Q3
Menu
 
Expand Query Name : SSU_FA_CPQ_SCHOLAR_ALL_3 Q4
Menu
 

Hello Susan,

Here is the out from the Q&A.  I tried to clean it up some from the CSV that was sent to me.

Hey Susan - I think in the end, we did not capture the individual questions from the Q&A, but I can check with Tesha about that.  You can review the PS Query / SQL library to see if there are resources there which might prove helpful.  That library can be found here - PS Query & SQL Library. 

Also, please feel free to post questions here - I know Dan and I will be happy to answer any questions we can, and there were several folks on the call who had experience with this tool.  

Take Care! 

I just watched the recording of the webinar...thanks for providing this!

You mentioned the Q&A from the session would be available... if it is posted on HEUG I haven't been able to find it.

Thanks again!

Susan

Hello all,

For who attended and have additional questions, comments, and/or insights to this webinar, please add them here.  I will get my scramble expression out there today.