HOW to create experession, which creates HOLD file containing 2 x rows (vertical split/cut and horizontal merge/cat)
TABLE TEST: ID | B | C ------------- 1 | 2 | 3 2 | 22 | 33
Analogy from SQL: SELECT B FROM TEST UNION SELECT C FROM TEST
RESULT: 2 22 3 33
Can someone write example expresiong with FILE CAR (without using SQL)?
February 26, 2004, 03:47 PM
The best way I've found is to hold your queries in FORMAT FOCUS, then union them with USE. Unfortunately, this doesn't pass any aggregation to the RDBMS. If you're concerned about that, then embedded SQL would be a better method.
The major caveat is that your hold files need to have EXACTLY the same format. If they don't, then you'll get funky characters in your output. The way I find out the hold file format is to use "? HOLD filename".
Here's an example. The data doesn't make any sense, but at least it illustrates the point!
SET HOLDLIST=PRINTONLY SET ASNAMES=ON TABLE FILE CAR SUM COMPUTE MYDATA/A16 = CAR; DEALER_COST BY CAR NOPRINT ON TABLE HOLD AS FIRST FORMAT FOCUS END TABLE FILE CAR SUM COMPUTE MYDATA/A16 = COUNTRY; DEALER_COST BY COUNTRY NOPRINT ON TABLE HOLD AS SECOND FORMAT FOCUS END -* View Source to make sure these HOLD files match field names & formats -* NOTE: CAR & COUNTRY do not have the same format, but both will fit into A16 ? HOLD FIRST ? HOLD SECOND -* Here's the UNION: USE FIRST.FOC AS FIRST SECOND.FOC AS FIRST END -* And finally... output the UNION TABLE FILE FIRST PRINT DEALER_COST BY MYDATA ENDThis message has been edited. Last edited by: <Mabel>,
February 26, 2004, 04:22 PM
pranas, i agree with mhuber, and wanted to add just another way, if the master files of the 2 hold files aren't exactly alike. TABLE FILE FIRST PRINT whatever WHERE whatver you might need ON TABLE HOLD AS THIRD MORE FILE SECOND WHERE whatver you might need again END ..Using MORE lets you glue together dissimilar files. and they can be just foctemps (.FTM) and dont have to be .FOC.