[Solved] Creating Attribute/Value Pairs From Oracle Table.
Greetings,
I have a requirement to create a file consisting of attribute/value pairs.
My input is an Oracle table.
Currently I am using the IB sample database 'EMPDATA' as my model.
Here is what I have at the moment..
Focexec:
APP PREPENDPATH IBISAMP
FILEDEF FSEQ DISK common/fseq.txt
JOIN CLEAR *
-RUN
-*
JOIN BLANK WITH PIN IN EMPDATA TO BLANK IN FSEQ AS J1
-*
DEFINE FILE EMPDATA
BLANK/A1 WITH PIN = ' ';
ATTRIBUTE/A25 = DECODE COUNTER( 1 'LASTNAME'
2 'FIRSTNAME');
VALUE/A25 = IF COUNTER EQ 1 THEN LASTNAME ELSE FIRSTNAME;
END
-*
TABLE FILE EMPDATA
PRINT ATTRIBUTE
VALUE
IF COUNTER EQ 1 OR 2
IF LASTNAME EQ VALINO
ON TABLE SAVE AS SVATTRS
END
-RUN
-SET &WORK = &LINES;
-*
-REPEAT ENDLOOP1 &WORK TIMES
-READ SVATTRS &ATTR.A25. &VAL.A25.
-TYPE Attribute: &ATTR Value: &VAL
-ENDLOOP1
-EXIT
Results:
0 NUMBER OF RECORDS IN TABLE= 2 LINES= 2
ALPHANUMERIC RECORD NAMED SVATTRS
0 FIELDNAME ALIAS FORMAT LENGTH
ATTRIBUTE A25 25
VALUE A25 25
TOTAL 50
Attribute: LASTNAME Value: VALINO
Attribute: FIRSTNAME Value: DANIEL
The attributes are a subset of the fields in the input file. And the attribute values, are the contents of the field in the file.
I am using the McGuyver technique to do a in memory read the incoming record n number of times.
n is equal to 2 in this case, because I want two attribute/value pairs.
My request: Is there a better way to do this, then what I am doing now?
In my real case I need to create 40 attribute/value pairs (from a very wide Oracle table), and I am concerned about the growing complexity of the IF THEN ELSE structure.
Thanks in advance, DaveThis message has been edited. Last edited by: David Briars,
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
November 14, 2012, 06:07 PM
Waz
How about this.
TABLE FILE SYSCOLUM
PRINT NAME USAGE
WHERE TBNAME EQ 'EMPDATA'
ON TABLE SAVE AS COLNAMES
END
-RUN
-SET &COLS = &LINES ;
FILEDEF SVATTRS DISK svattrs.ftm
TABLE FILE EMPDATA
PRINT
-REPEAT COLLOOP FOR &CNTR FROM 1 TO &COLS ;
-READ COLNAMES NOCLOSE &ColName.A66. &ColFmt.A8.
-SET &ColName = TRUNCATE(&ColName) ;
-SET &ColFmt = TRUNCATE(&ColFmt) ;
COMPUTE COL&CNTR/I2 = PUTDDREC('SVATTRS',7,LJUST(&ColName.LENGTH,'&ColName','A25') | FPRINT(&ColName,'&ColFmt','A25') ,50,COL&CNTR) ;
-COLLOOP
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS WRITEOUT
END
-RUN
-REPEAT ENDLOOP1 &COLS TIMES
-READ SVATTRS &ATTR.A25. &VAL.A25.
-TYPE Attribute: &ATTR Value: &VAL
-ENDLOOP1
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!
November 15, 2012, 10:43 AM
David Briars
Thank you Waz!
This is the 'quantum leap' improvement to my version, that I was hoping for.
The use SYSCOLUM reduces hardcoding.
Function PUTDDREC nicely stacks the attribute-value pairs.
And FPRINT greatly supports my need to reformat the non-text fields.