Actually, the only thing that could work as a Function is an UDF, which by its' nature has a performance overhead that makes it unsuitable for record selection that will act on a large result set.
----- Original Message ----- From: "ELOEN" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, January 08, 2003 2:13 AM Subject: [RBASE-L] - Re: Stored Procedure in Where Clause > Mike, > > you are verified by the results. My (wrong) understanding was that Stored > Procedure could have been used as a *function* (perhaps with intermediate > results and reselection) in a where clause: > > SELECT ... FROM tblname WHERE (SLEN(text_col_name)) > 5 , for example. > > So my new (right I hope) understanding is that this is a big difference > between Stored Procedures and functions: > *Stored procedures cannot be used to qualify rows in a where clause*. > > Of course we can always use TEMP tables to implement this qualification in 2 > stages: > 1. INSERT INTO temp_table all rows with SELE (CALL stp_name(stp_params)), > pk_column > 2. Qualify the new column of temp table in reference with primary table > but this is another matter. > > Thanks for your time Mike. > > Polychronis T. Kontos > Athens, Greece > > > > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of MikeB > > Sent: Wednesday, January 08, 2003 1:52 AM > > To: RBASE-L Mailing List > > Subject: [RBASE-L] - Re: Stored Procedure in Where Clause > > > > > > Polychronis, > > Each row is returned even if it is null because the Stored Procedure has > > to be run against the value of the select. Since the Select is already on > > the row to get the value, the result is displayed in the output device > > (screen,printer,file) > > > > Are you trying to "clean up" some data? Otherwise, it is best to run your > > stored procedure in an EEP to validate the data before writing to disk. > > > > > > > > ----- Original Message ----- > > From: "ELOEN" <[EMAIL PROTECTED]> > > To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> > > Sent: Tuesday, January 07, 2003 5:03 AM > > Subject: [RBASE-L] - Re: Stored Procedure in Where Clause > > > > > > > You are right Mike. The Stored Procedure that follows get var > > pon text as > > > input and check for the existence of special chars except (Greek,) > > English, > > > numeric and '-' chars. If there is, it returns the position of 1st > > > occurrence and the character. If not it returns null. > > > > > > I had to change the Greek part of the code because I could not see my > > reply > > > to the list. > > > > > > *(ElOn1.STP: Stored Procedure for checking names for special chars) > > > --pon: name parapeter text (30) > > > --stp: string of error > > > --chars allowed 0-9,A-Z,'-' > > > > > > CLEAR VAR stp_return > > > > > > SET V stp TEXT,plen INTEGER,p1t TEXT,pi INTEGER > > > > > > --initial values > > > SET V stp = NULL,pi = 0 > > > > > > SET V plen = (SLEN(pon)) > > > IF plen = 0 THEN > > > GOTO LOUT > > > ENDIF > > > > > > WHILE pi < .plen THEN > > > SET V pi = (.pi + 1) > > > SET V p1t = (SGET(.pon,1,.pi)) > > > IF (p1t BETWEEN 'A' AND 'Z') OR (p1t BETWEEN '0' AND '9') THEN > > > CONTINUE > > > ENDIF > > > IF p1t = '-' THEN > > > CONTINUE > > > ENDIF > > > IF p1t = ' ' THEN > > > SET V p1t = '[blank]' > > > ENDIF > > > SET V stp = ('Position:' + (CTXT(.pi)) + ', No allowed char:' + .p1t) > > > BREAK > > > ENDW > > > > > > LABEL LOUT > > > CLEAR VAR p% > > > RETURN .stp > > > > > > > -----Original Message----- > > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of MikeB > > > > Sent: Friday, January 03, 2003 4:49 PM > > > > To: RBASE-L Mailing List > > > > Subject: [RBASE-L] - Re: Stored Procedure in Where Clause > > > > > > > > > > > > We need to see the contents of your Stored Procedure.... > > > > > > > > ----- Original Message ----- > > > > From: "ELOEN" <[EMAIL PROTECTED]> > > > > To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> > > > > Sent: Friday, January 03, 2003 1:41 AM > > > > Subject: [RBASE-L] - Stored Procedure in Where Clause > > > > > > > > > > > > > Hello brotherhood of R! > > > > > > > > > > could someone confirm this? > > > > > > > > > > If I use a stored procedure in a WHERE clause, it returns all > > > > the rows in > > > > > the table. > > > > > For example: > > > > > > > > > > SELE ... WHERE (CALL stp_name(stp_prmtrs)) IS NOT NULL > > > > > > > > > > returns all the rows, where as > > > > > > > > > > SELE (CALL stp_name(stp_prmtrs)) FROM ... > > > > > > > > > > returns null and not null values. > > > > > > > > > > OS: win2k > > > > > Rbase Vers:1.862 x RT03 Win > > > > > > > > > > TIA > > > > > Polychronis T. Kontos > > > > > Athens, Greece > > > > > > > > > > > > > > > > > > > > --- RBASE-L > > > ================================================ > > > TO POST A MESSAGE TO ALL MEMBERS: > > > Send a plain text email to [EMAIL PROTECTED] > > > > > > (Don't use any of these words as your Subject: > > > INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, > > > REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP) > > > ================================================ > > > TO SEE MESSAGE POSTING GUIDELINES: > > > Send a plain text email to [EMAIL PROTECTED] > > > In the message SUBJECT, put just one word: INTRO > > > ================================================ > > > TO UNSUBSCRIBE: > > > Send a plain text email to [EMAIL PROTECTED] > > > In the message SUBJECT, put just one word: UNSUBSCRIBE > > > ================================================ > > > TO SEARCH ARCHIVES: > > > Send a plain text email to [EMAIL PROTECTED] > > > In the message SUBJECT, put just one word: SEARCH-n > > > (where n is the number of days). In the message body, > > > place any > > > text to search for. > > > ================================================ > > > > > > > > > >
