Cast varchar to int problem

paragvshah · Dec 14, 2002

I want to insert a row in a table where the datatype of customernumber is varchar(6) and with every insertion of row the cutomernumber should be incremented by 1 after getting the max id. for that i need to cast the customernumber from varchar to int. so i am using the following syntax to get the max id.

SELECT MAX(to_number(trim(CustomerNumber),'999999')) as max1 FROM Customers

I have cold fusion as my front end for coding.

I am able to run the above query successfully on my local postgresql database but not thru coldfusion.

I am getting an error while running the page through cold fusion:

Error Diagnostic Information
ODBC Error Code = S1000 (General error)

Error while executing the query; ERROR: Bad numeric input format ' '

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (13:1) to (13:90).

Please help me out with this problem.

Parag.

Leland123 · Dec 14, 2002

Hi paragvshah,

Most parent tables have a unique primary key field. Postgres can automatically provide a unique key to your customer table with a sequence. A sequence will automatically supply a unique number for each record inserted into your customer table. When using a sequence, it would not be necessary to make any reference to the init(4) or init(8) serial field, as postgres would take the periously used number, auto increment it by one, and automatically insert it into customer table during inserts. As of postgres 7.3, there is a new default value that can be used in the insert statement, but it really is not necessary. For example you might insert like:

insert into customer (customer_id,First,last,company) values (default,'Leland','Jackson','Software - Master')

Please check the syntax of [default] as I have never really seen it used, but am aware of it because it was mentioned on the postgres mail lists.

Here is a link about sequences:

http://www.ca.postgresql.org/users-lounge/docs/6.5/user/sql-createsequence.htm

Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Cast varchar to int problem

paragvshah

Programmer

Leland123

Programmer

Similar threads

Part and Inventory Search

Sponsor