Is there a way to do use FOCUS code instead of SQL to convert columns to rows, as in this SQL example:
SQL
SELECT 'DEALER_COST' AS COLNAME, DEALER_COST AS COLVALUE FROM CAR
WHERE COUNTRY = 'ENGLAND'
UNION
SELECT 'RETAIL_COST' AS COLNAME, RETAIL_COST AS COLVALUE FROM CAR
WHERE COUNTRY = 'ENGLAND'
UNION
SELECT 'SEATS' AS COLNAME, SEATS AS COLVALUE FROM CAR
WHERE COUNTRY = 'ENGLAND'
UNION
SELECT 'SALES' AS COLNAME, SALES AS COLVALUE FROM CAR
WHERE COUNTRY = 'ENGLAND';
TABLE ON TABLE HOLD AS H001 FORMAT ALPHA
END
Thanks,
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
September 18, 2008, 05:28 PM
j.gross
Match file can do it.
It's essential to pre-summarize the data, so there is one row per set of sort-key values. (That will allow us to treat the Value as a sort-key.)
Use Define (against the summed data) to create name and value vars with consistent formats, for all the dependent fields of interest.
Then issue a multi-leg Match file, one OLD-OR-NEW merge per UNION.
MATCH
FILE HOLD
COUNT ENTRIES NOPRINT
BY {the "real" keys, if applicable}
BY Name1 AS COLNAME
BY Value1 AS COLVALUE
RUN
...
- Jack Gross WF through 8.1.05
September 18, 2008, 05:35 PM
Francis Mariani
Oh, well - it sounds like it's a lot easier with SQL.
Thanks,
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
September 18, 2008, 06:05 PM
j.gross
De gustibus ... this looks no more complicated to me:
DEFINE FILE CAR
NAME1/A12 ='DEALER_COST '; VALUE1/D8.1= DEALER_COST ;
NAME2/A12 ='RETAIL_COST '; VALUE2/D8.1= RETAIL_COST ;
NAME3/A12 ='SALES '; VALUE3/D8.1= SALES ;
NAME4/A12 ='LENGTH '; VALUE4/D8.1= LENGTH ;
NAME5/A12 ='WIDTH '; VALUE5/D8.1= WIDTH ;
NAME6/A12 ='HEIGHT '; VALUE6/D8.1= HEIGHT ;
NAME7/A12 ='WEIGHT '; VALUE7/D8.1= WEIGHT ;
NAME8/A12 ='WHEELBASE '; VALUE8/D8.1= WHEELBASE ;
END
MATCH FILE CAR
BY COUNTRY BY CAR BY MODEL BY NAME1 AS COLNAME BY VALUE1 AS COLVALUE
RUN
FILE CAR
BY COUNTRY BY CAR BY MODEL BY NAME2 AS COLNAME BY VALUE2 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CAR
BY COUNTRY BY CAR BY MODEL BY NAME3 AS COLNAME BY VALUE3 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CAR
BY COUNTRY BY CAR BY MODEL BY NAME4 AS COLNAME BY VALUE4 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CAR
BY COUNTRY BY CAR BY MODEL BY NAME5 AS COLNAME BY VALUE5 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CAR
BY COUNTRY BY CAR BY MODEL BY NAME6 AS COLNAME BY VALUE6 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CAR
BY COUNTRY BY CAR BY MODEL BY NAME7 AS COLNAME BY VALUE7 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CAR
BY COUNTRY BY CAR BY MODEL BY NAME8 AS COLNAME BY VALUE8 AS COLVALUE
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD PRINT COLVALUE
BY COUNTRY BY CAR BY MODEL BY COLNAME
END
- Jack Gross WF through 8.1.05
October 01, 2008, 02:25 AM
Piipster
Hi Francis.
Hope all is well back in Canada.
How are you going to use the HOLD file? I am assuming that since you are putting the column titles in the rows you don't need a master file. My rows don't come out in the same order as yours but that should be fixable.
I used the following code:
TABLE FILE CAR
PRINT DEALER_COST
OVER RETAIL_COST
OVER SEATS
OVER SALES
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE HOLD AS H001 FORMAT LOTUS
-*ON TABLE HOLD AS H001 FORMAT WP
ON TABLE SET PAGE NOLEAD
END
...and... if you want to use constants instead of the field names you could DEFINE or COMPUTE those values and use AS '' for all the fields.This message has been edited. Last edited by: Piipster,
ttfn, kp
Access to most releases from R52x, on multiple platforms.
October 01, 2008, 03:16 PM
hammo1j
JG - nice technique for using MATCH to UNION rather than JOIN - I'm sure that I will find a use.
You could go the McGuiver route
JOIN ONE WITH LENGTH IN CAR TO ALL ONE IN MCGUYVER AS J1
DEFINE FILE CAR NAME/A12 = IF MCVAL EQ 1 THEN 'DEALER_COST ' ELSE IF MCVAL EQ 2 THEN 'RETAIL_COST ' ... VALUE/D8.1= IF MCVAL EQ 1 THEN DEALER_COST ELSE IF MCVAL EQ 2 THEN RETAIL_COST ... END TABLE FILE CAR PRINT NAME VALUE BY COUNTRY BY CAR BY MODEL BY MCVAL NOPRINT END
Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2 Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
October 01, 2008, 03:23 PM
hammo1j
This should be built into wf/SQL just like mcguyver with my MULTIPLY FILE ... AT ... TIMES syntax
Its a shame that OVER does not hold values in the same way as the report.
Could RECTYPEs be adapted in some way?
select over (x1, x2 ... xn) AS namecol valuecol
could be the syntax - will drop ANSI commitee an email.
Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2 Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
October 01, 2008, 06:21 PM
Waz
Here is another way to do it.
TABLE FILE CAR
PRINT COMPUTE NAME1/A20='DEALER_COST' ;
DEALER_COST/I9
COMPUTE NAME2/A20='RETAIL_COST' ;
RETAIL_COST/I9
COMPUTE NAME3/A20='SEATS' ;
SEATS/I9
COMPUTE NAME4/A20='SALES' ;
SALES/I9
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE SAVE AS TMP_FILE FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN
EX -LINES 5 EDAPUT MASTER,TMP_FILE ,CV,FILE
FILENAME=TMP_FILE , SUFFIX=FIX,$
SEGNAME=TMP_FILE ,OCCURS=4, $
FIELD=COLNAME ,ALIAS= ,A20 ,A20 ,$
FIELD=COLVALUE,ALIAS= ,I9 ,A9 ,$
-RUN
TABLE FILE TMP_FILE
PRINT *
END