using an alias in the where clause

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 2000 Forums

SQL Server Development (2000)

using an alias in the where clause

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2005-09-27 : 07:42:07

I am using the alias expenditure to collate the values. What I want to do is use the alias in the where clause but its not liking my syntax. I know I have done it before but I really can't remember how I got round it.

The syntax in bold is the bit I am having a problem with

select job_id, cast(sum(case when type = 'I' then valuenet
when type = 'C' then (-1 * valuenet)
else 0 end)as decimal(9, 2)) as 'expenditure', count(distinct s.job_id) as 'jobs', cast(round(sum(case when type = 'I' then valuenet
when type = 'C' then (-1 * valuenet)
else 0 end) / count(distinct s.job_id), 2) as decimal(9, 2)) as 'average'
from tblSalesInvoice s
join tblpurchquote p on p.est_id = s.est_id
where s.invoicedate < '2005-04-01' and s.invoicedate >= '2005-03-01' and cl_code='TEST' and p.ordered = 'y' and 'expenditure' > 0
group by job_id

Could you please help

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-27 : 07:47:44

I'm sorry, but you have to use the entire cast statement here :(

cast(sum(case when type = 'I' then valuenet
when type = 'C' then (-1 * valuenet)
else 0 end)as decimal(9, 2))

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-27 : 07:47:49

You should use derived table
Try this


Select * from 
	(
	select job_id, cast(sum(case when type = 'I' then valuenet
	when type = 'C' then (-1 * valuenet)
	else 0 end)as decimal(9, 2)) as 'expenditure', count(distinct s.job_id) as 'jobs', cast(round(sum(case when type = 'I' then valuenet 
	when type = 'C' then (-1 * valuenet)
	else 0 end) / count(distinct s.job_id), 2) as decimal(9, 2)) as 'average' 
	from tblSalesInvoice s 
	join tblpurchquote p on p.est_id = s.est_id 
	where s.invoicedate < '2005-04-01' and s.invoicedate >= '2005-03-01' and cl_code='TEST' and p.ordered = 'y' 
	group by job_id
	)
T where 'expenditure' > 0

Madhivanan

Failing to plan is Planning to fail

jparker
Posting Yak Master

118 Posts

Posted - 2005-09-27 : 07:53:31

sorry madhivanan that doesn't work for me I get

Syntax error converting the varchar value 'expenditure' to a column of data type int.

which is actually what I had before as an error message

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-27 : 08:15:38

What is the datatype of valuenet?

Madhivanan

Failing to plan is Planning to fail

jparker
Posting Yak Master

118 Posts

Posted - 2005-09-27 : 08:17:16

Its ok I have worked it out. I needed to use the HAVING keyword as I was using a group by

select job_id, cast(sum(case when type = 'I' then valuenet
when type = 'C' then (-1 * valuenet)
else 0 end)as decimal(9, 2)) as 'expenditure', count(distinct s.job_id) as 'jobs', cast(round(sum(case when type = 'I' then valuenet
when type = 'C' then (-1 * valuenet)
else 0 end) / count(distinct s.job_id), 2) as decimal(9, 2)) as 'average'
from tblSalesInvoice s
join tblpurchquote p on p.est_id = s.est_id
where s.invoicedate < '2005-04-01' and s.invoicedate >= '2005-03-01' and cl_code='TEST' and p.ordered = 'y'
group by job_id
HAVING cast(sum(case when type = 'I' then valuenet
when type = 'C' then (-1 * valuenet)
else 0 end)as decimal(9, 2)) > 0

Thanks for you guidance though ditch and Madhivanan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-27 : 08:49:52

quote:

Originally posted by madhivanan

You should use derived table
Try this


Select * from 
	(
	select job_id, cast(sum(case when type = 'I' then valuenet
	when type = 'C' then (-1 * valuenet)
	else 0 end)as decimal(9, 2)) as 'expenditure', count(distinct s.job_id) as 'jobs', cast(round(sum(case when type = 'I' then valuenet 
	when type = 'C' then (-1 * valuenet)
	else 0 end) / count(distinct s.job_id), 2) as decimal(9, 2)) as 'average' 
	from tblSalesInvoice s 
	join tblpurchquote p on p.est_id = s.est_id 
	where s.invoicedate < '2005-04-01' and s.invoicedate >= '2005-03-01' and cl_code='TEST' and p.ordered = 'y' 
	group by job_id
	)
T where expenditure > 0

lose the quotes and you are good to go .....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-27 : 08:54:19

Thanks Jeff
I think in Access this is valid

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources