TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD FORMAT ALPHA
END
TABLE FILE CAR
SUM RCOST 
BY COUNTRY
WHERE COUNTRY NE (HOLD);
END

quote:

Reference: FORMAT SQL_SCRIPT
Description: Creates an SQL subquery file or a file of data values with a corresponding synonym.

When used in a request against a relational data source, the HOLD FORMAT SQL_SCRIPT command generates the SQL SELECT statement needed to execute the current query. It then stores it in the application folder as a file with a .sql extension, along with the Master and Access File pair that describes the SQL answer set.

When used in a request against any other type of data source, the HOLD FORMAT SQL_SCRIPT command executes the current query and stores the retrieved values in the application folder as a sequential file with a .ftm extension, along with the Master File that describes the retrieved data.

Use: You can use the output from HOLD FORMAT SQL_SCRIPT as the target file for the DB_INFILE function. For information about the DB_INFILE function, see the Using Functions manual

TABLE FILE customerOrders
PRINT DST.customerNumber
WHERE
orderDate GE '&startDate'
AND orderDate LE '&endDate'
ON TABLE HOLD AS HSUBSEL1 FORMAT SQL_SCRIPT
END
-RUN

TABLE FILE customer
PRINT DST.customerNumber

WHERE NOT DB_INFILE (HSUBSEL1, customerNumber, customerNumber)
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

-SET &ECHO=ALL;
SET MSG=ON
SET TRACEOFF = ALL
SET TRACEON = SQLTRANS
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
-RUN

TABLE FILE WFR80_WRD_DIM_CUSTOMER
PRINT
GENDER
WHERE GENDER EQ 'Female'

ON TABLE HOLD AS BASEAPP/SQL_FEMALE2 FORMAT SQL_SCRIPT
END
-RUN

TABLE FILE WFR80_WRD_DIM_CUSTOMER
SUM
LAST_NAME
BY LAST_NAME NOPRINT

WHERE DB_INFILE(SQL_FEMALE2, GENDER, GENDER)

HEADING
"Female Customers"

ON TABLE HOLD
END
-RUN

SELECT
T1."Last_Name",
MAX(T1."Last_Name")
FROM
WF_REPOS.WRD_DIM_CUSTOMER T1
WHERE
(T1."Gender" IN 
( 
    SELECT
    (T1."Gender") AS E01
    FROM
    WF_REPOS.WRD_DIM_CUSTOMER T1
    WHERE
    (T1."Gender" = 'Female'))
)
GROUP BY
T1."Last_Name"
ORDER BY
T1."Last_Name";

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

SET SQLENGINE = SQLMSS (or SQLORA, etc.)

SQL

DECLARE 
 @startDate DATE,
 @endDate DATE

SELECT DISTINCT
 customerNumber
FROM
 customer
WHERE NOT EXISTS
 (
 SELECT DISTINCT
 customerNumber
 FROM
 customerOrders
 WHERE
 orderDate >= @startDate
 AND orderDate <= @endDate
 );

TABLE
 ON TABLE HOLD AS xxxx
END

• Make sure that you SET SHORTPATH = SQL, or NULLs will be omitted from the results, preventing you from filtering out the left join set.
  
• Put your NOT EXISTS condition in the JOIN conditions, not in the WHERE clause of the TABLE request, or WF will turn your join into an INNER join.
  
• Make sure that your left join does not return multiple rows for a single row in your source table, or you'll end up with duplicate results.
  
• Finally, exclude the LEFT JOIN results using WHERE {SOME_LEFT_JOINED_FIELD} IS MISSING;

SET SHORTPATH = SQL
SET ALL       = PASS
-*
JOIN CLEAR *
JOIN CUSTOMERNUMBER IN CUSTOMER TO ALL CUSTOMERNUMBER IN CUSTOMERORDERS AS J1
-*
DEFINE FILE CUSTOMER
 EXCLUDE_CUSTOMER/A1 MISSING ON = IF (CUSTOMERORDERS.CUSTOMERNUMBER IS MISSING) THEN MISSING ELSE
                                  IF (CUSTOMERORDERS.ORDERDATE FROM '&STARTDATE' TO '&ENDDATE') THEN 'Y' ELSE 'N';
END
-*
TABLE FILE CUSTOMER
 BY CUSTOMER.CUSTOMERNUMBER
 WHERE (EXCLUDE_CUSTOMER IS MISSING)
    OR (EXCLUDE_CUSTOMER EQ 'N');
END

quote:

SQL Direct Passthru (DPT) or Automatic Passthru (APT)

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