Yes that was it. Oracle coverts Access97 from currency to varchar.
Thank you very much and have a Happy Holiday from this side of the pond.
I don't know if I'd be a good candiate for your book as I am strictly an
PL/SQL end-user.
P.S. Is there a cross-tab query to transpose the final result to
something like this:
ID 10th 20th 30th 40th 50th 60th 70th 80th 90th 100th
XXXXX $48 $51 $53 $53 $57 $61 $64 $68 $75 $77
Jonathan Lewis wrote:
>
> For the moment I'm baffled, I can't spot the error.
>
> >> select id, percentile, max(amount)
> >> from (
> >> select id, amount, 10 * ntile(10) over (
> >> partition by id
> >> order by amount
) as percentile
> >> from normalised_table
> >> order by id, percentile ****
> >> )
> >> group by id, percentile
> >> order by id, percentile
> >> ;
>
> The line marked **** is redundant by the way.
>
> The bit which goes
> >> ntile(10) over (
> >> partition by id
> >> order by amount
> >> ) as percentile
>
> means that Oracle should take all rows for an ID,
> order them by amount, then add 'decile' values to
> each row - so the order of the 'deciles' and the
> order of the amount should match, which means
> that you can't possibly get ---
>
> >27199203 50 $121.58
> >27199203 60 $ 80
>
> Aha !! Flash of inspiration - is your amount
> stored as a CHARACTER rather than a
> number ??? Your output appears to have a
> character-based ordered
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
>
> Publishers: Addison-Wesley
> See a first review at:
> http://www.ixora.com.au/resources/index.htm#practical_8i
> More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
> JShrimip, Jr. wrote in message <3A436BC5.2714EB09_at_hotmail.com>...
> >This is very close BUT the percentiles are not ordered by amount. When
> >I try to order the result by amount,
> >I get an error saying amount is not part of the group by function -
> >(which it can't be). Percentiles are arrayed from lowest to hightest.
> >
> >Here's the result from the code:
> >
> >ID % Amount Should Be:
> >
> >27199203 10 $101 10 $ 80
> >27199203 20 $109 20 $ 81
> >27199203 30 $117 30 $ 81
> >27199203 40 $117 40 $ 98
> >27199203 50 $121.58 50 $ 98
> >27199203 60 $ 80 60 $101
> >27199203 70 $ 81 70 $109
> >27199203 80 $ 81 80 $117
> >27199203 90 $ 98 90 $117
> >27199203 100 $ 98 100 $121.58
> >
> >How it is possible to order by Ascending Amount?
Received on Fri Dec 22 2000 - 11:03:55 CST
