Comparing data via Oracle Query

Asked

Viewed 2,545 times

0

I have a table called Teste de aluno and a call Parametros I need to create a Query that compares if the test is in accordance with 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

Table Parameter:

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

Is there any way via Oracle to perform this comparison and check if the data matches?

I checked the controls MINUS, INTERSECT and CASE... but I thought it was kind of hazy and how would I do it...

sql oracle

  • How do the data relate ? I believe a simple JOIN solves.

  • The two tables do not relate to each other

  • But if the tables don’t have a relation how to compare them, go to something like a test.IDTESTE = parameter.idIDPARAMETRO and test.idano = parameter.idano , but it doesn’t seem to be this way.

  • Then, the parameter table literally only serves as a parameter to know which tests validate the students

  • Do not understand, try to improve your initial explanation. Maybe with examples.

  • You first need to know with which parameter of the table parameters you want to compare your test. Because in the table parameters by name, I think it can store N records of different parameters. Or an alternative would be you create a parameter table fk inside the table testeDeluno, but there would have been no sense to store the parameters in table testeDeluno.

Show 1 more comment

1 answer

1

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

Using Decode:
Decode would be with an if more 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 Voce can do N testing below each 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 keeps only one record, then you do not need to have the restriction: p.id_parameter = 1, this restriction serves to search in the table parameters which the record among all you want to compare with the test table.

Browser other questions tagged sql oracle

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