Long Numbers (many characters)

[Christo Kutrovsky]

Long numbers are presented in Scientific format, wile I need them to be in decimal.

SQL> select 20030325000000745 from dual;

20030325000000745
-----------------
2.00303250000007E

Is there any way to control when numbers are convert to scientific format ?

Using the "Numbers to_char" options is not acceptable, as then numbers are treated as strings and when sorting it's not sorting properly (i.e. 1,12,2,3,31 .. instead of 1,2,3,12,31)

 

[Christo Kutrovsky]

Also CLOBS are always show as "". I would like to be able to see the first N bytes from it (have an option to control N).

 

[Marco Kalter]

Both issues cannot be influenced. You can only set the "Number fields to_char" preference for the SQL Window, but like you said, it will no longer sort numerically that way.

------------------
Marco Kalter
Allround Automations

 

[Christo Kutrovsky]

Thanks,

Can those be added to the enhancements requset list ?

 

[aotte]

It looks like a date and time to me, so would converting it to a TIMESTAMP help (Assuming you are on 9iR2)?

If not, maybe this does the trick for you?

SELECT
   LPAD(nbr,17,'0') nbr
FROM
   (
   SELECT 20030325000000745 nbr
   FROM dual
   UNION
   SELECT 2
   FROM dual
   UNION
   SELECT 3
   FROM dual
   )
ORDER BY
   LPAD(nbr,17,'0')
;

------------------
Hakuna Matata,

Arnoud.

 

[Christo Kutrovsky]

It is a date and time and a sequence.

That's how we generate our Primary Keys.

I need it is a number

 

[Marco Kalter]

I have added this to the list of enhancement requests.

------------------
Marco Kalter
Allround Automations

 

[aotte]

Originally posted by Christo Kutrovsky:
It is a date and time and a sequence.

That's how we generate our Primary Keys.

I need it is a number

You made me curious now - well, ok, my parents did the prep work for you

Why add the date and time, if you are already using a sequence?

Why is the sequence alone not enough to function as a primary key?

------------------
Hakuna Matata,

Arnoud.

 

[Christo Kutrovsky]

Sorry about the delay

So that you can partition by RANGE and to be able to do partition maintenance easily.

You will ask imediatelly why not use a date column, well .. because it's not the master table that is the problem, it's the details ones. So by partitioning the detail tables by the key, you automatically get (wihtout changing the app) partition elimation, and you get the benefit of been able to quickly drop old data.

 

[Christo Kutrovsky]

Another problem with the numbers is when one uses the test window.

When you test a procedure with such big numbers, and use bind variables, and set the type to FLOAT, then an incorrect number is given to oracle.
The only work arround is to set the bind variable to string.

 

[aotte]

Originally posted by Christo Kutrovsky:
Sorry about the delay

Don't be silly, here only Marco has deadlines.... How's 5.1.4 coming along?

Why concatenate date and sequence for PK?

So that you can partition by RANGE and to be able to do partition maintenance easily.

You will ask imediatelly why not use a date column, well .. because it's not the master table that is the problem, it's the details ones. So by partitioning the detail tables by the key, you automatically get (wihtout changing the app) partition elimation, and you get the benefit of been able to quickly drop old data.

Actually, my immediate would have been: why do you need a date to partition by range? You can do the same with a sequence, achieving the same partition elimination.

Now, the old data argument sways me more. However, I find that operational systems have the nasty habit that you cannot just cut off a whole partition when it is based on date only. The cut-off always seems to be on a date and a certain status code of the transaction and/or it's details, commonly the header status.

If you don't have that need, your approach would be just handy-dandy. I suggest using a Julian date though, less space, and easy for 90 days ago type calculations. We have YYYYMMDD here as well in places, and I regret not having converted it to Julian.

Hmmm, nice idea, but that will of course not work due to the concatenation. Two variable length numbers is rather yucky, as it can cause duplicates. OK, I see the need for the YYYYMMDDHH24MISS now: Chronology, with the sequence to make it unique. Did you look at the TIMESTAMP with the fractions of seconds? We currently haven't been able to get it to return duplicates, but then we're running a DW here, so not that many concurrent users on the same table..... Oh, well, so much for my ideas, enough rambling from me....

------------------
Hakuna Matata,

Arnoud.

[This message has been edited by aotte (edited 03 October 2003).]

 

[Christo Kutrovsky]

Hmm .. just tought of something even better ...

I could just use a sequence, and partition by range on that sequence, and use another table to map sequence numbers to days ... just like oracle does with SCNs

but hey ... it's working fine now

P.S.
It has some performance improvements also, the load on the machine droped hugelly when we started using this.

 

[aotte]

Originally posted by Christo Kutrovsky:
I could just use a sequence, and partition by range on that sequence, and use another table to map sequence numbers to days ... just like oracle does with SCNs

but hey ... it's working fine now

P.S.
It has some performance improvements also, the load on the machine droped hugelly when we started using this.

Yeah, that would work. Have a single job run every midnight and write out NEXTVAL to a separate table. Negative piece being that it won't take into account any batch transactions that run over midnight, or other timezone activities that need to be adhered to, but that would be a problem anyway. You could put the job at the end of a nightly batch cycle. It would not be calendar daily, but batch daily. That might work better. Actually this discussion has put me onto a solution for one of my problems.

Thanks Christo!

Yeah, partition elimination can be a real help. Seen things go a factor 60 quicker in some cases! All depends on your partition choices and application of course.

------------------
Hakuna Matata,

Arnoud.

 

[Christo Kutrovsky]

Glad I could help

 

[Christo Kutrovsky]

I am updating this one as I found another issue with long numbers:

When you use the Linked Query feature, the generated SQL does not find the child rows, as it is using the sicentific format which is rounded.

 

[Marco Kalter]

We'll fix this.