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
 Syntax Help

Author  Topic 

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-13 : 15:04:58
[code]

SELECT Distinct a.contract_nbr
Sum(Case when Day(Beg_eff_date) = 1
Then c.rcpt_nom_vol-c.rcpt_fuel-c.rcpt_act_vol
Else 0
End Case) As Day 1,
from TIES_Gathering.dbo.contract a
Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date)
and (c.rcpt_dlvry_ind ='R')

[/code]

Is this the correct syntax to find day 01. I need help with the syntax I am having a syntax error.

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Case'.

Thanks for the help

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 15:05:45
um(Case when Day(Beg_eff_date) = 1
Then c.rcpt_nom_vol-c.rcpt_fuel-c.rcpt_act_vol
Else 0
End ) As Day 1,

You don't have to END CASE, just END (different than VB)



Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-13 : 15:27:38
[code]
SELECT Distinct a.contract_nbr
Sum(Case when Day(Beg_eff_date) = 1
Then c.rcpt_nom_vol-c.rcpt_fuel-c.rcpt_act_vol
Else 0
End) As Day 1,
from TIES_Gathering.dbo.contract a
Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date)
and (c.rcpt_dlvry_ind ='R')
[/code]

same error

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Case'.
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-13 : 15:34:16
You have a "," after "As Day 1"...before the FROM.
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 15:35:11
Comma missing after a.contract_nbr
a comma should be omitted after the last column

Column names with spaces should have [Day 1] brackets. or an Day_1 underscore.

this is basic syntax. Is this forum your debugger?


SELECT Distinct a.contract_nbr,
Sum(Case when Day(Beg_eff_date) = 1
Then c.rcpt_nom_vol-c.rcpt_fuel-c.rcpt_act_vol
Else 0
End) As Day_1
from TIES_Gathering.dbo.contract a
Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date)
and (c.rcpt_dlvry_ind ='R')


In my QA, if I double click the error message, it takes me right to the line.




Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-13 : 15:39:41
quote:
Originally posted by dataguru1971

this is basic syntax. Is this forum your debugger?



Lol.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 16:21:41
Best comment this week!



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

- Advertisement -