[SOLVED] Modify hold file

There are several ways to add records to a hold file.

Macgyver Technique
MODIFY (Focus/Relational)
FILEDEF (APPEND
FML/FRL/EMR/SML etc

It depends on the indiviuals case and preferences.

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006

Ignored post by Waz posted

Frans

Guru

posted

Hi Kev,

Do you want to add missing columns or missing rows? It was not really clear in you question.

In case of missing columns you can use JOIN's with TABLE FILE or MATCH. Your logic can then be put in define's or compute's.

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.

Posts: 454 | Location: Europe | Registered: February 05, 2007

Ignored post by Frans posted

<Kev>

posted

Hi Frans,

I want to add missing rows. Columns stay intact.

Expert

posted

I'd recommend along the lines of Waz' response to use either McGuyver or Universal Concatenation (MORE).

You can't do a MODIFY on a fixed-format HOLD file. You could load the rows into a temp FOCUS file using MODIFY, then load the new rows, then TABLE out into another hold file.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

<Kev>

posted

Hi Waz,

I can't find a good example of your mentioned techniques.
I want to in pseudo language do this:

LOOP OVER TABLE T
FOR EACH REC IN T
IF REC.COLUMN1 NOT EXISTS IN OTHER ENTRIES OF COLUMN1
INSERT REC WITH REC.COLUMN2='CHANGED VAL'
ELSE
CONTINUE
LOOP OVER
END

<Kev>

posted

Hi Ginny,

I can use Universal Concatenation fine. My issue is iterating over the rows and storing those rows that I need to insert using MORE statement later on (after the loop) - if I can get example/guidance on it, I'll be all set.

Thanks much!
-----------------------------------------------------

Expert

posted

How about first finding all the rows in T that only have 1 entry. I think based on your posts that is what you are looking for. You could then massage those and then use universal concatenation to put the two hold files back together.

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS CARHOLD FORMAT ALPHA
END
TABLE FILE CARHOLD
COUNT ENTRIES 
BY BODYTYPE
PRINT *
BY BODYTYPE
WHERE TOTAL COUNT EQ 1
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS ONLYONE FORMAT ALPHA
END

The first table is just to get a flat hold file. You can do more massaging in the 2nd table request to get your columns in order.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

<Kev>

posted

Hi Ginny,
That's a good start. Since both have the same tables, I can explain you exactly what I need to do.
So in the following, I need to make sure that there're exactly 3 entries of the combination {Country, Bodytype} (two randomly selected columns). So I need to first find which combination has a missing entry (less than 3 - it's a given that it'll be less than or equal, but never greater than 3). If any does, I need to take the content of the entire row with same {Country, Bodytype} combination, change one of the columns (say, `retail cost') and add it back in main table (via may be MORE).

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS CARHOLD FORMAT ALPHA
END
TABLE FILE CARHOLD
COUNT ENTRIES
BY BODYTYPE
PRINT *
BY BODYTYPE
BY COUNTRY
WHERE TOTAL COUNT EQ 1
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS ONLYONE FORMAT ALPHA
END

Thanks much!
------------------------------------------------

Expert

posted

And you want to get rid of the original rows that had the missing stuff, correct?

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

<Kev>

posted

Hmm... in my case, entire row is missing, not the individual column values.

Thanks!
-----------------------------------------------

Expert

posted

Then you have 2 files? I'm very confused.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

<Kev>

posted

Nah, just one file.
I know that that file is supposed to have 3 entries per combination {Country, Bodytype}, and that there're four such combinations.

Expert

posted

quote:
Hmm... in my case, entire row is missing, not the individual column values.

If the whole row is missing, how can you test for combinations.

I'm not trying to be obnoxious, really I'm not. Maybe you should post some data and annotate it so we can get a better idea.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

<Kev>

posted

Okay, I can't put in HTML formatted output here, so I'm going to try to explain my case with an example. Going with the table we've been discussing:

- I know {Convertible, England} (of {Body Type, Country}) should have three entries with retail_cost equal to {10000, 9000, 8000}. If any one of the entries {10000, 9000, 8000} is missing, I need to find that through existing entry (it's a given that there'll be one entry per combination for sure), and add that entry.
- There're four such combinations: {Convertible, England}, {Convertible, Italy}, {Roadster, England}, {Roadster, Italy}.
- So, in total, it has to have 12 entries.
- Entries for `retail_cost' are known in advance, and have to be {10000, 9000, 8000}.

This is a difficult problem to explain, so pardon my crude explanation.

I appreciate all the help.

Thanks once more!

-------------------------------------------------------------------------------

This message has been edited. Last edited by: <Kev>,

Tony A

Expert

posted

Would I be correct in saying that you have two fields, values unknown, but for each combination of those two fields there must be three rows of output?

If this is true then I would suggest that oen way is using the McGyver technique.

This example uses the sample database CAR and there are certain combinations of Country and Bodytype that do not exist. Using this code I can perform a loose join to provide every combination of those two fields. To extend that to have every combination of three fields is just a small change away -

DEFINE FILE CAR
  CTRY_KEY/A1 WITH COUNTRY  = ' ';
  BTYP_KEY/A1 WITH BODYTYPE = ' ';
END

TABLE FILE CAR
   BY CTRY_KEY
   BY COUNTRY
ON TABLE HOLD AS CTRYFILE FORMAT FOCUS INDEX CTRY_KEY
END

TABLE FILE CAR
   BY BTYP_KEY
   BY BODYTYPE
ON TABLE HOLD AS BODYFILE
END

JOIN CLEAR *
JOIN BTYP_KEY IN BODYFILE TO MULTIPLE CTRY_KEY IN CTRYFILE AS J1

TABLE FILE BODYFILE
   BY COUNTRY
   BY BODYTYPE
END

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Dave Ayers

Platinum Member

posted

Kev,

Your terminology is confusing me.

When you say 'entries', do you mean fields ?

I think you would get a lot farther with this problem if you used the terms 'record' and 'field'

quote:
So, in total, it has to have 12 entries.
- Entries for `retail_cost' are known in advance, and have to be {10000, 9000, 8000}.

I keep getting the feeling that you have all the records/rows that you need, but some of the fields are empty. And you could populate those field values with DEFINEs, as you intimated that you had the logic to do that.

Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server

Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003

Ignored post by Dave Ayers posted