Solved

multi-part identifier "..." could not be bound.

Posted on 2008-06-13
14
709 Views
Last Modified: 2013-11-05
Greetings,

I have a query that was running just fine until I added an additional field.  This query is part of a stored procedure that has four temp tables.  You can safely assume that all temp tables have been filled and the last query that ran fine looked like this:

               SELECT
      '<?xml version="1.0" encoding="ISO-8859-1"?><?xml-stylesheet type="text/xsl" href="http://....xsl"?><Range>' +
      ISNULL((SELECT Field_A, Field_B, Field_C FROM #TableResults1 FOR XML RAW ('Info'), ROOT ('Info1'), ELEMENTS), '') +
      ISNULL((SELECT Field_L, Field_M, Field_N FROM #TableResults2 FOR XML RAW ('Info'), ROOT ('Info2')), '') +
      ISNULL((SELECT Field_X, Field_Y, Field_Z FROM  #TableResults3 FOR XML RAW ('Info'), ROOT ('Info3')), '') +
      '</Range>'
      

I decided I needed to take the results of the query above and add it to a new temp table (for reasons out of the scope of this question) along with 'Field_A' which is already in the query above.  Hence, the new temp table and query looks like this:


CREATE TABLE #TableResults4 (Field_A VARCHAR(512), Field_Z text)  // Field_Z is the entire results of the query above

INSERT INTO #TableResults4  -- ******* ERROR *******
      SELECT (SELECT Field_A FROM #Table1Results4 X),
      '<?xml version="1.0" encoding="ISO-8859-1"?><?xml-stylesheet type="text/xsl" href="http://....xsl"?><Range>' +
      ISNULL((SELECT Field_A, Field_B, Field_C FROM #TableResults1 Y FOR XML RAW ('Info'), ROOT ('Info1'), ELEMENTS), '') +
      ISNULL((SELECT Field_L, Field_M, Field_N FROM #TableResults2 FOR XML RAW ('Info'), ROOT ('Info2')), '') +
      ISNULL((SELECT Field_X, Field_Y, Field_Z FROM  #TableResults3 FOR XML RAW ('Info'), ROOT ('Info3')), '') +
      '</Range>'
      WHERE X.Field_A  = Y.Field_A

You can see how I tried to bind the two fields but was not successful.  Here are the errors for the line marked with stars above:

Msg 4104, Level 16, State 1, Procedure SP_MY_PROCEDURE, Line 73
The multi-part identifier "X.Field_A" could not be bound.
Msg 4104, Level 16, State 1, Procedure SP_MY_PROCEDURE, Line 73
The multi-part identifier "Y.Field_A" could not be bound.
0
Comment
Question by:John500
  • 9
  • 4
14 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21780948
what about this:
INSERT INTO #TableResults4  -- ******* ERROR *******
      SELECT x.Field_A 
      FROM #Table1Results4 X
      JOIN ( SELECT 
      '<?xml version="1.0" encoding="ISO-8859-1"?><?xml-stylesheet type="text/xsl" href="http://....xsl"?><Range>' + 
      ISNULL((SELECT Field_A, Field_B, Field_C FROM #TableResults1 Y FOR XML RAW ('Info'), ROOT ('Info1'), ELEMENTS), '') +
      ISNULL((SELECT Field_L, Field_M, Field_N FROM #TableResults2 FOR XML RAW ('Info'), ROOT ('Info2')), '') + 
      ISNULL((SELECT Field_X, Field_Y, Field_Z FROM  #TableResults3 FOR XML RAW ('Info'), ROOT ('Info3')), '') +
      '</Range>'
      ) Y
      ON X.Field_A  = Y.Field_A

Open in new window

0
 
LVL 17

Expert Comment

by:xDJR1875
ID: 21780981
You are trying to address inner-scope tables in the outer scope of your query. There is no reference available in the outer scope.

0
 

Author Comment

by:John500
ID: 21781060
I forgot to mention that the Field_A comes out of TableResults1.

Thus, do you still think I should use #TableResults4 X rather than:

INSERT INTO #TableResults4  -- ******* ERROR *******
      SELECT x.Field_A
      FROM #Table1Results1 X
      ....
0
 

Author Comment

by:John500
ID: 21781110
Right now I get the errors below when using the following:



INSERT INTO #TableResults4  -- ******* ERROR *******
      SELECT X.Field_A
      FROM #TableResults1 X
      JOIN ( SELECT
      '<?xml version="1.0" encoding="ISO-8859-1"?><?xml-stylesheet type="text/xsl" href="http://....xsl"?><Range>' +
      ISNULL((SELECT Field_A, Field_B, Field_C FROM #TableResults1 Y FOR XML RAW ('Info'), ROOT ('Info1'), ELEMENTS), '') +
      ISNULL((SELECT Field_L, Field_M, Field_N FROM #TableResults2 FOR XML RAW ('Info'), ROOT ('Info2')), '') +
      ISNULL((SELECT Field_X, Field_Y, Field_Z FROM  #TableResults3 FOR XML RAW ('Info'), ROOT ('Info3')), '') +
      '</Range>'
      ) Y
      ON X.Field_A  = Y.Field_A


Msg 8155, Level 16, State 2, Procedure SP_MY_PROCEDURE, Line 73
No column was specified for column 1 of 'Y'.
Msg 207, Level 16, State 1, Procedure SP_MY_PROCEDURE, Line 83
Invalid column name 'Field_A'.
Msg 213, Level 16, State 1, Procedure SP_MY_PROCEDURE, Line 83
Insert Error: Column name or number of supplied values does not match table definition.
0
 

Author Comment

by:John500
ID: 21781157
The outer scope tables in this case would be #TableResults1 and the results of Y  - yes/no ?
0
 
LVL 17

Expert Comment

by:xDJR1875
ID: 21781295
X is in an outer scope and Y is inner-scope (a select within a select)
0
 

Author Comment

by:John500
ID: 21781378
Any suggestions?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 17

Expert Comment

by:xDJR1875
ID: 21781554
Sorry, I see you now have aliased your inner select as Y. So both are now in the outer scope or context. The one thing you have not done is name your column in Y as Field_A. and I am not sure with all of your xml in there that you can reliably do that.
0
 

Author Comment

by:John500
ID: 21781795
I made a mistake on the table I should be referening.  It's actually #TableResults2

Notice the changes on both lines with the stars and tell me if this is what you were talking about.  

Thanks

INSERT INTO #TableResults4  
      SELECT X.Field_A
      FROM #TableResults2 X    -- *******************************
      JOIN ( SELECT
      '<?xml version="1.0" encoding="ISO-8859-1"?><?xml-stylesheet type="text/xsl" href="http://....xsl"?><Range>' +
      ISNULL((SELECT Field_B, Field_C, Field_D FROM #TableResults1 Y FOR XML RAW ('Info'), ROOT ('Info1'), ELEMENTS), '') +
      ISNULL((SELECT Y.Field_A, Field_M, Field_N FROM #TableResults2 FOR XML RAW ('Info'), ROOT ('Info2')), '') +   -- ************************************************
      ISNULL((SELECT Field_X, Field_Y, Field_Z FROM  #TableResults3 FOR XML RAW ('Info'), ROOT ('Info3')), '') +
      '</Range>'
      ) Y
      ON X.Field_A  = Y.Field_A
0
 

Author Comment

by:John500
ID: 21781924
I had 'Y' in there where it wasn't supposed to be after #TableResults1 (but didn't mean to put it there.....)

INSERT INTO #TableResults4  
      SELECT X.Field_A
      FROM #TableResults2 X    -- *******************************
      JOIN ( SELECT
      '<?xml version="1.0" encoding="ISO-8859-1"?><?xml-stylesheet type="text/xsl" href="http://....xsl"?><Range>' +
      ISNULL((SELECT Field_B, Field_C, Field_D FROM    #TableResults1 FOR XML RAW ('Info'), ROOT ('Info1'), ELEMENTS), '') +
      ISNULL((SELECT Y.Field_A, Field_M, Field_N FROM #TableResults2 FOR XML RAW ('Info'), ROOT ('Info2')), '') +   -- ************************************************
      ISNULL((SELECT Field_X, Field_Y, Field_Z FROM     #TableResults3 FOR XML RAW ('Info'), ROOT ('Info3')), '') +
      '</Range>'
      ) Y
      ON X.Field_A  = Y.Field_A
0
 
LVL 17

Expert Comment

by:xDJR1875
ID: 21782032

remove the Y. from below line and try it
ISNULL((SELECT Y.Field_A, Field_M, Field_N FROM #TableResults2 FOR XML RAW ('Info'), ROOT
0
 

Author Comment

by:John500
ID: 21782524
This ended up being the ticket:


SELECT
      (SELECT TOP 1 [Name] FROM #RangeInfo),
      (SELECT

      '<?xml version="1.0" encoding="ISO-8859-1"?><?xml-stylesheet type="text/xsl" href="http://....xsl"?><Range>' +
      ISNULL((SELECT Field_B, Field_C, Field_D FROM    #TableResults1 FOR XML RAW ('Info'), ROOT ('Info1'), ELEMENTS), '') +
      ISNULL((SELECT Y.Field_A, Field_M, Field_N FROM #TableResults2 FOR XML RAW ('Info'), ROOT ('Info2')), '') +  
      ISNULL((SELECT Field_X, Field_Y, Field_Z FROM     #TableResults3 FOR XML RAW ('Info'), ROOT ('Info3')), '') +
      '</Range>'
0
 

Author Comment

by:John500
ID: 21782532
Bad, paste - this was the ticket:

SELECT
      (SELECT TOP 1 [Name] FROM #RangeInfo),
      (SELECT

      '<?xml version="1.0" encoding="ISO-8859-1"?><?xml-stylesheet type="text/xsl" href="http://....xsl"?><Range>' +
      ISNULL((SELECT Field_B, Field_C, Field_D FROM    #TableResults1 FOR XML RAW ('Info'), ROOT ('Info1'), ELEMENTS), '') +
      ISNULL((SELECT Field_A, Field_M, Field_N FROM    #TableResults2 FOR XML RAW ('Info'), ROOT ('Info2')), '') +  
      ISNULL((SELECT Field_X, Field_Y, Field_Z FROM     #TableResults3 FOR XML RAW ('Info'), ROOT ('Info3')), '') +
      '</Range>')

0
 

Accepted Solution

by:
John500 earned 0 total points
ID: 21782548
Let's try that again:


SELECT
      (SELECT TOP 1 Field_A FROM #TableResults1),
      (SELECT
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now