LEFT JOIN with subquery and accessing main table columns in select clause

Question

I have an insert statement like the following which gets sytax error of "the multi-part identifier "t2.Col1" could not be bound.". I over simplified the statement and it looks like below:

INSERT INTO dbo.T1
(
    Col1,
    Col2,
    Col3
)
SELECT 
    t2.Col1,
    SUBSTRING(aCase.CaseColumn, 0, CHARINDEX('%', aCase.CaseColumn)), --I expect this line gets the value "2"
    SUBSTRING(aCase.CaseColumn, CHARINDEX('%', aCase.CaseColumn) + 1, LEN(aCase.CaseColumn) - CHARINDEX('%', aCase.CaseColumn)) --I expect this line gets the value "3"
FROM 
    dbo.T2 t2
LEFT JOIN 
(
    SELECT 
        CASE --I have hundreds of WHEN conditions below and need to access the parent T2 tables' properties
            WHEN t2.Col1 = 1 THEN '2%3' --This line has a syntax error of "the multi-part identifier "t2.Col1" could not be bound."
        END AS CaseColumn
) 
AS aCase ON 1 = 1

The reason I use LEFT JOIN with CASE is that I have hundreds of conditions for which I need to select different values for different columns. I don't want to repeat the same CASE statements over and over again for all of the columns. Therefore, I use a single CASE which concatenates the values with a delimiter and then I parse that concatenated string and put the appropriate values in it's place.

Why not move the whole table querying into the join and then select from that? Since you're not using T2 in the main query at all it doesn't need to be there and simplifies things. — Sami Kuhmonen, Aug 08 '17 at 07:16
It's used actually but I didn't put it there to make it look more simple. Check my edit — yakya, Aug 08 '17 at 07:27
Ok, then you could select those columns out of the joined select and use them from there also since it seems to be row-for-row mapping between them. Seems it was already suggested as an answer — Sami Kuhmonen, Aug 08 '17 at 07:42

score 3 · Accepted Answer · answered Aug 08 '17 at 07:31

What you could do is use OUTER APPLY, as it allows your dbo.T2 and the aCase resultset to be related, like this:

INSERT INTO dbo.T1
(
    Col1,
    Col2,
    Col3
)
SELECT 
    1,
    SUBSTRING(aCase.CaseColumn, 0, CHARINDEX('%', aCase.CaseColumn)), --I expect this line gets the value "2"
    SUBSTRING(aCase.CaseColumn, CHARINDEX('%', aCase.CaseColumn) + 1, LEN(aCase.CaseColumn) - CHARINDEX('%', aCase.CaseColumn)) --I expect this line gets the value "3"
FROM 
    dbo.T2 t2
OUTER APPLY
(
    SELECT 
        CASE --I have hundreds of WHEN conditions below and need to access the parent T2 tables' properties
            WHEN t2.Col1 = 1 THEN '2%3' 
        END AS CaseColumn
) 
AS aCase ON 1 = 1

That is because the result of the subquery is not indipendent itself, it has to be defined based on the values of the dbo.T2 table.

Read more about OUTER APPLY and CROSS APPLY on this thread.

Number 3, "Reusing a table alias" is similiar to your case and the article linked to it perfectly explains how to use cross apply/outer apply in these cases.

LukStorms · Answer 2 · 2017-08-08T08:46:05.523

When using a join to a subquery, inside that subquery it doesn't know what t2 is, unless you select from a table aliased as t2 in that subquery.
And you could change that LEFT JOIN to an OUTER APPLY.

But you don't really need to JOIN or OUTER APPLY in this case.

Just select from T2 with the CASE in the subquery.

INSERT INTO dbo.T1
(
    Col1,
    Col2,
    Col3
)
SELECT 
    Col1,
    SUBSTRING(CaseColumn, 1, CHARINDEX('%', CaseColumn)-1),
    SUBSTRING(CaseColumn, CHARINDEX('%',CaseColumn)+1, LEN(CaseColumn))
FROM 
(
  SELECT 
   Col1,
   CASE Col1
    WHEN 1 THEN '2%3'
    -- more when's
   END AS CaseColumn
  FROM dbo.T2 t2
) q

Note how the CASE and the SUBSTRING's were changed a little bit.

Btw, personally I would just insert the distinct Col1 into T1, and just update Col2 and Col3 in that reference table manually. That could prove to be faster than writing those hundreds conditions. But then again, you did say this was simplified a lot.

You missed my last edit. I edited the part where I select 1 to -> t2.Col1 — yakya, Aug 08 '17 at 07:58
@sotn Indeed, I missed that one. And I had wondered why one would insert a fixed number. Fixed. — LukStorms, Aug 08 '17 at 08:10

LEFT JOIN with subquery and accessing main table columns in select clause

2 Answers2