Re: output date field from a flat file into a variable

A VIEW can not be created over a flat file :-( Besides, any extra file is really unnecessary and unnecessarily complex [minimally, adds something to be deleted], because the OPNQRYF could do any desired selection work with only a truly temporary ODP vs a /temporary/ permanent file object... and presumably without adding any complexity to the CL compile. Most notably, nothing about using the SQE vs CQE for a flat file would probably matter, if even there were any requirement; i.e. no WHERE clause was ever shown... the desired data may be in the first and only row. And even so, repeated RCVF and compare within the CLP for the desired row might be just as efficient. Yet depending on the provenance of the file and the nature of how the data was produced, an OVRDBF request might even enable positioning directly to the desired row on the first RCVF; i.e. upon the open.

Regards, Chuck

On 31 Jul 2013 21:54, Birgitta Hauser wrote:

Instead of creating a temporary table I'd suggest to create a
temporary view and read this view:

create View qtemp/zz_Date as
( SELECT substr(admadmrec, 9, 8) MyDate
from admfile
)

You'd still need a DCLF ZZ_Date and a RCVF.

Sam_L on Thursday, 01.8 2013 01:34 wrote:

As others have noted, DCLF and RCVF on admfile may be the answer.

If, however, you are doing some complicated SQL over a largish
file to get your date (which seems a bit unlikely if admfile is a
flat file) an SQL command with this might work:

create table qtemp/zz_Date as
( SELECT substr(admadmrec, 9,8) MyDate
from admfile
) with data

You'd still need a DCLF ZZ_Date and a RCVF.

Of course, this is a little trickier to compile.

On 7/30/2013 6:25 PM, A Paul wrote:

I am looking to <<SNIP>> get the date from a flat file with the
below SQL:

SELECT substr(admadmrec, 9,8)
from admfile

the value is in format yyyymmdd.

How do you output this date into a variable (&VAR) to compare
with another variable <<SNIP>>