Focal Point
Is it possible to update syscolum? (misterious and undocumented syscolum :) )
October 19, 2007, 03:57 AM
IngasIs it possible to update syscolum? (misterious and undocumented syscolum :) )
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
WF 7.6.2, WinNT/2K, MSSQL 2K, MSOLAP 2K, BID
October 19, 2007, 06:58 AM
GamPIngas,
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.
GamP
- Using AS 8.2.01 on Windows 10 - IE11. |
| in Focus since 1988 |
October 19, 2007, 08:13 AM
IngasHallo 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
WF 7.6.2, WinNT/2K, MSSQL 2K, MSOLAP 2K, BID
October 19, 2007, 09:49 AM
LeahSilly 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.
Leah
October 19, 2007, 09:51 AM
Francis MarianiYou 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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 19, 2007, 10:11 AM
IngasLeah,
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?
WF 7.6.2, WinNT/2K, MSSQL 2K, MSOLAP 2K, BID
October 19, 2007, 10:23 AM
Francis MarianiDoes 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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 19, 2007, 10:53 AM
LeahAppealing, 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.
Leah
October 19, 2007, 02:22 PM
IngasHi, 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
WF 7.6.2, WinNT/2K, MSSQL 2K, MSOLAP 2K, BID
October 19, 2007, 02:41 PM
Leahquote:
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
October 19, 2007, 03:04 PM
IngasLeah,
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! =) )
WF 7.6.2, WinNT/2K, MSSQL 2K, MSOLAP 2K, BID
October 19, 2007, 03:26 PM
LeahSorry, 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.
Leah