Help in Case statement

Hi,

I have to use case statement in where condition of a sql query. I get an error

Incorrect syntax near '<'.

Declare @.iPostingID int

set @.iPostingId = 1

Select PostingID from JobsDB_JobPostings where DateDiff(day,PostingDate,getdate())

case @.iPostingId

when 1 then '<1'

when 2 then '<7'

when 3 then '<30'

when 4 then '<60'

when 5 then '<90'

end

Can anyone help me please

Thanks,

Uma Ramiya

Hi,

Maybe you want this:

Code Snippet

SELECT

CASE PostingID

WHEN 1 THEN '<1'

WHEN 2 THEN '<7'

WHEN 3 THEN '<30'

WHEN 4 THEN '<60'

WHEN 5 THEN '<90'

END

FROM JobsDB_JobPostings WHERE DATEDIFF(DAY,PostingDate,GETDATE())

Regards,

Janos

|||The parameter @.iPostingId is used for comparison and not the column field.|||Well, you need the select before the CASE|||Hi,

We use similar construct in our daily life here. Have a look on following query, this will really work.

DECLARE @.iPostingID int

SET @.iPostingId = 1

SELECT
PostingID
FROM
JobsDB_JobPostings
WHERE
DateDiff(day,PostingDate,getdate()) < (CASE
WHEN @.iPostingId = 1 THEN 1
WHEN @.iPostingId = 2 THEN 7
WHEN @.iPostingId = 3 THEN 30
WHEN @.iPostingId = 4 THEN 60
WHEN @.iPostingId = 5 THEN 90
END)|||

Hi I have the following case.i would like a precise and efficient SQL Statement for it.

I have 7 parameters which are used for comparison. I need to run the query with these parameters. which ever is not null should be compared and the null ones should be ignored. AS a result I end with lots of "if' statements as the parameters form a lot of copmbination. Is there any way to cut short the code

ALTER PROCEDURE [dbo].[JobsDb_Resumes_SelectForMatchingSkills]

@.sSkill varchar(50),

@.iCountryID int=NULL,

@.iStateID int=NULL,

@.iJobTypeID int=NULL,

@.iMinSal int=NULL,

@.iMaxSal int NULL,

@.iPeriodPosted int NULL

AS

IF @.iCountryID!=NULL AND @.iStateID=NULL AND @.iJobTypeID = NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID = NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted != NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC