• carlosaamaral (9/11/2012)


    SELECT table1.column1,

    '99',

    left(table1.column2, 2),

    max(table1.column3),

    'Yes',

    'Test'

    FROM table1

    WHERE NOT EXISTS ( SELECT 1

    FROM TABLE2 WHERE ltrim(rtrim(table2.column1)) + ltrim(rtrim(left(table2.column2, 2)))=

    ltrim(rtrim(table1.column1)) + ltrim(rtrim(left(table1.column2, 2)))

    )

    AND table1.column4 = 'Yes'

    GROUP BY table1.column1, Left(table1.column2, 2)

    I believe the only reason to use concatenation was IN construction.

    NOT EXISTS does not need that:

    SELECT table1.column1,

    '99',

    left(table1.column2, 2),

    max(table1.column3),

    'Yes',

    'Test'

    FROM table1

    WHERE NOT EXISTS ( SELECT *

    FROM TABLE2

    WHERE table2.column1 = table1.column1

    and left(table2.column2, 2) = left(table1.column2, 2)

    )

    AND table1.column4 = 'Yes'

    GROUP BY table1.column1, Left(table1.column2, 2)

    I removed trims as most likely they are not required.

    RTRIM is not needed for sure, and LTRIM looks like just a sign of a bad habit.

    Please check your data to make sure my assumption is correct.

    _____________
    Code for TallyGenerator