September 16, 2010 at 3:29 pm
Is this syntax correct? Keep getting error "Incorrect syntax '>'"
Parameter I am passing in is @pm = '~JKB~~ASL~'
No matter what I do on the parens it still errors.
AND PM1.PM70.Projects.Project_Manager = (case when @pm is null then PM1.PM70.Projects.Project_Manager
else CHARINDEX( '~' + PM1.PM70.Projects.Project_Manager + '~', @pm) > 0)) end)
September 16, 2010 at 3:51 pm
If you're trying to find @pm in PM1.PM70.Projects.Project_Manager, then @pm needs to be the first parameter in CHARINDEX, not the second.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 16, 2010 at 4:20 pm
Yes, like that.
AND PM1.PM70.Projects.Project_Manager = (case when @pm is null then PM1.PM70.Projects.Project_Manager
else CHARINDEX( '~' + PM1.PM70.Projects.Project_Manager + '~', @pm) > 0)) end)
Okay, this looks like a where clause?
Lining it up differently, we have (with the switch):
AND PM1.PM70.Projects.Project_Manager =
(CASE WHEN @pm IS NULL THEN PM1.PM70.Projects.Project_Manager
ELSE CharIndex(@PM, '~' + PM1.PM70.Projects.Project_Manager + '~') > 0
)
)
END)
What I'm seeing is:
1. CharIndex() returns a numeric value
2. CharIndex() > 0 returns a boolean. Since this is part of the ELSE, it should return the same datatype as PM1.PM70.Projects.Project_Manager, which appears to be string-based.
3. # of closing parenthesis doesn't match # of opening parenthesis (1 open, 3 close besides those in CharIndex)
4. END is out of position relative to the closing parenthesis.
If the two closing parenthesis immediately prior to the END are removed, that looks like it would take care of #3 & #4.
You might need to go:
convert(char(1), CharIndex(@PM, '~' + PM1.PM70.Projects.Project_Manager + '~') > 0)
to take care of the datatype issue in #2.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 17, 2010 at 7:55 am
September 17, 2010 at 7:59 am
I got same error with convert. I could send my whole sp if you would like?
AND PM1.PM70.Projects.Project_Manager =
(case when @pm is null then PM1.PM70.Projects.Project_Manager
ELSE convert (char(1), CHARINDEX(@PM, '~' + PM1.PM70.Projects.Project_Manager + '~') > 0
)
)
END)
September 17, 2010 at 8:22 am
Yes, you probably should just post the whole thing.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 17, 2010 at 8:25 am
Here is the whole sp....I think its something else in it but I can't figure it out. I am sooooo close.
USE [PM1Time]
GO
/****** Object: StoredProcedure [dbo].[GET_Hour_Info_NULL_MJH] Script Date: 09/16/2010 10:18:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GET_Hour_Info_NULL_MJH]
-- Add the parameters for the stored procedure here
@workedtask nvarchar(4000),
@pm nvarchar(4000),
@Company nvarchar(4000),
@BeginDateofCompletion datetime,
@EndDateofCompletion datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT PM1.PM70.Company_List.Company_Name, SUM(TimeAlias.Hours) AS ACTUAL, SUM(TimeAlias.Esthrs) AS EST, Sum(TimeAlias.Hours) - Sum(TimeAlias.Esthrs) as DIFNUM,
PM1.PM70.Projects.MJH_Number, PM1.PM70.Projects.Project_Manager,
PM1.PM70.Projects.Completion_Date, PM1.PM70.Projects.Description
FROM PM1.PM70.Projects CROSS JOIN TimeDetailNew TimeAlias
INNER JOIN PM1.PM70.Company_List ON PM1.PM70.Projects.Company_ID = PM1.PM70.Company_List.Company_ID
INNER JOIN PM1.PM70.Projects PM1Alias
ON '10' + TimeAlias.[Worked Project] = PM1Alias.MJH_Number
WHERE (PM1.PM70.Projects.MJH_Number = '10' + TimeAlias.[Worked Project]) AND
(CHARINDEX( '~' + TimeAlias.[Worked Task] + '~', @workedtask) > 0)
--(TimeDetailNEW.[Worked Task] IN ('A01', 'A02', 'A03', 'A04', 'A05', 'A06', 'A07', 'A08', 'A09', 'P01', 'P02', 'L01', 'L02','L03','L04','L05','L06','L07','S1','S2','S5','S7','S9','S10','C1','C4','C8','C11','C12','C13','C14','M01','M03','M04', '700','R01','R02','R03','R07','R08','D01', 'D02', 'G01', 'G02', 'G03', 'G04', 'G05', 'G06'))
AND (PM1.PM70.Projects.Completion_Date >= @BeginDateofCompletion)
AND (PM1.PM70.Projects.Completion_Date <= @EndDateofCompletion)
--AND (CHARINDEX( '~' + PM1.PM70.Company_List.Company_Name + '~', @Company) > 0)
AND PM1.PM70.Company_List.Company_Name = (case when @Company is null then PM1.PM70.Company_List.Company_Name else @Company end)
AND PM1.PM70.Projects.Project_Manager =
(case when @pm is null then PM1.PM70.Projects.Project_Manager
ELSE CHARINDEX(@PM, '~' + PM1.PM70.Projects.Project_Manager + '~') > 0
END)
GROUP BY PM1.PM70.Company_List.Company_Name, PM1.PM70.Projects.MJH_Number, PM1.PM70.Projects.Project_Manager, PM1.PM70.Projects.Completion_Date, PM1.PM70.Projects.[Description]
END
September 17, 2010 at 8:45 am
I think you're getting confused about what Case does. The purpose is to return a different result based on the which case condition is true, you're testing whether one condition is true, then your ELSE is a catch-all for anything that doesn't match that condition - this must be an actual value (or NULL), you're saying when @pm is NULL then return another column, otherwise evaluate an expression (CHARINDEX(@PM, '~' + PM1.PM70.Projects.Project_Manager + '~') > 0) - this isn't a value and you can't use case like this.
It looks like you're trying to do a catch-all query which either returns everything if the parameter isn't passed in, or filter the results if it is.
The classic way is to do this with an OR. e.g.:
AND ( (CHARINDEX(@PM,
'~' + PM1.PM70.Projects.Project_Manager + '~') > 0
OR @pm IS NULL)
But this does have performance considerations.
Have a look at Gail's excellent article for other options here:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Edit: I know it wasn't the purpose of your post, but I've just read through the rest of the procedure - there's an awful lot of things here that ring alarm bells about the database design!
September 17, 2010 at 9:39 am
While that will work, it's not efficient. The entire case statement is unnecessary anyway (see my example above).
I would recommend reading up on catch all query performance even though you've solved your immediate problem - also, looking at the joins in that query, I'd also look into referential integrity and normalization. 🙂
September 17, 2010 at 9:49 am
I TOTALLY agree about this database but....it was brought over from access which was created by a non DBA or webdeveloper and I was hired to get a site up and running in sql on asp.net. Then this app which the main office will not permit me a datamart but will provide a export in excel or .csv file of the hours for each task and PM is a real pain in the arse. Trust me....I am no DBA but the whole DB thing stinks! I would much rather normalize this whole thing but I gotta do with what I got till I can remap the whole mess. LOL!! In regards to your code I don't know how else to run the query if a selection is not made other then what I have. I tried the ISNULL but it did not work for me. I got no results back but with the way I am doing it now I do get results.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply