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

help coverting a varchar to a float

Hi,

I'm using the following code to convert a varchar to a float in a trigger.

declare @.acre varchar (6)

set @.acre_size = 0.0

select @.acre = plotsizeacre
from inserted

declare @.num int
select @.num = isnumeric (@.acre)

if @.num = 1
set @.acre_size = @.acre

This normally works fine, but I'm getting errors if the plotsizeacre field is 1,75

Casting to a float or converting to a float also gives errors.

Any ideas how to solve this problem? (The field would normally be filled in properly, eg 1.75).

Thanks in advance,

Ian

You cannot use ISNUMERIC to do strick checking. This function will return 1 for value that can be converted to any of the integer, numeric, float and money data types. The value '1,75' can be converted to money but not float. Your best option is to chnage the schema and modify the column to float. This will require modifications from the client side also to make sure that the value that user enters is typed accordingly. If you have to keep the varchar data type then you will have to perform the cleaning of the value yourself - meaning you have to check for bad formats and convert appropriately or error out gracefully.