+ Post New Thread
Results 1 to 6 of 6
MIS Systems Thread, SERCO - CMIS - Extracting events from SQL Database in Technical; Hello I'm currently trying to take out data from the CMIS database as I want to be able to collate ...
  1. #1

    Join Date
    Feb 2013
    Posts
    6
    Thank Post
    1
    Thanked 2 Times in 1 Post
    Rep Power
    0

    SERCO - CMIS - Extracting events from SQL Database

    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:

    SetId
    AppCode
    AppEventId
    EventId
    FieldId
    ValueData
    2012/2013 1000 361616 16 1 UNI
    2012/2013 1000 361616 16 2 Isolation
    2012/2013 1000 361616 16 3 DN

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

    SetId
    AppCode
    AppEventId
    EventId
    FieldIf
    ValueData1
    ValueData2
    ValueData3
    2012/2013 1000 361616 16 1 UNI Isolation DN

    Any help greatly appreciated.

  2. #2
    limbo's Avatar
    Join Date
    Aug 2005
    Location
    Birmingham
    Posts
    460
    Thank Post
    2
    Thanked 41 Times in 36 Posts
    Rep Power
    24
    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.

  3. Thanks to limbo from:

    dawsonz (19th April 2013)

  4. #3

    Join Date
    Feb 2013
    Posts
    6
    Thank Post
    1
    Thanked 2 Times in 1 Post
    Rep Power
    0
    Quote Originally Posted by limbo View Post
    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.

  5. #4
    limbo's Avatar
    Join Date
    Aug 2005
    Location
    Birmingham
    Posts
    460
    Thank Post
    2
    Thanked 41 Times in 36 Posts
    Rep Power
    24
    What approach did you take - could be I am missing a trick?

  6. #5

    Join Date
    Feb 2013
    Posts
    6
    Thank Post
    1
    Thanked 2 Times in 1 Post
    Rep Power
    0
    Quote Originally Posted by limbo View Post
    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

  7. #6
    SkywOrca's Avatar
    Join Date
    Sep 2008
    Posts
    70
    Thank Post
    0
    Thanked 14 Times in 13 Posts
    Rep Power
    47
    A bit complicated, but this basically is a generic solution to pivoting the data from the APPAPPFIELDS table:
    Code:
    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.

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 11
    Last Post: 20th August 2012, 09:59 AM
  2. Moving an SQL Database from Windows 2003 - 2008
    By penfold in forum Windows Server 2008 R2
    Replies: 1
    Last Post: 30th May 2012, 12:53 PM
  3. Replies: 21
    Last Post: 18th January 2011, 12:20 PM
  4. PHP: Extracting data from a SQL generated array
    By BarryWAaMC in forum Coding
    Replies: 15
    Last Post: 27th March 2010, 07:27 PM
  5. Replies: 2
    Last Post: 28th March 2006, 12:15 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •