[acs] SERCO - CMIS - Extracting events from SQL Database

[dawsonz]

Hello

 

I'm currently trying to take out data from the CMIS database as I want to be able to collate a gridview with all events.

 

I've looked into the following tables:

 

CMIS.dbo.APPAPPEVENT

 

The above table stores the particular event with 'AppEventId'.

 

The 'AppEventId' is then stored in CMIS.dbo.APPAPPFIELDS - this table holds one line per value. So for example a single event in APPAPPFIELDS would look like the following:

 

[TABLE=class: grid, width: 500, align: center]

[TR]

[TD=align: center]SetId

[/TD]

[TD=align: center]AppCode

[/TD]

[TD=align: center]AppEventId

[/TD]

[TD=align: center]EventId

[/TD]

[TD=align: center]FieldId

[/TD]

[TD=align: center]ValueData

[/TD]

[/TR]

[TR]

[TD=align: center]2012/2013

[/TD]

[TD=align: center]1000

[/TD]

[TD=align: center]361616

[/TD]

[TD=align: center]16

[/TD]

[TD=align: center]1

[/TD]

[TD=align: center]UNI

[/TD]

[/TR]

[TR]

[TD=align: center]2012/2013

[/TD]

[TD=align: center]1000

[/TD]

[TD=align: center]361616

[/TD]

[TD=align: center]16

[/TD]

[TD=align: center]2

[/TD]

[TD=align: center]Isolation

[/TD]

[/TR]

[TR]

[TD=align: center]2012/2013

[/TD]

[TD=align: center]1000

[/TD]

[TD=align: center]361616

[/TD]

[TD=align: center]16

[/TD]

[TD=align: center]3

[/TD]

[TD=align: center]DN

[/TD]

[/TR]

[/TABLE]

 

Has anyone had any luck retrieving the above information CMIS in a easy to read format such as:

 

[TABLE=class: grid, width: 500, align: center]

[TR]

[TD=align: center]SetId

[/TD]

[TD=align: center]AppCode

[/TD]

[TD=align: center]AppEventId

[/TD]

[TD=align: center]EventId

[/TD]

[TD=align: center]FieldIf

[/TD]

[TD=align: center]ValueData1

[/TD]

[TD=align: center]ValueData2

[/TD]

[TD=align: center]ValueData3

[/TD]

[/TR]

[TR]

[TD=align: center]2012/2013

[/TD]

[TD=align: center]1000

[/TD]

[TD=align: center]361616

[/TD]

[TD=align: center]16

[/TD]

[TD=align: center]1

[/TD]

[TD=align: center]UNI

[/TD]

[TD=align: center]Isolation

[/TD]

[TD=align: center]DN

[/TD]

[/TR]

[/TABLE]

 

Any help greatly appreciated.

[limbo]

Unless you want to write some code, the easiest way to do this is with a cross tab query using MSAccess as the front end.

 

the SQL would be something like:-

 

TRANSFORM First(APPAPPFIELDS.ValueData) AS FirstOfValueData

SELECT APPAPPFIELDS.SetId, APPAPPEVENT.AppCode, APPAPPEVENT.AppEventId, APPAPPEVENT.EventId

FROM APPAPPEVENT INNER JOIN APPAPPFIELDS ON (APPAPPEVENT.AppCode = APPAPPFIELDS.AppCode) AND (APPAPPFIELDS.AppEventId = APPAPPEVENT.AppEventId) AND (APPAPPEVENT.SetId = APPAPPFIELDS.SetId)

WHERE (((APPAPPFIELDS.SetId)='2012/2013'))

GROUP BY APPAPPFIELDS.SetId, APPAPPEVENT.AppCode, APPAPPEVENT.AppEventId, APPAPPEVENT.EventId

PIVOT "ValueData" & [FieldId];

 

This would not output the FieldID field too, but then not sure you need it in this instance.

[dawsonz]

Unless you want to write some code, the easiest way to do this is with a cross tab query using MSAccess as the front end.

 

the SQL would be something like:-

 

TRANSFORM First(APPAPPFIELDS.ValueData) AS FirstOfValueData

SELECT APPAPPFIELDS.SetId, APPAPPEVENT.AppCode, APPAPPEVENT.AppEventId, APPAPPEVENT.EventId

FROM APPAPPEVENT INNER JOIN APPAPPFIELDS ON (APPAPPEVENT.AppCode = APPAPPFIELDS.AppCode) AND (APPAPPFIELDS.AppEventId = APPAPPEVENT.AppEventId) AND (APPAPPEVENT.SetId = APPAPPFIELDS.SetId)

WHERE (((APPAPPFIELDS.SetId)='2012/2013'))

GROUP BY APPAPPFIELDS.SetId, APPAPPEVENT.AppCode, APPAPPEVENT.AppEventId, APPAPPEVENT.EventId

PIVOT "ValueData" & [FieldId];

 

This would not output the FieldID field too, but then not sure you need it in this instance.

 

Hi Limbo

 

Thanks for your response - I have finally got something working but have taken a different approach.

 

The CMIS database is such a headache.

[limbo]

What approach did you take - could be I am missing a trick?

[dawsonz]

What approach did you take - could be I am missing a trick?

 

Yours would work, however I created a query like so:

 

SELECT SetId, AppCode, AppEventId, EventId, MAX(Code) AS Code, MAX(Outcome) AS Outcome, MAX(LecturerId) AS LecturerId, MAX(Period)

AS Period, MAX(Subject) AS Subject

FROM (SELECT SetId, AppCode, AppEventId, EventId, CASE WHEN FieldId = 1 THEN ValueData END AS Code,

CASE WHEN FieldId = 2 THEN ValueData END AS Outcome, CASE WHEN FieldId = 3 THEN ValueData END AS LecturerId,

CASE WHEN FieldId = 7 THEN ValueData END AS Period, CASE WHEN FieldId = 8 THEN ValueData END AS Subject

FROM dbo.APPAPPFIELDS AS APPAPPFIELDS_1

WHERE (SetId = '2012/2013') AND (EventId = '16')) AS APPAPPFIELDS

GROUP BY SetId, AppCode, AppEventId, EventId

ORDER BY Outcome, AppCode

[SkywOrca]

A bit complicated, but this basically is a generic solution to pivoting the data from the APPAPPFIELDS table:

DECLARE @cols VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)
SELECT @cols = COALESCE @cols + ', ', '') + QUOTENAME(ef.Mnemonic) FROM APPAPPFIELDS af INNER JOIN APPEVFIELDS ef ON af.SetId = ef.SetId AND af.RecType = ef.RecType AND af.EventId = ef.EventId AND af.FieldId = ef.FieldId AND ef.LineNum = 1 GROUP BY ef.Mnemonic ORDER BY ef.Mnemonic
SET @sql = 'SELECT * FROM
(SELECT af.SetId, af.RecType, af.AppCode, af.AppEventId, af.EventId, ef.Mnemonic, af.ValueData FROM APPAPPFIELDS af INNER JOIN APPEVFIELDS ef ON af.SetId = ef.SetId AND af.RecType = ef.RecType AND af.EventId = ef.EventId AND af.FieldId = ef.FieldId AND ef.LineNum = 1) AS SourceTable
PIVOT (MAX(ValueData) FOR Mnemonic IN (' + @cols + ')) AS PivotTable'
EXEC (@sql)

I should note that whilst I've tested this against a sample database I can't guarantee that it won't eat your hamster when you run it against your database, as such you should make sure you have suitable backups etc. before you play with it. As it is only really a slightly complicated select statement, however, I wouldn't anticipate anything really nasty happening, but as it works with an EXEC command it's always possible that some really bizarre stuff in your event specs could cause some SQL injection, which would be a bad thing... Testing on a non-live setup would be advisable.