LF DDS for Field with CONCAT and SST
Christian Luebke
I have one physical file designed years ago with a date field defined
as zoned 6,0, the date is filled in DDMMYY format. Wonderful for doing
queries with a date range :-(
Of course I can work with MAPFLD on OPNQRYF, but the file can have
some million records, and on slow machines it takes ages to run even
if only some records are needed.
I tried to create a LF-DDS with a pre-defined field to avoid the
MAPFLD. But I'd need something like this:
NEWFLD I CONCAT(SST(DATEFLD 5 2) -
SST(DATEFLD 3 2) SST(DATEFLD 1 2))
The compiler doesn't allow this structure. Also doing the SST's for 3
single fields an concatenating them results in an error (the 3 SSTed
fields are not found).
Any ideas?
Bye, Christian
Mercury
Use DIGITS to reverse and concatenate the zoned date field.
1/ SQL. Create View (Logical file).
CREATE VIEW MYLIB/MYVIEW (YYMMDD) AS SELECT SUBSTR(DIGITS(DATEFLD), 5, 2) !! SUBSTR(DIGITS(DATEFLD), 3, 2)
!! SUBSTR(DIGITS(DATEFLD), 1, 2) FROM MYLIB/MYPF
2/ QUERY
You can then play with fields individually or CONCAT them together on the Define Result Fields Screen this way:
YYMMDD SUBSTR(DIGITS(DATEFLD), 5, 2) !! SUBSTR(DIGITS(DATEFLD), 3, 2) !! SUBSTR(DIGITS(DATEFLD), 1, 2)
You could easily change this date into an ISO date by prefixing the century ('20') to the year field.
hth
Mercury
"Christian Luebke" <chluebke...@kurvas.de> a écrit dans le message de news: 3efb077d...@news.cis.dfn.de...
Jean-Philippe BALLAT
I'm french !!
Look my exemple and see if you can have idea :
It's possible to do field (result of sst) in hidden (N in pos 38) and it's
possible use field hidden in other construction.
Hidden field in my exemple : FIORG1 FIORGn
So look it's possible use in join the same file : use in join 1 2 3 ....
So look it's possible use in join the same field : rename .
So look it's possible use in join hidden field and use this hidden field
like join field
Friendly !!!!!
0010.00
A*----------------------------------------------------------------
0011.00 A UNIQUE
0012.00 A JDFTVAL
0013.00 A R FIFIOJF JFILE(FIFIORP PEFTROP1
0014.00 A CGCORP1 CGCORP1
0015.00 A OOPEP OOPEP
0016.00 A FIACTIP FIGACTP
0017.00 A FIIMFIP FITYFIP
0018.00 A FISEFIP
SITP1 )
0019.00 A*
0020.00
A*--EMETTEUR------------------------------------------------------
0021.00 A J JOIN(1 2)
0022.00 A JFLD(FICDEM MATCOD)
0023.00 A JFLD(FIMTEM MATNRO)
0024.00 A J JOIN(2 3)
0025.00 A JFLD(CODOR CODOR)
0026.00 A *
0027.00
A*--RESPONSABLE --------------------------------------------------
0028.00 A*
0029.00 A J JOIN(1 4)
0030.00 A JFLD(FIGSTC CODOR)
0031.00 A*
0032.00 A*--ORGANIGRAMME
PRODUIT------------------------------------------
0033.00 A*
0034.00 A J JOIN(1 5)
0035.00 A JFLD(FIORG1 ORORG1)
0036.00 A JFLD(FIORG2 ORORG2)
0037.00 A JFLD(FIORG3 ORORG3)
0038.00 A JFLD(FIORG4 ORORG4)
0039.00 A JFLD(FIORG5 ORORG5)
0040.00 A JFLD(FIORG6 ORORG6)
0041.00 A JFLD(FIORG7 ORORG7)
0042.00 A JFLD(FIORG8 ORORG8)
0043.00 A JFLD(FIORG9 ORORG9)
0044.00 A JFLD(FIORG10 OROR10)
0045.00 A JFLD(FIORG11 OROR11)
0046.00 A JFLD(FIORG12 OROR12)
0047.00 A JFLD(FIORG13 OROR13)
0048.00 A JFLD(FIORG14 OROR14)
0049.00 A JFLD(FIORG15 OROR15)
0050.00 A*
0051.00 A J JOIN(1 6)
0052.00 A JFLD(FIOG1 ORORG1)
0053.00 A JFLD(FIOG2 ORORG2)
0054.00 A JFLD(FIOG3 ORORG3)
0055.00 A JFLD(FIOG4 ORORG4)
0056.00 A JFLD(FIOG5 ORORG5)
0057.00 A JFLD(FIOG6 ORORG6)
0057.00 A JFLD(FIOG6 ORORG6)
0058.00 A JFLD(FIOG7 ORORG7)
0059.00 A JFLD(FIOG8 ORORG8)
0060.00 A JFLD(FIOG9 ORORG9)
0061.00 A JFLD(FIOG10 OROR10)
0062.00 A JFLD(FIOG11 OROR11)
0063.00 A JFLD(FIOG12 OROR12)
0064.00 A JFLD(FIOG13 OROR13)
0065.00 A JFLD(FIOG14 OROR14)
0066.00 A JFLD(FIOG15 OROR15)
0067.00 A*
0068.00 A*--ACTIVITE
PLO--------------------------------------------------
0069.00 A*
0070.00 A J JOIN(1 7)
0071.00 A JFLD(FIACTI ACCODE)
72.0 A*--GENRE
ACTIVITE------------------------------------------------
0073.00 A*
0074.00 A J JOIN(1 8)
0075.00 A JFLD(FIGACT GACODE)
0076.00
A*--GRAVITE ------------------------------------------------------
0077.00 A*
0078.00 A J JOIN(1 9)
0079.00 A JFLD(FIGRAV IMTYGR)
0080.00 A*--TYPE
D'INCIDENT-----------------------------------------------
0081.00 A*
0082.00 A J JOIN(1 10)
0083.00 A JFLD(FITYPE TYCODE)
0084.00 A*--SOUS
ETAT ----------------------------------------------------
0085.00 A*
0086.00 A J JOIN(1 11)
0087.00 A JFLD(FISETA SECODE)
0088.00
A*--SITE----------------------------------------------------------
0089.00 A*
0090.00 A J JOIN(1 12)
0091.00 A JFLD(FISITE SITE )
0092.00
A*----------------------------------------------------------------
0093.00 A FIANNE
0094.00 A FICHRO
0095.00 A FICAMP
0096.00 A FIETAT
0097.00 A FIGRAV
0098.00 A FIDTRT
0099.00 A FIHEUR
0100.00 A FINFA
0101.00 A FINPM
0102.00 A FIAN02
0103.00 A FICH02
0104.00 A FIAN01
0105.00 A FICH01
0106.00 A FIDT04
0107.00 A FIDT01
0108.00 A FIDTCT
0109.00 A FIHRCT
0110.00 A FIOP03
0111.00 A FILB01
0112.00 A FIOP01
0113.00 A FIOP02
0114.00 A FITYPE
0115.00 A FISETA
0116.00 A FISITE
0117.00 A FIGACT
0118.00 A FIACTI
0119.00 A FIMOC1
0120.00 A FIMOC2
0121.00 A FIMOC3
0122.00 A FIDCHG
0123.00 A FITMOD
0124.00 A FIDMOD
0125.00 A FIGSTC
0126.00 A FIDTRL
0127.00 A FIREFR TEXT('O/ ')
0128.00 A COLHDG('Tag Mod
éffectuée')
0129.00 A FICDEM
0130.00 A FIMTEM
0131.00 A*
0132.00 A*DERNIERE
COMMISSION --------------------------------------------
0133.00 A*
0134.00 A FIANCO
0135.00 A FICHCO
0136.00 A FIGRCO
0137.00 A FIDTCO
0138.00
A*--EMETTEUR------------------------------------------------------
0139.00 A FIEMNM RENAME(NOMPRE) JREF(2)
0140.00 A COLHDG('Nom émetteur')
0141.00 A FIEMSG RENAME(LIBSIG) JREF(3)
0142.00 A COLHDG('Sigle émetteur')
0143.00 A CODOEM JREF(2) RENAME(CODOR)
0144.00 A COLHDG('Codor émetteur')
0145.00
A*--RESPONSABLE ------------------------------------------------
0146.00 A FIRPSG RENAME(LIBSIG) JREF(4)
0147.00 A COLHDG('Sigle
responsable')
0148.00 A*--ORGANIGRAMME
PRODUIT----------------------------------------
0149.00 A FIORG1 N SST(FIOP01 1 1)
0150.00 A FIORG2 N SST(FIOP01 2 1)
0151.00 A FIORG3 N SST(FIOP01 3 1)
0152.00 A FIORG4 N SST(FIOP01 4 1)
0153.00 A FIORG5 N SST(FIOP01 5 1)
0154.00 A FIORG6 N SST(FIOP01 6 1)
0155.00 A FIORG7 N SST(FIOP01 7 1)
0156.00 A FIORG8 N SST(FIOP01 8 1)
0157.00 A FIORG9 N SST(FIOP01 9 1)
0158.00 A FIORG10 N SST(FIOP01 10 1)
0159.00 A FIORG11 N SST(FIOP01 11 1)
0160.00 A FIORG12 N SST(FIOP01 12 1)
0161.00 A FIORG13 N SST(FIOP01 13 1)
0162.00 A FIORG14 N SST(FIOP01 14 1)
0163.00 A FIORG15 N SST(FIOP01 15 1)
0164.00 A*
0165.00 A FIOG1 N SST(FIOP02 1 1)
0166.00 A FIOG2 N SST(FIOP02 2 1)
0167.00 A FIOG3 N SST(FIOP02 3 1)
0168.00 A FIOG4 N SST(FIOP02 4 1)
0169.00 A FIOG5 N SST(FIOP02 5 1)
0170.00 A FIOG6 N SST(FIOP02 6 1)
0171.00 A FIOG7 N SST(FIOP02 7 1)
0172.00 A FIOG8 N SST(FIOP02 8 1)
0173.00 A FIOG9 N SST(FIOP02 9 1)
0174.00 A FIOG10 N SST(FIOP02 10 1)
0175.00 A FIOG11 N SST(FIOP02 11 1)
0176.00 A FIOG12 N SST(FIOP02 12 1)
0177.00 A FIOG13 N SST(FIOP02 13 1)
0178.00 A FIOG14 N SST(FIOP02 14 1)
0179.00 A FIOG15 N SST(FIOP02 15 1)
0180.00 A FIOPAS RENAME(ORLIBE) JREF(5)
0181.00 A COLHDG('Libellé
OP associé'
0182.00 A FIOPCO RENAME(ORLIBE) JREF(6)
0183.00 A COLHDG('Libellé
OP Concerné
0184.00 A*--ACTIVITE
PLO------------------------------------------------
0185.00 A ACLIBE COLHDG('Libellé Activité
PL
0186.00 A*--GENRE
ACTIVITE----------------------------------------------
0187.00 A GALIBE COLHDG('Libellé Genre'
'Act
0188.00
A*--GRAVITE ----------------------------------------------------
0189.00 A IMLIBE COLHDG('Libellé
gravité')
0190.00 A*--TYPE
D'INCIDENT---------------------------------------------
0191.00 A TYLIBE COLHDG('Libellé Type'
'Inci
0192.00 A*--SOUS
ETAT --------------------------------------------------
0193.00 A SELIBE COLHDG('Libellé Sous
état')
0194.00
A*--SITE -------------------------------------------------------
0195.00 A CLESIT COLHDG('CLé Site ')
JREF(
0196.00 A LIBSIT COLHDG('Libellé Site ')
J
0197.00 A*--MOTS
CLEF --------------------------------------------------
0198.00 A MOTCLE CONCAT(FIMOC1 FIMOC2
FIMOC3
0199.00 A*
200.0 A*
201.0 0200.00 A*
202.0 0201.00 A*LES CLEFS
203.0 0202.00 A*
204.0 0203.00 A K FIANNE
DESCEND
205.0 0204.00 A K FICHRO
DESCEND
Ken
On Thu, 26 Jun 2003 14:56:40 GMT, chluebke...@kurvas.de
(Christian Luebke) wrote:
>I tried to create a LF-DDS with a pre-defined field to avoid the
>MAPFLD. But I'd need something like this:
>
>NEWFLD I CONCAT(SST(DATEFLD 5 2) -
> SST(DATEFLD 3 2) SST(DATEFLD 1 2))
>
>The compiler doesn't allow this structure. Also doing the SST's for 3
>single fields an concatenating them results in an error (the 3 SSTed
>fields are not found).
1. Since you have listed one field in the LF, you have to list all of
the fields that you want. For fields in the underlying PF, you just
need to list the field names, with no attributes specified.
2. Make a new field name for each SST, then CONCAT those three fields
to make NEWFLD.
Ken
http://www.ke9nr.net/
Opinions expressed are my own and do not necessarily represent the views of my employer or anyone in their right mind.
Christian Luebke
>2. Make a new field name for each SST, then CONCAT those three fields
>to make NEWFLD.
You mean something like this?
DATEDD I SST(DATEFLD 1 2)
DATEMM I SST(DATEFLD 3 2)
DATEYY I SST(DATEFLD 5 2)
NEWFLD I CONCAT(DATEYY DATEMM DATEDD)
That doesn't work, system starts crying because DATEDD, DATEMM and
DATEYY are unknown. Seems to me as if only fields of the underlying PF
are allowed for CONCAT...
Or am I doing something wrong?
Bye,
Christian
Jean-Philippe BALLAT
I writed before : Use intermediair field in HIDDEN !!
and use rename !!!!
Friendly !!
Ken
On Fri, 27 Jun 2003 06:25:14 GMT, chluebke...@kurvas.de
(Christian Luebke) wrote:
>You mean something like this?
>
>DATEDD I SST(DATEFLD 1 2)
>DATEMM I SST(DATEFLD 3 2)
>DATEYY I SST(DATEFLD 5 2)
>NEWFLD I CONCAT(DATEYY DATEMM DATEDD)
>
>That doesn't work, system starts crying because DATEDD, DATEMM and
>DATEYY are unknown. Seems to me as if only fields of the underlying PF
>are allowed for CONCAT...
>
>Or am I doing something wrong?
That was exactly what I meant. I've never needed to use CONCAT, I've
only ever needed to use SST. Looking at the manual, I see that CONCAT
has restrictions that SST does not. With SST you can reference not
only fields in the PF, but also fields previously defined in the LF
source. Looking at the manual, that's not true with CONCAT. I don't
know why not, except that CONCAT fields are not required to be
input-only.
Your best option may be to skip NEWFLD and just use DATEYY, DATEMM,
and DATEDD as key fields. That would mean a little more work in the
program for CHAIN/SETLL/SETGT/READE operations, but at least would
order the file in YMD order.
Christian Luebke
>I writed before : Use intermediair field in HIDDEN !!
>and use rename !!!!
Can you please give me an example? I don't really see the path you'd
like me to go...
Thanks & Bye,
Christian
Mercury
zoned fields, e.g.
A DATEFLD (This field is declared in the PF source member)
A ... (others fields to list from the PF)
A DATEDD I SST(DATEFLD 1 2)
A DATEMM I SST(DATEFLD 3 2)
A DATEYY I SST(DATEFLD 5 2)
A ...
A K DATEYY
A K DATEMM
A K DATEDD
HTH this time
Mercury
"Mercury" <nospam.ca...@hotmail.com> a écrit dans le message de news: bdf5cj$g99$1...@news-reader2.wanadoo.fr...
Christian Luebke
>Since you dont want to use a view, you have to list the from field for the SST to work in the LF provided that you use only alpha or
>zoned fields, e.g.
I didn't say I don't want to use a view :) Already tried it and looks
like it's working for my needs. But I'd prefer a "normal" LF,
because...
> A K DATEYY
> A K DATEMM
> A K DATEDD
I need to do an OPNQRYF on the field with a date range. Having 3
separate fields makes the Query more complicated, something like
DATEYY *LT ToYear *OR DATEYY *EQ ToYear *AND (DATEMM *LT ToMonth *OR
DATEMM = ToMonth *AND DATEDD *LE ToDay) ...
I prefer comparisons like DATEYMD *GE FromYMD *AND DATEYMD *LE ToYMD
Anyway, thanks for you View idea and this suggestion!
Bye,
Christian
J Anthony Easterday
'normal' thing to do in a logical file.
EXPRTNDT I CONCAT(EXP0YR EXP0MO EXP0DA)
COLHDG('EXPIRATION' 'DATE' +
'CYYMMDD')
ALIAS(EXPRTN_DT)
NOTE: This creates an alpha (text) field and it is input only (can't be the
target of a move statement in an update).
"Christian Luebke" <chluebke...@kurvas.de> wrote in message
news:3f00472a...@news.cis.dfn.de...
Ken
On Mon, 30 Jun 2003 14:32:06 GMT, chluebke...@kurvas.de
(Christian Luebke) wrote:
>I need to do an OPNQRYF on the field with a date range. Having 3
>separate fields makes the Query more complicated, something like
>DATEYY *LT ToYear *OR DATEYY *EQ ToYear *AND (DATEMM *LT ToMonth *OR
>DATEMM = ToMonth *AND DATEDD *LE ToDay) ...
You can concatenate DATEYY, DATEMM, and DATEDD in the OPNQRYF itself
for the purposes of record selection. You cannot use the
concatenation for KEYFLDS, however you can use DATEYY, DATEMM, and
DATEDD as KEYFLDS (or KEYFLDS(*FILE) if appropriate) to get date
ordering if desired.
Christian Luebke
>Here is a sample to take separate Y/M/D fields and combine. This is a
>'normal' thing to do in a logical file.
>
>EXPRTNDT I CONCAT(EXP0YR EXP0MO EXP0DA)
> COLHDG('EXPIRATION' 'DATE' +
> 'CYYMMDD')
> ALIAS(EXPRTN_DT)
But this technique requires the Y/M/D fields as separate fields in the
PF. I'd be happy, if it were so. The PF only has one "big" DDMMYY
field which I'd like to convert to YYMMDD in the LF.
As I wrote in another message:
DATEDD I SST(DATEFLD 1 2)
DATEMM I SST(DATEFLD 3 2)
DATEYY I SST(DATEFLD 5 2)
NEWFLD I CONCAT(DATEYY DATEMM DATEDD)
This doesn't work in a LF :-( Only an SQL View seems to be able to
handle this...
Bye,
Christian
Terrence Enger
Christian,
Sorry to jump in so late on this, but I have been away for a week. Is
your question still open?
You do not say whether you need to update your big file through the ODP
created by OPNQRYF.
If the OPNQRYF is for input only, you might consider this scheme:
Create a PF with a field for each date format, at least DDMMYY and
format over which you can easily code a selection of ranges. Then
create a LF joining your big file to this date file on the DDMMYY
fields. On the OPNQRYF statement, use the FORMAT() parameter so that
you need not change your HLL programs. This sounds pretty gross, but
your date file will only have 36524 records, and you may find other uses
for the file. I have seen this scheme give good query performance, and
I could tolerate the hit on update performance.
I have not needed to update a file like this through the ODP created by
OPNQRYF. So, I am pulling the next scheme out of the clear blue sky:
Create a new PF with all the data from your big file plus a column with
a more conveniently formatted date. Over this PF create a LF with the
old record format and the old file name. Put a trigger program on the
new PF to propagate changes from the DDMMYY field to the better date
field. Then write OPNQRYF FILE(the_PF) FORMAT(the_LF), and you can
write selection criteria using the better date field.
Hope this helps.
Terry.
Available for contract programming.