At 12:36 PM 26/05/2005 +0000, you 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..

Your problem here is that the "minimum" value of an alphanumeric character
is determined by the value of the character code, not the value that the
character represents. There is nothing (in SQL) that you could do to make
2/2005-2006 be evaluated as "of lowest value" in this set of strings. The
alpha character '1' is the lowest. '10' is a string, not a number.

I suggest that you add two extra columns (smallint) and write a Before
Insert Or Update trigger to extract the MONTH and YEAR (respectively) from
whatever date value was used to assemble the vc_billno and plug them into
those columns.

./heLen