0

I am trying to run a query in Informix, doing a join in a table of logs where we save the record of insertion of the rows.

Notice that the query below brings two results, with different insertion dates:

select * from log_cipessoa where cgccpf = 1541247000188

Results:

 cgccpf         tppessoa    tp_dml  dt_insercao
 -------------  --------    ------  ---------------------
 1541247000188         1    INSERT  2018-03-06 15:05:42.0
 1541247000188         2    INSERT  2018-03-27 09:54:45.0

Now, I want to bring the rows that were inserted after the 2018-03-21 day, but while doing the join, informix seems to merge the dt_insercao column with the most recent date.

And according to the log above, Informix should only return the second line, where the column tppessoa with the value 2 and dt_insercao 2018-03-27 09:54:45.0

SELECT pe.cgccpf 
      ,pe.tppessoa
      ,REPLACE(LTRIM(RTRIM(pe.nomepessoa)), '  ', ' ') AS nomepessoa
      ,lg.dt_insercao
FROM CIPESSOA pe
INNER JOIN LOG_CIPESSOA lg
ON lg.cgccpf = pe.cgccpf
WHERE    lg.tp_dml = 'INSERT'
     AND lg.dt_insercao > '2018-03-21 20:09:32' AND lg.dt_insercao <= '2018-03-31 20:00:00'
     and pe.cgccpf = 1541247000188 --FILTER ONLY FOR DEMONSTRATION

Results:

 cgccpf         tppessoa    nomepessoa      dt_insercao          
 -------------  --------    -----------     ---------------------
 1541247000188         1    XPTO LTDA       2018-03-27 09:54:45.0
 1541247000188         2    XPTO LTDA       2018-03-27 09:54:45.0
8
  • Perhaps you have 2 rows in CIPESSOA with the same cgccpf value (1541247000188)? You can easily check with select * from cipessoa where cgccpf = 1541247000188 ; It seems like it, since pe.tppessoa shows different values (1 and 2) in the two rows. Commented Mar 31, 2018 at 20:01
  • But the date filter should not filter, removing the type of person 1? Commented Mar 31, 2018 at 20:58
  • You don't have a filter on type. Why would the filter (which is on a date column in table two, LOG_CIPESSOA) filter rows from table one, CIPESSOA? Commented Mar 31, 2018 at 21:00
  • I think you should show us the table definitions and these 2 rows (all columns) from each table. You may be missing something and the join conditions could be altered. Is there a tppessoa column in both tables? Commented Mar 31, 2018 at 21:02
  • 1
    If there are, you could try FROM CIPESSOA pe INNER JOIN LOG_CIPESSOA lg ON lg.cgccpf = pe.cgccpf AND lg.tppessoa = pe.tppessoa Commented Mar 31, 2018 at 21:04

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.