Good morning,

I am trying to write a query that will compare results to a sub-query. In sql I would say where pidm NOT IN (subquery). How would I do this in web focus. Would the subquery be in the same procedure or would I create a separate procedure and have it look at an external file list? Is this subquery possible in WebFOCUS?

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

You could do what you call the sub-query in the same procedure, hold the results FORMAT FOCUS INDEX keyfield. Then join the results of the primary query to it.

Or if you are using the results of the subquery to screen the results of the primary query, you can use the WHERE IN FILE syntax to use the results from the subquery to select from the primary table and then join.

For better or worse, there are lots of methods depending on exactly what you want to do.

Maybe you should post your SQL.

(Attached is the code that I'm trying to write in focus. Look at the Where pidm_key NOT IN the the clause in parenthesis. I know it's not right...I don't know FOCUS language so I'm guessing.

TABLE FILE INFOHOLD
SUM
PIDM_KEY NOPRINT
ID
LAST_NAME
FIRST_NAME
MIDDLE_INITIAL
STYP_CODE
TOTAL_BILLING_HOURS
COMPUTE Amountpaid/D12.2 = RPRATRM_ACCEPT_AMT + TBRACCD_AMOUNT + TBRMEMO_AMOUNT;
AS 'Amount,Paid'
TBRACCD_BALANCE AS 'Account,Balance'
BY LAST_NAME NOPRINT
BY FIRST_NAME NOPRINT
BY MIDDLE_INITIAL NOPRINT
WHERE PIDM_KEY NOT IN (TABLE FILE AS_ADMISSIONS_APPLICANT
SUM
PIDM_KEY
WHERE TERM_CODE_KEY EQ '200809';
WHERE APDC_CODE1 EQ 'AC' OR 'PC';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS NWSTHOLD FORMAT FOCUS INDEX 'PIDM_KEY'
ON TABLE SET HTMLCSS ON)
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,,,,).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END

WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML

One of the things you can do is first run the sub-query and hold the output (format focus index on the join field), then to a left_outer join from the main file to the hold file, and use a where a field from the hold file is missing.

RSquared...I just tried your suggestion but only got back 0 persons. I know I'm close....could you look at the code?

JOIN
LEFT_OUTER STINFOHOLD.SEG01.PIDM_KEY IN STINFOHOLD TO UNIQUE
NWSTHOLD.SEG01.PIDM_KEY IN NWSTHOLD AS J2
END
TABLE FILE STINFOHOLD
SUM
ID
LAST_NAME
FIRST_NAME
MIDDLE_INITIAL
STYP_CODE
TOTAL_BILLING_HOURS
BY PIDM_KEY
HEADING
""
FOOTING
""
WHERE PIDM_KEY EQ MISSING;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,

quote:

WHERE PIDM_KEY EQ MISSING

You probably need to say WHERE NWSTHOLD.PIDM_KEY EQ MISSING;

Just using the PIDM_KEY would find the first field in the joined structure which would be the one in stinfohold. If that field value is null (missing), then the join wouldn't work at all. So if you want to test the field in the cross-referenced file, use the filename qualifier.

or TAG your joins and use TAGNAME.FIELDNAME. Same thing basically.

I'm trying to get all the pidms from STINFOHOLD that are not in NWSTHOLD. I think GinnyJakes described it a little when he said "Or if you are using the results of the subquery to screen the results of the primary query, you can use the WHERE IN FILE syntax to use the results from the subquery to select from the primary table and then join."

So do I use a WHERE NOT IN FILE? I used the WHERE NWSTHOLD.PIDM_KEY EQ MISSING, but I got 0 returns. The code is below:

TABLE FILE STINFOHOLD
SUM
ID
LAST_NAME
FIRST_NAME
MIDDLE_INITIAL
STYP_CODE
TOTAL_BILLING_HOURS
BY PIDM_KEY
HEADING
""
FOOTING
""
WHERE NWSTHOLD.PIDM_KEY EQ MISSING;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,

Just saw your comment Darin I looked at my join syntax and it looked tagged already.

JOIN
LEFT_OUTER STINFOHOLD.SEG01.PIDM_KEY IN STINFOHOLD TO UNIQUE
NWSTHOLD.SEG01.PIDM_KEY IN NWSTHOLD AS J2
END

So I changed the Where clause to:
WHERE NWSTHOLD.SEG01.PIDM_KEY EQ MISSING;

but I still got zero.

I'm not a 'he'.

Ginny...I am so sorry.....'girl'....won't happen again

That's ok. I've just never run across a guy in my whole life named Ginny (aka Virginia).

Try using another field, not the field that you join on.

Ginny...it was a typo....I could have sworn my finger hit the 's' before the 'h'.

Clarification about tags:

JOIN LEFT_OUTER
STINFOHOLD.SEG01.PIDM_KEY IN STINFOHOLD
TO UNIQUE
NWSTHOLD.SEG01.PIDM_KEY IN NWSTHOLD
AS J2
END

You coded a join-name (J2), but no tag-name. They are two separate parts of the syntax:

JOIN field1 IN file1 TO field2 IN file2 TAG tag2 AS joinid

That allows you to use either "joinid" (no dot) or "tagvalue." as an identifying prefix.

The tag is directly associated with the file (newer syntax allows tagging both files, which can be usefull when joining a file to itself),
while the joinid labels the join statement
(although the effect is similar -- the joinid used as a fieldname modifier refers to the fields made accessible by the join, viz. those in the second file).

Inserting
?FF STINFOHOLD
after the JOIN will display the filenames and aliases, with sufficient qualification to make the reference unambiguous.

- - - - -

Back to selection. "WHERE NWSTHOLD.SEG01.PIDM_KEY EQ MISSING;"

How is PIDM_KEY declared in the NWSTHOLD master? If it doesn't have MISSING=ON, the result will never be MISSING (if no matching row exists, it will have a default values of zero (or blank).

But, assuming STINFOHOLD.SEG01.PIDM_KEY always has a non-zero (or non-blank) value, you can use
WHERE STINFOHOLD.SEG01.PIDM_KEY NE NWSTHOLD.SEG01.PIDM_KEY ;
to select the instances for which the JOIN UNIQUE navigation came up emptyhanded.

I think I may have confused everyone....let me start again.

The unique
identifier in both files is the pidm_key. I want to pull all the pidms from the
main file that do not exist in the cross ref. file. Sorry 'not in' suggested that
there is some value that i want to exclude. I should have said NOT EXIST. Any new ideas for me now.

OMG!!! I got something back. I used the code suggest by j.g.: STINFOHOLD.SEG01.PIDM_KEY NE NWSTHOLD.SEG01.PIDM_KEY

Thank you so much Jack Gross.

Kitten,
There are basically 2 ways to do this.
1.
MATCH FILE MAIN
[here you use the fields you want to extract from the MAIN file and the screening conditions]
BY PIDM_KEY
RUN
FILE XREF
....
BY PIDM_KEY
AFTER MATCH HOLD OLD-NOT-NEW
END

2.
SET ALL=PASS
JOIN PIDM_KEY IN MAIN TO ALL PIDM_KEY IN XREF AS J
TABLE FILE MAIN
[here some impossible condition on the the XREF file]


You will get all the records in MAIN that do not have a corresponding in XREF

Thanks for the suggestion Danny...I will also try the match file.

kitten,

We had a similar issue, and discovered that the NOT goes with the WHERE and not with IN FILE. Using your code as an example, the syntax should be:

WHERE NOT PIDM_KEY IN FILE filename

Where the filename is the name used in a FILEDEF to hold the values you want it include/exclude in your query.

Below is an example using CAR file. The first thing I did was create a procedure that dynamically creates a list of Countries containing the letter 'E', and putting them in a ALPHA formatted HOLD file called CTRYLIST. Then, I run a second procedure, using the WHERE NOT syntax against the CTRYLIST hold file.

Sorry to come so late to the dance. Hope this is still relevent.

Rich

APP FI CTRYLIST DISK baseapp/CTRYLIST.ftm
-RUN

TABLE FILE CAR
SUM DEALER_COST NOPRINT
BY COUNTRY AS ' '
WHERE COUNTRY CONTAINS 'E'
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CTRYLIST FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
ENDSTYLE
END

TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL

WHERE NOT COUNTRY IN FILE CTRYLIST;

ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
ENDSTYLE
END

Thanks for this Rich! It certainly helps me

Jinx.