SQL query using several times the same function

Question

SQL query using several times the same function

Navigation

#1 by (2 votes)

2

I have a function that converts the ID of a version to number, for example, from 1.0.0 to 10000 , so I can filter the results.

Function:

CREATE OR REPLACE FUNCTION f_convert_tag_to_number(ptag varchar2(12))
  RETURN NUMBER IS
  vretorno NUMBER(12);
BEGIN
  SELECT ((regexp_substr(ptag,'[^.\D]+',1,1) * 10000) +
          (regexp_substr(ptag,'[^.\D]+',1,2) * 100) +
          (regexp_substr(ptag,'[^.\D]+',1,3) * 1)) 
    INTO vretorno
    FROM dual;

  RETURN(vretorno);
END;

Inquiry:

SELECT r.tag
      ,f_convert_tag_to_number(r.tag)
  FROM repositorio r
 WHERE f_convert_tag_to_number(r.tag) BETWEEN
       f_convert_tag_to_number('1.0.0') AND
       f_convert_tag_to_number('1.5.0')

Problem, this example above query was just an example, I have a much larger query that uses this function about 15 times, and what I want is to reduce the use of the f_convert_tag_to_number function

Does anyone know of any way?

Thank you

sql oracle query

asked by anonymous 23.11.2016 / 20:06

1 answer

Error opening MySQL connection via ADODB create dynamic subdomains in the registry br [closed]

score 2 · Accepted Answer

You can create a new column to mirror the information you want:

ALTER TABLE repositorio ADD numeric_tag NUMBER(12);

And fill it in with a trigger when inserting or updating:

CREATE OR REPLACE TRIGGER trigger_conversao_tag
BEFORE INSERT OR UPDATE of tag
ON repositorio
FOR EACH ROW
AS
BEGIN
  :new.numeric_tag = f_convert_tag_to_number(:new.tag);
END;

Using query as follows:

SELECT r.tag
      ,r.numeric_tag
  FROM repositorio r
 WHERE r.numeric_tag BETWEEN
       f_convert_tag_to_number('1.0.0') AND
       f_convert_tag_to_number('1.5.0')