why is this happening? 1

[ixnay5]

--begin create order record
define ord record
        sono char(10)
end record
--end create order record


--begin selecting orders
display "Selecting Orders..."
insert into sonos
select distinct(sono)
from sodetl
where soladat > p_yest
and sono[5,5] not in ('E','M','N')
and sono[4,4] not in ('E')
and sono[3,5] not in ('RA4')
--end selecting orders


--begin load tmpship table
let o = "select trim(leading from sono) from sonos"
prepare ex_o from o
declare o_curs cursor for ex_o

foreach o_curs into ord.*

        display ord.sono

        let myord = ""
        let myord = ord.sono
|_______________________^
|
|      A grammatical error has been found on line 115, character 25.
| The construct is not understandable in its context.
| See error number -4373.

        insert into tmpship
        select indoc,apart,inlots,cpart,serln
        from  inbos
        where indoc like myord


end foreach
--end load tmpship table

it's not letting me do anything to the ord.sono record inside the foreach loop?!?!?!

 

[olded]

Hi:

Wish I could help you, but you're not providing enough schema and variable info to determine why you are getting the gramatical error at line 115.

I don't mean to elaborate on the obvious, but your statement of "not letting me do anything ... inside the foreach loop" is indicative of their not being anything within the "soros" table or the foreach otherwise failing.

Regards,

Ed

 

[ixnay5]

thx ed.

i can tell you this. if i remove these lines:

let myord = ""
let myord = ord.sono

the line immediately above them:

display ord.sono

...successfully displays the value of ord.sono at run time, telling me that table sonos gets good data.

i don't understand what conditions could present such that i can display a record like ord.sono at run time, but when i try to modify the program by simply assigning it to a variable, i get a compile error. would it help if i just posted all the code? (it's not a very big program)

 

[PHV]

How is myord defined ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[olded]

Sure, go ahead and post it. Do we also need the table schemas?

Ed

 

[ixnay5]

myord is defined as: define myord char(10)

 

[ixnay5]

there's only one schema needed: table inbos

Column name          Type                                    Nulls
 
cpart                char(16)                                no
serln                char(18)                                no
apart                char(16)                                no
inlots               char(18)                                no
[COLOR=red]indoc                char(10)                                no[/color]
intype               char(1)                                 no
inmstat              char(1)                                 no
inprev               char(2)                                 no
inqty                decimal(13,6)                           no
rec_status           char(1)                                 no

here are the problems i have not been able to solve:

1) i can't assign ord.sono to variable myord in the foreach loop...i get the compile error.

2) i am unable to do a wildcard match on myord (or ord.sono) within an SQL statement contained in the same foreach loop. however, if i pull the sql statement out of the code and run it in isql, and i replace the variable with a literal (one that is displayed in the "display ord.sono" line) i get exactly the info i'm looking for. so i know it's a 4gl problem and not a SQL problem.

fyi -- the code commented out under the d_curs section has no bearing on these problems.


the code:

-- begin define variables
define o char(800)       
define d char(800)
define p_date date
define p_yest date
define site char(10)
define sdate date
define myord char(10)
--end define variables


main


-- begin define records
define ord record
        sono char(10)
end record

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



-- begin create temp tables
create temp table sonos
(
        sono char(10)
)

create temp table tmpship
(
        indoc char(10),
        apart char(16),
        inlots char(18),
        cpart char(16),
        serln char(18)
)

create temp table tmpall
(
        indoc char(10),
        apart char(16),
        inlots char(18),
        cpart char(16),
        serln char(18),
        fssitecd char(10),
        sotadat date
)
--end create temp tables




let p_date = today
let p_yest = today - 20 




--begin selecting orders
display "Selecting Orders..."

insert into sonos
select distinct(sono)
from sodetl
where soladat > p_yest 
and sono[5,5] not in ('E','M','N')
and sono[4,4] not in ('E')
and sono[3,5] not in ('RA4')
--order by sono
--end selecting orders




-- begin loop to build temp ship table
let o = "select trim(leading from sono) from sonos"
prepare ex_o from o
declare o_curs cursor for ex_o


display "Selecting Top Part and Serial Data..."

foreach o_curs into ord.*

        display ord.sono 
        let myord = ""
        let myord = ord.sono  #doesn't work


        insert into tmpship
        select indoc,apart,inlots,cpart,serln
        from  inbos
        where indoc matches "*myord*"  #doesn't work


end foreach
-- end loop to build temp ship table





-- begin loop to build ship table with site and dates
let d = "select * from tmpship"
prepare ex_d from d
declare d_curs cursor for ex_d


display "Selecting Sites and Ship Dates..."

foreach d_curs into dat.*

--select distinct fssitecd,sotadat into site,sdate
--from fssps
--where fssps.sono like dat.indoc 
--and sotadat >= p_yest
--and dat.apart = fssps.part

        display dat.indoc,dat.apart,dat.inlots,dat.cpart,dat.serln


--      if sdate >= p_yest then
--              insert into tmpall
--              (indoc,apart,inlots,cpart,serln,fssitecd,sotadat)
--              values
--              (dat.indoc,dat.apart,dat.inlots,dat.cpart,dat.serln,site,sdate)
--      end if

end foreach
-- end loop to build ship table with site and dates





-- begin transfer from temp to perm table
insert into cmship
select * from tmpall
-- end transfer from temp to perm table





-- begin drop all temp tables
drop table tmpall
drop table tmpship
drop table sonos
-- end drop all temp tables



end main

 

[olded]

Hi:

You definitely found a weird 4GL bug. In order to eliminate the compile error, I changed your record definition to a single variable:

#define ord record
# sono char(10)
#end record
define sono char(10) # MOD

I then changed the foreach like this:

foreach o_curs into sono

display sono
let myord = ""
let myord = "*sono*"


insert into tmpship
select indoc,apart,inlots,cpart,serln
from inbos
where indoc matches myord


end foreach
# end foreach

Also, similar the the "like" question before, have you tried building your matches string like above?

Regards,


Ed

 

[ixnay5]

thanks again ed,

the compile error has gone away, but building the matches string as prescribed just the literal string "*sono*" into the myord variable, not the value.

 

[ixnay5]

ok so doing it this way (below) solves passing a literal string into the variable:

display mysono

let myord = ""
let myord = mysono

display myord

insert into tmpship
select indoc,apart,inlots,cpart,serln
from inbos
where indoc matches "*myord*"

but the wildcard match still isn't happening.

the informix 7.x reference manuals and cathy kipp's book (Programming Informix SQL/4GL A Step by Step Approach) all say any SQL statement can be used in 4GL (which i imagine includes wildcard matches), but of course all 4GL examples in these texts use exact matches.

btw, we're using Informix 4GL version 7.3.UC4.

 

[olded]

Hi:

IMO, the problem is the 4GL is interpreting "give me everything that has the string 'myord' in it" which is not what you want.

.... where indoc matches "*myord*"

Try building an insert cursor by building the string with the myord var. Here's a stub program I wrote using my test data:

DATABASE testdb

MAIN
DEFINE scratch CHAR(1000),
p_carton CHAR(10),
myord CHAR(10)

CREATE TEMP TABLE my_tmp
(
carton CHAR(10)
)

LET myord = "065566"
LET scratch = "INSERT INTO my_tmp SELECT carton FROM c_batch WHERE carton MATCHES \"*", myord CLIPPED,"*\""
PREPARE insert_stmt FROM scratch

EXECUTE insert_stmt

# I only have one row
SELECT * INTO p_carton FROM my_tmp
DISPLAY p_carton

END MAIN
# end stub

Let me know if you have any other questions.

Regards,

Ed

 

[ixnay5]

hey ed,

looks like it's working. besides than building the variable string the way you've suggested all along, the only other thing done differently was to define the variables as varchar, like this:

define myord varchar(10)
define mysono varchar(10)

build up the variable string like you said:

let myord = ""
let myord = "%",mysono CLIPPED,"%"

then refer to it as you said:

insert into tmpship
select indoc,apart,inlots,cpart,serln
from inbos
where indoc like myord

 

[ixnay5]

p.s. if you live in the San Diego area, i'd like to buy you a beer!

thanks again for all your help!

 

[olded]

Hi:

Thank you! That's a very kind offer. Unfortunately, I live in Portland, OR.

Regards,


Ed