You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Nicolas Dufour <ni...@neometsys.fr> on 2005/09/30 20:49:28 UTC
Tests in SQL
Hello
I try to put tests in select clause.
I mean I have a field which can be null and I want to create a select
which return 0 when the field is null and 1 in the other case.
Is it possible to do that in Derby ?
I try also to look in a string and if i recognize a pattern extract two
parts of it and if not just take the all value.
How can i do that ?
Thanks
Nicolas
Re: Tests in SQL
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Nicolas,
I don't know if anyone got back to you on this one. You can always
create your own function to do this. The java code would look something
like this:
public static String dayOfWeek( java.sql.Date date )
throws Exception
{
GregorianCalendar calendar = new GregorianCalendar();
calendar.setTime( date );
int weekday = calendar.get(
Calendar.DAY_OF_WEEK );
switch( weekday )
{
case 1: return "Sunday";
case 2: return "Monday";
case 3: return "Tuesday";
case 4: return "Wednesday";
case 5: return "Thursday";
case 6: return "Friday";
case 7: return "Saturday";
default: throw new SQLException( "Unknown weekday: " +
weekday );
}
}
Then you could declare this as a function and use it as you described:
create function app.dayOfWeek
( dateValue date )
returns varchar( 8 )
parameter style java
no sql
language java
external name 'z.dayOfWeek'
;
values app.dayOfWeek( date( '2005-10-03' ) );
Cheers,
-Rick
Nicolas Dufour wrote:
> Thanks
>
> Derby is really complete :)
>
> However, I didnt find in the builtin function a way to retrieve the
> day of the week of a date or a timestamp, I mean monday, tuesday
> ...and so on.
> Is there such function anywhere ?
>
> Nicolas
>
> Suavi Ali Demir wrote:
>
>> SELECT CASE WHEN COL1 is NULL THEN 0 ELSE 1 END FROM MYTABLE
>>
>> Regards,
>> Ali
>>
>> */Nicolas Dufour <ni...@neometsys.fr>/* wrote:
>>
>> Hello
>>
>> I try to put tests in select clause.
>> I mean I have a field which can be null and I want to create a
>> select
>> which return 0 when the field is null and 1 in the other case.
>> Is it possible to do that in Derby ?
>> I try also to look in a string and if i recognize a pattern
>> extract two
>> parts of it and if not just take the all value.
>> How can i do that ?
>>
>> Thanks
>>
>> Nicolas
>>
>
>
Re: Tests in SQL
Posted by Nicolas Dufour <ni...@neometsys.fr>.
Thanks
Derby is really complete :)
However, I didnt find in the builtin function a way to retrieve the day
of the week of a date or a timestamp, I mean monday, tuesday ...and so on.
Is there such function anywhere ?
Nicolas
Suavi Ali Demir wrote:
> SELECT CASE WHEN COL1 is NULL THEN 0 ELSE 1 END FROM MYTABLE
>
> Regards,
> Ali
>
> */Nicolas Dufour <ni...@neometsys.fr>/* wrote:
>
> Hello
>
> I try to put tests in select clause.
> I mean I have a field which can be null and I want to create a select
> which return 0 when the field is null and 1 in the other case.
> Is it possible to do that in Derby ?
> I try also to look in a string and if i recognize a pattern
> extract two
> parts of it and if not just take the all value.
> How can i do that ?
>
> Thanks
>
> Nicolas
>
Re: Tests in SQL
Posted by Suavi Ali Demir <de...@yahoo.com>.
SELECT CASE WHEN COL1 is NULL THEN 0 ELSE 1 END FROM MYTABLE
Regards,
Ali
Nicolas Dufour <ni...@neometsys.fr> wrote:
Hello
I try to put tests in select clause.
I mean I have a field which can be null and I want to create a select
which return 0 when the field is null and 1 in the other case.
Is it possible to do that in Derby ?
I try also to look in a string and if i recognize a pattern extract two
parts of it and if not just take the all value.
How can i do that ?
Thanks
Nicolas