Hi,
I am having a huge problem with an INSERT to Oracle and formatting of numbers. I am using PHP to perform the INSERT.
My problem is this: I am trying to insert a dollar amount into a table. The field I am inserting to is of type NUMBER(15,2). The SQL I am using is as follows:
Code:
$query = "INSERT INTO ETECDBA.IVR_COMMANDTABLE(
RECORDKEY,
AGENCYDESIGNATOR,
PAYMENTAMOUNT,
CREDITORDEBITCARDTYPE,
CREDITORDEBITCARDNUMBER,
CREDITORDEBITCARDEXPIRYDATE,
CREDITORDEBITCARDAUTHNUM,
VENDORID,
VENDORTRANSACTIONNUMBER,
COMMANDNUMBER
) VALUES (
$recordKey,
'$agencyCode',
to_number ($amountDue, '999.99'),
'VISA',
'4242********4242',
'04/06',
'111111',
'$vendorID',
001,
1)";
$amountDue when echo'd equals 4.50
The insert executes fine. After this is done I do a query on a view to find the total cash paid.
Code:
$query = "SELECT RECORDKEY, to_char(AMOUNTDUE, '999.99') as MYAMOUNTDUE, to_char(TOTALCASHPAID,'999.99')as MYTOTALCASHPAID,
AGENCYDESIGNATOR, ISSUENO, ISSUEDATE, ISSUETIME, LICPLATE, LICSTATEPROV, PAYABLE
FROM ETECDBA.VIEWIVR_XXX_ISSUENO
WHERE ISSUENO = '$issueNo'
AND AGENCYDESIGNATOR = '$agencyCode'";
The TOTALCASHPAID field now shows 45.00.
Somewhere my 4.50 is having the decimal precision changed to be 45.00, but I don't know where!
If I explicity set the amountDue in the to_number, like this:
to_number ('4.50', '999.99')
.. all works fine.