Topic
2 replies Latest Post - ‏2007-03-28T00:05:02Z by Stan
SystemAdmin
SystemAdmin
1525 Posts
ACCEPTED ANSWER

Pinned topic Need this query to work with Derby

‏2007-03-27T15:57:37Z |
The query shown below works with Oracle 9i
However, derby does not support "decode"
I know I have to do an Insert into versus select as for the new table..

here is the quesy: (How do I write it to work in Derby?
I trying to do a crosstab query >> (365 rows 3 columns (id,data,date)to >> 31 rows and 13 columns (id (is day), data (12 columns))
Thanks:

create table NewTable as select to_char(wdDate,'dd') as day
,min(decode(to_char(wdDate,'mm'),'01',wdValue)) as Jan
,min(decode(to_char(wdDate,'mm'),'02',wdValue)) as Feb
,min(decode(to_char(wdDate,'mm'),'03',wdValue)) as Mar
,min(decode(to_char(wdDate,'mm'),'04',wdValue)) as Apr
,min(decode(to_char(wdDate,'mm'),'05',wdValue)) as May
,min(decode(to_char(wdDate,'mm'),'06',wdValue)) as Jun
,min(decode(to_char(wdDate,'mm'),'07',wdValue)) as Jul
,min(decode(to_char(wdDate,'mm'),'08',wdValue)) as Aug
,min(decode(to_char(wdDate,'mm'),'09',wdValue)) as Sep
,min(decode(to_char(wdDate,'mm'),'10',wdValue)) as Oct
,min(decode(to_char(wdDate,'mm'),'11',wdValue)) as Nov
,min(decode(to_char(wddate,'mm'),'12',wdValue)) as Dec
from oldTable
group by to_char(wdDate,'dd'),to_char(wdDate,'yyyy');
Updated on 2007-03-28T00:05:02Z at 2007-03-28T00:05:02Z by Stan
  • Jean_Anderson
    Jean_Anderson
    179 Posts
    ACCEPTED ANSWER

    Re: Need this query to work with Derby

    ‏2007-03-27T16:40:01Z  in response to SystemAdmin
    What does the decode function in this expression do?
    code,min(decode(to_char(wdDate,'mm'),'01',wdValue)) as Jan[/code]
    If there aren't any Derby functions that support that functionality directly, you might be able to code a SQL function to do what's needed. More information is at:
    http://wiki.apache.org/db-derby/DerbySQLroutines

    -jean
  • Stan
    Stan
    267 Posts
    ACCEPTED ANSWER

    Re: Need this query to work with Derby

    ‏2007-03-28T00:05:02Z  in response to SystemAdmin
    Hi -

    Like Jean I'm not entirely sure what you are trying to do. I found out that decode is part of the PL/SQL API - the Derby API is Java so, as Jean suggests, you could add decode capability to Derby by coding a similar function. But you might also be able to use the CASE statement to do the decode substitution. Here's a syntax example that produces a crosstab of sums column1 based the value of column2.

    ij> select sum(case when col2 like 't%' THEN col1 ELSE 0 END),
    sum(case when col2 like '%e' THEN col1 ELSE 0 END) from tab1;
    1 |2

    5 |4

    You can use the MONTH() function to return the month portion of your date and translate it to whatever code you please using the CASE statement.

    Hope this helps.