November 10, 2015 at 11:46 am
Hi,
I am working on a PROC I am using for an SSRS report and am having issues with the WHERE clause:
Here is the PROC:
@BegInvDate Date,
@EndInvDate Date,
@Project varchar(150)
as
declare @BegDate varchar(10)
set @BegDate = CAST(@BegInvDate as varchar(10))
declare @EndDate varchar(10)
set @EndDate = CAST(@EndInvDate as varchar(10))
declare @Proj varchar(150)
set @Proj = @Project
Select VCHR.VEND_ID
,P.PROJ_ID
,A.ACCT_ID
,cast(VCHR.INVC_DT as Date) as INVC_DT
,V.VEND_NAME
,V.VEND_NAME_EXT
,SUM(LNHS.CST_AMT) as CST_AMOUNT
,vchr.VCHR_NO
,V.S_CL_SM_BUS_CD
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
FROM WEBAPP_CP.DELTEK.V_VEND V
RIGHT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR
ON V.VEND_ID = VCHR.VEND_ID
RIGHT JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS
ON VCHR.VCHR_KEY = LNHS.VCHR_KEY
JOIN WEBAPP_CP.DELTEK.ACCT A
ON LNHS.ACCT_ID = A.ACCT_ID
JOIN WEBAPP_CP.DELTEK.PROJ P
ON LNHS.PROJ_ID = P.PROJ_ID
GROUP BY V.VEND_NAME_EXT
,V.S_CL_SM_BUS_CD
,VCHR.VEND_ID
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
,V.VEND_NAME
,P.PROJ_ID
,VCHR.INVC_DT
,A.ACCT_ID
,VCHR.VCHR_NO
WHERE P.PROJ_ID LIKE @PROJ_ID + '%' AND
VCHR.INVC_DT BETWEEN @BEG_DATE AND @END_DATE
I think the syntax is incorrect. My error is:
Msg 156, Level 15, State 1, Procedure Rpt_Vendor_Activity_By_EEOC, Line 64
Incorrect syntax near the keyword 'WHERE'.
Any advice is always much appreciated, Guys.
November 10, 2015 at 11:52 am
The WHERE clause has to precede the GROUP BY clause.
Cheers!
November 10, 2015 at 11:54 am
From Books Online:
The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:
[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ]
SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
November 10, 2015 at 12:25 pm
A bit off topic, but why are you casting the dates as varchars?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 10, 2015 at 1:38 pm
The DATE cast was a mistake. I've corrected some of the mistakes, but am getting an error now that states:
Msg 206, Level 16, State 2, Procedure Rpt_Vendor_Payment_CP7_Vend_List, Line 0
Operand type clash: int is incompatible with date
Here is the PROC now:
USE [WEBAPP_CP]
GO
/****** Object: StoredProcedure [dbo].[Rpt_Vendor_Activity_By_EEOC] Script Date: 11/10/2015 3:37:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[Rpt_Vendor_Activity_By_EEOC]
@BegInvDate Date,
@EndInvDate Date,
@Project varchar(29)
as
BEGIN
Select VCHR.VEND_ID
,P.PROJ_ID
,A.ACCT_ID
,cast(VCHR.INVC_DT as Date) as INVC_DT
,V.VEND_NAME
,V.VEND_NAME_EXT
,SUM(LNHS.CST_AMT) as CST_AMOUNT
,vchr.VCHR_NO
,V.S_CL_SM_BUS_CD
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
FROM WEBAPP_CP.DELTEK.V_VEND V
RIGHT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR
ON V.VEND_ID = VCHR.VEND_ID
RIGHT JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS
ON VCHR.VCHR_KEY = LNHS.VCHR_KEY
JOIN WEBAPP_CP.DELTEK.ACCT A
ON LNHS.ACCT_ID = A.ACCT_ID
JOIN WEBAPP_CP.DELTEK.PROJ P
ON LNHS.PROJ_ID = P.PROJ_ID
WHERE P.PROJ_ID LIKE '%' + @Project + '%' AND
VCHR.INVC_DT BETWEEN @BegInvDate AND @EndInvDate
GROUP BY V.VEND_NAME_EXT
,V.S_CL_SM_BUS_CD
,VCHR.VEND_ID
,V.CL_VET_FL
,V.CL_SD_VET_FL
,V.CL_ANC_IT_FL
,V.CL_DISADV_FL
,V.CL_WOM_OWN_FL
,V.CL_LAB_SRPL_FL
,V.CL_HIST_BL_CLG_FL
,V.CL_ANC_IT_FL
,V.VEND_NAME
,P.PROJ_ID
,VCHR.INVC_DT
,A.ACCT_ID
,VCHR.VCHR_NO
END
GO
November 10, 2015 at 1:50 pm
Most likely INVC_DT is stored as an integer, and the BETWEEN comparison is throwing that error. The explicit CAST of INVC_DT to a date type would also fail in that case.
There will be ways around that, but to help much more we'll have to know exactly how the date is currently stored in the INVC_DT column (what datatype and what format).
Cheers!
November 10, 2015 at 1:54 pm
INVC_DT (smalldatetime, null)
November 10, 2015 at 2:00 pm
Ah, in that case it's likely the statement calling the procedure. Are you putting single quotes around the date values you're passing as the two date parameters? If not, those will be interpreted as integers and will throw the error you're seeing.
Cheers!
November 10, 2015 at 2:06 pm
Just as you were typing, I wrapped the date in quotes and bam, it worked. Thanks again!
November 10, 2015 at 2:09 pm
Good to hear! Had I read the error completely (doh!) that would have been pretty clear from the get-go, due to its occurrence on line 0.
At any rate, I'm glad we could help.
Cheers!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply