0

I've a query like this:

SELECT personas.IDDNI                                                      iddni,
       PERSONAS.NOMBRE                                                     NOMBRE,
       PERSONAS.APELLIDO1                                                  APELLIDO1,
       PERSONAS.APELLIDO2                                                  APELLIDO2,
       PERSONAS.ANTIGUEDAD                                                 ANTIGUEDAD,
       VACACIONES(personas.IDDNI, to_char(add_months(sysdate, 0), 'YYYY')) VACACIONES
FROM   personas personas,
       trieniosobservaciones t
WHERE  personas.iddni = t.iddni
       AND ( personas.iddni = '47656567' )

I'd like to know what VACACIONES(personas.IDDNI,to_char(add_months(sysdate,0),'YYYY')) VACACIONES does in the query, as depending on the personas.iddni value it can return one row or give the following error:

The number specified in exact fetch is less than the rows returned.

MT0
  • 143,790
  • 11
  • 59
  • 117
user2638180
  • 1,013
  • 16
  • 37
  • 1
    https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms – Reporter Jan 05 '23 at 08:39
  • 1
    Tip of today: Always use modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! – jarlh Jan 05 '23 at 08:42
  • 1
    VACACIONES is obviously a function that you or somebody else added to your database. Use SQL Developer to open that function code and see what it does. – Thorsten Kettner Jan 05 '23 at 08:44
  • 1
    On a side note: Why are you joining trieniosobservaciones? You don't seem to be using it in your query. And `add_months(sysdate, 0)` is the same as a mere `sysdate`. – Thorsten Kettner Jan 05 '23 at 08:49
  • 1
    As thorsten says, `VACACIONES()` is a custom function. Contain your database administrator to find out more. If you tag your DB vendor, we might be able to tell you how to identify the function definition – moo Jan 05 '23 at 09:44
  • For Oracle: https://stackoverflow.com/questions/7167307/how-to-print-out-the-definition-of-a-procedure-in-oracle – moo Jan 05 '23 at 09:46

1 Answers1

1

VACACIONES is a user-defined function that takes two arguments, an IDDNI value for a person and a year. Beyond that, we cannot tell you what it does because it is a user-defined function and we do not have access to your database or the source code of the function.

You can find the source-code of the function using:

SELECT *
FROM   all_source
WHERE  name = 'VACACIONES'
AND    type = 'FUNCTION'
ORDER BY owner, line;

and then you can work out what it does.

MT0
  • 143,790
  • 11
  • 59
  • 117