Using WHERE with FETCH

[jyoung]

This is somewhat related to this question. http://vlforum.lansa.com.au/viewtopic.php?f=3&t=1228

Using WHERE with SELECT_SQL is super easy, yet I can never get it to work with FETCH.

For example this fetch will not work

Code: Select all

fetch fields(#RF1107Fields) from_file(RF110704) where('#WLCKCN = :WLCKCN') with_key(#WLOFID #WLOFD #WLDWP)

The error is "Expression #WLCKCN (type Number) cannot be compared with expression :WLCKCN (type String)".

In this case the field WLCKCN has a number from a previous fetch (from a different File).

So FETCH does not do variable substitution in the WHERE? It does with SELECT_SQL, why not FETCH?

I've tried doing something like this

Code: Select all

#wk_Sql := ("#WLCKCN = &1").Substitute( #WLCKCN.AsString )
fetch fields(#RF1107Fields) from_file(RF110704) where(#wk_Sql) with_key(#WLOFID #WLOFD #WLDWP)

but that does not work as it is not a conditional expression. Is there any way to use WHERE when the expression is only known at run time?

The where is required as the WLCKCN field is not part of the key and just using the key returns multiple records.
I just need the single record, I realize I can use a select to loop through the records and test against WLCKCN or use select_sql to get a single record but why should fetch not be able to do the same thing?

[billcross]

Because the field names you want to compare are the same, I believe the only way to do it is to store the first files WLCKCN in another field (#TWLCKCN) and then change the where in the fetch to where('#WLCKCN = #TWLCKCN').

[jyoung]

Hi Bill,

I believe you may be right. I keep thinking the "where" is a where in terms of a SQL query.

Using fields as an "expression", works. The warning was trying to tell me that, I just was not getting it because the above thought kept getting in the away.

This works where TICKCN is a field from another file that has already been populated via another fetch.

Code: Select all

fetch fields(#RF1107Fields) from_file(RF110704) where(#WLCKCN = #TICKCN) with_key(#WLOFID #WLOFD #WLDWP)

Thanks,
Joe