simple query

We have a lot of tables and sometimes I need to see the data in the fields. Usually I run a simple program like this:

TABLE FILE MYTABLE
PRINT *
WHERE READLIMIT EQ '1000';
END

But, I believe that this only returns the first 1,000 rows in the table. What I really would like to see is some records where every field is populated, so that I can see what kind of data is in each field simply by scrolling to the right. So, is there a way that I can add a WHERE statement to return only records where all fields are populated? Something like this perhaps:

WHERE * IS NOT MISSING or
WHERE * NE MISSING

Windows version 768

Posts: 215 | Registered: March 16, 2006

Jim_at_LM

Gold member

posted

Hide Post

DEFINE FILE MYTABLE
FLD1_SEL /I1 [MISSING ON] = [IF MISSING THEN 0 ELSE 1] ;
- OR -
FLD1_SEL1 /I1 = IF FLD1 EQ [0 , or " " for alpha] THEN 1 ELSE 0 ;
:
SEL/I1 IF FLD_SEL1 EQ 1 AND FLD_SEL2 EQ 1 ... THEN 1 ELSE 0 ;

:

TABLE
:
WHERE SEL EQ 1

WebFOCUS 7.6.11, WINDOWS, HTML, PDF, EXCEL

Posts: 77 | Location: Baltimore | Registered: May 31, 2006

Ignored post by Jim_at_LM posted

Show Post

Francis Mariani

Expert

posted

Hide Post

There is no shortcut to selecting rows where each column has a value. I can't think of any programming language that could offer that.

What you need to do is have a WHERE statement for each of the columns:

WHERE CAR IS NOT MISSING
WHERE MODEL IS NOT MISSING
WHERE COUNTRY IS NOT MISSING OR COUNTRY IS-NOT ''
...

AND is implied in multiple WHERE statements, you could code the statments as:

WHERE CAR IS NOT MISSING
AND MODEL IS NOT MISSING
AND (COUNTRY IS NOT MISSING OR COUNTRY IS-NOT '')
...

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

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

Lusheng

Platinum Member

posted

Hide Post

You have to test each field.

Posts: 118 | Location: Omaha, NE | Registered: June 12, 2003

Ignored post by Lusheng posted

Show Post

Mark1

Platinum Member

posted

Hide Post

OK, thanks. I will condition on each field.

Windows version 768

Posts: 215 | Registered: March 16, 2006

Ignored post by Mark1 posted

Show Post

hammo1j

Master

posted

Hide Post

Quick and dirty is SUM * since this applies the max prefix to non numeric data. Not so good for numerics tho!

Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo

Posts: 888 | Location: Airstrip One | Registered: October 06, 2006

Ignored post by hammo1j posted

Show Post

Leah

Virtuoso

posted

Hide Post