Sql case query with different condition

Question

1.00/5 (1 vote)

See more:

SQL-Server-2014

I HAVE 2 ITEM ,ITEM NAME 'CHAIR' AND 'BASKET' AND A QUANTITY...I NEED THAT WHERE QUANTITY > 20 AND ITEM='CHAIR'; AND FOR ITEM='BASKET' ,QUANTITY >'09' SHOULD COME IN OUTPUT..

I FAILED ,NULLS ARE COMING..

I NEED THAT FOR ITEM CHAIR DQTY SHOULD SHOW >20 AND FOR ITEM BASKET DQTY SHOULD BE > 9,,IT SHOULD BE SHOWED IN MY OUTPUT..I DON'T WANT OTHER DQTY NUMBER IN MY OUTPUT

USING SQL-server-2014

What I have tried:

SELECT 
CASE WHEN op.dqty >  '20' AND ti.SubCategoryName ='CHAIR' THEN ti.SubCategoryName 
 WHEN op.dqty >  '9' AND ti.SubCategoryName ='BASKET' THEN ti.SubCategoryName  ELSE NULL END AS SubCategoryName  ,


TI.SubCategoryName,op.site,sasitename,symn,dqty,count(distinct(orna)) as orna
from ABCD

WHERE TI.SubCategoryName in ('CHAIR' ,'BASKET') 
GROUP BY TI.SubCategoryName,op.site,sasitename,symn,dqty;

Posted 8-Jan-18 21:01pm

Member 13518187

Updated 8-Jan-18 23:38pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

debasish mishra · Answer 1 · 2018-01-08T21:14:00

Solution 1

How can you compare quantity with a string like '20' and '9'?
Just Remove '' and make sure your column data type is integer. if datatype is not integer then in sql convert your column data to integer and make sure for data which are not in integer format, o/p it as 0.Then obviously 0>20 and 0>9 will always false for non integer values and you will get nulls.Please up vote if this helps you out.

Posted 8-Jan-18 21:14pm

debasish mishra

Updated 8-Jan-18 21:17pm

v3

Comments

Member 13518187 9-Jan-18 3:19am

@debasish mishra ......it is already integer and i have written > 20 only ..by mistake i wrote here

Member 13518187 9-Jan-18 3:19am

but output is not coming correct as per my question

[no name] 9-Jan-18 3:35am

You have not mentioned any table with alias TI and OP.Please write the sql query properly else it would be hard to know what is going wrong

Debasis_Saha · Answer 2 · 2018-01-08T21:50:00

Solution 2

You use op.dqty AND ti.SubCategoryName that means u have two tables. First check without case condition that ur normal query return all data or not. other wise provide the full query with join clause so that it clearly understandable the table relation.

Posted 8-Jan-18 21:50pm

Debasis_Saha

Comments

Member 13518187 9-Jan-18 4:26am

SELECT
CASE WHEN op.dqty > '20' AND ti.SubCategoryName ='CHAIR' THEN ti.SubCategoryName
WHEN op.dqty > '9' AND ti.SubCategoryName ='BASKET' THEN ti.SubCategoryName ELSE NULL END AS SubCategoryName ,

TI.SubCategoryName,op.site,op.sasitename,op.symn,op.dqty,count(distinct(orna)) as orna
from ABCD op
inner join item ti

WHERE TI.SubCategoryName in ('CHAIR' ,'BASKET')
GROUP BY TI.SubCategoryName,op.site,op.sasitename,op.symn,op.dqty;

Debasis_Saha · Answer 3 · 2018-01-08T23:38:00

As per ur query, i write down the below script. please check this.

CREATE TABLE item
(
	Id					int,
	SubCategoryName		nvarchar(100)
)
go
insert into item values (1,'CHAIR')
go
insert into item values (2,'BASKET')
go
insert into item values (3,'TOOL')
go
CREATE TABLE ABCD
(
	ID			INT,
	CategoryId	Int,
	[site]		nvarchar(100),
	dqty		nvarchar(100),
	sasitename	nvarchar(100),
	symn		nvarchar(100),
	orna		int
)
GO
insert into abcd values (1,1,'00',25,'ss','22',1)
go
insert into abcd values (1,1,'00',15,'ss1','221',1)
go
insert into abcd values (1,2,'00',25,'ss2','222',2)
go
insert into abcd values (1,2,'00',18,'ss3','223',2)
go
insert into abcd values (1,1,'00',45,'ss4','224',1)
go
insert into abcd values (1,3,'00',25,'ss5','225',0)
go
insert into abcd values (1,3,'00',25,'ss6','226',0)
go
SELECT 
	CASE	WHEN op.dqty > 20 AND ti.SubCategoryName ='CHAIR' THEN ti.SubCategoryName 
			WHEN op.dqty > 9 AND ti.SubCategoryName ='BASKET' THEN ti.SubCategoryName 
	ELSE NULL END AS SubCategoryName , 
	TI.SubCategoryName,
	op.[site],
	op.sasitename,
	op.symn,
	op.dqty,
	count(distinct(orna)) as orna 
from ABCD op 
inner join item ti on op.CategoryId=ti.id
WHERE TI.SubCategoryName in ('CHAIR' ,'BASKET') 
GROUP BY TI.SubCategoryName,op.[site],op.sasitename,op.symn,op.dqty;

Hope this will solve ur problem. Please up vote if this helps you out.