John500
asked on
multi-part identifier "..." could not be bound.
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"?><?x ml-stylesh eet 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"?><?x ml-stylesh eet 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.
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"?><?x
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"?><?x
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.
You are trying to address inner-scope tables in the outer scope of your query. There is no reference available in the outer scope.
ASKER
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
....
Thus, do you still think I should use #TableResults4 X rather than:
INSERT INTO #TableResults4 -- ******* ERROR *******
SELECT x.Field_A
FROM #Table1Results1 X
....
ASKER
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"?><?x ml-stylesh eet 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.
INSERT INTO #TableResults4 -- ******* ERROR *******
SELECT X.Field_A
FROM #TableResults1 X
JOIN ( SELECT
'<?xml version="1.0" encoding="ISO-8859-1"?><?x
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.
ASKER
The outer scope tables in this case would be #TableResults1 and the results of Y - yes/no ?
X is in an outer scope and Y is inner-scope (a select within a select)
ASKER
Any suggestions?
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.
ASKER
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"?><?x ml-stylesh eet 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
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"?><?x
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
ASKER
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"?><?x ml-stylesh eet 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
INSERT INTO #TableResults4
SELECT X.Field_A
FROM #TableResults2 X -- **************************
JOIN ( SELECT
'<?xml version="1.0" encoding="ISO-8859-1"?><?x
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
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
ASKER
This ended up being the ticket:
SELECT
(SELECT TOP 1 [Name] FROM #RangeInfo),
(SELECT
'<?xml version="1.0" encoding="ISO-8859-1"?><?x ml-stylesh eet 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>'
SELECT
(SELECT TOP 1 [Name] FROM #RangeInfo),
(SELECT
'<?xml version="1.0" encoding="ISO-8859-1"?><?x
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>'
ASKER
Bad, paste - this was the ticket:
SELECT
(SELECT TOP 1 [Name] FROM #RangeInfo),
(SELECT
'<?xml version="1.0" encoding="ISO-8859-1"?><?x ml-stylesh eet 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>')
SELECT
(SELECT TOP 1 [Name] FROM #RangeInfo),
(SELECT
'<?xml version="1.0" encoding="ISO-8859-1"?><?x
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>')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window