6 Replies Latest reply on Oct 12, 2015 7:02 AM by beverly

    How to convert PeopleSoft (Oracle) SQL to FileMaker 13 calculation or script

    kaewansj

      I am working with our Technology Services in trying to figure out how to run criteria that will say Fall 2015 instead of the TERM CODE which is 1151.

       

      The script that my colleague from Technology Services created is below:

       

      SELECT A.EMPLID, A.INSTITUTION, A.STRM

        FROM PS_RL_STDNT_ROOM A

       

      WHERE

       

      ( A.EFFDT =

              (SELECT MAX(A_ED.EFFDT) FROM PS_RL_STDNT_ROOM A_ED

              WHERE A.EMPLID = A_ED.EMPLID

                AND A.INSTITUTION = A_ED.INSTITUTION

                AND A.ACAD_CAREER = A_ED.ACAD_CAREER

                AND A.STRM = A_ED.STRM

                AND A.SESSION_CODE = A_ED.SESSION_CODE

                AND A_ED.EFFDT <= SYSDATE)

          AND A.EFFSEQ =

              (SELECT MAX(A_ES.EFFSEQ) FROM PS_RL_STDNT_ROOM A_ES

              WHERE A.EMPLID = A_ES.EMPLID

                AND A.INSTITUTION = A_ES.INSTITUTION

                AND A.ACAD_CAREER = A_ES.ACAD_CAREER

                AND A.STRM = A_ES.STRM

                AND A.SESSION_CODE = A_ES.SESSION_CODE

                AND A.EFFDT = A_ES.EFFDT) )


      I hope someone will be able to explain in simple steps on how to make that happen.


      Much appreciated,


      Karl

        • 1. Re: How to convert PeopleSoft (Oracle) SQL to FileMaker 13 calculation or script
          beverly

          There is no "conversion" as the TERM CODE is likely an "id" type of column/field. Is there another 'term' field/column which has text?

           

          I do an import that is similar and there is a term description, which is text and can be parsed (converted) to a term/year (in any format you wish).

           

           

          beverly

          • 2. Re: How to convert PeopleSoft (Oracle) SQL to FileMaker 13 calculation or script
            sstewart

            I also work at a university that utilizes PS and although I don't know your specific structure, I am certain there is a separate PS table that is just term translations.    In the table is something like: term_code, term_long_description, term_short_description (like Fall 2015), term_start_date, term_end_date, etc.    You would then just create a join on term_code.

            Also, here at Cal Poly, we utilize "warehouse" tables which makes it even easier to write SQL code.   It takes the effective date and effective sequence out of the equation.   It's tables and views that are already joined.    Check to see if you have access to warehouse tables.

            • 3. Re: How to convert PeopleSoft (Oracle) SQL to FileMaker 13 calculation or script
              kaewansj

              Stewart and Beverly,

               

              I linked the description to TERM CODE in relationships.

               

              Now it is showing Spring 2016 in lieu of 1151. It is supposed to be Fall 2015.

               

              Is it an issue in the PS data or?

               

              Everything else is fine.

               

              Karl

              • 4. Re: How to convert PeopleSoft (Oracle) SQL to FileMaker 13 calculation or script
                sstewart

                Hi Karl,

                I'm not sure I understand your question.   Did you find a PS TERM file that contains term information?   Did you create a new table in FileMaker that contains TERM CODE and you defined a description field?

                 

                I don't think this is a FileMaker question any longer as much as a question about what's in your TERM table.   Maybe wherever you are pulling "Spring 2016" from is a field for "next quarter" instead of "current quarter."     Have you looked at the data in that file?

                • 5. Re: How to convert PeopleSoft (Oracle) SQL to FileMaker 13 calculation or script
                  kaewansj

                  In order to clear up the confusion, let me state out where we got stumped when using FileMaker Pro.  I am able to get the description for STRM in connection with using DESCR  e.g. 1151  - Fall 2015  It is important to have the effective date included so we could get correct results.

                  Our actual SQL query at Peoplesoft shows as follows:

                   

                  SELECT A.EMPLID, A.STRM, B.DESCR
                    FROM PS_RL_STDNT_ROOM A, PS_TERM_TBL B
                    WHERE ( A.EFFDT =
                          (SELECT MAX(A_ED.EFFDT) FROM PS_RL_STDNT_ROOM A_ED
                          WHERE A.EMPLID = A_ED.EMPLID
                            AND A.INSTITUTION = A_ED.INSTITUTION
                            AND A.ACAD_CAREER = A_ED.ACAD_CAREER
                            AND A.STRM = A_ED.STRM
                            AND A.SESSION_CODE = A_ED.SESSION_CODE
                            AND A_ED.EFFDT <= SYSDATE)
                      AND A.EFFSEQ =
                          (SELECT MAX(A_ES.EFFSEQ) FROM PS_RL_STDNT_ROOM A_ES
                          WHERE A.EMPLID = A_ES.EMPLID
                            AND A.INSTITUTION = A_ES.INSTITUTION
                            AND A.ACAD_CAREER = A_ES.ACAD_CAREER
                            AND A.STRM = A_ES.STRM
                            AND A.SESSION_CODE = A_ES.SESSION_CODE
                            AND A.EFFDT = A_ES.EFFDT)
                       AND A.INSTITUTION = B.INSTITUTION
                       AND A.ACAD_CAREER = B.ACAD_CAREER
                       AND A.STRM = B.STRM
                       AND A.SESSION_CODE = B.SESSION_CODE
                       AND A.STRM = '1151' )

                   

                   

                  I helped Karl adding the criteria between two tables ( PS_RL_ROOM and PS_TERM_TBL) in his FileMaker project  for those:

                   

                       AND A.INSTITUTION = B.INSTITUTION
                       AND A.ACAD_CAREER = B.ACAD_CAREER
                       AND A.STRM = B.STRM
                       AND A.SESSION_CODE = B.SESSION_CODE
                       AND A.STRM = '1151'

                  This is where I got stumped on this how to add these as follows in FileMaker:

                  ( A.EFFDT =
                          (SELECT MAX(A_ED.EFFDT) FROM PS_RL_STDNT_ROOM A_ED
                          WHERE A.EMPLID = A_ED.EMPLID
                            AND A.INSTITUTION = A_ED.INSTITUTION
                            AND A.ACAD_CAREER = A_ED.ACAD_CAREER
                            AND A.STRM = A_ED.STRM
                            AND A.SESSION_CODE = A_ED.SESSION_CODE
                            AND A_ED.EFFDT <= SYSDATE)
                      AND A.EFFSEQ =
                          (SELECT MAX(A_ES.EFFSEQ) FROM PS_RL_STDNT_ROOM A_ES
                          WHERE A.EMPLID = A_ES.EMPLID
                            AND A.INSTITUTION = A_ES.INSTITUTION
                            AND A.ACAD_CAREER = A_ES.ACAD_CAREER
                            AND A.STRM = A_ES.STRM
                            AND A.SESSION_CODE = A_ES.SESSION_CODE
                            AND A.EFFDT = A_ES.EFFDT)

                  • 6. Re: How to convert PeopleSoft (Oracle) SQL to FileMaker 13 calculation or script
                    beverly
                    DESCR  e.g. 1151  - Fall 2015

                     

                    yes! the DESCR is what I use. But since I want F15 or 2015F or other "text", I will Parse the result in the final and change to my client's preference (shown in the field). Using the text functions in FM, it's possible to do wonderous things.! Since there may be other fields needing parsing, I'll import into a temporary table, make corrections and then push the corrections to the final table.