Animation:
Église Nouvelle Vie | Calvary Chapel of Downey | Calvary Evangelism Center | Calvary of Albuquerque | Church by the Glades | Church of the King | Church of the Open Door | Edmond’s First Baptist Church | Grace Christian Church | Grace Community Church, Houston TX | Grosse Pointe United Methodist
Harvest Christian Fellowship
Metropolitan Baptist Church
Northview Christian Church
Point of Grace Church
Rancho Community Church
Revolution
Richmond Baptist Church
Son Rise Baptist Church
St Paul Missionary Baptist Church
Sunridge Community Church
Thanksgiving Church
Union Evangelical Church
Watermark Community Church
World Overcomers Christian Church
Yorba Linda Friends Church
Great Church Websites
A List Function for Oracle
SQL Anywhere has a very convenient LIST function for combining rows of data into a single column.
Ex.
SELECT LIST( emp_id, ';' ORDER BY emp_lname ) AS "Sorted IDs"
FROM EMPLOYEE
GROUP BY dept_id
Sorted IDs
160;105;1250;247;266;249;445;…
1039;129;1142;195;667;1162;902;…
1336;879;586;390;757;148;1483;…
1751;591;1062;1191;992;888;318;…
1013;191;750;921;868;1658;703;…
Since I have to move between SQL Anywhere and Oracle quite often, I needed a way to duplicate, as much as possible, this function in Oracle.
Tom’s stringAgg routine is a great start. It is a good example of building a custom aggregate function in Oracle. However, it doesn’t lend itself to specifying the delimiter. Certainly you can use REPLACE, but if your strings contain commas, you’re out of luck.
Here is a solution, built on Tom’s StringAggType, that uses an object to pass in a delimiter. It’s a little clumsy to call, and doesn’t support DISTINCT, but it does work. Suggestions for improvement are welcome.
SQL> select deptid, list(listparm(lname, '|')) from emp group by deptid; DEPTID LIST(LISTPARM(LNAME,'|')) ---------- -------------------------------------------------------------------------------- 10 Jones|Johnson|Hendricks 20 Sewell|Rogers|Yancy 30 Yost|Smith|Carter|Smith 40 Michaels
The Code:
drop type ListType
/
create or replace type ListParm as object
(
theString varchar2(4000),
is_Delimiter varchar2(100)
);
/
create or replace type ListType as object
(
is_Delimiter varchar2(100),
theString varchar2(4000),
static function
ODCIAggregateInitialize(sctx IN OUT ListType )
return number,
member function
ODCIAggregateIterate(self IN OUT ListType ,
value IN ListParm )
return number,
member function
ODCIAggregateTerminate(self IN ListType,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT ListType,
ctx2 IN ListType)
return number
);
/
show errors
create or replace type body ListType
is
static function ODCIAggregateInitialize(sctx IN OUT ListType)
return number
is
begin
sctx := ListType( ',', null );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT ListType,
value IN ListParm )
return number
is
begin
self.is_Delimiter := value.is_Delimiter;
self.theString := self.theString || self.is_Delimiter || value.theString;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN ListType,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := rtrim( ltrim( self.theString, self.is_Delimiter ), self.is_Delimiter );
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT ListType,
ctx2 IN ListType)
return number
is
begin
self.theString := self.theString || self.is_Delimiter || ctx2.theString;
return ODCIConst.Success;
end;
end;
/
CREATE OR REPLACE FUNCTION List(input ListParm)
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING ListType;
/
Displaying Currency with Adaptive Server/SQL Anywhere
SQL Anywhere, also known as Adaptive Server Anywhere, does not provide support to display values as currency.
So, I had to write my own function. Since I’m copying my SQL from Oracle, I want to easily replace the TO_CHAR( amount, ‘$9,999.99′) expressions. So, this function is named TO_MONEY() and has the same signature as TO_CHAR()’s. I just replace CHAR with MONEY in SQL Anywhere.
TO_MONEY( IN ad_value double, IN as_format varchar(50) DEFAULT ‘$9,999.99′ )
ALTER FUNCTION "dba"."to_money"(
IN ad_value double,
IN as_format varchar(50) DEFAULT '$9,999.99' )
RETURNS varchar(50)
DETERMINISTIC
BEGIN
DECLARE ls_value varchar(50);
DECLARE ls_v varchar(50);
DECLARE len integer;
DECLARE ls_separator char(1);
SET ls_separator = '.';
SET ls_value = CAST( ROUND(ad_value*100,0) AS CHAR );
SET len = LENGTH( ls_value );
SET ls_v = RIGHT( ls_value, 2 );
SET ls_value = SUBSTR( ls_value, 1, len - 2 );
SET len = len - 2;
WHILE len > 3 LOOP
SET ls_v = RIGHT( ls_value, 3 ) + ls_separator + ls_v;
SET ls_value = SUBSTR( ls_value, 1, len - 3 );
SET len = len - 3;
SET ls_separator = ',';
END LOOP;
IF len > 0 THEN
SET ls_v = ls_value + ls_separator + ls_v;
ELSEIF len = 0 THEN
SET ls_v = '0' + ls_separator + ls_v;
END IF;
RETURN '$' + ls_v;
END
Getting HTMLArea to work in Drupal
I installed HTMLArea on my drupal website, and it wasn’t working. After a little digging on drupal.org, I found the solution.
finally got my htmlarea module to work… fixed my Gagarin theme by editing the page.tpl.php file and adding <?php print $closure ?> just before the </body> tag…
I, indeed, suffered the same problem. The theme I was using did not have this closure tag. Very annoying. I expect the theme template requirements are changing quicker than the designers can handle. I guess. One of the reasons I switched to drupal from Xoops was the tighter control over the code. Ah well.
Would be great if there were a drupal theme syntax checker to verify that a theme is up to muster.
