invalid column name - 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

invalid column name

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-26 : 05:38:41

Dear Sir,

Please see the total_counter this column error ?
-------------------------------------------------------
My Query
case when salesman_code is null or salesman_code=' ' then 0 else 1 end +
case when salesman_code2 is null or salesman_code2=' ' then 0 else 1 end +
case when salesman_code3 is null or salesman_code3=' ' then 0 else 1 end +
case when salesman_code4 is null or salesman_code4=' ' then 0 else 1 end +
case when salesman_code5 is null or salesman_code5=' ' then 0 else 1 end +
case when salesman_code6 is null or salesman_code6=' ' then 0 else 1 end as total_counter,
----------------------------------------------------------

- error in this column " total_counter" is invalid column name
case when Total_counter>0 then
trx_acct_amt*100
else 0
end as percent_ratio

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-26 : 05:49:15

Please post full query...

Select

Case...

From MyTable...

We are the creators of our own reality!

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-26 : 06:01:28

Dear Sir,
We query error " trx_acc_amt/ bonus*100 as perd " , prompt "Bonus " the invalid column name.

SELECT
sh_code as "??",
user_name AS "????",
user_hdr.user_id as "????",
trx_acc_amt AS "??????? ",

trx_date AS "????",
trx_no AS "??",

salesman_code AS "??",
salesman_code2 AS "??",
salesman_code3 AS "??",
salesman_code4 AS "???",
salesman_code5 AS "???",
salesman_code6 AS "???",
user_group "??",
case when salesman_code is null or salesman_code=' ' then 0 else 1 end +
case when salesman_code2 is null or salesman_code2=' ' then 0 else 1 end +
case when salesman_code3 is null or salesman_code3=' ' then 0 else 1 end +
case when salesman_code4 is null or salesman_code4=' ' then 0 else 1 end +
case when salesman_code5 is null or salesman_code5=' ' then 0 else 1 end +
case when salesman_code6 is null or salesman_code6=' ' then 0 else 1 end as total_counter,

case when trx_acc_amt<1000
then trx_acc_amt/nullif((Sum(
case when salesman_code is null or salesman_code=' ' then 0 else 1 end +
case when salesman_code2 is null or salesman_code2=' ' then 0 else 1 end +
case when salesman_code3 is null or salesman_code3=' ' then 0 else 1 end +
case when salesman_code4 is null or salesman_code4=' ' then 0 else 1 end +
case when salesman_code5 is null or salesman_code5=' ' then 0 else 1 end +
case when salesman_code6 is null or salesman_code6=' ' then 0 else 1 end)),0)
else
trx_acc_amt / 2
--- end as "?????????"
end as bonus,
------ Error in here.
trx_acc_amt/ bonus*100 as perd

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 trx_acc_amt>0 and user_group='CON' and salesman_code<>'Company' and salesman_code<>'Marketing' and salesman_code<>'MKT' and salesman_code<>'BOSS'
group by user_name,user_hdr.user_id,salesman_code, user_group,trx_acc_amt,trx_date,trx_no,sh_code,
salesman_code2,
salesman_code3,
salesman_code4,
salesman_code5,
salesman_code6

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-26 : 06:13:01

Are you trying to call alias bonus? if its not an actual column name you will need to wrap it inside a cte select to call it.

else
trx_acc_amt / 2
--- end as "?????????"
end as bonus,
------ Error in here.
trx_acc_amt/ bonus*100 as perd

We are the creators of our own reality!

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-26 : 20:48:24

I am trying to call alias bonus, this field is not an actual column name , how to edit this query ?

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-27 : 06:18:47

Something like this, again depending on what you are trying to see.

With tx
As
(
SELECT
sh_code as "??",
user_name AS "????",
user_hdr.user_id as "????",
trx_acc_amt AS "??????? ",

trx_date AS "????",
trx_no AS "??",

salesman_code AS "??",
salesman_code2 AS "??",
salesman_code3 AS "??",
salesman_code4 AS "???",
salesman_code5 AS "???",
salesman_code6 AS "???",
user_group "??",
case when salesman_code is null or salesman_code=' ' then 0 else 1 end +
case when salesman_code2 is null or salesman_code2=' ' then 0 else 1 end +
case when salesman_code3 is null or salesman_code3=' ' then 0 else 1 end +
case when salesman_code4 is null or salesman_code4=' ' then 0 else 1 end +
case when salesman_code5 is null or salesman_code5=' ' then 0 else 1 end +
case when salesman_code6 is null or salesman_code6=' ' then 0 else 1 end as total_counter,

case when trx_acc_amt<1000
then trx_acc_amt/nullif((Sum(
case when salesman_code is null or salesman_code=' ' then 0 else 1 end +
case when salesman_code2 is null or salesman_code2=' ' then 0 else 1 end +
case when salesman_code3 is null or salesman_code3=' ' then 0 else 1 end +
case when salesman_code4 is null or salesman_code4=' ' then 0 else 1 end +
case when salesman_code5 is null or salesman_code5=' ' then 0 else 1 end +
case when salesman_code6 is null or salesman_code6=' ' then 0 else 1 end)),0)
else
trx_acc_amt / 2
--- end as "?????????"
end as bonus
------ Error in here.
--trx_acc_amt/ bonus*100 as perd

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 trx_acc_amt>0 and user_group='CON' and salesman_code<>'Company' and salesman_code<>'Marketing' and salesman_code<>'MKT' and salesman_code<>'BOSS'
group by user_name,user_hdr.user_id,salesman_code, user_group,trx_acc_amt,trx_date,trx_no,sh_code,
salesman_code2,
salesman_code3,
salesman_code4,
salesman_code5,
salesman_code6
)
Select -- put alias's here for calculations
convert(varchar, convert(decimal(5,2), 100.0 * (trx_acc_amt)/(bonus)))+'%' as percentof,
Sum(total_counter) TotalCount,
Sum(bonus) TotalBonus
From tx
-- where some condition
-- Group By

We are the creators of our own reality!

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-27 : 21:10:51

convert(varchar, convert(decimal(5,2), 100.0 * (trx_acc_amt)/(bonus)))+'%' as percentof, I am try this but prompt error " invalid column name " . ? how to solve it ?

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-28 : 04:40:33

What column is it saying? post the error message.

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources