sorry for the <no subject > mailing. It would be great, if someone could help me. Is it a bug of MaxDB?
[EMAIL PROTECTED] wrote:
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]
