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.
| Author |
Topic |
|
usafelix
Posting Yak Master
165 Posts |
 Posted - 2015-01-27 : 22:38:49
|
Dear All, who can help me to resolve it error ?
SELECT
trx_hdr.sh_code ,
user_hdr.user_name ,
user_hdr.user_id as ",
trx_hdr.trx_acc_amt ,
trx_hdr.trx_date ",
trx_hdr.trx_no ",
trx_hdr.salesman_code ",
trx_hdr.salesman_code2 ",
trx_hdr.salesman_code3 ",
trx_hdr.salesman_code4 ",
trx_hdr.salesman_code5 ",
trx_hdr.salesman_code6 ",
user_dat.user_group "",
sum(trx_dat.item_Qty) as total_qty,
case when trx_hdr.salesman_code is null or trx_hdr.salesman_code=' ' then 0 else 1 end +
case when trx_hdr.salesman_code2 is null or trx_hdr.salesman_code2=' ' then 0 else 1 end +
case when trx_hdr.salesman_code3 is null or trx_hdr.salesman_code3=' ' then 0 else 1 end +
case when trx_hdr.salesman_code4 is null or trx_hdr.salesman_code4=' ' then 0 else 1 end +
case when trx_hdr.salesman_code5 is null or trx_hdr.salesman_code5=' ' then 0 else 1 end +
case when trx_hdr.salesman_code6 is null or trx_hdr.salesman_code6=' ' then 0 else 1 end as "total_counter",
case when trx_hdr.trx_acc_amt<1000 and sum(total_qty) = 1
then
trx_hdr.trx_acc_amt/nullif((Sum(
case when trx_hdr.salesman_code is null or trx_hdr.salesman_code=' ' then 0 else 1 end +
case when trx_hdr.salesman_code2 is null or trx_hdr.salesman_code2=' ' then 0 else 1 end +
case when trx_hdr.salesman_code3 is null or trx_hdr.salesman_code3=' ' then 0 else 1 end +
case when trx_hdr.salesman_code4 is null or trx_hdr.salesman_code4=' ' then 0 else 1 end +
case when trx_hdr.salesman_code5 is null or trx_hdr.salesman_code5=' ' then 0 else 1 end +
case when trx_hdr.salesman_code6 is null or trx_hdr.salesman_code6=' ' then 0 else 1 end)),0)
else
0
end as "total man "
from trx_hdr
|
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2015-01-28 : 04:48:44
|
The code is very messy, double quotes and other formattkng issues. Try this
SELECT
trx_hdr.sh_code ,
user_hdr.user_name ,
user_hdr.user_id ,
trx_hdr.trx_acc_amt,
trx_hdr.trx_date ,
trx_hdr.trx_no ,
trx_hdr.salesman_code ,
trx_hdr.salesman_code2 ,
trx_hdr.salesman_code3 ,
trx_hdr.salesman_code4 ,
trx_hdr.salesman_code5 ,
trx_hdr.salesman_code6 ,
user_dat.user_group,
sum(trx_dat.item_Qty) as total_qty,
case when trx_hdr.salesman_code is null or trx_hdr.salesman_code=' ' then 0 else 1 end +
case when trx_hdr.salesman_code2 is null or trx_hdr.salesman_code2=' ' then 0 else 1 end +
case when trx_hdr.salesman_code3 is null or trx_hdr.salesman_code3=' ' then 0 else 1 end +
case when trx_hdr.salesman_code4 is null or trx_hdr.salesman_code4=' ' then 0 else 1 end +
case when trx_hdr.salesman_code5 is null or trx_hdr.salesman_code5=' ' then 0 else 1 end +
case when trx_hdr.salesman_code6 is null or trx_hdr.salesman_code6=' ' then 0 else 1 end as "total_counter",
case when trx_hdr.trx_acc_amt<1000 and sum(total_qty) = 1
then
trx_hdr.trx_acc_amt/nullif((Sum(
case when trx_hdr.salesman_code is null or trx_hdr.salesman_code=' ' then 0 else 1 end +
case when trx_hdr.salesman_code2 is null or trx_hdr.salesman_code2=' ' then 0 else 1 end +
case when trx_hdr.salesman_code3 is null or trx_hdr.salesman_code3=' ' then 0 else 1 end +
case when trx_hdr.salesman_code4 is null or trx_hdr.salesman_code4=' ' then 0 else 1 end +
case when trx_hdr.salesman_code5 is null or trx_hdr.salesman_code5=' ' then 0 else 1 end +
case when trx_hdr.salesman_code6 is null or trx_hdr.salesman_code6=' ' then 0 else 1 end)),0)
else
0
end as totalman
from trx_hdr
We are the creators of our own reality! |
 |
|
|
|
|
|
|
|
Error on Total_qty = 1