SQL Server 2005 The multi-part identifier "tblPrefix.columnName" could not be bound.

We just went into Production with MSSQL 2005, and even though I knew the change of interpreter where Union / Order by is processed a little different, i thought the error thrown would make sense.  For example, the below query in 2000 will run fine, but in 2005 will Error.

select
     a.aCol,
     b.bCol
from a inner join b on a.id = b.id

union

select
     a.acol,
     c.bcol
form a inner join c on a.id = c.id

order by b.bCol



The known interpreter change gives a semi-cryptic error of "The multi-part identifier "tblPrefix.columnName" could not be bound."  This basically means that in the order by b.bCol doesn't exists in both union queries.  Therefore, the order by needs to drop the "b." becoming "bCol".

Solution:
select

     a.aCol,

     b.bCol

from a inner join b on a.id = b.id



union



select

     a.acol,

     c.bcol

form a inner join c on a.id = c.id



order by b.bCol


Comments

Post a Comment

Popular posts from this blog

ColdFusion in an Enterprise Environment - Part 1 - Understanding how to use SubVersion (SVN)

Subversion can be used in many ways, so this isn't the holy grail of how you need to use subversion, but it does add application management to subversion. What does application management mean? Well, the definition for this article is how to control what gets released into production. This article is not going to go into how to setup version, or how to add an external to the repository tree. But how in concept to use SVN and an external to achieve application management. First of course you need a SVN repository, which for the purpose of this article our repository will be noted as "/". Second we need to know that will we still be using the concepts of trunk, tags, and branches, they just won't live in the root of the repository. The trunk is the current working copy of development, while the tag folder is to version releases (by tagging). Branches will be used when "new" development is going on and emergency coding is required on the existin...
Read more

coldfusion builder 2 extension not displaying browse button on type=projectdir

I'm in the process of creating an extension that fires from the RDSView (from a database table).  Basically I would like it to create 7 files for me by using the table's columns I selected.  *PERFECT*, I got the files being created, but I have to TYPE in the LOCATION to store the files <-- WHAT --  Yes, I said I have to type in the full path to where I want the files to be stored. So my question to anyone who might see this blog is -->  Is there a way via (call back, response, etc) in an cfbuilder extension for the user to pick the project and/or folder as an input? PS.  if you read this and don't know the answer, please "socialize" it (tweet, FaceBook, etc) so possibly the correct person with the answer can answer it.  *THANKS* **UPDATE 9/29/2011** I was able to email a contact at Adobe with this issue, and after working back and forth, it came up that the input of  type="projectdir" does NOT work when using a REMOTE server. How disappoin...
Read more

Being Thread Safe in Coldbox/Coldfusion

Thread safe never crossed my mind.  I never put CFC in the application scope to allow quicker execution (bypassing the createobject function).  But now that my office has implemented ColdBox, it cache's a lot for you, including its handlers - which are CFCs.  Well, we got bit by that evil words "THREAD SAFE".  Our code WASN'T... yes including my code. So what does that mean.  Well I'll try to do a quick explanation, thread safe is any variable that is dedicated to a SINGLE user will NOT be shared to other users.  For example, in a object a "private" variable is NOT thread safe.  So if many users use the same object (implementation of a class), then we must make sure the variables being passed around are LOCAL to that function.  Therefore, in a CFC (depending on language) you must force your function variables to be LOCAL to the function only. In CF8 use of the VAR keyword provides this <cfset var myLocalVar = "" /> In CF9 the use o...
Read more