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
ndinakar: