The multi-part identifier "ev.loc_n" could not be bound.

[capronton]

Every now and then the Query statement below will produce the error
"The multi-part identifier "ev.loc_n" could not be bound." Can anyone explain why and how I can prevent or correct query below?

Thanks.

[SQL]: select ev.loc_n "Location",
sum(case when ev.ts >= '2014-04-01 04:00:00.000' and ev.ts <= '2014-04-02 03:59:00.000' then ev.key7 else 0 end) "Apr 1",
sum(case when ev.ts >= '2014-04-02 04:00:00.000' and ev.ts <= '2014-04-03 03:59:00.000' then ev.key7 else 0 end) "Apr 2",
sum(case when ev.ts >= '2014-04-28 04:00:00.000' and ev.ts <= '2014-04-29 03:59:00.000' then ev.key7 else 0 end) "Apr 28",
sum(case when ev.ts >= '2014-04-29 04:00:00.000' and ev.ts <= '2014-04-30 03:59:00.000' then ev.key7 else 0 end) "Apr 29",
sum(case when ev.ts >= '2014-04-30 04:00:00.000' and ev.ts <= '2014-05-01 03:59:00.000' then ev.key7 else 0 end) "Apr 30"
[Error]: SQLSTATE = 42S22
Microsoft SQL Server Native Client 10.0
The multi-part identifier "ev.loc_n" could not be bound.

select ev.loc_n "Location", sum(case when ev.ts >= '2014-04-01 04:00:00.000' and ev.ts <= '2014-04-02 03:59:00.000' then ev.key7

[SQL]: from ev, ml
where ml.loc_n=ev.loc_n and ml.id=ev.id
and ev.ts >= '2014-04-01 04:00:00.000' and ev.ts <= '2014-05-01 03:59:00.000'
and run>100 and route<9000 and ev.fs in (1,2,3,4,5,6,7,8,9)
group by ev.loc_n
order by ev.loc_n
[Error]: You must login as a Genfare administrator to execute this statement

 

[SQLBill]

Is ev a real tablename or is it an alias? If it's an alias, I don't see where you have identified it as one:
FROM everything_table ev

If it is a real table, is there a loc_n column?

Also, the column alias should really have single quotes:
select ev.loc_n 'Location'

I would also suggest, for read-ability using AS:

selec ev.loc_n AS 'Location'

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[capronton]

Yes ev is a real table and loc_n is a real column. The alias doesn't seem to be the problem because it works with double quotes on several other queries and even the query below without an alias doesn't work.

[SQL]: select route,
sum(case when ev.ts >= '2014-04-01 04:00:00.000' and ev.ts <= '2014-04-02 03:59:00.000' then ev.key7 else 0 end) "Apr 1",
sum(case when ev.ts >= '2014-04-02 04:00:00.000' and ev.ts <= '2014-04-03 03:59:00.000' then ev.key7 else 0 end) "Apr 2",
sum(case when ev.ts >= '2014-04-03 04:00:00.000' and ev.ts <= '2014-04-04 03:59:00.000' then ev.key7 else 0 end) "Apr 3",
sum(case when ev.ts >= '2014-04-30 04:00:00.000' and ev.ts <= '2014-05-01 03:59:00.000' then ev.key7 else 0 end) "Apr 30"
[Error]: SQLSTATE = 42S22
Microsoft SQL Server Native Client 10.0
Invalid column name 'route'.

select route, sum(case when ev.ts >= '2014-04-01 04:00:00.000' and ev.ts <= '2014-04-02 03:59:00.000' then ev.key7 else 0 end) "Apr 1", sum(case when

[SQL]: from ev, ml
where ml.loc_n=ev.loc_n and ml.id=ev.id
and ev.ts >= '2014-04-01 04:00:00.000' and ev.ts <= '2014-05-01 03:59:00.000'
and run>100 and route<999 and ev.fs in (1,2,3,4,5,6,7,8,9)
group by route
order by route
[Error]: You must login as a Genfare administrator to execute this statement

 

[capronton]

The Query below has never failed, can you detect a difference between the query below and the other two I posted?

Thanks in advance for all your help.

select ev.loc_n "Location", run "Run", route "Route",
sum(case when ev.ts >= '2014-04-01 04:00:00.000' and ev.ts <= '2014-04-02 03:59:00.000' then ev.rdr_c else 0 end) "Apr 1",
sum(case when ev.ts >= '2014-04-02 04:00:00.000' and ev.ts <= '2014-04-03 03:59:00.000' then ev.rdr_c else 0 end) "Apr 2",
sum(case when ev.ts >= '2014-04-30 04:00:00.000' and ev.ts <= '2014-05-01 03:59:00.000' then ev.rdr_c else 0 end) "Apr 30"


from ev, ml
where ml.loc_n=ev.loc_n and ml.id=ev.id
and ev.ts >= '2014-04-01 04:00:00.000' and ev.ts <= '2014-05-01 03:59:00.000'
and run>100 and route<9000 and ev.fs in (1,2,3,4,5,6,7,8,9)
group by ev.loc_n, run, route
order by ev.loc_n, run, route

 

[capronton]

I changed the double quotes to single quotes and got the same error.

 

[fredericofonseca]

would you please post the full sql, not the output of whatever you are using.
posting as you do doesn't really show the full sql executed as there are extra lines for sure of things that should not be there at all.

Also will it be possible that the real source of your error is that you do not have permissions to execute that sql at all (at least according to the message you are showing up)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[capronton]

Apologize for not posting full sql; remember that for next time. But I figured out the problem. I had some extra "white space" toward the end of the query.

Thanks for looking into this!