CODEWITHSUNDEEP
sqlsql-server-2005
Programmer
Programmer

Reputation: 129

The multipart identifier could not be bound

I have created a query below but i dont understand why i am getting the error:

The multi-part identifier "fi.FacetsImportId" could not be bound. (For each field)

INSERT INTO [Elig].[dbo].[ErrorTable]
(
    [SeqId],
    [CodeId],
    [SubjectArea],
    [FieldName],
    [TableName],
    [ErrorValue],
    [ActiveFlag]
)
Select 
    [fi].[FacetsImportId] AS [SeqId],
    @InvalidLength AS [CodeId],
    @SubjectArea AS [SubjectArea], 
    FieldName,
    'Facets.FacetsImport', 
    ErrorValue, 
    '@ActiveFlag'
From (
    select [fi].[FacetsImportId]
    ,Case When len([fi].[FacetsData]) > 200 Then [FacetsData] Else 'CorrectValue' end as [FacetsData]
    ,Case When len([fi].[DataIndicator]) > 4 Then [DataIndicator] Else 'CorrectValue' end as [DataIndicator]
    from Facets.FacetsImport) [fi]
Unpivot (
    ErrorValue for FieldName in ([FacetsData], [DataIndicator] )) as x
where x.ErrorValue <> 'CorrectValue'

Upvotes: 0

Views: 1979

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

You need [fi] aliases inside AND outside the subquery. Change one line in your query

    from Facets.FacetsImport [fi]) [fi]

For this though FacetsData is Varchar(200) and DataIndicator is Varchar(4), change 1 more

,Case When len([fi].[DataIndicator]) > 4 Then convert(varchar(200), [DataIndicator]) Else 'CorrectValue' end as [DataIndicator]

But then that begs the question, when will the test 

len([fi].[DataIndicator]) > 4

ever return true for a varchar(4) column??

Upvotes: 0

ChrisLively
ChrisLively

Reputation: 88074

Your subselect doesn't alias Facets.FacetsImport. This should work, note that I took out the aliasing of the subselect. Doesn't look like it was necessary:

INSERT INTO [Elig].[dbo].[ErrorTable] ( [SeqId], [CodeId], [SubjectArea], [FieldName], [TableName], [ErrorValue], [ActiveFlag])
Select [fi].[FacetsImportId] AS [SeqId],
    @InvalidLength AS [CodeId],
    @SubjectArea AS [SubjectArea],
    FieldName,
    'Facets.FacetsImport', 
    ErrorValue,
    '@ActiveFlag'
From ( 
    select [FacetsImportId],
        Case When len([FacetsData]) > 200 Then [FacetsData] Else 'CorrectValue' end as [FacetsData],
        Case When len([DataIndicator]) > 4 Then [DataIndicator] Else 'CorrectValue' end as [DataIndicator]
    from Facets.FacetsImport
) [fi]
Unpivot (ErrorValue for [et].FieldName in ([FacetsData], [DataIndicator] )) as x
where x.ErrorValue <> 'CorrectValue'

Upvotes: 4

Related Questions