jgusuldeweb.de
Date: Mon Feb 09 2004 - 14:36:00 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]