Hi, How do I filter out an entire record based on a particular value?
For example
TABLE FILE CAR
PRINT SEATS
BY CAR
END
I want to eliminate CAR's where SEATS EQ to 2. So Alpha Romeo has values of 4, 2 and 2. I don't want to see the entire record of Alpha Romeo, because one of the values is a 2. The end result should only show, AUDI, BMW, DATSUN, JENSEN, PEUGEOT and TOYOTA. Thank you.This message has been edited. Last edited by: nickz,
October 21, 2019, 04:21 PM
pav
Hello, Hope this will be useful
SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
TABLE FILE CAR
PRINT
SEATS
BY CAR
ON TABLE HOLD AS TEST_1 FORMAT ALPHA
END
-RUN
TABLE FILE CAR
PRINT
SEATS AS 'FIL_COL'
BY CAR AS 'CAR_1'
WHERE SEATS EQ 2
ON TABLE HOLD AS TEST_2 FORMAT ALPHA
END
-RUN
JOIN CAR IN TEST_1 TO CAR_1 IN TEST_2 AS J2
TABLE FILE TEST_1
PRINT
CAR
SEATS
WHERE FIL_COL NE 2
END
-RUN
Thank You.
October 21, 2019, 04:52 PM
Hallway
This works with a single request:
DEFINE FILE CAR
SEATFLAG/I1=IF SEATS EQ 2 THEN 1 ELSE 0;
END
TABLE FILE CAR
PRINT SEATS
SUM.SEATFLAG WITHIN CAR NOPRINT
BY CAR
WHERE TOTAL SEATFLAG EQ 0;
END
Hallway
Prod: 8202M1
Test: 8202M4
Repository:
OS:
Outputs:
October 21, 2019, 04:55 PM
Hallway
Or if you just want a list of the cars without showing the seat numbers:
DEFINE FILE CAR
SEATFLAG/I1=IF SEATS EQ 2 THEN 1 ELSE 0;
END
TABLE FILE CAR
SUM SEATFLAG WITHIN CAR NOPRINT
BY CAR
WHERE TOTAL SEATFLAG EQ 0;
END
Hallway
Prod: 8202M1
Test: 8202M4
Repository:
OS:
Outputs:
October 22, 2019, 05:56 AM
Martin vK
It is true as Hallway indicates it can be done in one table request. However the WHERE TOTAL has the disadvantage of being performed by WebFocus, so all data is first transferred from the database to the Webfocus server. No problem with small datasets, but if you are dealing with large dataset it could be better to do in 2 passes:
TABLE FILE CAR
PRINT DST.CAR
WHERE SEATS EQ 2;
ON TABLE HOLD AS HLDSEATS2 FORMAT ALPHA
END
-RUN
TABLE FILE CAR
PRINT
CAR
SEATS
WHERE NOT CAR IN FILE HLDSEATS2;
END
-RUN
Or even better with subquery to leave everything to database:
TABLE FILE CAR
PRINT DST.CAR
WHERE SEATS EQ 2;
ON TABLE HOLD AS SQLSEATS2 FORMAT SQL_SCRIPT
END
-RUN
TABLE FILE CAR
PRINT
CAR
SEATS
WHERE NOT DB_INFILE(SQLSEATS2, CAR, CAR);
END
-RUN
-EXIT
Martin.
WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
October 22, 2019, 06:20 AM
Wep5622
Or, if you don't need the output right away:
MATCH
FILE CAR
PRINT SEATS
BY CAR
RUN
FILE CAR
BY CAR
WHERE SEATS EQ 2;
AFTER MATCH HOLD AS WHATEVER
OLD-NOT-NEW
END
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
October 22, 2019, 07:34 AM
nickz
Thank you everyone.
October 22, 2019, 09:08 AM
pav
October 22, 2019, 09:52 AM
David Briars
quote:
...Or even better with subquery to leave everything to database:...FORMAT SQL_SCRIPT...