problem with string concatenation in auto-incremented field

[boss1]

hi all
i m using oracle9i and php4 to developa webbased application.
i wrote the code for auto increment in primary key field.but now i want to add a string value with that increment field when a new insert is occured.

so is there anybody to solve it out by providing the sample source code.

bye-

[ronverdonk]

This is an Oracle SQL question. I will move it to that forum.

moderator

[amitpatel66]

hi all
i m using oracle9i and php4 to developa webbased application.
i wrote the code for auto increment in primary key field.but now i want to add a string value with that increment field when a new insert is occured.

so is there anybody to solve it out by providing the sample source code.

bye-

What is the datatype of your primary key field? If it is number then you will not be able to concatenate a string value to an inceremented value and insert it. If the datatype is varchar then it is possible to insert a concatenated string value into a primary key field

Pelase provide your table structure for reference of our experts

[boss1]

hello,
here is my table :
[code=oracle]
CREATE TABLE MRR_ENTRY2
(
MRR_NO VARCHAR2(17)pri mary key,
MRR_DATE DATE,
ACCOUNT_CODE VARCHAR2(17),
AMOUNT NUMBER(10,2),
CHECK_NO VARCHAR2(12),
CHECK_ISSUE_DAT E DATE,
DRAWN_ON_BANK_I D VARCHAR2(10),
BANK_BRANCH_ID VARCHAR2(10),
AGAINST VARCHAR2(50),
PMT_MODE_ID VARCHAR2(10) NOT NULL,
PARTY_CODE VARCHAR2(20) NOT NULL,
COMMISSION NUMBER(10,2),
USERNAME VARCHAR2(20),
LOCATION_ID VARCHAR2(10),
SL_NO VARCHAR2(10),
REMARKS VARCHAR2(500),
PARTY_GL_CODE VARCHAR2(20) NOT NULL,
DO_NO VARCHAR2(17),
DO_DATE DATE
)[/code]


the primary key field is incrementing automatically but i also want to add a sting with incremented value in that field when inserting from html page depending on which string is selected by user.

so can u now help me? plz..it's very very important .

thanks.

[debasisdas]

What is the problem you are facing ?

You should be able to concatenate the string with the number and store to the database.

[amitpatel66]

hello,
here is my table :
[code=oracle]
CREATE TABLE MRR_ENTRY2
(
MRR_NO VARCHAR2(17)pri mary key,
MRR_DATE DATE,
ACCOUNT_CODE VARCHAR2(17),
AMOUNT NUMBER(10,2),
CHECK_NO VARCHAR2(12),
CHECK_ISSUE_DAT E DATE,
DRAWN_ON_BANK_I D VARCHAR2(10),
BANK_BRANCH_ID VARCHAR2(10),
AGAINST VARCHAR2(50),
PMT_MODE_ID VARCHAR2(10) NOT NULL,
PARTY_CODE VARCHAR2(20) NOT NULL,
COMMISSION NUMBER(10,2),
USERNAME VARCHAR2(20),
LOCATION_ID VARCHAR2(10),
SL_NO VARCHAR2(10),
REMARKS VARCHAR2(500),
PARTY_GL_CODE VARCHAR2(20) NOT NULL,
DO_NO VARCHAR2(17),
DO_DATE DATE
)[/code]


the primary key field is incrementing automatically but i also want to add a sting with incremented value in that field when inserting from html page depending on which string is selected by user.

so can u now help me? plz..it's very very important .

thanks.

Are you looking at updating existing values in the table with concatenating them with the string?

or

hence forth concatenate the string and then insert?

[boss1]

Thanks for ur reply

i want to concate string with primary key and then insert in db .

u can also show me the code for updating existing values with concatenation

bye-

[amitpatel66]

Thanks for ur reply

i want to concate string with primary key and then insert in db .

u can also show me the code for updating existing values with concatenation

bye-

[code=oracle]

update table1 SET col1 = COL1||'Welcome'

[/code]

This will update all the values of col1 with col1 concatenated with the string 'Welcome'.

[QVeen72]

Hi,

SAY YOU HAVE ALREADY STORED NUMERIC FROM 1, 2, 3
and Now you want to change them to
A00001
A00002
A00003

Run this query:

[code=oracle]
UPDATE MRR_ENTRY
SET MRR_NO = 'A' || REPLACE (LPAD(MRR_NO, 5), ' ', '0')
[/CODE]

To get Next highest value:
[CODE=ORACLE]
SELECT MAX(MRR_NO) FROM MRR_ENTRY
WHERE MRR_NO LIKE 'A%'
[/CODE]

Regards
Veena