| Author |
 Topic  |
|
|
osirisa
Constraint Violating Yak Guru
USA
289 Posts |
 Posted - 11/14/2007 : 17:10:35
|
Hi: I am having a problem with the Syntax. I am trying to get the daily value for a contract_nbr. The selection below shows that I am selecting by Case statement when day = 2 (2nd Day) and the month is a parameter provider by the user. For this particular example, I am using 2 (February).
Thanks for the help !!!!
[code]
Set @Req_Month = '2'
SELECT Distinct a.contract_nbr,
Case when Day(c.Beg_eff_date) = 2 and month(c.Beg_eff_date)= @Req_Month
Then c.rcpt_nom_vol-c.rcpt_fuel-rcpt_act_vol
Else 0
End As Day_2
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')
Group by a.contract_nbr
[\code]
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 11/14/2007 : 17:14:07
|
Would it be possible for you to let us know what the syntax error message that you are geting is?
CODO ERGO SUM |
 |
|
|
osirisa
Constraint Violating Yak Guru
USA
289 Posts |
Posted - 11/14/2007 : 17:17:01
|
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.rcpt_nom_vol' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.rcpt_fuel' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.rcpt_act_vol' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.beg_eff_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.beg_eff_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 11/14/2007 : 17:20:42
|
Put a SUM() around your case statement.
CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/14/2007 : 17:20:59
|
Both DISTINCT and GROUP BY?SET @Req_Month = 2
SELECT a.contract_nbr,
SUM(CASE
WHEN DAY(c.Beg_eff_date) = 2 AND MONTH(c.Beg_eff_date) = @Req_Month THEN c.rcpt_nom_vol - c.rcpt_fuel - c.rcpt_act_vol
ELSE 0
END) As Day_2
FROM TIES_Gathering.dbo.contract AS a
INNER JOIN TIES_Gathering.dbo.NOm AS b on b.contract_nbr = a.contract_nbr
Inner Join TIES_Gathering.dbo.Nom_vol_detail AS c on c.Nom_id = b.Nom_id
where a.contract_sub_type = 'INT'
and a.Contract_type_code ='GTH'
and DATEADD(month, DATEDIFF(month, 0, current_timestamp), 0) < a.current_expirtn_date
and c.rcpt_dlvry_ind = 'R'
Group by a.contract_nbr
order by a.contract_nbr
E 12°55'05.25"
N 56°04'39.16" |
Edited by - SwePeso on 11/14/2007 17:21:51 |
|
|
|
dataguru1971
Flowing Fount of Yak Knowledge
USA
1464 Posts |
Posted - 11/14/2007 : 17:21:12
|
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91542
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92585
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92388
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92515
I am seeing a pattern develop.
If they aren't included in your group by clause, just going out on a limb, but you might want to include them in your group by clause.
SELECT Distinct a.contract_nbr,
Case when Day(c.Beg_eff_date) = 2 and month(c.Beg_eff_date)= @Req_Month
Then c.rcpt_nom_vol-c.rcpt_fuel-rcpt_act_vol
Else 0
End As Day_2
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')
Group by a.contract_nbr,
Case when Day(c.Beg_eff_date) = 2 and month(c.Beg_eff_date)= @Req_Month
Then c.rcpt_nom_vol-c.rcpt_fuel-rcpt_act_vol
Else 0
End
You need to make some progress on debugging your own basic syntax. As said previously, this forum should not simply be your default debugger.
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
osirisa
Constraint Violating Yak Guru
USA
289 Posts |
Posted - 11/14/2007 : 17:27:17
|
| Thank You both !!!! |
|
|
| |
Topic |
|
Daily Calculation need help