SQL statment help

ccmcbride · Post by **ccmcbride** » Mon 04 Jun 2007 20:52

the query :
Select 1 as rType, m.UID, m.OrderNumber, M.Closed,
M.Invoiced, M.BillingStat,
t.UID as oUID, t.TechUID, t.DateIn, t.Tech, T.TimeStart, t.TimeEnd,
M.OrderStatus, OS.Color, t.IsSub, t.TotalHours, T.StartDateTime, T.EndDateTime, 1 as Layer
from Service M, OrderTech T
left outer join OrderStatus OS on M.OrderStatusUID = OS.UID
where M.projectUID = :UID and M.UID = T.OrderUID
and (:OrderUID is null or m.UID = :OrderUID)

The error (from msSQL)
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "M.OrderStatusUID" could not be bound.

I have also tried including M.OrderStatusUID in the query, but still get the same error.
Any ideas?

Antaeus · Post by **Antaeus** » Tue 05 Jun 2007 06:52

Please provide a script to create tables that appear in your query. You can send the script to me at evgeniyd*crlab*com, or post it to this form.
Does the problem appear if you open this query with the standard SQL Server client tool? What versions of SQL Server and SDAC do you use?

ccmcbride · Post by **ccmcbride** » Tue 05 Jun 2007 16:46

Yes, the error occurs if I run it in mssql Manager directly. This is not component related. I'm just trying to figure out what's wrong with it.

ccmcbride · Post by **ccmcbride** » Wed 06 Jun 2007 17:27

Tracked it down to the order the tables are listed in the 'from' clause.
Changing the from clause to :
from OrderTech T, Service M left outer join OrderStatus OS on M.OrderStatusUID = OS.UID
fixed the error.