Query not working, problem to compare years

Asked

Viewed 178 times

2

Hello I’m trying to elaborate a query to perform an Insert, as it uses only database data I am a select for this.
My problem is in Where clause in which either end of operation has to be null or end year of operation has to be less than period, more precisely the problem is in 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);

While trying to execute is fighting this error: SQL Error: ORA-00904: "STRCMP": Invalid identifier 00904. 00000 - "%s: invalid Dentifier"

pe.PERIODO - VARCHAR2
fi.FIMOPERATION - DATE

query oracle

  • Why not compare the fields as DATE by doing the required validation? Something like: .... AND ( fi.FIMOPERACAO is NULL OR (TO_DATE(to_char(fi.FIMOPERACAO, 'YYYY'), 'YYYY') < TO_DATE(pe.PERIODO, 'YYYY'))

  • Marllon , worked perfectly. Thank you

  • I’ll put as an answer to help anyone who has the same question, okay?

  • Yo, yo, yo, yo :)

1 answer

2

I suggest we make the comparison by 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'))

Note the year extraction in both columns and the conversion to DATE

Browser other questions tagged query oracle

You are not signed in. Login or sign up in order to post.