Pivot in FM 10.2.1?

[roddawg]

I have a student list of grades for the same class over several marking periods.  I want to pivot those in a query subject to create 1 line per Course.  I have SQL that does this in SSMS, however when I cut and past that over to a data Query subject. I get an error:RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 14: Syntax error near "pivot".  Does FM support the pivot function?

[roddawg]

Code Select Expand

select student_id
,school_year
,building
,section_key
,course_status
,COURSE
        ,COURSE_SECTION
        ,DESCRIPTION
,M1
,M2
,M3
,M4
,M5
,M6
,DEPARTMENT
  from (
              select mk.student_id
            ,mk.section_key
            ,ms.DESCRIPTION
            ,ms.SCHOOL_YEAR
             ,crs.COURSE_STATUS
            ,ms.COURSE
           ,ms.COURSE_SECTION
           ,ms.BUILDING
           ,MARKING_PERIOD
           ,MARK_VALUE
          ,ms.department
      from MR_STU_MARKS mk
      inner join schd_ms ms on ms.SECTION_KEY=mk.SECTION_KEY
      inner join schd_stu_course crs on mk.STUDENT_ID=crs.STUDENT_ID and mk.SECTION_KEY=crs.SECTION_KEY
      inner join reg r on mk.STUDENT_ID=r.STUDENT_ID
      where MARK_TYPE in ('6wk','SEM', 'FIN')
      and ms.DEPARTMENT in ('1','3','4','5')   and r.CURRENT_STATUS='a' and SCHOOL_YEAR=(select school_year from [Live].[dbo].[REG_DISTRICT]) and crs.COURSE_STATUS <> 'd'
      ) as m
      pivot(max(mark_value) for marking_period in (M1,M2,M3,M4,M5,M6)) as pvt
 


[CognosPaul]

In general it's a bad idea to use FM like this. Model the tables correctly, then the report developer can use a crosstab to pivot the output.


On the other hand, if you want to persist in this, you can try wrapping the SQL in braces:
{select whatever from whatever}
or setting the SQL to passthrough.

[roddawg]

The problem is we really want this data as a single row and not to have to utilize a crosstab each time we would like to use it.  Would we be better off having a stored procedure bring the data in this way and model accordingly? Or, would bringing the table in, then creating a business layer query which would aggregate the information as we want it be a better solution? We get this data from a third party and don't have the ability to change the native structure.

[CognosPaul]

Have you tried setting the SQL to passthrough, or using the braces?

[roddawg]

Yes I did...I wasn't aware that putting it in brackets utilized a pass through connection type.  Thank you.