I created a script library and inside that library I have a function that returns a message returned from a SQL script:

function extern domain tccpft
tipcfdllcits01.getmessage(domain tcsern psern, domain tccpft pcpft, domain tccopt pcopt )
{
import("tipcf500.item", item)

string ret(300)

select tipcf920.cvcmd
from tipcf920
where tipcf920.item = item
and tipcf920.sern = :psern
and tipcf920.cpft = :pcpft
and trim$(tipcf920.copt) = :pcopt
as set with 1 rows
selectdo
ret = tipcf920.cvcmd
selectempty
ret = ""
endselect

return (ret)

}


However when I try to compile the library script I get an error:

Error SQL: Unknown column 'strip$'
Error SQL: Unknown column 'shiftl$'


Am I missing an include required to use this function?

I did not think there was a function trim$ - what version is this - ln, baan 4 or 5? I have always used strip$, shiftl$ or tt.align.according.domain(include bic_tt).

This is Baan IVc4.

There must be a function trim$ because it would appear that this function just calls strip$ and shiftl$. But those are the functions the compiler is complaining about.

At first there is no built in function trim$ in Baan IV. But general practice is to define a macro like : #define trim(x) strip$(shift$(X))
Now, if you use this macro trim() in you code, compiler interprets it as strip$(shiftl$()).

You can not directly use baan functions in where clause of your query.
You may try using WHEREBIND clause.
Refer embedded SQL on following link:
http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_embedded_sql

Hi,

First of all you need to use strip$(shift$)) instead of trim$() and that too in the selectdo loop rather than in the where clause.

Regards,
Amit Mokashi

Alternatively use a new variable and set it before you do your select and refer to that in your query
e.g.

option = strip$(shiftl$(pcopt))

select tipcf920.cvcmd
from tipcf920
where tipcf920.item = item
and tipcf920.sern = :psern
and tipcf920.cpft = :pcpft
and tipcf920.copt = :option
as set with 1 rows
selectdo
etc
etc

Ed,

You code is working on the parameter which in my case, for this particular feature is "Y" or "N". It does not have any spaces so those functions don't change it at all. (I had hoped it would pad it with leading spaces).

I either have to strip the spaces from the value stored in the table (I thought I was doing that with the previous code posted), or I have to add leading spaces to the parameter passed in.

I just thought there would be something easy like the code I have shown above, versus doing something like testing the length of the parameter and padding it with leading spaces to fill the char(8) datatype to match what's in the column.

What I typically do is something like this:

rc = tt.align.according.domain(read.floc,xfer.floc,"tdilc.loca")

read.floc will be a character string where I read from a flat file. Then I move the read location into a variable with the same format just like the field. In this example it would be tdilc.loca used on an inventory table. Then in my query I could say tdilcxxx.loca = :cfer.loca. Just how I do things like this.