Home » Other » Training & Certification » Leading Value
Leading Value [message #267365] Thu, 13 September 2007 02:00 Go to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Hi guys

Im using Oracle 10g and I am new and was wondering how I can set the field to have a letter that appears in front of a number that can be entered each time so that C is automatically there and the user dont have to type it in only the number eg.

C: 555555




Any help would be greatly appreciated thanks!
Re: Leading Value [message #267367 is a reply to message #267365] Thu, 13 September 2007 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What did you already search and/or try?

Regards
Michel
Re: Leading Value [message #267373 is a reply to message #267367] Thu, 13 September 2007 02:17 Go to previous messageGo to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Hi I tried googling leading value but cant find anything I also tried to set 'c' as the default but it doesnt appear.


thanks
Re: Leading Value [message #267390 is a reply to message #267373] Thu, 13 September 2007 03:17 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Will 'C: ' always be in front of the rest of the string? Will it change to, for example, 'A: '? Could you consider changing the model? For example, alter a table to add another column which would contain this first part of a string (and set its DEFAULT value).

As it is, a database trigger might solve the problem. Something like this:
SQL> create table test (col varchar2(20));

Table created.

SQL> create or replace trigger trg_test_c
  2    before insert on test
  3    for each row
  4  begin
  5    :new.col := 'C: ' || :new.col;
  6  end;
  7  /

Trigger created.

SQL> insert into test (col) values ('1234');

1 row created.

SQL> select * From test;

COL
--------------------
C: 1234

SQL>

If you are looking for a solution in an front-end application (such as Oracle Forms), this principle would work as well. Or, you might write a form trigger to do the same thing.
Re: Leading Value [message #267398 is a reply to message #267390] Thu, 13 September 2007 03:29 Go to previous messageGo to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Perfect!!!

Thank you so much for your help and explanation it is much appreciated.
icon8.gif  Re: Leading Value [message #267446 is a reply to message #267390] Thu, 13 September 2007 05:21 Go to previous messageGo to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
It seems im having a problem implementing this, I have


create or replace trigger trg_Reservation_CN
  2    before insert on Reservation
  3    for each row
  4  begin
  5    :new.confirmationNumber := 'CN: ' || :new.confirmationNumber;
  6  end;
  7  /

Re: Leading Value [message #267450 is a reply to message #267365] Thu, 13 September 2007 05:36 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What problem? What error comes? Give detail information.
Re: Leading Value [message #267599 is a reply to message #267450] Thu, 13 September 2007 17:45 Go to previous messageGo to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Arju the problem is stated below, I have tried the code posted and its just not working there is no error coming up.
Re: Leading Value [message #267630 is a reply to message #267599] Fri, 14 September 2007 00:16 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Think twice before implementing this. If you already thought twice, think again.
Why would you want to store this constant in the same column as the number? If the code is the same for all records, there is no point in storing it, you could simply add it when you display it; if it differs for the distinct parts of your application, store it in a separate column.
This way, you can filter on the prefix much easier.

This is what Littlefoot also told you, but I am afraid you might have read over it in the excitement of getting a 'solution'.
Previous Topic: New Dump for Oracle 10G iz0-007
Next Topic: function with out variable and other doubt
Goto Forum:
  


Current Time: Thu Dec 05 16:28:35 CST 2024