Hi!

Thanks to Alan B and Francis Mariani for information about
sysfiles, syscolum, syskeys, systable.

I've tried to go a little further - I want to update TITLE in HOLD-files.

I've tried such thing (don't laugh):

SET ASNAMES = ON 

DEFINE FILE CAR 
        COUNTRY_N/A20 = STRREP(20,COUNTRY,1,' ',0,'X',14,'A20'); 
END 

TABLE FILE CAR 
ACROSS COUNTRY_N 
BY CAR 
BY MODEL 
SUM SALES AS 'SLZ_' 
ON TABLE HOLD AS CAR_HOLD 
END 

SQL 
	UPDATE SYSCOLUM
		SET
			TITLE = 'CAR TITLE'
		WHERE
			TBNAME = 'CAR_HOLD'
			AND TBCREATOR = 'EDADBA'
			AND SEGNO = 1
			AND NAME = 'CAR';
	COMMIT
END

TABLE FILE SYSCOLUM 
PRINT TBNAME SEGNO TBCREATOR NAME TITLE 
WHERE TBNAME EQ 'CAR_HOLD'; 
ON TABLE HOLD AS FIELD_LIST
END 

TABLE FILE FIELD_LIST
PRINT *
END

Funny - UPDATE is working.

0 TRANSACTIONS: TOTAL = 1 ACCEPTED= 1 REJECTED= 0
SEGMENTS: INPUT = 0 UPDATED = 1 DELETED = 0

But TITLE is not changed.

Is it possible at all?
(It would be sooo good. Ability to redefine titles of ACROSS fields with logic IF THEN ELSE IF .. )

ALSO:
Can somebody point me to full documentation for SQL FMI syntax?

Regards,
Alex

Ingas,

The sysfiles and syscolum master files have a very special meaning.
What happens when you access these files, is that at that momentban internal data structure is built that contains information about all master files the server can find in its current search path.
The keyword here is internal.
So yes, updating a certain piece of information for a certain column in the syscolum table will be successful, but after having done that, the internal structure will be destroyed again (by ending the agent session), and any subsequent query against the table will again give you the original unchanged result.
Please note that by trying to update information in the syscolum table, it does just that. It will not propagate your change to the corresponding master file.
Hope this clarifies things.

Hallo GamP,

I'm aware of internal essence of this files.
(And I want know more about SUFFIX = MFI)

I have positive experience with updating MSSQL 2K system tables. (for some tables and some fields it's possible for some - not. I hoped that some tricks can be used agains WF internal tables. I should be glad if in WF was smth like ALTER TABLE ALTER COLUMN )

quote:

.. but after having done that, the internal structure will be destroyed again ` (by ending the agent session) , and any subsequent query against the table will again give you the original unchanged result.

I don't understand this "by ending the agent session" - does this mean that SQL UPDATE ... END starts and ends a separate agent session?

Thank for you reply,
Regards,
Alex

Silly question here, is it just you want to be able to call the field something else? Or are you wanting to use SQL to physically change something in the table. Or is it the TITLE used for the member as coded in the MFD which is generated by the SQL.

You cannot update the "title" in syscolumns, you cannot update anything - this data doesn't exist as real columns - the data comes from the Master for FOCUS DB's and from system tables in dbms.

Leah,
Answers are two:
1. Modest: change ACROSS-titles in hold-files by one instruction.
2. Not modest: make modifications in master-files using SQL or MODIFY.

Why not? It seemed very appealing.

I understand that using -READ/-WRITE/-LOOP is possible in theory to make modification in MFDs. But I can't imagine a persone who can like such process.

But suppose such thing:
Load TITLE, DESCRIPTION, HELPMESSAGE for fields from external source (text-file, relational table) and update all MFDs.

Doesn't it appealing?

Does not sound appealing because if you need to regenerate the dbms metadata, you will lose all the changes you made. I never modify metadata that is generated by the WebFOCUS Console metadata creator.

Appealing, don't know if I'd agree, but if you are just wanting the title changed for a report two options. Manually edit the generated MFD and change the title of the column. Or super simple

PRINT CAR AS 'Car Title'

Sometimes it's fun to try things, but sometimes it's better to do what works.

Have a great weekend.

Hi, Leah

Yes, it's simple when it's about PRINT, BY and SUM.
But changing TITLE of ACROSS-fields not simple for me.
(I'm just learning WebFOCUS)

I use code similar to:

SET ASNAMES = ON

DEFINE FILE CAR
	COUNTRY_N/A20 = STRREP(20,COUNTRY,1,' ',0,'X',14,'A20');
END

TABLE FILE CAR
ACROSS COUNTRY_N
BY CAR 
SUM SALES AS 'SLZ_'
ON TABLE HOLD AS CAR_HOLD
END

TABLE FILE SYSCOLUM
BY NAME
WHERE TBNAME EQ 'car_hold'
WHERE NAME NE 'CAR'
ON TABLE HOLD AS FIELD_LIST
END

-RUN
-SET &FIELDS = '';
-LOOP
-READ FIELD_LIST &NAME.20
-IF &IORETURN NE 0 THEN GOTO ENDLOOP;
-SET &FIELDS = &FIELDS | ' ' | &NAME;
-GOTO LOOP
-ENDLOOP

TABLE FILE CAR_HOLD
BY CAR
PRINT &FIELDS
END
-FINISH

Now I think about adding for each &NAME in &FIELDS 'AS 'TITLE ...''.
I can't say that I'm much happy about it.

If it was possible to write something like:
UPDATE syscolum
SET TITLE = 'Sales in ' | SUBSTR(..,NAME, ..)
WHERE
NAME LIKE 'SLZ%'

I should be happy
And I do not think it's so crazy idea - I hoped that such possibity exists, just undocumented.


Regards,
Alex

quote:

Yes, it's simple when it's about PRINT, BY and SUM.
But changing TITLE of ACROSS-fields not simple for me.
(I'm just learning WebFOCUS)

You can use the AS in the ACROSS as well.

TABLE FILE CAR
PRINT MODEL AS 'Car Model'
ACROSS COUNTRY AS 'Country Is'
BY CAR AS 'Brand'
END

Leah,

I design report now with dynamic quantity of columns.
And I need to make several different ACROSS and several measures to count.

I do several hold files with same BY fields and differing ACROSSs.
Than I MATCH this holds-files and make additional COMPUTEs.
(COMPUTEs with C[number] fields - are good)
I do not know names and quantity of columns generated by ACROSS.
Also I can't use ACROSS names with spaces.

(And I need to colspan headers above all this! =) )

Sorry, I was thinking more of 'titles for columns and fields' not field names which I think is what you are wanting.

Mull it over time here for me. I've never had a need for that particular method.