I'm creating a script using the SQL Like Clause.

When I try to use a variable in order to be evaluated inside the "like" clause, the system returns an sql error 302. Does some one to know how can I use the Like Clause with a variable value? (not a static).

I try to use the parse clause but the error continues.

Part of the script

|***************************************************
select tcxxx105.codig.d:str.wbscod.level1
from tcxxx105
where nivel.d = 1
selectdo
str.wbscod.level1=str.wbscod.level1 & ".*"
sql.id=sql.parse( "select tipcs360.* " &
"from tipcs360,tcxxx105 " &
"where tcxxx105.compo.d = tipcs360.cpcp " &
"and tcxxx105.codig.d like :1" )

sql.where.bind(sql.id,1,str.wbscod.level1)
sql.exec(sql.id)
while ( true )
on case ( sql.fetch(sql.id) )
case eendfile:
break
case 0:
num.array.bau(num.counter)=lval(tcxxx105.codig.d)
num.counter=num.counter+1
num.array.bau(num.counter)=-1
continue
default: | error
message("Error %d occurred", db.error())
endcase
break
endwhile
sql.break(sql.id)
sql.close(sql.id)
sql.id = 0
endselect
|***************************************************

Thanks


Maurix

Hi there , I examined your script a bit , in part of script like that str.wbscod.level1=str.wbscod.level1 & ".*"
sql.id=sql.parse( "select tipcs360.* " &
"from tipcs360,tcxxx105 " &
"where tcxxx105.compo.d = tipcs360.cpcp " &
"and tcxxx105.codig.d like :1" )

I think that tcxxx105.codig.d is numeric and you should not use ":" character with static numeric value. it should be like that "and tcxxx105.codig.d like 1"

you should test it , I don't test it but it willl be work.

Good lucks

If wild cards are already there in variable str.wbscod.level1 ,
write following statement before sql.parse . The error 302 should
go.

str.wbscod.level1 = """" & str.wbscod.level1 & """"

I have another question.

As I know to use the Like clause usually I have to add the ".*" characters.

What if Need I to include the "." character in the like clause?

for example I need to find
"1."
which should be the correct sentence?

"1..*" does not work

Thanks a lot


Maurix



If wild cards are already there in variable str.wbscod.level1 ,
write following statement before sql.parse . The error 302 should
go.

str.wbscod.level1 = """" & str.wbscod.level1 & """"

Maurix ,

I have never tried that bcos our data coding schema does not include period .

However u can try open square brackets and include period in that position. It should work.

Maurix,

Here's the way I did it.

| 'name' is a form input field
extern domain tcnama name


function read.main.table()
{
long sql

sql = sql.parse("select tiedm310.*, tiedm301.*, tccom001.*, tized910.* " &
"from tiedm310, tiedm301, tccom001, tized910 " &
"where tiedm310._index1 inrange {:orno.f} " &
"and {:orno.t} " &
"and tiedm310.osta between :osta.f and :osta.t " &
"and tiedm310.prcd refers to tiedm301 " &
"and tiedm310.orno refers to tized910 " &
"and tiedm310.ecom refers to tccom001 " &
"and tccom001.nama like """ & name & """ " &
"order by tiedm310._index1")
sql.exec(sql)
while not sql.fetch(sql)
rprt_send()
endwhile
sql.close(sql)
}


This is my form 2 notes for the wildcard usage in the 'name' field.

+------------------------------------------------------------------------------+
| VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV Company: VVV |
|------------------------------------------------------------------------------|
| Wildcard Characters |
| ^ beginning of line |
| $ end of line |
| . any character |
| * all or none characters matching previous character |
| \ literal use of next character |
| [] characters within brackets, ^(not), -(through) |
| |
| Examples |
| .* all patterns |
| ^A.* patterns beginning with A |
| .*Z$ patterns ending with Z |
| .*M.* patterns containing M |
| [A-E].* patterns beginning with A through E |
| [^A].* patterns not beginning with A |
| [ACE].* patterns beginning with A, C or E |
| |
| |
+------------------------------------------------------------------------------+

Hope this helps,
fallguyjg