• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

This is my function, it is not working

i have this query
INSERT INTO   SEGUIMIENTO
          (
              NUMEROINSTRUCCION,
            ...
              ,SECUENCIA
              ,OBSERVACION
              ,CODIGOROL
          )
select
              CodSolicitud,
            ...
              NumSecuencia,
              null,
              30
from tmp_SolicitudFlujo;

I replace the column by a corresponding NumSecuencia starting at 1, ie, if the numeroinstruccion this 6 times, then the NumSecuencia should go from 1 to 6. See graphic


This is my function, but it works well
CREATE OR REPLACE FUNCTION fn_SECUENCIASEGUIMIENTO(
                            pic_NUMEROINSTRUCCION IN INSTRUCCIONOPERATIVA.NUMEROINSTRUCCION%TYPE
                          )
    RETURN INTEGER
  IS
      v_result INTEGER;
  BEGIN

    SELECT NVL(COUNT(*),0)
    INTO v_result
    FROM SEGUIMIENTO S
    WHERE S.NUMEROINSTRUCCION = pic_NUMEROINSTRUCCION;

    IF v_result  = 0 THEN
       v_result  := 1;
    ELSE
       v_result  := v_result + 1;
    END IF;

    IF v_result IS NULL THEN
          RETURN (NULL);
     ELSE
          RETURN (v_result);
     END IF;

  EXCEPTION
      WHEN OTHERS THEN
          RAISE;

END fn_SECUENCIASEGUIMIENTO;
instruccionSECUENCIA.jpg
useSECUENCE.jpg
0
enrique_aeo
Asked:
enrique_aeo
  • 4
  • 3
3 Solutions
 
sdstuberCommented:
what is the picture?  Is that input or expected output?

if input, what is the expected output?
If output, what is the input?

Also,  how are you expecting to use this function?  You won't be able to do it with a select statement.

If you simply want sequential numbers, why not use ROWNUM?
0
 
slightwv (䄆 Netminder) Commented:
I have to assume you are looking for gap-less numbers continuing on from the total number of rows in the database.

Even if you could use it in a insert/select like you want, that function would not guarantee unique or gapless.

Go with an Oracle sequence.

Also in the function as you have it, the following code doesn't really do anything:

    IF v_result IS NULL THEN
          RETURN (NULL);
     ELSE
          RETURN (v_result);
     END IF;

0
 
enrique_aeoAuthor Commented:
VIEW FILE
and use the rownum in the insert into?

sequenceROWNUM.jpg
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
slightwv (䄆 Netminder) Commented:
sdstuber has already stated you cannot use that function the way you want to:  in an insert/select.

The reason:  The function gets called only once when the statement is parsed and never again.

It's the same as:
select sysdate from dba_objects;

No matter how long it takes to select all the objects, the sysdate value stays the same.
0
 
slightwv (䄆 Netminder) Commented:
try something like this using a count and getting rid of your function all together.

Note: It still might not ensure unique numbers.

INSERT INTO   SEGUIMIENTO
          (
...
          )
select
(count(1) over() + rownum)
...
from tmp_SolicitudFlujo;

0
 
enrique_aeoAuthor Commented:
RANK() OVER (PARTITION BY CodSolicitud ORDER BY fechorainicio)
0
 
enrique_aeoAuthor Commented:
please, confirm
0
 
slightwv (䄆 Netminder) Commented:
>>RANK() OVER (PARTITION BY CodSolicitud ORDER BY fechorainicio)

That should work if you want to start over with 1 every time.  It that's the case, rownum will work.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now