Error on Total_qty = 1 - 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

General SQL Server Forums

New to SQL Server Programming

Error on Total_qty = 1

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources