problem with case in a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

problem with case in a query

Author

Topic

shantanu88d
Starting Member

35 Posts

Posted - 2011-05-18 : 01:45:00

hi,
I have a table bill_info with phone_no field.
This phone_no is bound in table ph_no_alottment to field alottment_group which can be Employee,Non-Employee, branch, residence. In same table there is a field alotted_to which contains emp id if alottment group is Employee, name if alottment group is other than Employee.

I have a table emp_master, which contain emp_id and emp_name
Now i want a query which gets details from bill_info table like

phone_no, bill_amt, user_name, bill_paid

the field user_name will be name of user pf phone_number. Now for alottment_group other than Employee I am storing names directly. But in case of employee group i am storing emp_id. So in this case query I want that when alottment group is Employee then emp name will be found from emp_master table, otherwise user_name will be same as alotted_to mentioned in ph_no_alottment table
I tried this query:


select b.phone_no, 
	bill_date, 
	alottment_group,
	alotted_to = 
		CASE 
		WHEN 'Employee'
		THEN (select emp_name from emp_master where pf_no = alotted_to)
		END, 
	payment_mode, 
        bill_paid
from bill_info b, ph_no_alottment p
where b.phone_no = p.phone_no

but it's giving me this error
Msg 4145, Level 15, State 1, Line 7
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ','.

Please help !!

There are no failures...only experiences!

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-18 : 02:13:51

Ok you cant use Select statement in case. Because your emp_name may have multiple records and case statement take only true of false.
But you can first join all three table emp_master E,bill_info b, ph_no_alottment p once you joined then you can use this case statement like
Case When <alias Name>.alotted_to='Employee' then E.emp_name end

In Love... With Me!

shantanu88d
Starting Member

35 Posts

Posted - 2011-05-18 : 02:35:48

I did what you said, for that I used this query:


select 
	temp.phone_no,
	bill_date,
	alottment_group,
	alotted_to = 
		CASE 
			WHEN alotted_to = 'Employee'
			THEN emp_name
		END,
	payment_mode, 
	bill_paid, 
	emp_name 
from
	(select b.phone_no, 
		bill_date, 
		alottment_group,
		alotted_to,
		payment_mode, bill_paid
	from bill_info b, ph_no_alottment p
	where b.phone_no = p.phone_no) temp left outer join emp_master
on temp.alotted_to = emp_master.pf_no

but it giveas me this result:


phone_no      bill_date                 alottment_group   alotted_to  payment_mode  bill_amt  emp_name
-----------------------------------------------------------------------------------------------------------
9819569290    2011-05-06 00:00:00.000	Employee	  NULL	      acc	    2000.00   Sample Sample
9969007385    2011-05-10 00:00:00.000	Employee	  NULL	      acc	    350.00    Anil Deshmukh

I tried alotted_to = emp_name but it gave error ! :O

There are no failures...only experiences!

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-18 : 03:24:52

What is your alotted_to data type and what it contain data?

In Love... With Me!

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-18 : 03:40:09

There may be only one issue your alotted_to not contain any Employee record so you are getting null.

In Love... With Me!

shantanu88d
Starting Member

35 Posts

Posted - 2011-05-19 : 00:35:20

Hey, entire query was right. There was logical error or more precisely, typing mistake.
I had written alotted_to instead of alottment_group. Null was coming because there was 'Employee' value in alotted_to column. So now it works !!


select b.phone_no, 
	bill_date, 
	alottment_group,
	alotted_to = 
		CASE 
		WHEN alottment_group = 'Employee'
		THEN (select emp_name from emp_master where pf_no = alotted_to)
		ELSE alotted_to
	        END, 
	payment_mode, bill_paid
from bill_info b, ph_no_alottment p
where b.phone_no = p.phone_no

Thanks a lot for help !! :)

There are no failures...only experiences!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources