Error: Line 8: Incorrect syntax near '.' ; Help!

Fly4High · August 10th, 2006, 11:22 PM

Hi everyone,
I have the problem when trying to create a view in MSSQL 2000, this is the code:

Code:

CREATE VIEW vwPreview (WorkFileNo, DateEntry, FirstName, SurName, LoanAmt,IntAmt, Penalty, AdminFees, MOF, Refund, fns, Repayment, IntRebate,PenaltyRebate, RateDiff, Overpayment,Description, ActualBalance
)
AS
  SELECT tblClient.WorkFileNo, tblActBal.DateEntry, tblClient.FirstName, tblClient.SurName,
  tblDebit.LoanAmt, tblDebit.IntAmt, tblDebit.Penalty, tblDebit.AdminFees, tblDebit.MOF
  tblDebit.Refund, tblDebit.fns, tblCredit.Repayment, tblCredit.IntRebate, tblCredit.PenaltyRebate,
  tblCredit.RateDiff, tblCredit.Overpayment, tblDebit.Description, tblActBal.ActualBalance
  FROM tblClient INNER JOIN tblCredit INNER JOIN tblDebit INNER JOIN tblActBal
  ON tblClient.WorkFileNo = tblCredit.WorkFileNo ON tblClient.WorkFileNo = tblDebit.WorkFileNo
        ON tblClient.WorkFileNo = tblActBal.WorkFileNo
GO

When I compile this code to get the view, promting the error: Line 8: Incorrect syntax near '.'

Help to resolve this problem. Pls!
Thanks for kindly help!

Think as you're someone else, act as the way you are!

Gert · August 11th, 2006, 08:12 AM

I belive the ON clause should come after every join..

FROM tblClient INNER JOIN tblCredit ON tblClient.WorkFileNo = tblCredit.WorkFileNo INNER JOIN tblDebit ON tblClient.WorkFileNo = tblDebit.WorkFileNo INNER JOIN tblActBal ON tblClient.WorkFileNo = tblActBal.WorkFileNo

Peso · August 15th, 2006, 03:02 AM

You are missing a comma after tblDebit.MOF.

Rewrite yuor query as this

CREATE VIEW vwPreview
AS

SELECT tblClient.WorkFileNo,
        tblActBal.DateEntry,
        tblClient.FirstName,
        tblClient.SurName,
        tblDebit.LoanAmt,
        tblDebit.IntAmt,
        tblDebit.Penalty,
        tblDebit.AdminFees,
        tblDebit.MOF,
        tblDebit.Refund,
        tblDebit.fns,
        tblCredit.Repayment,
        tblCredit.IntRebate,
        tblCredit.PenaltyRebate,
        tblCredit.RateDiff,
        tblCredit.Overpayment,
        tblDebit.Description,
        tblActBal.ActualBalance
FROM tblClient
INNER JOIN tblCredit ON tblCredit.WorkFileNo = tblClient.WorkFileNo
INNER JOIN tblDebit ON tblDebit.WorkFileNo = tblClient.WorkFileNo
INNER JOIN tblActBal ON tblActBal.WorkFileNo = tblClient.WorkFileNo
GO