Geek Answers Handbook

Output conclusion: an identifier with several parts cannot be associated

I run into the terrible error "Failed to bind the identifier of several parts" in the stored procedure I'm working on now. I have a few questions regarding the request below.

  • Why am I getting this error?
  • Why does this error occur in ImportFundingDateTime instead of FloorplanId, given that both come from the same query, but FloorplanId is specified in the first expression?
  • Is it possible to configure this query so as not to get an error while maintaining the overall structure?

.

DECLARE @Results                Table(
    [FloorPlanId]               UNIQUEIDENTIFIER,
    [ImportFundingDateTime]     DATETIME,
    [TimeStamp]                 VARBINARY(8),
    [BusinessId]                UNIQUEIDENTIFIER
    )

UPDATE CacRecord 
    SET MatchFound = 1
    OUTPUT  fp.[FloorplanId], cr.[ImportFundingDateTime],
            fp.[TimeStamp], buyer.[BusinessId]
    INTO @Results(  [FloorplanId], [ImportFundingDateTime], 
                    [TimeStamp], [BusinessId])
    FROM CacRecord cr WITH (NOLOCK)
    INNER JOIN CacBatch cb WITH (NOLOCK)
        ON cr.CacBatchId = cb.CacBatchId
    INNER JOIN Floorplan fp WITH (NOLOCK)
        ON fp.UnitVIN = cr.ImportVin
        AND COALESCE(fp.UnitVIN, '') <> ''
    INNER JOIN Business buyer WITH (NOLOCK)
        ON buyer.BusinessId = fp.BuyerBusinessId
    LEFT OUTER JOIN BusinessContact bc WITH (NOLOCK)
        ON bc.BusinessId = buyer.BusinessId
    LEFT OUTER JOIN Contact c WITH (NOLOCK)
        ON c.ContactId = bc.ContactId
    WHERE cb.CacJobInstanceId = @cacJobInstanceId
        AND fp.FloorplanStatusId = 1 --Approved
        AND COALESCE(cr.ImportVin, '') <> ''
        AND 1 = 
            CASE
                WHEN cr.ImportFein = buyer.FederalTaxID  
                    AND COALESCE(cr.ImportFein, '') <> '' THEN 1
                WHEN cr.ImportSsn = c.Ssn 
                    AND COALESCE(cr.ImportSsn, '') <> '' THEN 1
                ELSE 0
            END;
+5
source share
1 answer

, OUTPUT OUTPUT MSDN

Syntax

<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }

from_table_name

Is a column prefix that specifies a table included in the FROM clause
of a DELETE or UPDATE statement that is used tospecify the rows to
update or delete.

, CacRecord FROM "cr", UPDATE.

. FROM UPDATE SQL Server, , CacRecord UPDATE, INSERTED cr .

UPDATE cr
SET MatchFound = 1
OUTPUT fp.[FloorplanId], INSERTED.[ImportFundingDateTime],
  fp.[TimeStamp], buyer.[BusinessId]
INTO @Results( [FloorplanId], [ImportFundingDateTime], 
    [TimeStamp], [BusinessId])
FROM CacRecord cr WITH (NOLOCK)
INNER JOIN CacBatch cb WITH (NOLOCK)
 ON cr.CacBatchId = cb.CacBatchId
INNER JOIN Floorplan fp WITH (NOLOCK)
 ON fp.UnitVIN = cr.ImportVin
 AND COALESCE(fp.UnitVIN, '') <> ''
INNER JOIN Business buyer WITH (NOLOCK)
 ON buyer.BusinessId = fp.BuyerBusinessId
LEFT OUTER JOIN BusinessContact bc WITH (NOLOCK)
 ON bc.BusinessId = buyer.BusinessId
LEFT OUTER JOIN Contact c WITH (NOLOCK)
 ON c.ContactId = bc.ContactId
WHERE cb.CacJobInstanceId = @cacJobInstanceId
 AND fp.FloorplanStatusId = 1 --Approved
 AND COALESCE(cr.ImportVin, '') <> ''
 AND 1 = 
  CASE
   WHEN cr.ImportFein = buyer.FederalTaxID  
    AND COALESCE(cr.ImportFein, '') <> '' THEN 1
   WHEN cr.ImportSsn = c.Ssn 
    AND COALESCE(cr.ImportSsn, '') <> '' THEN 1
   ELSE 0
  END;

OUTPUT.

create table TO1 (id int, a int);
create table TO2 (id int, b int);
create table TO3 (id int, c int);
insert into TO1 select 1,1;
insert into TO2 select 1,2;
insert into TO3 select 1,3;
insert into TO3 select 1,4;

declare @catch table (a int, b int, c int)
update c
set c = a.a
output a.a, b.b, INSERTED.c
into @catch(a,b,c)
from TO1 a
inner join TO2 b on a.id=b.id
inner join TO3 c on a.id=c.id
+6

More articles:


All Articles