Invalid Column?

Two things I could use help with. First one is I know there is probbaly a
way better method to do this and I would love someone to show me what it is
having just begun in the SQL trans language. The 2nd is, I am curious why,
even though its clearly not the best method, why it doesn't work and returns
an error "invalid column name 'tod' " for the line containing the 'on'
statement for the join. It looks like it should work, but doesn't...:(
tnx
select datepart(hh,a.timearrived) as TOD, count(a.calldate) as total,
b.total
from tbldatapcr a
inner join
(
select datepart(hh,timearrived) as TOD, count(calldate) as Total
from tbldatapcr
where calldate between '09/01/04' and '12/01/04'
and timearrived is not null
group by datepart(hh,timearrived)
) b
on a.tod = b.tod
where calldate between '09/01/04' and '12/01/04'
and timearrived is not null
and cdisp = 40
group by datepart(hh,a.timearrived)
order by datepart(hh,a.timearrived)You need to specify for the join
"ON a.timearrived = b.tod"
"Dave S." <davidstedman@.colliergov.net> wrote in message
news:uIVaAEnHFHA.2744@.tk2msftngp13.phx.gbl...
> Two things I could use help with. First one is I know there is probbaly a
> way better method to do this and I would love someone to show me what it
is
> having just begun in the SQL trans language. The 2nd is, I am curious why,
> even though its clearly not the best method, why it doesn't work and
returns
> an error "invalid column name 'tod' " for the line containing the 'on'
> statement for the join. It looks like it should work, but doesn't...:(
> tnx
>
> select datepart(hh,a.timearrived) as TOD, count(a.calldate) as total,
> b.total
> from tbldatapcr a
> inner join
> (
> select datepart(hh,timearrived) as TOD, count(calldate) as Total
> from tbldatapcr
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> group by datepart(hh,timearrived)
> ) b
> on a.tod = b.tod
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> and cdisp = 40
> group by datepart(hh,a.timearrived)
> order by datepart(hh,a.timearrived)
>
>|||If TOD doesn't exist in the table then you can't reference it unless it
exists in a derived table query. It looks like you also need to add
B.total to the outer query's GROUP BY list.
Here's a shorter (and untested) version of the query:
SELECT DATEPART(HH,timearrived) AS tod,
COUNT(calldate) AS total,
COUNT(CASE WHEN cdisp=40 THEN calldate END) AS total
FROM tbldatapcr
WHERE calldate BETWEEN '20040901' and '20041201'
AND timearrived IS NOT NULL
GROUP BY DATEPART(HH,timearrived)
Use the format I have used for date literals. Your version isn't safe
under all connection settings and may cause errors in a production
environment.
David Portas
SQL Server MVP
--|||Dave,

> Two things I could use help with. First one is I know there is probbaly a
> way better method to do this and I would love someone to show me what it i
s
> having just begun in the SQL trans language.
It is always welcome when you post DDL, sample data and expected result. As
you can see there no one piece of info about your tables, constraints, etc.
What are you trying to accomplish?
Are you looking for a guess?

> even though its clearly not the best method, why it doesn't work and retur
ns
> an error "invalid column name 'tod' " for the line containing the 'on'
> statement for the join. It looks like it should work, but doesn't...:(
There is not a column named "tod" in table "tbldatapcr". You can not use a
column alias in the WHERE clause.
...
> on a.tod = b.tod
...
should be:
...
on datepart(hh,a.timearrived) = b.tod
...
The reason you can reference "b.tod" is because "b" is a derived table.
AMB
"Dave S." wrote:

> Two things I could use help with. First one is I know there is probbaly a
> way better method to do this and I would love someone to show me what it i
s
> having just begun in the SQL trans language. The 2nd is, I am curious why,
> even though its clearly not the best method, why it doesn't work and retur
ns
> an error "invalid column name 'tod' " for the line containing the 'on'
> statement for the join. It looks like it should work, but doesn't...:(
> tnx
>
> select datepart(hh,a.timearrived) as TOD, count(a.calldate) as total,
> b.total
> from tbldatapcr a
> inner join
> (
> select datepart(hh,timearrived) as TOD, count(calldate) as Total
> from tbldatapcr
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> group by datepart(hh,timearrived)
> ) b
> on a.tod = b.tod
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> and cdisp = 40
> group by datepart(hh,a.timearrived)
> order by datepart(hh,a.timearrived)
>
>
>|||Sorry, forgot this part
column_alias can be used in an ORDER BY clause. However, it cannot be used
in a WHERE, GROUP BY, or HAVING clause. If the query expression is part of a
DECLARE CURSOR statement, column_alias cannot be used in the FOR UPDATE
clause.
"SW" <simon.worth@.gmail.com> wrote in message
news:%23xexMMnHFHA.1476@.TK2MSFTNGP09.phx.gbl...
> You need to specify for the join
> "ON a.timearrived = b.tod"
> "Dave S." <davidstedman@.colliergov.net> wrote in message
> news:uIVaAEnHFHA.2744@.tk2msftngp13.phx.gbl...
a
> is
why,
> returns
>|||Here is the answer.
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"Dave S." wrote:

> Two things I could use help with. First one is I know there is probbaly a
> way better method to do this and I would love someone to show me what it i
s
> having just begun in the SQL trans language. The 2nd is, I am curious why,
> even though its clearly not the best method, why it doesn't work and retur
ns
> an error "invalid column name 'tod' " for the line containing the 'on'
> statement for the join. It looks like it should work, but doesn't...:(
> tnx
>
> select datepart(hh,a.timearrived) as TOD, count(a.calldate) as total,
> b.total
> from tbldatapcr a
> inner join
> (
> select datepart(hh,timearrived) as TOD, count(calldate) as Total
> from tbldatapcr
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> group by datepart(hh,timearrived)
> ) b
> on a.tod = b.tod
> where calldate between '09/01/04' and '12/01/04'
> and timearrived is not null
> and cdisp = 40
> group by datepart(hh,a.timearrived)
> order by datepart(hh,a.timearrived)
>
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Here is the answer.
> Dynamic Search Conditions in T-SQL
> http://www.sommarskog.se/dyn-search.html
>
> AMB
> "Dave S." wrote:
>