Solved

This is my function, it is not working

Posted on 2011-03-03
8
295 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
ID: 35030829
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)
ID: 35030881
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
ID: 35031021
VIEW FILE
and use the rownum in the insert into?

sequenceROWNUM.jpg
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35031056
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
ID: 35031094
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
ID: 35031106
RANK() OVER (PARTITION BY CodSolicitud ORDER BY fechorainicio)
0
 

Author Comment

by:enrique_aeo
ID: 35031129
please, confirm
0
 
LVL 76

Assisted Solution

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

920 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

16 Experts available now in Live!

Get 1:1 Help Now