SQL: simple custom column in select doesn't work?

Ad

If I add custom column in select, I get this error:

20018 Invalid column name '2'.

Here is query example

SELECT
    [msg].[MessageTo],
    [msg].[MessageFrom],
    [msg].[SendTime],
    [msg].[ReceiveTime],
    [msg].[id],
    '2' AS source,
    [kat].[id] AS [CategoriId],
    [kat].[naziv] AS [CategoriName]
FROM
    [SMSServer_1].[dbo].[MessageIn] AS [msg]
LEFT JOIN [Tekijanka].[dbo].[crm_poruka] AS [por] ON [por].[fk_poruka] = [msg].[id]
AND [por].[fk_source] = [2]
LEFT JOIN [Tekijanka].[dbo].[crm_kategorije_poruka] AS [kat] ON [kat].[id] = [por].[fk_kategorija]
WHERE
    msg.id NOT IN (
        SELECT
            fk_poruka
        FROM
            Tekijanka.dbo.crm_poruka
        WHERE
            fk_status <> 1
    )
ORDER BY
    [SendTime] DESC

Is there any way to fix it?

Ad

Answer

Ad

The problem is in LEFT JOIN, not in the SELECT section:

AND [por].[fk_source] = [2]

This condition tries to join fk_source and column named[2]. Of course, there's no such column in tables MessageIn and crm_poruka. You have to change this part of the code (remove the condition or change it to AND [por].[fk_source] = 2).

Ad
source: stackoverflow.com
Ad