Solved

This is my function, it is not working

Posted on 2011-03-03
8
294 Views
Last Modified: 2012-05-11
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
Comment
Question by:enrique_aeo
  • 4
  • 3
8 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 167 total points
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 

Author Comment

by:enrique_aeo
Comment Utility
VIEW FILE
and use the rownum in the insert into?

sequenceROWNUM.jpg
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 333 total points
Comment Utility
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
 

Author Comment

by:enrique_aeo
Comment Utility
RANK() OVER (PARTITION BY CodSolicitud ORDER BY fechorainicio)
0
 

Author Comment

by:enrique_aeo
Comment Utility
please, confirm
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 333 total points
Comment Utility
>>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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now