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 2012 Forums
 Transact-SQL (2012)
 case when

Author  Topic 

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-23 : 02:14:32
Dear Sir,

This query is run time error. how to solve it ?

Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) as total_counter,

case when trx_acc_amt>1000 and trx_acc_amt/total_counter then bonus
else 0 end as no_bonus

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-23 : 05:48:54
SELECT salesman_code,user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,
salesman_code4,salesman_code5,salesman_code6,

Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) as total_counter,

sum(case when trx_acc_amt>=1000 then trx_acc_amt-1000/ nullif(( Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) ),0 )
else 0 end) as bonus,

sum(case when trx_acc_amt<1000 then trx_acc_amt/( nullif(Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) ),0)
else 0 end) as nobonus


from trx_hdr
inner join user_hdr on user_hdr.user_id = trx_hdr.salesman_code
inner join user_dat on user_dat.user_id = trx_hdr.salesman_code
where user_group = 'CON' or user_pos = 'THRAP-USER' or salesman_code4<>'' or salesman_code5<>'' or salesman_code6<>''
group by salesman_code, user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-26 : 21:56:03
How to divide by a total from an alias column ?

----- error in this column
trx_acc_amt / bonus*100 as commission
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2015-01-27 : 01:13:09
you can use an alias only in an Order By clause

--------------------
Rock n Roll with SQL
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-27 : 20:53:23
you can use an alias only in an Order By clause , how to edit my query ?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-27 : 21:33:37
If you post the whole query we'll take a look ok
Go to Top of Page

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-28 : 02:39:53
You cannot divide by using alias column. If you want to simplify the query use CTE.

Regards
Viggneshwar A
Go to Top of Page
   

- Advertisement -