• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

SQL Question...

Page 3 - Seeking answers? Join the AnandTech community: where nearly half-a-million members share solutions and discuss the latest tech.
Let's make sure we are all on the same page.

1) You have 2 tables, X and Y, respectively.
1) The data in table X is stored in regular numeric format: '123.45' or '12' or whatever.
2) The data in table Y needs to be stored in the fancy padded format: '00123.45' or '00012.00' or whatever, but still be numeric.
3) You are moving data from X into Y, and attempting to pad it as you are inserting.

Is this all correct?

I'm telling you now, if the column in table Y is defined as a numeric datatype, Oracle will strip all nonsignificant digits from the data before inserting it into the table. If you absolutely need the data stored in '00000.00' format, you will have to define it as VARCHAR or VARCHAR2. Otherwise, leave the data in the table in standard numeric format, and simply pad the data for display purposes (forms, web pages, any database applications, etc).

Something I noticed. The columns are all defined as whole numbers - ie, all precision is lost when inserting into the table. There is still something you either aren't telling us, or are forgetting.
 
Originally posted by: KnightBreed
Let's make sure we are all on the same page.

1) You have 2 tables, X and Y, respectively.
1) The data in table X is stored in regular numeric format: '123.45' or '12' or whatever.
2) The data in table Y needs to be stored in the fancy padded format: '00123.45' or '00012.00' or whatever, but still be numeric.
3) You are moving data from X into Y, and attempting to pad it as you are inserting.

Is this all correct?

I'm telling you now, if the column in table Y is defined as a numeric datatype, Oracle will strip all nonsignificant digits from the data before inserting it into the table. If you absolutely need the data stored in '00000.00' format, you will have to define it as VARCHAR or VARCHAR2. Otherwise, leave the data in the table in standard numeric format, and simply pad the data for display purposes (forms, web pages, any database applications, etc).

Something I noticed. The columns are all defined as whole numbers - ie, all precision is lost when inserting into the table. There is still something you either aren't telling us, or are forgetting.
Click to expand...

When he passes it, he's getting 12 converted to 0000.12 instead of 0012.00 or 0012
 
Originally posted by: Beau6183
When he passes it, he's getting 12 converted to 0000.12 instead of 0012.00 or 0012
Click to expand...
I don't think Oracle isn't going to store all those extra zero's in the table, so why bother trying to pad it? In any event, to_char(data_field, '00000.00') should work.

Hey spooner can you post the syntax you're using to move the data from table X to table Y?
 
Originally posted by: KnightBreed
Originally posted by: Beau6183
When he passes it, he's getting 12 converted to 0000.12 instead of 0012.00 or 0012
Click to expand...
I don't think Oracle isn't going to store all those extra zero's in the table, so why bother trying to pad it? In any event, to_char(data_field, '00000.00') should work.

Hey spooner can you post the syntax you're using to move the data from table X to table Y?
Click to expand...
The table I'm pulling the data from is defined as a "varchar2" field.

I'm passing all the field that I grab into one long datastring (the variable's actually called vDataString. I concatenate them all with the null val funciton like this:

nvl( UPPER(LPAD(vDepAmt,8,'00000.00')) , LPAD('00000.00',8) )

It's LPAD'ed because if the screen_entry_value (the value i'm grabbing passed into a varible called vDepAmt) is less than 5 digits, they vendor wants the leading zero's attached.

I hope this helps... this problem is annoying the hell out of me :disgust:
 
Originally posted by: Spooner
The table I'm pulling the data from is defined as a "varchar2" field.

I'm passing all the field that I grab into one long datastring (the variable's actually called vDataString. I concatenate them all with the null val funciton like this:

nvl( UPPER(LPAD(vDepAmt,8,'00000.00')) , LPAD('00000.00',8) )

It's LPAD'ed because if the screen_entry_value (the value i'm grabbing passed into a varible called vDepAmt) is less than 5 digits, they vendor wants the leading zero's attached.

I hope this helps... this problem is annoying the hell out of me :disgust:
Click to expand...
Ok, finally we're getting somewhere. The field you are pulling from you said is VARCHAR2, fine. What kind of data is in it? It it all numeric?

Have you tried something as simple as:

trim(to_char(vDeptAmt, '00000.00'))

It worked perfectly fine for me.

Original || Formatted
-----------------------------
0      ||      00000.00
1      ||      00001.00
1003      ||      01003.00
1046      ||      01046.00
11056      ||      11056.00
11487      ||      11487.00
12459      ||      12459.00
13820      ||      13820.00
1489      ||      01489.00
15      ||      00015.00
 
Originally posted by: KnightBreed
Originally posted by: Spooner
The table I'm pulling the data from is defined as a "varchar2" field.

I'm passing all the field that I grab into one long datastring (the variable's actually called vDataString. I concatenate them all with the null val funciton like this:

nvl( UPPER(LPAD(vDepAmt,8,'00000.00')) , LPAD('00000.00',8) )

It's LPAD'ed because if the screen_entry_value (the value i'm grabbing passed into a varible called vDepAmt) is less than 5 digits, they vendor wants the leading zero's attached.

I hope this helps... this problem is annoying the hell out of me :disgust:
Click to expand...
Ok, finally we're getting somewhere. The field you are pulling from you said is VARCHAR2, fine. What kind of data is in it? It it all numeric?

Have you tried something as simple as:

trim(to_char(vDeptAmt, '00000.00'))

It worked perfectly fine for me.

Original || Formatted
-----------------------------
0      ||      00000.00
1      ||      00001.00
1003      ||      01003.00
1046      ||      01046.00
11056      ||      11056.00
11487      ||      11487.00
12459      ||      12459.00
13820      ||      13820.00
1489      ||      01489.00
15      ||      00015.00
Click to expand...

That's what I originally suggested, storing it as a string, but he said it has to be numeric.
 
LPAD is not going to work for you purposes. LPAD pads the left of the string with pattern you specify in the third argument. Meaning:

LPAD('123.1', 28, '00000.00')

will give you:

00000.0000000.0000000.0123.1

It repeats the pattern you specify over and over until it reaches the length you specify in the second argument. It pads the left of the string with the specified pattern. It will not reformat '123' into '00123.00'.
 
Originally posted by: KnightBreed
LPAD is not going to work for you purposes. LPAD pads the left of the string with pattern you specify in the third argument. Meaning:

LPAD('123.1', 28, '00000.00')

will give you:

00000.0000000.0000000.0123.1

It repeats the pattern you specify over and over until it reaches the length you specify in the second argument. It pads the left of the string with the specified pattern. It will not reformat '123' into '00123.00'.
Click to expand...

That's what I found, as well.... to_char(MyVal, '0999.99') is going to be your best bet.
 
If you absolutely must use LPAD(), then you'll need to do the following:

DECODE(INSTR(vDeptAmt, '.'),
-- If the string doesn't contain a decimal
    0,
-- pad the left side and then add the decimal manually.
    LPAD(vDeptAmt, 5, '0') || '.00',
-- else, assume there is a decimal, and pad each side separately.
    LPAD(SUBSTR(vDeptAmt, 1, INSTR(vDeptAmt, '.') - 1), 5, '0') ||
    '.' ||
    RPAD(SUBSTR(vDeptAmt, INSTR(vDeptAmt, '.') +1), 2, '0'))
 
Originally posted by: KnightBreed
Originally posted by: Spooner
The table I'm pulling the data from is defined as a "varchar2" field.

I'm passing all the field that I grab into one long datastring (the variable's actually called vDataString. I concatenate them all with the null val funciton like this:

nvl( UPPER(LPAD(vDepAmt,8,'00000.00')) , LPAD('00000.00',8) )

It's LPAD'ed because if the screen_entry_value (the value i'm grabbing passed into a varible called vDepAmt) is less than 5 digits, they vendor wants the leading zero's attached.

I hope this helps... this problem is annoying the hell out of me :disgust:
Click to expand...
Ok, finally we're getting somewhere. The field you are pulling from you said is VARCHAR2, fine. What kind of data is in it? It it all numeric?

Have you tried something as simple as:

trim(to_char(vDeptAmt, '00000.00'))

It worked perfectly fine for me.

Original || Formatted
-----------------------------
0      ||      00000.00
1      ||      00001.00
1003      ||      01003.00
1046      ||      01046.00
11056      ||      11056.00
11487      ||      11487.00
12459      ||      12459.00
13820      ||      13820.00
1489      ||      01489.00
15      ||      00015.00
Click to expand...
that doesn't work for me

vDepAmt := trim(to_char(vDepAmt,'00000.00'));
dbms_output.put_line(vDepAmt);

gets me
27.27 instead of 00027.27


 
Originally posted by: Spooner
that doesn't work for me

vDepAmt := trim(to_char(vDepAmt,'00000.00'));
dbms_output.put_line(vDepAmt);

gets me
27.27 instead of 00027.27
Click to expand...
Stupid question, perhaps, but how is vDeptAmt defined in your function/package/procedure/whatever? It has to be VARCHAR/2.
 
Originally posted by: KnightBreed
Originally posted by: Spooner
that doesn't work for me

vDepAmt := trim(to_char(vDepAmt,'00000.00'));
dbms_output.put_line(vDepAmt);

gets me
27.27 instead of 00027.27
Click to expand...
Stupid question, perhaps, but how is vDeptAmt defined in your function/package/procedure/whatever? It has to be VARCHAR/2.
Click to expand...
vDepAmt is delcared at the top of the package as a Number

If it's declared as a varchar2, i get a " PLS-00307: too many declarations of 'TO_CHAR' match this call" error
 
Hmm.... I've been testing my suggestions in Oracle8i v8.1.7, and it's been accepting to_char(some_string, 'xxx')

It appears that to_char() is erroring because its attempting to convert a string that is already a string. Not a problem, keep vDeptAmt as VARCHAR2, but convert it implicitly with to_number() first - like so:

trim(to_char(to_number(vDeptAmt), '00000.00'))

It works. 🙂 Just keep vDeptAmt as a string variable! 😀
 
It looks like that's working!!!!!

Guys, thank you so much with all your help! I reaaaaaaaaaaaaaaaally appreciate it. 10's all around 😀
 
Originally posted by: KnightBreed
Originally posted by: Spooner
It looks like that's working!!!!!

Guys, thank you so much with all your help! I reaaaaaaaaaaaaaaaally appreciate it. 10's all around 😀
Click to expand...
'Bout f*cking time! 😛
Click to expand...
i know, right?

it only took 70 posts 😉

thanks again!

 
Originally posted by: Beau6183
Awesome 😀

Guess the trim made all the difference 🙂
Click to expand...
No, you had the to_number() and to_char() backwards. 😀 You need to convert it to number first, then back to string. I put the trim() in there because, for some reason, the conversion back to string was adding a space before the string. I may have just had bad data, I don't know. *shrug*
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top