SQL 'ONCREATE' command

Question

I am using DeZign for Databased v6 to design my sql database.

It has worked perfectly, up until this current view SQL code:

CREATE VIEW %viewname%
AS
SELECT     dbo.tblSiteChallengeMember.id, tblSiteMemberDetail_2.memberNickname AS mediator, dbo.tblSiteGame.name AS gameName,
                      tblSiteMemberDetail_1.memberNickname AS teamMemberName, dbo.tblSiteChallengeMemberTeam.accepted, dbo.tblSiteChallengeMemberTeam.teamNumber,
                      dbo.tblSiteChallengeMember.matchDate, dbo.tblSiteChallengeMember.dateAdded, dbo.tblSiteChallengeMember.completed,
                      dbo.tblSiteMemberDetail.memberNickname AS creatorName, dbo.tblSiteChallengeMediator.refferalCount,
                      dbo.tblSiteChallengeMediator.dateAdded AS mediatorJoinDate
FROM         dbo.tblSiteChallengeMemberWin RIGHT OUTER JOIN
                      dbo.tblSiteChallengeMember INNER JOIN
                      dbo.tblSiteMemberDetail ON dbo.tblSiteChallengeMember.creatorId = dbo.tblSiteMemberDetail.id ON
                      dbo.tblSiteChallengeMemberWin.challengeId = dbo.tblSiteChallengeMember.id LEFT OUTER JOIN
                      dbo.tblSiteMemberDetail AS tblSiteMemberDetail_1 INNER JOIN
                      dbo.tblSiteChallengeMemberTeam ON tblSiteMemberDetail_1.id = dbo.tblSiteChallengeMemberTeam.memberId ON
                      dbo.tblSiteChallengeMember.id = dbo.tblSiteChallengeMemberTeam.challengeId RIGHT OUTER JOIN
                      dbo.tblSiteGame ON dbo.tblSiteChallengeMember.gameId = dbo.tblSiteGame.id LEFT OUTER JOIN
                      dbo.tblSiteMemberDetail AS tblSiteMemberDetail_2 INNER JOIN
                      dbo.tblSiteChallengeMediator ON tblSiteMemberDetail_2.id = dbo.tblSiteChallengeMediator.memberId ONCREATE VIEW %viewname%
AS
SELECT     dbo.tblSiteChallengeMember.id, tblSiteMemberDetail_2.memberNickname AS mediator, dbo.tblSiteGame.name AS gameName,
                      tblSiteMemberDetail_1.memberNickname AS teamMemberName, dbo.tblSiteChallengeMemberTeam.accepted, dbo.tblSiteChallengeMemberTeam.teamNumber,
                      dbo.tblSiteChallengeMember.matchDate, dbo.tblSiteChallengeMember.dateAdded, dbo.tblSiteChallengeMember.completed,
                      dbo.tblSiteMemberDetail.memberNickname AS creatorName, dbo.tblSiteChallengeMediator.refferalCount,
                      dbo.tblSiteChallengeMediator.dateAdded AS mediatorJoinDate
FROM         dbo.tblSiteChallengeMemberWin RIGHT OUTER JOIN
                      dbo.tblSiteChallengeMember INNER JOIN
                      dbo.tblSiteMemberDetail ON dbo.tblSiteChallengeMember.creatorId = dbo.tblSiteMemberDetail.id ON
                      dbo.tblSiteChallengeMemberWin.challengeId = dbo.tblSiteChallengeMember.id LEFT OUTER JOIN
                      dbo.tblSiteMemberDetail AS tblSiteMemberDetail_1 INNER JOIN
                      dbo.tblSiteChallengeMemberTeam ON tblSiteMemberDetail_1.id = dbo.tblSiteChallengeMemberTeam.memberId ON
                      dbo.tblSiteChallengeMember.id = dbo.tblSiteChallengeMemberTeam.challengeId RIGHT OUTER JOIN
                      dbo.tblSiteGame ON dbo.tblSiteChallengeMember.gameId = dbo.tblSiteGame.id LEFT OUTER JOIN
                      dbo.tblSiteMemberDetail AS tblSiteMemberDetail_2 INNER JOIN
                      dbo.tblSiteChallengeMediator ON tblSiteMemberDetail_2.id = dbo.tblSiteChallengeMediator.memberId ON
                      dbo.tblSiteChallengeMember.mediatorId = dbo.tblSiteChallengeMediator.id

Which throws a SQL error:

Msg 102, Level 15, State 1, Procedure vwGetChallengeDetails, Line 19
Incorrect syntax near 'ONCREATE'.

I am still fairly new to databasing, but I have never seen the ONCREATE command (above AS in the code). Could someone please check this and tell me what's wrong?

Looks like an error to me. Most likely, the ON bit belongs to the JOIN, even though the JOIN seems to be established succesfully. The CREATE is the start of a new CREATE VIEW statement. I'd venture a guess that the amount of text used in the first CREATE VIEW statement exceeds the length of what Dezign allows. Try manually removing the ON and running the script in Sql Server Manegemtn Studio? — SchmitzIT, Oct 24 '12 at 10:33
Even though DeZign auto-created it... And I just checked - I'm able to add more text. Could you post a possible code fix to this... Pleease :) — TheGeekZn, Oct 24 '12 at 10:35
It actually misses part of the `ON` statement. The amount of `JOIN` statements do not match the amount of `ON` statements. — SchmitzIT, Oct 24 '12 at 10:44

score 2 · Accepted Answer · answered Oct 24 '12 at 10:41

Isn't that just the same code twice, but with part of the first version cut off and some whitespace missing? The second copy looks complete. Try this:

CREATE VIEW %viewname%
AS
  SELECT dbo.tblSiteChallengeMember.id,
         tblSiteMemberDetail_2.memberNickname AS mediator,
         dbo.tblSiteGame.name AS gameName,
         tblSiteMemberDetail_1.memberNickname AS teamMemberName,
         dbo.tblSiteChallengeMemberTeam.accepted,
         dbo.tblSiteChallengeMemberTeam.teamNumber,
         dbo.tblSiteChallengeMember.matchDate,
         dbo.tblSiteChallengeMember.dateAdded,
         dbo.tblSiteChallengeMember.completed,
         dbo.tblSiteMemberDetail.memberNickname AS creatorName,
         dbo.tblSiteChallengeMediator.refferalCount,
         dbo.tblSiteChallengeMediator.dateAdded AS mediatorJoinDate
  FROM   dbo.tblSiteChallengeMemberWin
         RIGHT OUTER JOIN dbo.tblSiteChallengeMember
                          INNER JOIN dbo.tblSiteMemberDetail
                            ON dbo.tblSiteChallengeMember.creatorId = dbo.tblSiteMemberDetail.id
           ON dbo.tblSiteChallengeMemberWin.challengeId = dbo.tblSiteChallengeMember.id
         LEFT OUTER JOIN dbo.tblSiteMemberDetail AS tblSiteMemberDetail_1
                         INNER JOIN dbo.tblSiteChallengeMemberTeam
                           ON tblSiteMemberDetail_1.id = dbo.tblSiteChallengeMemberTeam.memberId
           ON dbo.tblSiteChallengeMember.id = dbo.tblSiteChallengeMemberTeam.challengeId
         RIGHT OUTER JOIN dbo.tblSiteGame
           ON dbo.tblSiteChallengeMember.gameId = dbo.tblSiteGame.id
         LEFT OUTER JOIN dbo.tblSiteMemberDetail AS tblSiteMemberDetail_2
                         INNER JOIN dbo.tblSiteChallengeMediator
                           ON tblSiteMemberDetail_2.id = dbo.tblSiteChallengeMediator.memberId
           ON dbo.tblSiteChallengeMember.mediatorId = dbo.tblSiteChallengeMediator.id

Exactly that. I posted that answer just as you did. Yours is more complete. Thanks for the effort made :) — TheGeekZn, Oct 24 '12 at 10:45

score 0 · Answer 2 · answered Oct 24 '12 at 10:44

0

DeZign somehow tried to create the same view twice, but removed only a portion of the previous code to create the new one.

Removing all the code before 'on' in 'oncreate' fixed the code.

answered Oct 24 '12 at 10:44

TheGeekZn

3,696
10
55
91

SQL 'ONCREATE' command

2 Answers2