Incorrect syntax near the keyword 'Where'.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Incorrect syntax near the keyword 'Where'.

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-31 : 10:31:56

Hey guys

Hope your well, i am having some issues with my query, and i am hoping you will be able to help

I am receiving the error msg

Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'Where'.

And my query is this
, does anyone have any ideas ?

-------------------------------- Sales ------------------------------

Declare @Rolling12 varchar(10)
set @Rolling12 = (select dateadd(MM,-12,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)
SELECT ParentID,
sum([Net_Sales]) as Net_Sales_Rolling12,
SUM ([Net_Intg]) as Net_Intg_Rolling12,
SUM ([Scheme_Fees]) as Scheme_Fees_Rolling12,
SUM ([Funding_Amt]) as Funding_Amt_Rolling12

Into #Sales
FROM [FDMS].[dbo].[Fact_Financial_History]f full outer join Dim_Outlet o
on f.hst_merchnum = o.FDMSAccountNo_First9
Where hst_date_processed > @Rolling12
group by o.ParentID

--select* from #Sales

--------------------------------ProcessingCost -----------------------

SELECT ParentID
,sum([Value]) as ProcessingCost_Rolling12
Into #Processing
FROM [FDMS].[dbo].[Fact_ProcessingCost]f full outer join Dim_Outlet o
--on f.FDMSAccountNo = o.ParentID
Where Period > @Rolling12
group by o.ParentID

--select * from #Processing
--drop table #Processing

------------------------------------MSC ----------------------------

SELECT
FDMSAccountNo,
[Month_end_date] as hst_date_processed,
Case when Fee_Code IN ('42B','42C','42D','42E') Then 'PCI'
When Fee_Code = '00Y' Then 'Refund Transaction Charge'
When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'Y' then 'Other repeatable'
When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'N' then 'Other Non repeatable'
Else salesMI_Group1 end as description,
d.repeat_msc,
Sum([Retail_amount]) As Amount
Into #MscCount
FROM FDMS.dbo.Fact_Fee_History f
INNER JOIN
FDMS.dbo.Dim_Fee_Codes D ON f.Fee_Sequence_Number = D.Fee_Code
--where Month_end_date >= dateadd(mm,datediff(mm,0,getdate())-12,0)
--and Month_end_date < dateadd(mm,datediff(mm,0,getdate()),0)
where Month_end_date> @Rolling12
Group by FDMSAccountNo, Month_end_date, d.Fee_Code, d.SalesMI_Group1, d.Repeat_MSC

--select * from #MscCount

--drop table #MscCount,#sales, #Processing

SELECT [FDMSAccountNo],hst_date_processed,
SUM ( case when description ='MSC' then ([Amount])else 0 end ) as [Rolling12_MSC],
SUM ( case when description ='PCI' then ([Amount])else 0 end ) as [Rolling12_PCI],
SUM ( case when description ='Refund Transaction Charge' then ([Amount])else 0 end ) as [RefundTransactionCharge_2012],
SUM ( case when description ='Other repeatable' then ([Amount])else 0 end ) as [Other_repeatable2012],
SUM ( case when description ='Other Non repeatable' then ([Amount])else 0 end ) as [Other_Non_repeatable2012]
FROM #MscCount
group by [FDMSAccountNo],hst_date_processed

--select * from #msc

------------------------------------Results Layout---------------------------------
Select

f.FDMSAccountNo,
[Net_Sales_Rolling12] as [Rolling 12 Net Sales],
[Net_Intg_Rolling12] as [Rolling 12 Net Interchange],
[Scheme_Fees_Rolling12] as[Rolling 12 Scheme Fees],
[Funding_Amt_Rolling12] as [Rolling 12 Funding],
[Rolling12_MSC] as [Rolling 12 MSC],
[Rolling12_PCI] as [Rolling 12 PCI],
[RefundTransactionCharge_2012] as [Rolling 12 RTCharge],
[Other_repeatable2012] as [Rolling 12 OtherRep],
[Other_Non_repeatable2012] as [Rolling 12 NonRep]

Into #results
from #Sales s
full outer join #Processing p on s.ParentID = p.ParentID
full outer join #MscCount m on p.FDMSAccountNo = m.FDMSAccountNo

------------------------------ Calculations ------------------------------
Select *,
[Rolling12_MSC]-[Rolling 12 Net Interchange] as [Rolling 12 DI],
[Rolling 12 MSC]-[Rolling 12 Net Interchange] -[Rolling 12 Scheme Fees] as [Rolling 12 DIA],
SUM ([Rolling 12 DIA]+[Rolling 12 OtherRep]+[Rolling 12 Funding])- SUM([Rolling 12 Funding]) as [rolling 12 Repcon]

----------------------------------- F Results -----------------

Into #FinalResults
From #results
group by [FDMSAccountNo],
[Rolling 12 Net Sales],
[Rolling 12 Net Interchange],
[Rolling 12 Scheme Fees],
[Rolling 12 Funding],
[Rolling 12 MSC],
[Rolling 12 PCI],
[Rolling 12 RTCharge],
[Rolling 12 OtherRep],
[Rolling 12 NonRep]

select o.ParentID,F.* from #FinalResults f
inner join fdms.dbo.Dim_Outlet o on o.FDMSAccountNo = f.FDMSAccountNo

--Drop table #Sales

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 10:35:21

you've on condition commented out for below select


SELECT ParentID
,sum([Value]) as ProcessingCost_Rolling12
Into #Processing
FROM [FDMS].[dbo].[Fact_ProcessingCost]f full outer join Dim_Outlet o
--on f.FDMSAccountNo = o.ParentID
Where Period > @Rolling12
group by o.ParentID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-31 : 10:37:32

Hi visakh16

What does that mean ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 11:14:05

quote:
Originally posted by masond

Hi visakh16

What does that mean ?

that means it doesnt have required on clause specified without which its syntactically wrong

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 11:15:59

with 100 + posts this is something I would expect you to pick yourselves.
Suggest you to understand more on joins

http://www.w3schools.com/sql/sql_join.asp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-31 : 11:46:58

Joins is my downful unfort, i am trying to read up on it !

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-31 : 13:28:50

hi visakh16,
you remember what we discussed a while ago, i just re sent you an email to your gmail. Let me know your thoughts

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 15:00:43

quote:
Originally posted by masond

hi visakh16,
you remember what we discussed a while ago, i just re sent you an email to your gmail. Let me know your thoughts

i have replied with mine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources