You can download rFunc UDF Library (http://rfunc.sourceforge.net/),
put the rfunc.dll in UDF folder and register the following UDFs:

DECLARE EXTERNAL FUNCTION STRPOS
CSTRING(16384), CSTRING(16384)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'fn_strpos' MODULE_NAME 'rfunc';

DECLARE EXTERNAL FUNCTION SUBSTR
CSTRING(256), INTEGER, INTEGER
RETURNS CSTRING(256)
ENTRY_POINT 'fn_substr' MODULE_NAME 'rfunc';


After that you can run this SQL:


SELECT FIRST 1
VC_BILLNO,
CAST(SUBSTR(VC_BILLNO, 1, STRPOS('/', VC_BILLNO) - 1) AS INTEGER)
FROM PURC_BILL_MAST
ORDER BY
CAST(SUBSTR(VC_BILLNO, 1, STRPOS('/', VC_BILLNO) - 1) AS INTEGER)

Hope it helps ;)


--- In firebird-support@yahoogroups.com, "shaamim_ply"
<shaamim_ply@y...> wrote:

> Hi experts,
> I am doing an inventory s/w using Firebird.I have tried the following
> query to select the First record which satisfies the criteria
>
> Select COALESCE(MIN(VC_BILLNO),'NULL') CODE from PURC_BILL_MAST
> where NN_YEAR = 2005
>
> but i am not getting the needed answer..
> Eg:in my table the values of vc_billno are like this
>
> 10/2005-2006
> 2/2005-2006
> 3/2005/2006
> 9/2005-2006
>
> and i want to get the result 2/2005-2006 when using the "MIN function"
> But notgetting the right one.
> NB:vc_billno is declared as varchar type
>
> Hope u understand the problem, i am new in firebird..i expect ur
> valuable sugestions..
> Thanx
> shaamim