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.
Author |
Topic |
osirisa
Constraint Violating Yak Guru
289 Posts |
Posted - 2007-11-13 : 15:04:58
|
[code]SELECT Distinct a.contract_nbrSum(Case when Day(Beg_eff_date) = 1Then c.rcpt_nom_vol-c.rcpt_fuel-c.rcpt_act_vol Else 0 End Case) As Day 1,from TIES_Gathering.dbo.contract aInner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbrInner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_idwhere (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 2Incorrect 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) = 1Then 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. |
|
|
osirisa
Constraint Violating Yak Guru
289 Posts |
Posted - 2007-11-13 : 15:27:38
|
[code]SELECT Distinct a.contract_nbrSum(Case when Day(Beg_eff_date) = 1Then c.rcpt_nom_vol-c.rcpt_fuel-c.rcpt_act_vol Else 0 End) As Day 1,from TIES_Gathering.dbo.contract aInner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbrInner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_idwhere (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 2Incorrect syntax near the keyword 'Case'. |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-11-13 : 15:34:16
|
You have a "," after "As Day 1"...before the FROM. |
|
|
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 columnColumn 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) = 1Then c.rcpt_nom_vol-c.rcpt_fuel-c.rcpt_act_vol Else 0 End) As Day_1from TIES_Gathering.dbo.contract aInner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbrInner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_idwhere (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. |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
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" |
|
|
|
|
|
|
|