using a 4gl variable in a sql "like" statement 1

[ixnay5]

hi -

is there special syntax for handling a wildcard search if the value i'm matching on is a 4gl variable?

the basic statement is:

select something
from somewhere
where something like my.value

something could be a large field and my.value could be anywhere in it.

so i tried:

select something
from somewhere
where something like %my.value%

but it didn't work.

thx!

 

[olded]

Hi:

If I've correctly interpreted what you are asking for try building your like string outside the select:

DEFINE p_carton CHAR(15),
myvar CHAR(15)

LET myvar="%EV00656812%"

SELECT carton INTO p_carton FROM c_batch WHERE carton LIKE myvar

Regards,

Ed

 

[ixnay5]

hi ed -

it looks like you've assigned a literal (with wildcards) to myvar.

in my case, i have to use a 4gl variable that is part of a previously defined record, and the whole thing is in a cursor loop. would this be different?

here's how it goes:

define dat record
        indoc char(10),
        apart char(16),
        inlots char(18),
        cpart char(16),
        serln char(18)
end record

--...setup the cursor

foreach d_curs into dat.*

select distinct fssitecd,sotadat into site,sdate
from fssps
where fssps.sono like dat.indoc

--...do some stuff 

end foreach

 

[olded]

Hi:

You're right. That's what I've done. Any reason why you can not do something like this: (untested)

define dat record
indoc char(10),
apart char(16),
inlots char(18),
cpart char(16),
serln char(18)
end record,
my_indoc CHAR(15)

--...setup the cursor

foreach d_curs into dat.*

LET my_indoc = "%", dat.indoc CLIPPED, "%"
select distinct fssitecd,sotadat into site,sdate
from fssps
where fssps.sono like my_indoc # MOD

--...do some stuff

end foreach

 

[ixnay5]

thx ed. i tried that and some other variations with no luck.

turns out the value i'm feeding into the variable is coming out of the table with some leading spaces which need to be removed. it's char(10) but we're storing values with 6 or 8 meaningful chars. i thought CLIPPED would take care of that but it's not. so whenever i try to use the LIKE clause, i'm trying to match on leading spaces + the value.

i need to know how to trim off those spaces....

 

[olded]

Hi:

Informix has an internal function called TRIM for removing leading and trailing characters. Here's how it works: Given table c_batch with character column carton:

# untested
select TRIM(LEADING FROM carton) from c_batch

should remove the leading spaces.

I'm not certain you can build this into a cursor. I've never tried it.

Regards,


Ed

 

[ixnay5]

thx ed.

i found out you can build it into a cursor statement and it will work. but if try to use it in a SQL statement contained in a foreach cursor loop, it won't.

still having trouble doing wildcard matches in SQL statements in foreach cursor loops tho...

thanks again for all your help!

 

[olded]

In this 4GL forum's FAQ, I've included a 4GL function called
squeeze:

function squeeze(source_string)

It returns a string with all the spaces squeezed out so you could do something like this inside the foreach loop:

LET dat.indoc = squeeze(dat.indoc)

If you have required spaces as part of the internal data, we can modify squeeze to stop once a valid character is reached.

Regards,

Ed