Home » SQL & PL/SQL » SQL & PL/SQL » need suggestion with logic
need suggestion with logic [message #254177] Thu, 26 July 2007 01:42 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i am i am preparing scripts so that the end user can just run the scripts
which would insert the data into tables

now i am using a sequence and populating the seqname.nextval into a variable (myvar)

lets say for a particular customer, this myvar value is 3..(populated by a sequence)


there is another column called sindex..which should begin with 700..and a formula
is involved like this to populate the sindex

sindex:=100*myvar+100 (700), and then i increment this sindex for further insertion

now my problem is...for the next customer, which would be customer 4, there would be
another file, another script.

the sindex should be 1000 ( a differece of 300)..and i need to arrive at a
generic formula (similar to sindex:=100*myvar+100), which would work for all the
customers.

i mean this formula should be same for all the files, but it should show a
difference of 300 per each customer

can anyone suggest me?
Re: need suggestion with logic [message #254183 is a reply to message #254177] Thu, 26 July 2007 01:57 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How about creating a FUNCTION which would have one IN parameter ('myvar') and return 'sindex'; something like this:
SQL> CREATE OR REPLACE FUNCTION fun_sindex (par_myvar IN NUMBER)
  2  RETURN NUMBER
  3  IS
  4    retval NUMBER;
  5  BEGIN
  6    retval := 100 * par_myvar + 100;
  7    RETURN (retval);
  8  END;
  9  /

Function created.

SQL> CREATE TABLE TEST (id NUMBER, name VARCHAR2(20));

Table created.

SQL> INSERT INTO TEST (id, name) VALUES (fun_sindex(3), 'Littlefoot');

1 row created.

SQL> INSERT INTO TEST (id, name) VALUES (fun_sindex(4), 'gautamvv');

1 row created.

SQL> SELECT * FROM TEST;

        ID NAME
---------- --------------------
       400 Littlefoot
       500 gautamvv

SQL>

I just copied your formula. Modify it to fulfill your needs if necessary.

[Updated on: Thu, 26 July 2007 01:57]

Report message to a moderator

Re: need suggestion with logic [message #254185 is a reply to message #254177] Thu, 26 July 2007 02:05 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
right, but i am using anonymous blocks for all the things here,
not procs..

also the difference has to be 300.( not 100).i am actually looking to
arrive at a generic formula..

which i would initialize to sindex initially...

Re: need suggestion with logic [message #254194 is a reply to message #254185] Thu, 26 July 2007 02:25 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So "rename" anonymous block to a procedure. Or, are you going to write that anonymous block every time you need to do ... whatever you do.

If difference has to be 300, fine - sit down and think about it, take a pencil and write formulas until you invent a working one. Then code it into the procedure.
Previous Topic: ORA-00604: error occurred at recursive SQL level 1
Next Topic: how to get first day in the date
Goto Forum:
  


Current Time: Tue Dec 06 14:02:34 CST 2016

Total time taken to generate the page: 0.16258 seconds