Is there a better way to write this SQL query? (UNION)

I tried using OR in the WHERE clause for this code but I get an error on the TIMESTAMP_FORMAT function, but this as such can’t be the issue because it’s exactly the same as in the working code when using UNION.

When I use a UNION in the code it works perfectly and I get exactly what I want, but I seriously doubt that’s the correct way to do it.

I want all records that have text starting with (PUP) on field UPTEXT which DO NOT have an empty value on field UPLSTD AND that have a LAST USED date that’s older than 12 days on field UPLSTD I don’t want the empty ones here because I can’t use the TIMESTAMP_FORMAT function on empty values. But what I also need is all records that also have text starting with (PUP) on field UPTEXT but do have an empty value on field UPLSTD AND that have a CREATION date that’s older than 12 days on field UPCRTD.

This is the faulty code:

SELECT UPUPRF,
       UPTEXT,
       UPLSTD,
       UPCRTD
    FROM SYSENGTMP.LLDLSTPRF
    WHERE (
              UPTEXT LIKE '(PUP)%'
              AND UPLSTD <> ' '
              AND TIMESTAMP_FORMAT(UPLSTD, 'RRMMDD') < CURRENT_DATE - 12 DAYS)
          OR (
              UPTEXT LIKE '(PUP)%'
              AND UPLSTD = ' '
              AND TIMESTAMP_FORMAT(UPCRTD, 'RRMMDD') < CURRENT_DATE - 12 DAYS);

This is the working code:

SELECT UPUPRF,
       UPTEXT,
       UPLSTD,
       UPCRTD
    FROM SYSENGTMP.LLDLSTPRF
    WHERE (
            UPTEXT LIKE '(PUP)%'
            AND UPLSTD <> ' '
            AND TIMESTAMP_FORMAT(UPLSTD, 'RRMMDD') < CURRENT_DATE - 12 DAYS)
UNION
    SELECT UPUPRF,
           UPTEXT,
           UPLSTD,
           UPCRTD
        FROM SYSENGTMP.LLDLSTPRF
        WHERE (
                UPTEXT LIKE '(PUP)%'
                AND UPLSTD = ' '
                AND TIMESTAMP_FORMAT(UPCRTD, 'RRMMDD') < CURRENT_DATE - 12 DAYS);
``

This is the result of the working UNION code which is exactly what I want.
I don't get any result with the faulty code excep for the error message on the TIMESTAMP_FORMAT.

UPUPRF       UPTEXT                     UPLSTD  UPCRTD
------------------------------------------------------
USER1       (PUP) TEXT ABCDEFG          190805  190805
USER2       (PUP) TEXT ABCDEFG          181113  181113
USER3       (PUP) TEXT ABCDEFG          190728  190625
USER4       (PUP) TEXT ABCDEFG          190726  190613
USER5       (PUP) TEXT ABCDEFG                  190625
USER6       (PUP) TEXT ABCDEFG                  190625
USER7       (PUP) TEXT ABCDEFG                  190625
USER8       (PUP) TEXT ABCDEFG                  180725
USER9       (PUP) TEXT ABCDEFG          190730  190625

Answer

Make two steps:

1) put all relevant data in a temp-table
2) do query over the temp-table

like this:

1) selection: only relevant rows
SELECT UPUPRF,
       UPTEXT,
       UPLSTD,
       UPCRTD
    into #MyTempTable 
    FROM SYSENGTMP.LLDLSTPRF
    WHERE (UPLSTD <> ' '
              AND UPLSTD = ' ');

2) selection: work on relevant data
// column selection is allready done
  SELECT *
        FROM #MyTempTable 
        WHERE (
                UPTEXT LIKE '(PUP)%'
                AND TIMESTAMP_FORMAT(UPCRTD, 'RRMMDD') < CURRENT_DATE - 12 DAYS);

Advertisement

Answer