Unable to use the code with left join

LEFT JOIN 
    (SELECT
         CASE
            WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
                         WHERE TABLE_NAME = 'TABLE_A') 
               THEN
                  SELECT q23.MRID, q23.NTP 
                  FROM 
                      (SELECT MRID, NSIA4 NTP FROM TABLE_A a, TABLE_B b 
                       WHERE a.obj = b.co
                       UNION 
                       SELECT MRID, CASE WHEN a.NBR = 0 then NTPSERV END NTP FROM TABLE_C a, TABLE_B b WHERE a.obj = b.co) q23
ELSE SELECT q23.MRID , q23.NTP from ( SELECT MRID, CASE WHEN a.NBR = 0 then NTPSERV END NTP FROM TABLE_C a, TABLE_B b WHERE a.obj = b.co) q23
END ) q24 ON q0.MRID = q24.MRID

This will combine the results from two tables and show them as union. Since we have multiple databases and in some database the TABLE_A doesn't exist.

2 Answers

0
Gordon Linoff On

You seem to mixing up different concepts in SQL. A CASE expression is exactly that: a scalar expression. It returns a column value, with a specific type. It is not control flow, but data flow (that is, it just operates on data within a query).

A SQL query refers to specific tables and columns. These cannot be dynamic or conditional, unless you are using dynamic SQL in T-SQL. T-SQL is a scripting language and it supports conditional control-flow logic:

declare @sql nvarchar(max);

if exists (select 1 from information_schema.tables where table_name = 'Table_a')
begin
    @sql = 'select col from table_a';
end;
else
begin
    @sql = 'select col from table_b';
end;

exec sp_executesql @sql;
0
Marcus Vinicius Pompeu On

With a single statement, you may query

execute(N'
    DECLARE @sql nvarchar(max) = N''
        ...
        ... YOUR QUERY SO FAR UNTIL THE LEFT JOIN
        ... 
            LEFT JOIN
            ('' +
                CASE
                    WHEN EXISTS(SELECT * FROM information_schema.tables WHERE table_name = ''TABLE_A'')
                        THEN ''
                SELECT mrid, nsia4 ntp 
                FROM table_a a, table_b b 
                WHERE a.obj = b.co 
                ----------
                UNION
                ----------''
                        ELSE ''''
                END +
            ''
                SELECT
                    mrid,
                    CASE WHEN a.nbr = 0 THEN ntpserv
                    END ntp
                FROM table_c a, table_b b
                WHERE a.obj = b.co
            ) q24
            on q0.mrid = q24.mrid
        ...
        ... THE REST OF THE QUERY
        ...''

        execute sp_executesql @sql')