Need help with SQL

[PaulSpell]

I'm trying (being the operative word) to build a passthrough query and am having some problems. When I try running the following SQL it returns "Incorrect Syntax near '!'". Can anyone see what's wrong?

SELECT dbo.vSyndicate.CalYearID AS CalYr, Right([ReturnCalQuarterID],1) AS Qtr,
dbo.vReturnHeader.SyndicateNumber,
case YOA
when [ReportingCalYearID] -2 then Null
else [ReportingCalYearID]
end,
dbo.vReturnBaseElementMappingUIDescription.FormLabel,
dbo.vReturnExpenseType.ReturnExpenseTypeID,
Cast([ExpenseTypeNID] as int) AS Id,
[expensetypenid] + " - " + [expensetypedesc] AS Expense,
Cast((case value when IsNull([valueint],1) then [valuedecimal] else [valueint] end) as int)
FROM ((((((dbo.vReturn LEFT JOIN dbo.vReturnBaseElementMapping ON dbo.vReturn.ReturnBaseElementMappingID = dbo.vReturnBaseElementMapping.ReturnBaseElementMappingID)
LEFT JOIN dbo.vReturnHeader ON dbo.vReturn.fReturnHeaderID = dbo.vReturnHeader.fReturnHeaderID)
LEFT JOIN dbo.vReturnPeriod ON dbo.vReturnHeader.ReturnPeriodID = dbo.vReturnPeriod.ReturnPeriodID)
LEFT JOIN dbo.vSyndicate ON dbo.vReturnHeader.SyndicateSCDYearID = dbo.vSyndicate.SyndicateSCDYearID)
LEFT JOIN dbo.vReturnCurrency ON dbo.vReturn.ReturnCurrencyID = dbo.vReturnCurrency.ReturnCurrencyID)
LEFT JOIN dbo.vReturnBaseElementMappingUIDescription ON dbo.vReturnBaseElementMapping.ReturnBaseElementMappingID = dbo.vReturnBaseElementMappingUIDescription.ReturnBaseElementMappingID)
LEFT JOIN dbo.vReturnExpenseType ON dbo.vReturn.ReturnExpenseTypeID = dbo.vReturnExpenseType.ReturnExpenseTypeID
WHERE (((Cast([dbo.vReturnBaseElementMappingUIDescription]![ReturnTypeID]) as int)=3)
AND ((Cast([dbo.vReturnHeader]![ReturnStatusID]) as int)=6)
AND ((Trim(Left([formlabel],3)))=130))
ORDER BY dbo.vSyndicate.CalYearID, Right([ReturnCalQuarterID],1),
dbo.vReturnHeader.SyndicateNumber,
(where [ReportingCalYearID]=-2 then Null else [ReportingCalYearID]),
Cast([ExpenseTypeNID] as int);​

 

[Meltdown]

As far as I Know SQL Server doesn't use/understand the BANG ! operator, replace where it occurs in your code with a . dot

 

[PaulSpell]

As far as I Know SQL Server doesn't use/understand the BANG ! operator, replace where it occurs in your code with a . dot

Thanks, tried that but it still didn't work.

 

[PaulSpell]

Done it:

​

"SELECT dbo.vSyndicate.CalYearID AS CalYr,
Right(ReturnCalQuarterID,1) AS Qtr,
dbo.vReturnHeader.SyndicateNumber,
Case when ReportingCalYearID = -2 THEN Null else ReportingCalYearID end AS YOA,
dbo.vReturnBaseElementMappingUIDescription.FormLabel,
dbo.vReturnExpenseType.ReturnExpenseTypeID,
Cast(ExpenseTypeNID as int) AS Id,
expensetypenid + ' - ' + expensetypedesc AS Expense,
Case when valueint is null THEN valuedecimal else valueint end AS [Value]
FROM dbo.vReturn LEFT JOIN dbo.vReturnBaseElementMapping ON dbo.vReturn.ReturnBaseElementMappingID = dbo.vReturnBaseElementMapping.ReturnBaseElementMappingID
LEFT JOIN dbo.vReturnHeader ON dbo.vReturn.fReturnHeaderID = dbo.vReturnHeader.fReturnHeaderID
LEFT JOIN dbo.vReturnPeriod ON dbo.vReturnHeader.ReturnPeriodID = dbo.vReturnPeriod.ReturnPeriodID
LEFT JOIN dbo.vSyndicate ON dbo.vReturnHeader.SyndicateID = dbo.vSyndicate.SyndicateID
LEFT JOIN dbo.vReturnCurrency ON dbo.vReturn.ReturnCurrencyID = dbo.vReturnCurrency.ReturnCurrencyID
LEFT JOIN dbo.vReturnBaseElementMappingUIDescription ON dbo.vReturnBaseElementMapping.ReturnBaseElementMappingID = dbo.vReturnBaseElementMappingUIDescription.ReturnBaseElementMappingID
LEFT JOIN dbo.vReturnExpenseType ON dbo.vReturn.ReturnExpenseTypeID = dbo.vReturnExpenseType.ReturnExpenseTypeID
WHERE dbo.vReturnBaseElementMappingUIDescription.ReturnTypeID =3
AND dbo.vReturnHeader.ReturnStatusID=6
AND LTrim(Left(formlabel,3))='130'
AND dbo.vReturnExpenseType.ReturnExpenseTypeID >= 0
AND dbo.vSyndicate.SCDCurrentFlag = 1
ORDER BY dbo.vSyndicate.CalYearID,
Right(ReturnCalQuarterID,1),
dbo.vReturnHeader.SyndicateNumber,
ReportingCalYearID,
Cast(ExpenseTypeNID as int );"