SQL Problem, error given = Incorrect syntax near the keyword 'as'.

The errors that the sql is giving me is

Msg 156, Level 15, State 1, Procedure dayfigures, Line 44
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Procedure dayfigures, Line 54
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Procedure dayfigures, Line 64
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Procedure dayfigures, Line 74
Incorrect syntax near the keyword 'as'.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER procedure [dbo].[dayfigures] 
 
@AdvertiserID int,
@YearMonthDayFrom varchar(6),
@YearMonthDayTo varchar(6),
@ProductId varchar(50),
@Datefrom datetime,
@DateTo datetime
 
as
 
select convert(varchar, cast('20' + yearmonthday as datetime), 103)  as day ,productName, 
 
 
cast((select COALESCE(sum(coalesce(amount,0) -coalesce(bonus,0) -coalesce(adjustment,0) ) ,0)
from tbl_playeractivity as pa , tbl_player as p, tbl_affiliate as a
where p.affiliateID = a.affiliateID
and a.advertiserID = 1
and pa.activityTypeID = 1
and a.affiliatestatusID =2 and
p.playerId = pa.playerID and 
activitydate >='20'+ yearmonthday +' 00:00:00'  and activitydate <='20'+ yearmonthday + ' 23:59:59'
and pa.activityDate < p.playerExpiration 
and pa.productId = prod.productId 
and prod.productId*dbo.QueryFilterBit(@ProductId) = dbo.QueryFilterID(@ProductId)) as decimal(8,2)) 
as NetRevenue
from tbl_adlogdailyarchive, tbl_Product as prod
where yearmonthday >=@YearMonthDayFrom and yearmonthday <=@YearMonthDayTo 
and prod.productId*dbo.QueryFilterBit(@ProductId) = dbo.QueryFilterID(@ProductId)
 
 
(select COALESCE(sum(deposit) ,0)
 
from tbl_playeractivity as pa , tbl_player as p, tbl_affiliate as a
where p.affiliateID = a.affiliateID
 
and a.advertiserID = 1
and pa.activityTypeID = 1
and a.affiliatestatusID =2 and
p.playerId = pa.playerID and
 
activitydate >='20'+ yearmonthday +' 00:00:00'  and activitydate <='20'+ yearmonthday + ' 23:59:59'
and pa.activityDate< p.playerExpiration) as Daily Deposits,
 
 
(select sum(COALESCE(pa.bonus,0)) from tbl_playeractivity as pa
,tbl_player as pl
where pl.affiliateID = a.affiliateID
and pl.playerId = pa.playerID
and pa.activityTypeID = 1
and pa.activityDate >= @DateFrom
and pa.activityDate < @DateTo and pa.activityDate< pl.playerExpiration)as Bonuses,
 
(select sum(COALESCE(pa.adjustment,0)) from tbl_playeractivity as pa
,tbl_player as pl
where pl.affiliateID = a.affiliateID
and pl.playerId = pa.playerID
and pa.activityTypeID = 1
and pa.activityDate >= @DateFrom
and pa.activityDate < @DateTo and pa.activityDate< pl.playerExpiration)as adjustments
 
 
(select sum(COALESCE(pa.amount,0)) from tbl_playeractivity as pa
,tbl_player as pl
where pl.affiliateID = a.affiliateID
and pl.playerId = pa.playerID
and pa.activityTypeID = 1
and pa.activityDate >= @DateFrom
and pa.activityDate < @DateTo and pa.activityDate< pl.playerExpiration)as GrossAmount
 
 
 
select  count(a.affiliateID) as TotalAffiliates from tbl_affiliate as a,tbl_country as c , 
tbl_publishpoint as p
where c.countryId =a.countryId and a.advertiserId = @advertiserID and affiliateStatusID =1 and p.affiliateID = a.affiliateID
 
 
 
 
group by yearmonthday, prod.productId, prod.productname
order by yearmonthday desc

Open in new window

LVL 3
MezillinuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brejkCommented:
First of all it is very hard to read your code... Also, there are so many things wrong in your snippet that I don't even know what to start with...

1) The first SELECT statement in your code ends in line 33.
2) After line 33 there is a bunch of SELECT subqueries which I guess you wanted to return as single columns in a single query (?). But there is no SELECT or FROM of the parent query...
3) You use non-standard alias names to easily: go to the line 47 and see: ... as Daily Deposits (should be: ... as [Daily Deposits]).

You should definitely try to make your code more readable (use tabs, use aliases, separate selected columns into single lines of code... etc.).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee SavidgeCommented:
Hi,

I'm not sure who wrote that code but it is pretty badly done to be honest. There are multiple problems. Firstly the column aliases cannot contain spaces as pointed out by brejk.

Also, your sub queries are doing a select statement returning more than a single column which you are trying to alias as a different name.

Reformatting your code would be the best start you could do so you can see the structure of what you're trying to do.

Try using joins rather than sub queries.

Other than it is difficult to help more than that.

Regards,

Lee
0
MezillinuAuthor Commented:
I guess the problem here was about my names were not in [].
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.