Comparing data via Oracle Query

0

I have a table named Teste de aluno and a call Parametros I need to create a Query that compares if the test conforms to the Parameters

The problem is that the tables are not 100% similar

Test Table:

IDTESTE
PARAMETRO1
PARAMETRO2
PARAMETRO3
PARAMETRO4
PARAMETRO5
PARAMETRO6
PARAMETRO7
PARAMETRO8
PARAMETRO9
PARAMETRO10
IDANO
IDMATRICULA
IDALUNO

Parameter Table:

IDPARAMETRO
IDANO
PARAMETRO1
PARAMETRO2
PARAMETRO3
PARAMETRO4
PARAMETRO5
PARAMETRO6
PARAMETRO7
PARAMETRO8
PARAMETRO9
PARAMETRO10

Is there a way through Oracle to make this comparison and check if the data hits?

I checked the commands MINUS , INTERSECT and CASE ... but I found it foggy and how I would do it ...

    
asked by anonymous 20.01.2015 / 12:38

1 answer

1

If I understood correctly your question would have to be something like this: The test checks if the value of the two columns are equal returns 1, if they are different returns 0 (Zero). So everything that returns "1" will be true.

Using decode:
Decode would be with an if condition that you can use in the query.

SELECT DECODE(tda.parametro1, p.parametro1, 1, 0) check_parametro1
      ,DECODE(tda.parametro2, p.parametro2, 1, 0) check_parametro1
  FROM teste_de_aluno tda
      ,parametros p
 WHERE p.id_parametro = 1
   AND tda.id_ano = p.id_ano;

Using case:
Case also serves as an if condition, but you can do N tests one below the other using the when (condition);

SELECT CASE
         WHEN tda.parametro1 = p.parametro1 THEN
          1
         ELSE
          0
       END check_parametro1
      ,CASE
         WHEN tda.parametro2 = p.parametro2 THEN
          1
         ELSE
          0
       END check_parametro2
  FROM teste_de_aluno tda
      ,parametros p
 WHERE p.id_parametro = 1
   AND tda.id_ano = p.id_ano;

If the parameter table always saves only one record, then you do not need to have the following constraint: p.id_parametro = 1 , this restriction is to look up in the parameter table which record among all you want to compare with the test table.

    
12.03.2015 / 15:06