Re: [SPAM 1]sql rank() problem

[Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>]

Close... you are going to need digits() and substr() or right() to limit 
it to 6 characters as the result of rank is 20 digits.

...concat right(digits( ...rank... ,6))...


On 05/18/2010 06:26 AM, David FOXWELL wrote:
> I hope I have this right, as I am new to this kind of request :
>
> My request :
> SELECT 'ADH_' concat char( Rank() OVER ( order by Fld1, Fld2 )), Fld1, Fld2, Fld3
> FROM mytable
>
> Result :
> CONCAT FLD1    FLD2 FLD3
> ADH_1	 biggs   ron  10000
> ADH_2  blogs   fred 12000
> ADH_2  blogs   fred 11300
> ADH_3  smith   al   00002
>
>
> I need the number part of the result of the concat to have 6 digits, how would I do that?
> CONCAT      FLD1    FLD2 FLD3
> ADH_000001	biggs   ron  10000
> ADH_000002  blogs   fred 12000
> ADH_000002  blogs   fred 11300
> ADH_000003  smith   al   00002
> ....
> ADH_012345  xxxxx   xx   01234
>
> Finally, I will want to update a new field FLD4 with the result of the concat. Is that as easy as I think? Update mytable set fld4 = 'ADH_' concat char( Rank() OVER ( order by Fld1, Fld2 ))?
>
> Thanks