Hi,

This Works-
SQL
SELECT CAR,MODEL FROM CAR ;
TABLE FILE SQLOUT
PRINT *
END

This DOES NOT!
SELECT CAR,MODEL
FROM
( SELECT * FROM CAR )
;
TABLE FILE SQLOUT
PRINT *
END

it gives me this error message
(FOC012) THE WORD 'FILE' OR THE FILENAME APPEARS TWICE
BYPASSING TO END OF COMMAND
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLD10

Thanks for your help and suggestions.

This message has been edited. Last edited by: Kerry,

The first is correct syntax for SQL Translation.
The second is partial syntax for SQL Passthrough. They are not the same. The SQL Passthrough needs the information statements on the RDBMS database you are connecting to, which you don't supply. Just as well -- the CAR file is FOCUS, not RDBMS anyway.

So, I would have to save the output from the first SQL statement(inner query) to a hold file and then perform another SQL (outer query) against the hold file?

thanks!

If you were really doing SQL Passthrough to an RDBMS, you can use a subselect. Not in SQL Translation.

In our trials, this techinque of coding faux SQL pass-through statements against non-RDMBS data sources has very limited functionality. Forget going as far as sub-selects, it does not support most of the basic native SQL functions either.

However when using "SQL DB2" or "SQL SQLMSS" against a real RDBMS, the query can be as complex as anywhere else.

Sandeep Mamidenna

I am not connecting to any RDBMS(not using SQL passthru).

How can I capture the output from a simple SQL translator to a hold file so that i can create a report using that hold file. Is that possible?

SQL
SELECT COUNTRY,CAR FROM CAR ;
END

SQL
SELECT CAR,MODEL
FROM
( SELECT * FROM CAR )
;
TABLE ON TABLE HOLD AS H001
END

TABLE FILE H001
PRINT *
END

works without any errors.

The "TABLE FILE SQLOUT" syntax may be reserved for DBMS SQL only

Hi Francis.

I had the exact same thing at my end and it wasn't working this morning.So, I thought something was wrong.Hmm.

It works now.Thanks!!

It works even without the HOLD

SQL
SELECT CAR, MODEL
FROM 
(SELECT * FROM CAR WHERE COUNTRY = 'ENGLAND')
;
ON TABLE PCHOLD FORMAT HTML
END

Simply,

  
SQL
SELECT SSALES, COUNTRY
FROM
(SELECT SUM(SALES) SSALES, COUNTRY, CAR FROM CAR WHERE SALES > 0 GROUP BY COUNTRY, CAR)
;
GROUP BY SSALES DESC
END