I want to do this

select * from mytable order by (cast(item18 as integer)

But if item is blank, it throws an exception.

Following a suggestion in an earlier thread I used this function:
FUNCTION "ConvertToInt" (IN "Value" VARCHAR(200) COLLATE ANSI)
RETURNS INTEGER
BEGIN
 DECLARE Result INTEGER;
 BEGIN
  SET Result = CAST(Value as INTEGER);
 EXCEPTION
  SET Result = 0;
 END;
 RETURN Result;
END

To allow this statement:

select * from mytable order by ConvertToInt(item18)

-The statement works in the ELevateDB manager, but it STILL throws the conversion error exception in the delphi application.

You could use IF:

select * from mytable order by cast(if(item18 = '', '0', item18) as integer)

--
David Cornelius
Cornelius Concepts

"Bryn Lewis" wrote in message
news:2DBA36D9-B6D6-4778-9A84-E30FC9BE1E95@news.elevatesoft.com...
> I want to do this
>
> select * from mytable order by (cast(item18 as integer)
>
> But if item is blank, it throws an exception.
>
> Following a suggestion in an earlier thread I used this function:
> FUNCTION "ConvertToInt" (IN "Value" VARCHAR(200) COLLATE ANSI)
> RETURNS INTEGER
> BEGIN
>  DECLARE Result INTEGER;
>  BEGIN
>   SET Result = CAST(Value as INTEGER);
>  EXCEPTION
>   SET Result = 0;
>  END;
>  RETURN Result;
> END
>
> To allow this statement:
>
> select * from mytable order by ConvertToInt(item18)
>
> -The statement works in the ELevateDB manager, but it STILL throws the
> conversion error exception in the delphi application.
>

Bryn


PC: Dell Vostro 1700 RAM: 3Gb Disk 250Gb
OS: Vista
Delphi 2006 SP2
ElevateDB 2.04b4 non-unicode


Just doing a bit of testing with thw two possible cases for "blank" (ie empty string and NULL)

CAST('' AS INTEGER) - fine in SELECT, bombs in ORDER BY

CAST(NULL AS INTEGER) - fine in SELECT and ORDER BY

so you'll need to extend David's idea roughly as

select * from mytable order by cast(IF(item18 IS NULL,'0',IF(item18='','0',item18))as integer)

I think the brackets are right.

Final point - remember EDBManager is just another Delphi app used ElevateDB. If something works in there and doesn't work in your app there is a difference, and its generally that there is a problem with your app or you're using different data or pointing at a different configurations file.

Roy Lambert [Team Elevate]

I was going to handle the "... IS NULL" condition, but my tests in EDB Mgr
2.03b22 didn't break on it, so I left it to only worry about non-null blank
strings.

However, my example does not handle non-blank, non-numeric strings, such as
'abc'.  But then, neither does yours!  

--
David Cornelius
Cornelius Concepts

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:649C6512-7256-42AF-95E7-4577C62963E8@news.elevatesoft.com...
> Bryn
>
>
> PC: Dell Vostro 1700 RAM: 3Gb Disk 250Gb
> OS: Vista
> Delphi 2006 SP2
> ElevateDB 2.04b4 non-unicode
>
>
> Just doing a bit of testing with thw two possible cases for "blank" (ie
> empty string and NULL)
>
> CAST('' AS INTEGER) - fine in SELECT, bombs in ORDER BY
>
> CAST(NULL AS INTEGER) - fine in SELECT and ORDER BY
>
> so you'll need to extend David's idea roughly as
>
> select * from mytable order by cast(IF(item18 IS
> NULL,'0',IF(item18='','0',item18))as integer)
>
> I think the brackets are right.
>
> Final point - remember EDBManager is just another Delphi app used
> ElevateDB. If something works in there and doesn't work in your app there
> is a difference, and its generally that there is a problem with your app
> or you're using different data or pointing at a different configurations
> file.
>
> Roy Lambert [Team Elevate]
>

David


>However, my example does not handle non-blank, non-numeric strings, such as
>'abc'. But then, neither does yours!

<cop out> we were only asked about blank strings </cop out>

Roy Lambert

Exactly!  Which is another reason I didn't worry about NULL values all that
much.  (I say this partially in jest, remembering you equate NULL values
with "blank" strings...!)

--
David Cornelius
Cornelius Concepts

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:81544231-21C6-42B4-BAF6-C1556B5783E6@news.elevatesoft.com...
> David
>
>
>>However, my example does not handle non-blank, non-numeric strings, such
>>as
>>'abc'. But then, neither does yours!
>
> <cop out> we were only asked about blank strings </cop out>
>
> Roy Lambert

David

>Exactly! Which is another reason I didn't worry about NULL values all that
>much. (I say this partially in jest, remembering you equate NULL values
>with "blank" strings...!)

Rechecking the thread I see I have a bit of an error in my cop ou so here goes again

<cop out> we were only asked about "if item is blank" </cop out>

and blank is open to two interpretations <vbg>

Roy Lambert [Team Elevate]

It would be nice to handle non-numeric, which is why the function and exception handling was what I wanted to do.

The question is then why EDB manager eats the exception and my app does not. The delphi code is:

   LocalQuery.SQL.Clear;
   LocalQuery.SQL.Add(sql);
   LocalQuery.prepare;
   LocalQuery.open;
   Result:=LocalQuery;

I appreciate it may be a problem with my app - but it is definitely the same edb config file.

I can expand it with this:

select item18,* from project91 order by cast(if((item18 > '9')or(item18 < '0'), '0', item18) as integer)

This will handle: 'a123', 'asdf', etc

But it dies on: '2abc'

I suppose the eception needs to be caught inside the IF, but not sure if thats ok.

Bryn,

> select item18,* from project91 order by cast(if((item18 > '9')or(item18 < '0'), '0', item18) as integer)
>
> This will handle: 'a123', 'asdf', etc
>
> But it dies on: '2abc'

If you want to sort just the first char if it's a number, there is no
need to convert to an integer:

select item18,* from project91 order by
case when (item18 > '9') or (item18 < '0') then '0' else item18 end;

should work fine.

Uli