Home » SQL & PL/SQL » SQL & PL/SQL » Need to find fast (oracle forums 6i)
Need to find fast [message #576788] Fri, 08 February 2013 00:11 Go to next message
Srini_DEV
Messages: 20
Registered: January 2012
Location: India
Junior Member

Hi All,

I'm in a situation to create a folder number.

Here is my sample table structure
Table Name: client_details

Column       Datatype

client_ID    varchar2(15)
client_Name  varchar2(15)
Folder_No    varchar2(9)



Note: One client can have multiple folders

Hint: folder number prefix with client_ID

here is my sample data in my Table

Client_ID     Client_Name      Folder_NO
HE0011         semaphore       HE0011
HE0011         semaphore       HE0011/1
HE0011         semaphore       HE0011/2
HE0011         semaphore       HE0011/5
HE0011         semaphore       HE0011/99
HE0012         Raph            HE0012
HE0013         Stranger        HE0013
HE0014         xero            HE0014


My Current folder generation code:

DECLARE
V_FOLDER_COUNT NUMBER;

SELECT SUBSTR(FOLDER_NO, DECODE(INSTR(FOLDER_NO, '/'), 0, NULL, INSTR(FOLDER_NO, '/'))+1)
		AS V_FOLDER_COUNT
		FROM client_details
		WHERE CLENT_ID='HE0011';


and now I'm using that V_FOLDER_COUNT to generate next FOLDER_NO.

But,If I execute the above one I'm getting 100. But If I tries to generate the FOLDER_NO it will generate HE0011/100 (length would be 10chars).. column length problem

MY IDEA:

I want to use the intermediate numbers...
[I need an algorithm which generates a FOLDER_NO and it should be effective and fast ]

It was an urgent requirement.
Re: Need to find fast [message #576790 is a reply to message #576788] Fri, 08 February 2013 00:14 Go to previous messageGo to next message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I need an algorithm which generates a FOLDER_NO and it should be effective and fast

A sequence.

As of the column length (9 characters) and output you got: either the column size is too small, or your algorithm is wrong. What would you do if someone REALLY reaches 100? What's the problem in making that column larger?

[Updated on: Fri, 08 February 2013 00:16]

Report message to a moderator

Re: Need to find fast [message #576791 is a reply to message #576790] Fri, 08 February 2013 00:15 Go to previous messageGo to next message
Srini_DEV
Messages: 20
Registered: January 2012
Location: India
Junior Member

Yes,We can use sequence.
but...this is different situation. I'm looking for a code to generate FOLDER_NO
Re: Need to find fast [message #576793 is a reply to message #576791] Fri, 08 February 2013 00:16 Go to previous messageGo to next message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
(I added a few more words while you were typing the message. Have a look, please).
Re: Need to find fast [message #576794 is a reply to message #576793] Fri, 08 February 2013 00:25 Go to previous messageGo to next message
Srini_DEV
Messages: 20
Registered: January 2012
Location: India
Junior Member

Yes we can increase the column size..
I would like to include one more point here.. FOLDER_NO can generate in 2ways
1.Manually: User can enter any FOLDER_NO prefix by his CLIENT_ID
2. Auto generation. {above mention code i'm using to generate}

Consider a situation If I increase the FOLDER_NO column size to 20 then the user entered HE0011/9999999999999 (max column size). If I use the same code for AUTO GENERATION. I can't use Sad
Re: Need to find fast [message #576796 is a reply to message #576794] Fri, 08 February 2013 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not allow user to enter a value for folder number, generate and return it when it create a new row in the table.

Regards
Michel
Re: Need to find fast [message #576797 is a reply to message #576796] Fri, 08 February 2013 00:34 Go to previous messageGo to next message
Srini_DEV
Messages: 20
Registered: January 2012
Location: India
Junior Member

We cann't do that.. Client can enter FOLDER_NO
As I already mentioned
Quote:

FOLDER_NO can generate in 2ways
1.Manually: User can enter any FOLDER_NO prefix by his CLIENT_ID
2. Auto generation. {above mention code i'm using to generate}
Re: Need to find fast [message #576799 is a reply to message #576797] Fri, 08 February 2013 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you said it, nothing prevent from changing this.
It seems your folder must start with client_id (which is already a bad design to store that), so what happens if a client enters something different like 'XXXXXXXXX'?

Regards
Michel

[Updated on: Fri, 08 February 2013 00:39]

Report message to a moderator

Re: Need to find fast [message #576801 is a reply to message #576799] Fri, 08 February 2013 00:43 Go to previous messageGo to next message
Srini_DEV
Messages: 20
Registered: January 2012
Location: India
Junior Member

we are validating the FOLDER_NO.. where it has his CLIENT_ID or not..

We are storing the FOLDER_NO with CLIENT_ID because, to identify the folder client..
Re: Need to find fast [message #576805 is a reply to message #576801] Fri, 08 February 2013 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
e are validating the FOLDER_NO.. where it has his CLIENT_ID or not..


So a better design is to generate the folder name.
You have a wrong design you try to workaround with an implementation trick, This will fail soonner or later. There is no doubt about this.
Fix your design: do not allow a client to enter a folder, use a sequence to generate it and store ONLY the number (and so the name FOLDER_NO has a correct name in opposite than with the current design).

Regards
Michel

Re: Need to find fast [message #576811 is a reply to message #576805] Fri, 08 February 2013 02:22 Go to previous message
Srini_DEV
Messages: 20
Registered: January 2012
Location: India
Junior Member

Thanks for you suggestions..

But.. Any other solutions, much appreciated Smile
Previous Topic: COUNTING THE DISTINCT OCCURENCE OF DATA FROM MULTIPLE COLUMNS
Next Topic: Make columns out of records.
Goto Forum:
  


Current Time: Sat Nov 22 23:14:48 CST 2014

Total time taken to generate the page: 0.08044 seconds