Query not working, problem comparing years

Navigation
2

Hello, I'm trying to compile a query to perform an insert, as it uses only database data I'm a select for this. My problem is in the where clause where either end of operation has to be null or the year of the end of operation has to be shorter than period, more precisely the problem is in the STRCMP function. 

INSERT INTO EMPRESAS_FILIAL(IDPERIODO, IDEMPRESA, IDFILIAL)
 select ".{IDPERIODO}.", IDEMPRESAS emp, COD_PLANTA fi 
 FROM EMPRESAS emp, FILIAL fi, PERIODO pe 
 WHERE (emp.ASSOCIADO = '1' 
 AND fi.ATUACAORESPONSAVEL = '1' AND fi.ECONOMICO ='1' 
 AND emp.IDEMPRESAS = fi.EMPRESAS_IDEMPRESAS) 
 AND pe.IDPERIODO = ".{IDPERIODO}." 
 AND ( fi.FIMOPERACAO = NULL OR STRCMP(pe.PERIODO, (to_char(fi.FIMOPERACAO, 'YYYY'))) = 1);

Attempt to run is generating this error: SQL Error: ORA-00904: "STRCMP": Invalid identifier 00904. 00000 - "% s: invalid identifier"

pe.PERIODO - VARCHAR2
fi.FIMOPERACAO - DATE

    
asked by anonymous 12.01.2017 / 20:17

1 answer

2

I suggest comparing dates.

Something like: 

INSERT INTO EMPRESAS_FILIAL(IDPERIODO, IDEMPRESA, IDFILIAL)
 select ".{IDPERIODO}.", IDEMPRESAS emp, COD_PLANTA fi 
 FROM EMPRESAS emp, FILIAL fi, PERIODO pe 
 WHERE (emp.ASSOCIADO = '1' 
 AND fi.ATUACAORESPONSAVEL = '1' AND fi.ECONOMICO ='1' 
 AND emp.IDEMPRESAS = fi.EMPRESAS_IDEMPRESAS) 
 AND pe.IDPERIODO = ".{IDPERIODO}." 
 AND ( fi.FIMOPERACAO is NULL OR (TO_DATE(to_char(fi.FIMOPERACAO, 'YYYY'), 'YYYY') < TO_DATE(pe.PERIODO, 'YYYY'))

Notice the year's extraction in both columns and the conversion to DATE

    
12.01.2017 / 20:36
How to create a composite index in SQLite via Microsoft.EntityFrameworkCore.SQLite? Toggle - get result true or false