CODEWITHSUNDEEP
sql-servert-sql
CryptoJones
CryptoJones

Reputation: 745

TSQL ignoring where statement after UNION

After doing a union all on two tables, now it is ignoring my WHERE statement's @Badge Parameter. The code is below. 

SELECT     TOP (100) PERCENT ID_BADGE, LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 2) 
                      + ':' + SUBSTRING(LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 4), 3, 2) AS ClockTime, DATE_TRX, CODE_TRX

FROM omi.DCUTRX_ZERO 

UNION ALL 

SELECT     TOP (100) PERCENT ID_BADGE, LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 2) 
                      + ':' + SUBSTRING(LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 4), 3, 2) AS ClockTime, DATE_TRX, CODE_TRX
FROM omi.TAHIST_ZERO 

WHERE     (ID_BADGE = '     ' + @Badge)  AND (DATE_TRX BETWEEN @Date AND @Date2) AND (SEQ_REC IN ('0', '1000')) AND (CODE_TRX IN ('100', '101'))

Any Help is Greatly Appreciated!

Upvotes: 0

Views: 251

Answers (3)

Atheer Mostafa
Atheer Mostafa

Reputation: 735

WHERE filter is applied over the last UNION SELECT ..

Better SQL to be as follows:

SELECT     TOP (100) PERCENT ID_BADGE, LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 2) 
+ ':' + SUBSTRING(LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 4), 3, 2) AS ClockTime, DATE_TRX, CODE_TRX
FROM omi.DCUTRX_ZERO 
WHERE     (ID_BADGE = '     ' + @Badge)  AND (DATE_TRX BETWEEN @Date AND @Date2) AND (SEQ_REC IN ('0', '1000')) AND (CODE_TRX IN ('100', '101'))

UNION ALL 

SELECT     TOP (100) PERCENT ID_BADGE, LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 2) 
                  + ':' + SUBSTRING(LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 4), 3, 2) AS ClockTime, DATE_TRX, CODE_TRX
FROM omi.TAHIST_ZERO
WHERE     (ID_BADGE = '     ' + @Badge)  AND (DATE_TRX BETWEEN @Date AND @Date2) AND (SEQ_REC IN ('0', '1000')) AND (CODE_TRX IN ('100', '101'))

Upvotes: 1

Rui Jarimba
Rui Jarimba

Reputation: 17994

If you want to apply the WHERE clause to all the results, try this:

select * from (
SELECT TOP (100) PERCENT 
    ID_BADGE, 
    LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 2) + ':' + SUBSTRING(LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 4), 3, 2) AS ClockTime, 
    DATE_TRX, 
    CODE_TRX
FROM omi.DCUTRX_ZERO 

UNION ALL 

SELECT TOP (100) PERCENT 
    ID_BADGE, 
    LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 2) + ':' + SUBSTRING(LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 4), 3, 2) AS ClockTime, 
    DATE_TRX, 
    CODE_TRX
FROM omi.TAHIST_ZERO 
) omi
WHERE (omi.ID_BADGE = '     ' + @Badge)  
    AND (omi.DATE_TRX BETWEEN @Date AND @Date2) 
    AND (omi.SEQ_REC IN ('0', '1000')) 
    AND (omi.CODE_TRX IN ('100', '101'))

Upvotes: 2

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

If you want the WHERE applied to both then you need to wrap the statement and apply it to the resulting set, like this:

SELECT *
FROM (
    SELECT TOP (100) PERCENT ID_BADGE,
        LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 2) + ':' + SUBSTRING(LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 4), 3, 2) AS ClockTime,
        DATE_TRX,
        CODE_TRX
    FROM omi.DCUTRX_ZERO 
    UNION ALL 
    SELECT TOP (100) PERCENT ID_BADGE,
        LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 2) + ':' + SUBSTRING(LEFT(RIGHT('000000' + CAST(TIME_TRX AS varchar), 6), 4), 3, 2) AS ClockTime,
        DATE_TRX,
        CODE_TRX
    FROM omi.TAHIST_ZERO
) q
WHERE (ID_BADGE = '     ' + @Badge)  AND
    (DATE_TRX BETWEEN @Date AND @Date2) AND
    (SEQ_REC IN ('0', '1000')) AND
    (CODE_TRX IN ('100', '101'))

Upvotes: 3

Related Questions