Hi WebFOCUS gurus,

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)?

Thanks,
Pranas

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!

Regards,
Michael


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
END

This message has been edited. Last edited by: <Mabel>,

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.

Thanks for sharing your knowledge!

Pranas