Hi,
I created the following db function and tried to use it in a couple of select
statements and other db functions. I don't know what is wrong, but sometimes the
results are not as expected. If you read the following script I will show you my
problems. Every help is welcome.
regards and thanks
Jan Gaedicke
----------------------------------------------------------------
-- First create the function
CREATE DBFUNCTION primitive ( str VARCHAR ) RETURNS VARCHAR AS
VAR str2 VARCHAR(20);
result VARCHAR(20);
strIndex INT;
strLength INT;
c CHAR(1);
cOld CHAR(1);
BEGIN
IF str IS NULL OR LENGTH(str) = 0 THEN RETURN NULL;
SET str2 = lower(TRIM(str));
SET str2 = REPLACE(str2, 'ph', 'f');
SET str2 = REPLACE(str2, 'ae', 'a');
SET str2 = REPLACE(str2, 'oe', 'o');
SET str2 = REPLACE(str2, 'ue', 'u');
SET str2 = TRANSLATE(str2, '�������','aaaaaaa');
SET str2 = TRANSLATE(str2, '����','eeee');
SET str2 = TRANSLATE(str2, '����','iiii');
SET str2 = TRANSLATE(str2, '�������','ooooooo');
SET str2 = TRANSLATE(str2, '����','uuuu');
SET str2 = TRANSLATE(str2, '��','yy');
SET str2 = TRANSLATE(str2, '�','n');
SET str2 = TRANSLATE(str2, 'ߚ�','sss');
SET strIndex = 1;
SET strLength = LENGTH(str2);
SET cOld = substring(str2,1,1);
SET result = cOld;
WHILE strIndex < strLength DO BEGIN
SET c = substring(str2, strIndex+1,1);
SET strIndex = strIndex+1;
IF cOld <> c THEN BEGIN
SET result = result || c;
SET cOld = c;
END;
END;
RETURN result;
END;
-----------------------------------------------------------
-- a table with some data for my selects
create table participant (
id DEC PRIMARY KEY,
last_name VARCHAR(20),
fk_group_manager DEC
)
--
insert participant (id,last_name,fk_group_manager) values(1,'Muster', 1)
--
insert participant (id,last_name,fk_group_manager) values(2,'Meier', 1)
--
insert participant (id,last_name,fk_group_manager) values(3,'M�ller', 1)
--
insert participant (id,last_name,fk_group_manager) values(4,'Meier2', null)
--
The dbfunction works fine for this statement
select p.id, primitive(p.last_name) from participant p
-- result
-- 1 muster
-- 2 meier
-- 3 muler
-- 4 meier2
First problem with this statement. Looks like the result of primitve is always of size
20 and the concatenated string is put after these 20 chars. My intention was, that the
results size is more dynamical, that's why I used VARCHAR and not CHAR
select p.id, 'non' || primitive(p.last_name) || 'sense', 'few' || p.last_name ||
'sense' from participant p
-- result
-- 1 nonmuster sense fewMustersense
-- 2 nonmeier sense fewMeiersense
-- 3 nonmuler sense fewM�llersense
-- 4 nonmeier2 sense fewMeier2sense
This works fine again, but I have to use the statement below it.
select
p.id,
p.last_name,
gm.last_name
from participant p, participant gm
where
p.fk_group_manager=gm.id(+) and
primitive(p.last_name) like '%meier%'
-- result
-- 2 Meier Muster
-- 4 Meier2 ?
Here is the really mystic thing. it results in an error. problem seems to be, that I
use the result of my primitive function and concatenates it to other strings. Error
says, that the db don't know which last_name column to use, but I used the alias 'p'
to give a hint. As you can see in the previous select statement this only happens when
I use the result in the concatenation.
select
p.id,
p.last_name,
gm.last_name
from participant p, participant gm
where
p.fk_group_manager=gm.id(+) and
(primitive(p.last_name) like '%meier%'
or
'meier' like ('%' || primitive(p.last_name) || '%')
)
-- result
-- General error;-5014 POS(200) Missing definite column name:LAST_NAME.
______________________________________________________________________________
Erdbeben im Iran: Zehntausende Kinder brauchen Hilfe. UNICEF hilft den
Kindern - helfen Sie mit! https://www.unicef.de/spe/spe_03.php
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
