[SOLVED] SQL Translator on Hold Files, is it possible to Hold the Results?

Focal Point

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4227090986

January 23, 2018, 10:51 AM

Mike Williams

[SOLVED] SQL Translator on Hold Files, is it possible to Hold the Results?

Spotted an earlier thread that involved using the SQL translator to insert a row into a hold file, I was curious, is it possible to query hold files (or master files) using the translator then save the results into another file?

Code example below:

 
ENGINE INT CACHE SET ON
TABLE FILE CAR
	SUM SALES
	BY	CAR
ON TABLE HOLD AS HOLD_CAR
END

TABLE FILE CAR
	SUM RETAIL_COST
	BY	CAR
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE HOLD AS HOLD_RETAIL
END

SQL
SELECT a.CAR
     ,a.SALES
     ,b.RETAIL_COST
FROM HOLD_CAR a
     INNER JOIN HOLD_RETAIL b
          ON (a.CAR = b.CAR);
END
-RUN

And the result would display:

CAR SALES RETAIL_COST
JAGUAR 12000 22,369
JENSEN 0 17,850
TRIUMPH 0 5,100

This message has been edited. Last edited by: Mike Williams,

January 23, 2018, 12:16 PM

Francis Mariani

ENGINE INT CACHE SET ON
TABLE FILE CAR
	SUM SALES
	BY	CAR
ON TABLE HOLD AS HOLD_CAR
END

TABLE FILE CAR
	SUM RETAIL_COST
	BY	CAR
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE HOLD AS HOLD_RETAIL
END

SQL
SELECT a.CAR
     ,a.SALES
     ,b.RETAIL_COST
FROM HOLD_CAR a
     INNER JOIN HOLD_RETAIL b
          ON (a.CAR = b.CAR);
TABLE
ON TABLE HOLD AS H001
END
-RUN

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