Subject Re: [firebird-support] Simple Simple format question Svein Erling Tysvaer 2006-10-18T19:12:42Z
I'm not into UDFs, but you could try something like

select p.zpart, p.zplength,
case
when cast(p.zplength as integer) = p.zplength then
(p.zpart || '-'||cast(p.zplength as integer)
when cast(p.zplength*10 as integer) = p.zplength*10 then
(p.zpart || '-'||cast(p.zplength as NUMERIC(15,1)
when cast(p.zplength*100 as integer) = p.zplength*100 then
(p.zpart || '-'||cast(p.zplength as NUMERIC(15,2)
when cast(p.zplength*1000 as integer) = p.zplength*1000 then
(p.zpart || '-'||cast(p.zplength as NUMERIC(15,3)
when cast(p.zplength*10000 as integer) = p.zplength*10000 then
(p.zpart || '-'||cast(p.zplength as NUMERIC(15,4)
else
(p.zpart || '-'||p.zplength)
end as partno
from parts p

Though I don't know how this works with big numbers, and haven't tested
it at all.

HTH,
Set
-The mail I sent 12 hours ago, still hasn't reached this list, I don't
understand the delays imposed on my mails to yahoogroups.

colincoleman2002 wrote:
> We are running Fb 1.5.3 and have a table that has five fields
>
> CREATE TABLE PARTS (
> ZPART INTEGER NOT NULL,
> ZPLENGTH NUMERIC(15,5) NOT NULL,
> ZTYPE INTEGER NOT NULL,
> ZPARTDESC VARCHAR(75),
> ZTMPART VARCHAR(40)
> );
> ALTER TABLE PARTS ADD PRIMARY KEY (ZPART, ZPLENGTH);
> ALTER TABLE PARTS ADD CONSTRAINT REF_219 FOREIGN KEY (ZTYPE)
> REFERENCES TYPES (ZTYPE);
>
> Using the Query :=
> select p.zpart, p.zplength, (p.zpart || '-'||p.zplength) as partno
> from parts p
>
> The data looks like :-
> ZPART ZPLENGTH PARTNO
> 35442 12 35442-12.00000
> 35443 15 35443-15.45000
> 35444 15 35444-15.00000
> 11678 15 11678-15.00000
> 55655 15 55655-15.00000
>
> Id Like the last column to show 35442-12 , or 35443-15.45 from the
> 2nd line, is there
> a way to do this in Firebird, maybe using some standard udf to strip
> the trailing 0's and any trailing '.' ?
>
> Thanks Colin Coleman