DB2: Left Join Over-ridden by Where clause

db2

I have a query that uses a left join. There are some instances where no matching records will be present in the joined table but I still want all the records from table A that match to be returned. What I have seems to work well until AND TRIM (status) in ('Complete') is processed, which filters out the records from table A that have no matches in myjoined table. How do I make DB2 ignore the status criteria (table b) when there are no relevant records in it? I still want everythign from table A that matches the rest of the criteria.

SELECT DISTINCT evecas, everef, evetyp, evesfc, eveplc, eveofb,
trim(evetrt) || '-' || (eveprg) as PGM,
evestf,
DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)) AS EVENTDATE,
(select stfnam from staffmstr where smmnum = evestf), 
lwsys.isodates(evefyr, evefmm, evefdd) AS DOSYYMD, 
evetim,
eveend,
TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) AS TOTAL_MINUTE_TIME
,
(select trim (cltfst) || ' ' || (cltlst) as CLIENT from cltmstpf where cltcas = evecas), 
evecnf, evesrv, evecpt, eveamt, evepun,
eveprc, status, lwsys.isodates(evepyr, evepmm, evepdd) AS PROCYYMD,
(case
when evecpt LIKE '%90832%' AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) > 38 then '1'
when evecpt LIKE '%90832%' AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) > 15 AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) < 38 then '.5'
when evecpt LIKE '%90832%' AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) < 16 then '0'
when evecpt LIKE '%90834%' AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) > 38 then '1'
when evecpt LIKE '%90834%' AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) > 15 AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) < 38 then '.5'
when evecpt LIKE '%90834%' AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) < 16 then '0'
when evecpt LIKE '%90837%' OR evesrv LIKE '%T001%' AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) > 38 then '1'
when evecpt LIKE '%90837%' OR evesrv LIKE '%T001%' AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) > 15 AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) < 38 then '.5'
when evecpt LIKE '%90837%' OR evesrv LIKE '%T001%' AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) < 16 then '0'
when evecpt LIKE '%90792%' then '1'
when evecpt LIKE '%90831%' then '.25'
when evecpt LIKE '%90833%' then '.25'
when evecpt LIKE '%90836%' then '1'
when evecpt LIKE '%90838%' then '1'
when evecpt LIKE '%T004%' then '1'
when evecpt LIKE '%T003%' then '1'
when evecpt LIKE '%T002%' then '1'
when evecpt LIKE '%90853%' then '.25'
when evecpt LIKE '%90870%' then '.55'
when evecpt LIKE '%96372%' then '.2'
when evecpt LIKE '%99212%' then '.25'
when evecpt LIKE '%99213%' then '.33'
when evecpt LIKE '%99214%' then '.5'
when evecpt LIKE '%99215%' then '1'
when evecpt LIKE '%E001%' then 'NONE'
when evecpt LIKE '%G001%' then 'NONE'
when evecpt LIKE '%I001%' then 'NONE'
when evecpt LIKE '%L001%' then 'NONE'
when evecpt LIKE '%M001%' then 'NONE'
when evecpt LIKE '%P010%' then 'NONE'
when evecpt LIKE '%TP05%' then '.5'
when evecpt LIKE '%TP10%' then '1'
when evecpt LIKE '%TP15%' then '1.5'
when evecpt LIKE '%TP20%' then '2'
when evecpt LIKE '%TP25%' then '2.5'
when evecpt LIKE '%TP30%' then '3'
when evecpt LIKE '%TP35%' then '3.5'
when evecpt LIKE '%TP40%' then '4'
when evecpt LIKE '%TP45%' then '4.5'
when evecpt LIKE '%TP50%' then '5'
when evecpt LIKE '%TP55%' then '5.5'
when evecpt LIKE '%TP60%' then '6'
when evecpt LIKE '%T099%' then 'NONE'
when evecpt LIKE '%T100%' then 'NONE'
when evecpt LIKE '%TP01%' then 'NONE'
when evecpt LIKE '%H0032%' then 'NONE'
when evecpt LIKE '%H0035%' then 'NONE'
when evecpt LIKE '%P010%' then 'NONE'
when evecpt LIKE '%99221%' then '.5'
when evecpt LIKE '%99222%' then '1.5'
when evecpt LIKE '%99223%' then '1.5'
when evecpt LIKE '%99231%' then '.5'
when evecpt LIKE '%99232%' then '.5'
when evecpt LIKE '%99233%' then '.5'
when evecpt LIKE '%99238%' then '.5'
when evecpt LIKE '%99239%' then '.5'
when evecpt LIKE '%99408%' then '.5'
when evecpt LIKE '%99409%' then '1'
when evecpt LIKE '%G0177%' then '.25'
when evecpt LIKE '%G0410%' then '.25'
when evecpt LIKE '%G0176%' then '.25'
when evecpt LIKE '%T007%' then '.25'
when evecpt LIKE '%T005%' then '.25'
when evecpt LIKE '%T006%' then '.25'
when evecpt LIKE '%T002%' then '1'
when evecpt LIKE '%T003%' then '1'
ELSE NULL
END
) AS VALUE
--  from evetrnpf, doctsk
from evetrnpf
LEFT JOIN doctsk AS DT ON DT.evetrn_id = everef AND DT.client_id = evecas 
WHERE lwsys.isodates(evefyr, evefmm, evefdd) BETWEEN ? AND ?
AND TRIM (status) in ('Complete')
AND evetyp = 'S'
 AND evecas <> 0
ORDER BY stfnam, evecas

Best Answer

DB2: Left Join Over-ridden by Where clause?

If you add search arguments to the where clause, it is going to filter that out, even if it is a left join. As the where clause is processed after the left join. If you want to keep the records even if

Status != ('Complete')

Try changing this

LEFT JOIN doctsk AS DT ON DT.evetrn_id = everef AND DT.client_id = evecas 
WHERE lwsys.isodates(evefyr, evefmm, evefdd) BETWEEN ? AND ?
AND TRIM (status) in ('Complete')

To this:

LEFT JOIN doctsk AS DT ON DT.evetrn_id = everef AND DT.client_id = evecas    
AND TRIM (status) in ('Complete')
WHERE lwsys.isodates(evefyr, evefmm, evefdd) BETWEEN ? AND ?

EDIT 2

On AND TRIM (status) <> '' returns everything with my dataset:

Query

   SELECT DISTINCT * 
   FROM evetrnpf
   LEFT JOIN doctsk AS DT ON DT.evetrn_id = everef AND DT.client_id = evecas 
   AND TRIM (status) <> '';

EDIT

In fact, I get the same number of results even after I comment the "AND TRIM (status) in ('Complete')" out as with it in

That is normal behaviour since you are doing a LEFT JOIN as requested with this question:

How do I make DB2 ignore the status criteria (table b) when there are no relevant records in it?

Example of this behaviour:

Query:

SELECT DISTINCT * 
FROM evetrnpf
LEFT JOIN doctsk AS DT ON DT.evetrn_id = everef AND DT.client_id = evecas 
AND TRIM (status) in ('Complete');

RESULT:

Test Data CREATE TABLE doctsk ( evetrn_id int,status varchar(20), client_id varchar(20) );

INSERT INTO doctsk(evetrn_id,status,client_id)

VALUES(1,'  Complete  ',1);


INSERT INTO doctsk(evetrn_id,status,client_id)

VALUES(2,'  NOTComplete  ',2);


INSERT INTO doctsk(evetrn_id,status,client_id)

VALUES(3,'Complete  ',3);


INSERT INTO doctsk(evetrn_id,status,client_id)

VALUES(4,'   Complete',4);


CREATE TABLE evetrnpf  (    everef   int,val varchar(20),evecas  int);

INSERT INTO evetrnpf(everef,val,evecas)

VALUES(1,'bla',1);



INSERT INTO evetrnpf(everef,val,evecas)

VALUES(2,'bla',2);

INSERT INTO evetrnpf(everef,val,evecas)

VALUES(3,'bla',3);

INSERT INTO evetrnpf(everef,val,evecas)

VALUES(4,'bla',4);

Related Solutions

Db2 range partitioning attach and detach issue

Use this:

db2 +c "lock table tabname in share mode"
db2 +c "alter table tabname add partition <partname> starting 'date' ending 'date' exclusive"
db2 "commit"

Here is the explanation:

+c : disable auto commit, so the command you send will not be committed until an explicit commit command sent or connection closed.

locking table in share mode: means that you want to acquire a shared lock which will prevent other transactions to make changes to the table. So, following command won't be blocked while working.

In this approach you say that: lock the table for me, then issue partition command, while issuing this command other transaction will wait till you finished your job and send commit statement.

I am not sure if it works or not. But it worth a try.

Db2 – Searching for pairs of date/time values in a single row

I'm not sure of db2 syntax, but how about this?

-- declare your 4 variables
@START_ROSDAT
@STOP_ROSDAT
@START_ROSTIM
@STOP_ROSTIM

select  *
from    PHONELOGTABLE
where   (   @start_rosdat <> @stop_rosdat                                    -- search covers more than 1 day
           and  (       (ROSDAT > @START_ROSDAT AND ROSDAT < @STOP_ROSDAT)   -- catches all full days between start and stop
                   or   (ROSDAT = @START_ROSDAT AND ROSTIM >= @START_ROSTIM) -- catches everything on the "start" day
                   or   (ROSDAT = @STOP_ROSDAT AND ROSTIM <= @STOP_ROSTIM)   -- catches everything on the "stop" day
                )
        )
  or    (   @start_rosdat = @stop_rosdat                                     -- only search a single "day"
           and  (       (ROSDAT = @START_ROSDAT AND ROSTIM >= @START_ROSTIM)
                   and  (ROSDAT = @STOP_ROSDAT AND ROSTIM <= @STOP_ROSTIM)
                )
        )

WHERE condition simplified a bit (the parentheses are there only for clarity, you can safely remove them):

where   ( ROSDAT > @start_rosdat   and  ROSDAT < @stop_rosdat )

   or   (  @start_rosdat < @stop_rosdat 
      and  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim )

   or   (  @start_rosdat < @stop_rosdat 
      and  ROSDAT = @stop_rosdat   and  ROSTIM <= @stop_rostim  )

   or   (  @start_rosdat = @stop_rosdat 
      and  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim
                                   and  ROSTIM <= @stop_rostim  )

and another version (parentheses are needed here):

where   (  ROSDAT > @start_rosdat  
       or  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim )

   and  (  ROSDAT < @stop_rosdat
       or  ROSDAT = @stop_rosdat   and  ROSTIM <= @stop_rostim  )

Best Answer

Related Solutions

Db2 range partitioning attach and detach issue

Db2 – Searching for pairs of date/time values in a single row

Related Question