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 for stored procedure and Null...

Hi,
I have write a stored procedure which makes update in a numeric (int) field.

Depending on data in other table, in some case the result of query get a
Null value instead a zero value...

How can I tell to Update query to NOT update field if the value is Null ?

I hope my word clear...

here the stored procedure:

UPDATE dbo.ANAUTENTI

SET dist1punti = dist1punti +

(SELECT SUM(TEMPIMPORTAZIONEDIST1.qnt * ANAARTICOLI.punti) AS totalepunti

FROM TEMPIMPORTAZIONEDIST1 INNER JOIN

ANAARTICOLI ON TEMPIMPORTAZIONEDIST1.codicearticolo =
ANAARTICOLI.codartdist1

WHERE (TEMPIMPORTAZIONEDIST1.piva = ANAUTENTI.piva))

WHERE (piva IN

(SELECT piva

FROM TEMPIMPORTAZIONEDIST1

GROUP BY piva))

Thanks in advance

Piero

Italypiero (g.pagnoni@.pesaroservice.com) writes:
> Depending on data in other table, in some case the result of query get a
> Null value instead a zero value...
> How can I tell to Update query to NOT update field if the value is Null ?

UPDATE tbl
SET col = col + coalesce((SELECT ...), 0)

The coalesce function takes list of arguments and returns the first non-NULL
value in the list, or NULL if all values are NULL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> ha scritto nel messaggio
news:Xns944153FCAB58Yazorman@.127.0.0.1...
> UPDATE tbl
> SET col = col + coalesce((SELECT ...), 0)
>
> The coalesce function takes list of arguments and returns the first
non-NULL
> value in the list, or NULL if all values are NULL.

It works fine !
Thank You very much !

Piero
Italysql

help for query

Hello,
I have a table "log" with the following fields:

-log_id(int)
-cust_id(string)
-type(int)
-service_id(int)

type may be 0,1 or 10

I need to fetch one row for each type for each service_id for a specific
cust_id.
That is, if I have 3 different service_id's and I assume that at least
one row for each type exists with the specific cust_id, I should get 3*3
= 9 rows: no more, no less.
I prefer not to use a stored procedure.
How may I do?
Is a self-join the right way?

Thank you
Regards

--
elimina carraro per rispondere via emailYou don't appear to have any criteria for
the one row you want if there are more than one.
If that's the case and log_id is unique, this should work

select max(log_id),cust_id,type,service_id
from log
group by cust_id,type,service_id|||markc600@.hotmail.com ha scritto:
> You don't appear to have any criteria for
> the one row you want if there are more than one.
> If that's the case and log_id is unique, this should work
> select max(log_id),cust_id,type,service_id
> from log
> group by cust_id,type,service_id

It work great, thanks!

I got the same result with the following, but was much more complicated:

select *
from log a, log b
where a.cust_id='myid' and a.type IN (0,10,1) and a.log_id=b.log_id
and b.log_id IN (select top 1 log_id from log c where
c.service_id=a.service_id and c.type=a.type)
order by a.service_id

--
elimina carraro per rispondere via email

help for creating stored procedure

ALTER PROCEDURE companyinsert

@.companyid INT IDENTITY(1,1) NOT NULL,
@.companyname varchar(20),
@.address1 varchar(30)

AS

INSERT INTO companymaster
( companyname, address1)
VALUES (@.companyname,@.address1)

i don't want the companyname having the same names are recorded again with the different company id..

Can anyone help me and modify my code according it's giving error...in the @.companyid.

It is being done in sql server 2005 with asp.net C# 2005

You cannot declare a parameter with IDENTITY property in a stored proc. I think what you are looking for is more along these lines. You might want to read up documentation on SCOPE_IDENTITY(). Briefly, it returns the Identity value that was created due to your INSERT.

ALTER PROCEDURE companyinsert@.companyidINT OUTPUT,@.companynamevarchar(20),@.address1varchar(30)ASBEGINSET NOCOUNT ONIFNOT EXISTS(SELECT *FROM companymasterWHERE CompanyName = @.companynameAND Address1 = address1)BEGININSERT INTO companymaster ( companyname, address1)VALUES (@.companyname,@.address1)SELECT @.companyid = SCOPE_IDENTITY()ENDSET NOCOUNT OFFEND

help for a search procedure

hello,
please help me to give me solution
i have a table
CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT
NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20),
CITY VARCHAR2(30), STREET VARCHAR2(40))
MY DATA IS
INSERT INTO T_STUDENT_MT VALUES (1,
'ABC1','FABC1','CCC1','SSS1','CTT1','STT
1');
INSERT INTO T_STUDENT_MT VALUES (2,
'ABC2','FABC2','CCC2','SSS2','CTT2','STT
2');
INSERT INTO T_STUDENT_MT VALUES (3,
'ABC3',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO T_STUDENT_MT VALUES (4,
'XXBC3','FCDD','ABDD',NULL,NULL,NULL,NUL
L);
PLEASE SUGGEST TO WRITE A PROCEDURE FOR
'CONDITION 1: If i pass iNAME to null it should NOT check in where
condition
'CONDITION 2:if i pass iNAME Parameter as 'A' IT HAS TO CHECK AS NAME
LIKE iNAME || '%'
CREATE PROCEDURE STUDENTSEARCH(iNAME VARCHAR2, iFATHERNAME VARCHAR2 ,
COUNTRY VARCHAR2, STATE VARCHAR2, CITY VARCHAR2, STREET VARCHAR2)
AS
BEGIN
--SINGE QUERY TO RETURN STUDENTT DATA
END;
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!kamal hussain wrote:
> hello,
> please help me to give me solution
> i have a table
> CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT
> NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20),
> CITY VARCHAR2(30), STREET VARCHAR2(40))
>
> MY DATA IS
> INSERT INTO T_STUDENT_MT VALUES (1,
> 'ABC1','FABC1','CCC1','SSS1','CTT1','STT
1');
> INSERT INTO T_STUDENT_MT VALUES (2,
> 'ABC2','FABC2','CCC2','SSS2','CTT2','STT
2');
> INSERT INTO T_STUDENT_MT VALUES (3,
> 'ABC3',NULL,NULL,NULL,NULL,NULL,NULL);
> INSERT INTO T_STUDENT_MT VALUES (4,
> 'XXBC3','FCDD','ABDD',NULL,NULL,NULL,NUL
L);
>
> PLEASE SUGGEST TO WRITE A PROCEDURE FOR
> 'CONDITION 1: If i pass iNAME to null it should NOT check in where
> condition
> 'CONDITION 2:if i pass iNAME Parameter as 'A' IT HAS TO CHECK AS NAME
> LIKE iNAME || '%'
>
> CREATE PROCEDURE STUDENTSEARCH(iNAME VARCHAR2, iFATHERNAME VARCHAR2 ,
> COUNTRY VARCHAR2, STATE VARCHAR2, CITY VARCHAR2, STREET VARCHAR2)
> AS
> BEGIN
> --SINGE QUERY TO RETURN STUDENTT DATA
> END;
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I'm assuming condition 1 means if the variable @.Name is NULL, then you
want all the rows.
Try this:
CREATE PROCEDURE STUDENTSEARCH(
@.NAME VARCHAR(50) = NULL
)
AS
SELECT [ID]
,[NAME]
,FATHERNAME
,COUNTRY
,STATE
,CITY
,STREET
FROM T_STUDENT_MT
WHERE ((@.NAME IS NOT NULL AND [NAME] = @.NAME)
OR (@.NAME IS NULL))
OR (LEN(@.NAME)=1 AND [NAME] LIKE @.NAME + '%')
Note: In SQL Server there isn't a data type of VARCHAR2 and the
concatenation symbol is the plus sign (+).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQi0xPoechKqOuFEgEQJmOwCg1I3RunB5ASmn
7rtRnj7EZDpoKTkAoMrL
wTkd98HDDTfkao35SpHRNH7S
=WkyP
--END PGP SIGNATURE--

Help building a #temp table

I have the following query:

--build query for #temp table
select 'L' as RECTYPE, identity(int, 1, 1) as JELINENO, max(i.jeno) + 1 as JENO, 'AJE' as JECODE,
--build the fiscal year, when month = October, November or December add 1 to actual year
FY = case
when datepart(month, GetDate()) = '10' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '11' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '12' then datepart(year, GetDate()) + 1
else datepart(year, GetDate())
end,
-- The PD starts in April and increases by 1 each month
PD = case
when datepart(month, GetDate()) = '1' then '10'
when datepart(month, GetDate()) = '2' then '11'
when datepart(month, GetDate()) = '3' then '12'
when datepart(month, GetDate()) = '4' then '1'
when datepart(month, GetDate()) = '5' then '2'
when datepart(month, GetDate()) = '6' then '3'
when datepart(month, GetDate()) = '7' then '4'
when datepart(month, GetDate()) = '8' then '5'
when datepart(month, GetDate()) = '9' then '6'
when datepart(month, GetDate()) = '10' then '7'
when datepart(month, GetDate()) = '11' then '8'
when datepart(month, GetDate()) = '12' then '9'
else 'error'
end,
'1' as SUBPD, 'N' as REVFLAG, 'Journal Entry from MAXIMO' as HEADDESC,
--If the issuetype is a return the AMOUNT is a negative value, multiply by -1
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
go
select * from #temp
drop table #temp

When I run this query, I get the following error:

Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#temp'.

I can find no reason for this error. If I run the query without the AMOUNT = case statement, it works fine. Can anyone help?

Thanks,

Dave

The mistake only arises in the AMOUNT=CASE statement since it's the only one (except from the MAX(i.jeno) column) that's accesing a real column in the database. The rest of the columns are calculated.

So, as you already have an aggregate function (the MAX) the rest of the columns from real tables must either have an aggregate function or the SELECT clause must contain a GROUP BY clause.

You can't form a SELECT with only one MAX column and the rest of them normal columns without telling SQL how it should group the results from where to choose the MAX value from.

If you really want to achieve that you have to calculate the MAX column either before in a variable or in the same SELECT with a sub-SELECT query.

IE: SELECT var1, var2, var3, (SELECT MAX(othervar) FROM table)

Hope i was clear.
Good luck,
Alan.

|||

First of all I would compress the code and rewrite it as following.

Please post DDLs as it's not clear what do you want to do. the problem is clear as you have a reference to the column m.linecost that is not in a group by list. Just to get rid of the error you can add a group by abs(m.linecost), but it doesn't look like what you want to do.

select
'L' as RECTYPE,
identity(int, 1, 1) as JELINENO,
max(i.jeno) + 1 as JENO,
'AJE' as JECODE,

--build the fiscal year, when month = October, November or December add 1 to actual year

FY = datepart(year, GetDate()) + (datepart(month, GetDate()) + 2) / 12,

-- The PD starts in April and increases by 1 each month

PD = (datepart(month, GetDate()) + 8) % 12 + 1,

'1' as SUBPD,

'N' as REVFLAG,

'Journal Entry from MAXIMO' as HEADDESC,

--If the issuetype is a return the AMOUNT is a negative value, multiply by -1

AMOUNT = abs(m.linecost)

into #temp

from matusetrans m, issuesreturns i

|||Dave -

You can't use an Aggregate Function on only one column without using a Group By for the other columns

So you either need to group by m.linecost OR put an Agrregate around it - like max() or min().

To simpify your query

select
max(i.jeno) + 1 as JENO, -- YOU USED MAX HERE
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
-- SO YOU EITHER NEED TO USE AN AGRREGRATE HERE OR
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
-- OR GROUP BY m.linecost HERE

Hope that helps

AWAL

Help - Conditional Checks within a SQL Query - other ways of doing it?

I'm trying to simplify a SQL Stored Procedure.

The query accepts an int, @.ItemTypeID int

I have the Query:

SELECT ... FROM ItemList WHERE ItemTypeID = @.ItemTypeID

or, if @.ItemTypeID is 0,

SELECT ... FROM ItemList

Is there a way to do this query without doing:

IF @.ItemTypeID = 0
BEGIN
...SELECT QUERY...
END
ELSE
BEGIN
...SELECT QUERY...
END

?

Try if you can combine the query by using joins. If not, then stay with the current. A Sql stored procedure is not meant to be very readable like C#, it goes by the execution plans. Hence even the dynamica sql should be used reluctantly.

|||

select... from itemList where (@.itemTypeID = 0) or (@.ItemTypeID = ItemTypeID)

|||

Yes, although what you have will execute the fastest.

If you feel it's not worth the performance difference, you can always do this:

SELECT * FROM ItemList WHERE (ItemTypeID=@.ItemTypeID OR @.ItemTypeID=0)

|||

Ok, thanks guys.

I guess the extra

WHERE (ItemTypeID=@.ItemTypeID OR @.ItemTypeID=0)

Adds a check on each row that is returned - which slows it down. I'll keep the current ugly method :)

Just sometimes I might have to check 5-6 things... which looks very ugly.

|||

I'd be amazed if you could measure the difference.