#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    81
    Rep Power
    16

    Need help converting MSSQL View to mySQL


    Im pretty sure this is possible but i dont know how.

    I need to change these two views from MSSQL into views for mySQL.

    Can anyone help me please?


    CREATE VIEW [dbo].[AuditRecordView]
    AS
    SELECT [Date], [RecordType] =
    CASE [RecordType]
    WHEN '301' THEN 'Sign On'
    WHEN '302' THEN 'Sign Off'
    WHEN '303' THEN 'Join'
    WHEN '304' THEN 'Part'
    WHEN '305' THEN 'Nick Change'
    WHEN '306' THEN 'Signoff Error'
    END,
    [OriginatorNick], [OriginatorHostname], [Detail]
    FROM [dbo].[EntryExitEvents]
    UNION
    SELECT [Date], [RecordType] =
    CASE [RecordType]
    WHEN '401' THEN 'Kick'
    WHEN '402' THEN 'Ban'
    WHEN '403' THEN 'Unban'
    WHEN '404' THEN 'Kill'
    WHEN '405' THEN 'Server Ban'
    WHEN '406' THEN 'Server Unban'
    WHEN '407' THEN 'Quiet'
    WHEN '408' THEN 'UnQuiet'
    WHEN '409' THEN 'Server Ban Hit'
    END,
    [OriginatorNick], [OriginatorHostname], [Detail]
    FROM [dbo].[BanEvents]
    UNION
    SELECT [Date], [RecordType] =
    CASE [RecordType]
    WHEN '501' THEN 'Oper'
    WHEN '502' THEN 'Moderator'
    WHEN '503' THEN 'Die'
    WHEN '504' THEN 'Restart'
    WHEN '505' THEN 'Bad Nick Pass'
    WHEN '506' THEN 'Impersonate SVCAGENT'
    WHEN '507' THEN 'Mass Invite'
    WHEN '508' THEN 'Nick Add'
    WHEN '509' THEN 'Nick Drop'
    WHEN '510' THEN 'Nick Expire'
    WHEN '511' THEN 'Global Room Limit'
    WHEN '512' THEN 'Bad Oper Pass'
    WHEN '513' THEN 'Room Member ADD'
    WHEN '514' THEN 'Room Member DEL'
    WHEN '515' THEN 'Invite'
    WHEN '516' THEN 'Tour'
    WHEN '517' THEN 'Room ADD'
    WHEN '518' THEN 'Room DEL'
    WHEN '519' THEN 'Room Change'
    WHEN '520' THEN 'Nick Setting Change'
    ELSE 'Unknown'
    END,
    [OriginatorNick], [OriginatorHostname], [Detail]
    FROM [dbo].[SecurityEvents]



    GO
    SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON
    GO






    CREATE VIEW [dbo].[TranscriptView]
    AS
    SELECT ALL [RecordType], [Date], [OriginatorNick], [OriginatorHostname], [Room], CONVERT(VARCHAR(512),
    [MsgText]) AS [Detail]
    FROM [dbo].[RoomTranscripts]
    WHERE ([dbo].[RoomTranscripts].[RecordType] = 101)
    UNION
    SELECT ALL [RecordType], [Date], [OriginatorNick], [OriginatorHostname],
    [Detail] AS [Room], NULL AS [Detail]
    FROM [dbo].[EntryExitEvents]
    WHERE ([dbo].[EntryExitEvents].[RecordType] = 303) OR
    ([dbo].[EntryExitEvents].[RecordType] = 304)
    UNION
    SELECT ALL [RecordType], [Date], [OriginatorNick], NULL
    AS [OriginatorHostname], LEFT(Detail, charindex(' ', Detail) - 1)
    AS [Room], SUBSTRING(Detail, charindex(' ', Detail) + 1, 512)
    FROM [dbo].[SecurityEvents]
    WHERE ([dbo].[SecurityEvents].[RecordType] = 516)
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by seanincali
    Can anyone help me please?
    sure, several of us -- what's your question?

    oh, you just want us to do your work for you? sure, we can do that
    Code:
    CREATE VIEW dbo.AuditRecordView
    AS
    SELECT Date
         , CASE RecordType
                WHEN '301' THEN 'Sign On'
                WHEN '302' THEN 'Sign Off'
                WHEN '303' THEN 'Join'
                WHEN '304' THEN 'Part'
                WHEN '305' THEN 'Nick Change'
                WHEN '306' THEN 'Signoff Error'
            END AS RecordType
         , OriginatorNick
         , OriginatorHostname
         , Detail
      FROM dbo.EntryExitEvents
    UNION
    SELECT Date
         , CASE RecordType
                WHEN '401' THEN 'Kick'
                WHEN '402' THEN 'Ban'
                WHEN '403' THEN 'Unban'
                WHEN '404' THEN 'Kill'
                WHEN '405' THEN 'Server Ban'
                WHEN '406' THEN 'Server Unban'
                WHEN '407' THEN 'Quiet'
                WHEN '408' THEN 'UnQuiet'
                WHEN '409' THEN 'Server Ban Hit'
            END
         , OriginatorNick
         , OriginatorHostname
         , Detail
      FROM dbo.BanEvents
    UNION
    SELECT Date
         , CASE RecordType
                WHEN '501' THEN 'Oper'
                WHEN '502' THEN 'Moderator'
                WHEN '503' THEN 'Die'
                WHEN '504' THEN 'Restart'
                WHEN '505' THEN 'Bad Nick Pass'
                WHEN '506' THEN 'Impersonate SVCAGENT'
                WHEN '507' THEN 'Mass Invite'
                WHEN '508' THEN 'Nick Add'
                WHEN '509' THEN 'Nick Drop'
                WHEN '510' THEN 'Nick Expire'
                WHEN '511' THEN 'Global Room Limit'
                WHEN '512' THEN 'Bad Oper Pass'
                WHEN '513' THEN 'Room Member ADD'
                WHEN '514' THEN 'Room Member DEL'
                WHEN '515' THEN 'Invite'
                WHEN '516' THEN 'Tour'
                WHEN '517' THEN 'Room ADD'
                WHEN '518' THEN 'Room DEL'
                WHEN '519' THEN 'Room Change'
                WHEN '520' THEN 'Nick Setting Change'
                ELSE 'Unknown'
            END
         , OriginatorNick
         , OriginatorHostname
         , Detail
      FROM dbo.SecurityEventsCREATE VIEW dbo.AuditRecordView
    AS
    SELECT Date
         , CASE RecordType
                WHEN '301' THEN 'Sign On'
                WHEN '302' THEN 'Sign Off'
                WHEN '303' THEN 'Join'
                WHEN '304' THEN 'Part'
                WHEN '305' THEN 'Nick Change'
                WHEN '306' THEN 'Signoff Error'
            END AS RecordType
         , OriginatorNick
         , OriginatorHostname
         , Detail
      FROM dbo.EntryExitEvents
    UNION
    SELECT Date
         , CASE RecordType
                WHEN '401' THEN 'Kick'
                WHEN '402' THEN 'Ban'
                WHEN '403' THEN 'Unban'
                WHEN '404' THEN 'Kill'
                WHEN '405' THEN 'Server Ban'
                WHEN '406' THEN 'Server Unban'
                WHEN '407' THEN 'Quiet'
                WHEN '408' THEN 'UnQuiet'
                WHEN '409' THEN 'Server Ban Hit'
            END
         , OriginatorNick
         , OriginatorHostname
         , Detail
      FROM dbo.BanEvents
    UNION
    SELECT Date
         , CASE RecordType
                WHEN '501' THEN 'Oper'
                WHEN '502' THEN 'Moderator'
                WHEN '503' THEN 'Die'
                WHEN '504' THEN 'Restart'
                WHEN '505' THEN 'Bad Nick Pass'
                WHEN '506' THEN 'Impersonate SVCAGENT'
                WHEN '507' THEN 'Mass Invite'
                WHEN '508' THEN 'Nick Add'
                WHEN '509' THEN 'Nick Drop'
                WHEN '510' THEN 'Nick Expire'
                WHEN '511' THEN 'Global Room Limit'
                WHEN '512' THEN 'Bad Oper Pass'
                WHEN '513' THEN 'Room Member ADD'
                WHEN '514' THEN 'Room Member DEL'
                WHEN '515' THEN 'Invite'
                WHEN '516' THEN 'Tour'
                WHEN '517' THEN 'Room ADD'
                WHEN '518' THEN 'Room DEL'
                WHEN '519' THEN 'Room Change'
                WHEN '520' THEN 'Nick Setting Change'
                ELSE 'Unknown'
            END
         , OriginatorNick
         , OriginatorHostname
         , Detail
      FROM dbo.SecurityEventsCREATE VIEW dbo.AuditRecordView
    AS
    SELECT Date
         , CASE RecordType
                WHEN '301' THEN 'Sign On'
                WHEN '302' THEN 'Sign Off'
                WHEN '303' THEN 'Join'
                WHEN '304' THEN 'Part'
                WHEN '305' THEN 'Nick Change'
                WHEN '306' THEN 'Signoff Error'
            END AS RecordType
         , OriginatorNick
         , OriginatorHostname
         , Detail
      FROM dbo.EntryExitEvents
    UNION
    SELECT Date
         , CASE RecordType
                WHEN '401' THEN 'Kick'
                WHEN '402' THEN 'Ban'
                WHEN '403' THEN 'Unban'
                WHEN '404' THEN 'Kill'
                WHEN '405' THEN 'Server Ban'
                WHEN '406' THEN 'Server Unban'
                WHEN '407' THEN 'Quiet'
                WHEN '408' THEN 'UnQuiet'
                WHEN '409' THEN 'Server Ban Hit'
            END
         , OriginatorNick
         , OriginatorHostname
         , Detail
      FROM dbo.BanEvents
    UNION
    SELECT Date
         , CASE RecordType
                WHEN '501' THEN 'Oper'
                WHEN '502' THEN 'Moderator'
                WHEN '503' THEN 'Die'
                WHEN '504' THEN 'Restart'
                WHEN '505' THEN 'Bad Nick Pass'
                WHEN '506' THEN 'Impersonate SVCAGENT'
                WHEN '507' THEN 'Mass Invite'
                WHEN '508' THEN 'Nick Add'
                WHEN '509' THEN 'Nick Drop'
                WHEN '510' THEN 'Nick Expire'
                WHEN '511' THEN 'Global Room Limit'
                WHEN '512' THEN 'Bad Oper Pass'
                WHEN '513' THEN 'Room Member ADD'
                WHEN '514' THEN 'Room Member DEL'
                WHEN '515' THEN 'Invite'
                WHEN '516' THEN 'Tour'
                WHEN '517' THEN 'Room ADD'
                WHEN '518' THEN 'Room DEL'
                WHEN '519' THEN 'Room Change'
                WHEN '520' THEN 'Nick Setting Change'
                ELSE 'Unknown'
            END
         , OriginatorNick
         , OriginatorHostname
         , Detail
      FROM dbo.SecurityEventsCREATE VIEW dbo.AuditRecordView
    AS
    SELECT Date
         , CASE RecordType
                WHEN '301' THEN 'Sign On'
                WHEN '302' THEN 'Sign Off'
                WHEN '303' THEN 'Join'
                WHEN '304' THEN 'Part'
                WHEN '305' THEN 'Nick Change'
                WHEN '306' THEN 'Signoff Error'
            END AS RecordType
         , OriginatorNick
         , OriginatorHostname
         , Detail
      FROM dbo.EntryExitEvents
    UNION
    SELECT Date
         , CASE RecordType
                WHEN '401' THEN 'Kick'
                WHEN '402' THEN 'Ban'
                WHEN '403' THEN 'Unban'
                WHEN '404' THEN 'Kill'
                WHEN '405' THEN 'Server Ban'
                WHEN '406' THEN 'Server Unban'
                WHEN '407' THEN 'Quiet'
                WHEN '408' THEN 'UnQuiet'
                WHEN '409' THEN 'Server Ban Hit'
            END
         , OriginatorNick
         , OriginatorHostname
         , Detail
      FROM dbo.BanEvents
    UNION
    SELECT Date
         , CASE RecordType
                WHEN '501' THEN 'Oper'
                WHEN '502' THEN 'Moderator'
                WHEN '503' THEN 'Die'
                WHEN '504' THEN 'Restart'
                WHEN '505' THEN 'Bad Nick Pass'
                WHEN '506' THEN 'Impersonate SVCAGENT'
                WHEN '507' THEN 'Mass Invite'
                WHEN '508' THEN 'Nick Add'
                WHEN '509' THEN 'Nick Drop'
                WHEN '510' THEN 'Nick Expire'
                WHEN '511' THEN 'Global Room Limit'
                WHEN '512' THEN 'Bad Oper Pass'
                WHEN '513' THEN 'Room Member ADD'
                WHEN '514' THEN 'Room Member DEL'
                WHEN '515' THEN 'Invite'
                WHEN '516' THEN 'Tour'
                WHEN '517' THEN 'Room ADD'
                WHEN '518' THEN 'Room DEL'
                WHEN '519' THEN 'Room Change'
                WHEN '520' THEN 'Nick Setting Change'
                ELSE 'Unknown'
            END
         , OriginatorNick
         , OriginatorHostname
         , Detail
      FROM dbo.SecurityEvents
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    81
    Rep Power
    16
    no lol, i just need to know how to change the variables i guess to be for mysql instead.

    If you wanted to do the whole thing i could donate for your time. But no, i would rather learn how.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    81
    Rep Power
    16
    jackass

    Comments on this post

    • Guelphdad disagrees
  10. #6
  11. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,142
    Rep Power
    1321
    Of course this person you refer to modified your view for you and posted that didn't they?

    Please look in the mirror before posting.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    81
    Rep Power
    16
    hockey face

    not sure if that will really work. ill try it,. but i dont think mysql accepts .dbo

    Comments on this post

    • pabloj disagrees : You got a lot to learn ...
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by seanincali
    ...but i dont think mysql accepts .dbo
    sure it does, but you gots to create it first
    Code:
    CREATE DATABASE dbo;
    
    USE dbo;
    
    CREATE TABLE foo 
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT 
    , bar VARCHAR(9) 
    );
    
    INSERT INTO foo ( bar ) 
    VALUES ('curly') 
         , ('larry')
         , ('moe')
    ;
    
    SELECT * FROM dbo.foo;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    81
    Rep Power
    16
    im using an existing database for this view

    the database for this is called chatspace.

    isnt dbo a type of database from mssql? sorry as you can see im not that smart on databases yet

    And sorry, i thought your variables comment was making fun of me i didnt understand you are smart and dont need them.

    Thanks
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    actually, in ms sql server, dbo is an owner

    if you already have a database, then you wouldn't want to create one called dbo, which i did only to show that the syntax of your existing queries did not need to be changed just to make it run

    so that's one more thing you'll have to do when converting your queries, strip off the dbo owner
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo