Error

Transact-SQL
1

What does this mean I get this when I try and run my stored procedure??

Cannot call methods on varchar.

2

Post your code. you probably have a reserved word in your store procedure.

create table [User](name varchar(50))

insert into [User]
select 'Katie' union
select 'Suzie'

--User is a reserved word in SQL Server
select User.name from [User]

drop table [User]
3

Here is my code I know get Incorrect syntax near 'GROUP'

INSERT INTO [dbo].[PRODUCTION]
([SOURCE]
,[WELL]
,[OPERATOR]
,[WELL_ID]
,[DATE]
,[TBG_PSI]
,[CSG_PSI]
,[DIFF_PSI]
,[LINE_PSI]
,[BB GAS]
,[CUM_GAS]
,[GAS_SPOT_MCFD]
,[NET_GAS_MCFD]
,[GAS_INJ_SPOT_MCFD]
,[BOPD]
,[CUM_OIL]
,[BWPD]
,[CUM_WTR]
,[PERCENT_LOAD]
,[HOL]
,[DOL]
,[GPI]
,[FUEL_GAS])

SELECT
'DPD'
,ac.LEASE
,ac.OPERATOR
,ac.WELL_ID
,dp.D_DATE
,dp.FTP
,dp.CP
,dp.DIFF
,dp.LINE
,dp.BUY_BACK
,ROUND(SUM(dp.[GAS]),2) AS [CUM_GAS]
,dp.GAS
,dp.GAS - dp.BUY_BACK
,dp.GAS_LIFT
,dp.OIL
,ROUND(SUM(dp.[WATER]),2) AS [CUM_WTR]
,dp.WATER
,ROUND(SUM(dp.[OIL]),2) AS [CUM_OIL]
,ct.PERCENT_LOAD
,fb.ROUND(SUM(PRODUCTION.[HOL]),0 + 24) AS [HOL]
,fb.ROUND(SUM(PRODUCTION.[DOL]),0 + 1) AS [DOL]
,ac.GPI
,dp.FUEL_GAS
FROM [RESERVES].dbo.PROPERTY ac
INNER JOIN [RESERVES].dbo.DAILY dp
ON ac.WELL_ID = dp.WELL_ID
INNER JOIN [COMPLETIONS].dbo.COMPLETION_TOTALS ct
ON ac.WELL_ID = ct.WELL_ID
INNER JOIN [COMPLETIONS].dbo.PRODUCTION fb
ON ac.WELL_ID = fb.WELL_ID
HAVING (dp.D_DATE >= DATEADD(DAY, CASE WHEN DATEDIFF(DAY, 0, GETDATE())
% 7 = 0 THEN - 4 ELSE - 2 END, CAST(GETDATE() AS date))) AND (dp.D_DATE < DATEADD(DAY, - 1, CAST(GETDATE() AS date)))
GROUP BY ac.LEASE, ac.WELL_ID, dp.D_DATE
;
END

4

If I move the GROUP BY before the having I still get this:

Column 'RESERVES.dbo.DAILY.FTP' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. on every column unless I add them all to the GROUP BY why does it do this??

5

when you do any sort of calculation , and you have multiple columns listed on the select, you need to add them in the group by.

You are jumping all over the place. On your previous post you said you resolve it. On this post you were asking about
Cannot call methods on varchar.

and now you are talking about group by which you said you resolved?

Which is it?

6

My apologize I got the Cannot call methods on varchar then I added the DateDiff but I am still getting the Group By error, I understand the aggregates, but why is it wanting ones that are not calculated in the group by?

7

You need to group by all columns that are not being aggregated. Are you confusing group by with order by?

1 Like
8

No I guess in the past I did not think I had to include all that were not be aggregated. Thanks so much

9

Than you so much that worked. And sorry for any confusion.