Problem with wildcard function

**ASHER** · 16-02-2010

I created the below function

Code:

CREATE FUNCTION BIB/fgeshoklt4(ST VARCHAR(3))           
returns varchar(256)                                     
LANGUAGE sql                                             
                                                         
deterministic                                            
disallow parallel                                        
no external action                                       
Returns NULL ON NULL Input                               
begin                                                    
declare stat varchar(256);                               
SET stat = ST;                                           
IF ST='*' OR ST='' OR ST='ALL' then                      
SET  stat ='''SO''' concat ',' concat ''' VA''' concat ','
concat ''' ES''';                                        
end IF;                                                  
RETURN stat;                                             
end

when I tested

select * from prod.doslief where dosggdo in (BIB.fgeshoklt4( 'ES'))

this brings me back values well if I do:
select * from prod.doslief where dosggdo in ( 'ES', 'VA', 'N')

but if I do
select * from prod.doslief where dosggdo in (BIB.fgeshoklt4( 'ALL'))

The query brings me nothing (not even an error code ). Is this what you see or have I miss something somewhere? Can you explain me what is the problem with my wildcard function?

**CitricAcid** · 16-02-2010

A look at your application more closely, using the function fgeshoklt4 the predicate WHERE I realize now that its wording is wrong. In fact, if you indicate

Code:

WHERE dosggdo IN (BIB.fgeshoklt4('*'))

This means that you compare the value of the column dosggdo directly with the literal value "'' 'N'','''','' VA ES''' and not with the list values'' 'N'','''','' VA ES''' as you hope. However, the query works correctly if you indicate

Code:

WHERE dosggdo IN (BIB.fgeshoklt4('ES'))

because the function in this case returns the value "ES" and the column dosggdo contains the value "ES". This is the same as testing equality

Code:

WHERE dosggdo = 'ES'

Same probably for "N" and "VA".

You can not return a list of values in a single parameter. To do this kind of thing, you must use as many parameters as there are values in the list, and that, you can not make a simple function scalar which accepts only one value in return.

**ASHER** · 16-02-2010

OK, so the best would do something like this

Code:

CREATE FUNCTION BIB/fgeshoklt5(ST VARCHAR(4))            
returns varchar(256)                                      
LANGUAGE sql                                              
deterministic                                             
disallow parallel                                         
no external action                                        
Returns NULL ON NULL Input                                
begin                                                     
declare stat varchar(256);                                
SET stat = ST;                                            
IF ST='*' OR ST=''OR ST='ALL' then RETURN                
(SELECT dosggdo FROM prod/doslief GROUP BY dosggdo); 
else RETURN stat;                                         
end IF;                                                   
end

Besides that I have a small mistake or I'm going to have employed cursors for now but if you would confirm that it could work

**CitricAcid** · 16-02-2010

OK, so the best would do something like this

No because even if you manage to develop your office with your "select group by", it would be pointless since you can not use the parameter returned by the function as if its acting in a list of values separated by commas that you would type by hand. SQL sees that one value equal to your nickname list and not a list of values separated by commas as you think. I hope to make it clear on this one

I think you just want to avoid having to type the list values in the WHERE and that you count on your function to do it for you. If that's it, I drop the function and simply would create a new table showing the values of all rows (records) that there is value in the list in your WHERE predicate. For example, insert in this table a line with "ES" line with "N" and a line with "VA"

Code:

CREATE TABLE MYBIB.MYTABLE (DOSGQDO CHAR ( 2) NOT NULL WITH DEFAULT)
INSERT INTO MYBIB.MYTABLE VALUES('ES')
INSERT INTO MYBIB.MYTABLE VALUES('SO')
INSERT INTO MYBIB.MYTABLE VALUES('VA')

Then, if I want "ALL", I would type directly:

Code:

SELECT a.* 
FROM   PROD.DOSLIEF a 
JOIN     MYBIB.MYTABLE b
ON       a.dosggdo = b.dosggdo

Otherwise, I will indicate directly value in the WHERE:

Code:

SELECT * 
FROM   PROD.DOSLIEF  
WHERE dosggdo = 'MyValue'

Note: wildcard characters have nothing to see here. For simplicity say a wildcard is a character whose nickname is taken into account the position but not the value. It is usually represented by a ?.

Example :
?ut searches all values such as but, rut, nut, mut, etc..

**ASHER** · 16-02-2010

I agree with you and I understand what you say but then this request

Code:

select * from infocprod.doslief where dosggdo in (select dosggdo from prod.doslief group by dosggdo )

works if I

Code:

select dosggdo from prod.doslief group by dosggdo

it sent me a column with several line and not several fields separated by commas. Your idea of the table is only but I must function well. it will in my user interface. I want them to understand '*' for all their data and if they want a particular field they seize. I'll see if I can not be a function of the suggestion that you have made.