So I've got this simple code that creates a classic one field lookup hold file.

quote:

-* List of Survey IDs, Names, Start Date and End Date
TABLE FILE SURVEY
BY SURVEYNAME
WHERE ( DATESTART LE DT(&YYMD) ) AND ( DATEEND GE DT(&YYMD) );
WHERE SURVEYNAME CONTAINS 'PRJ - ';
WHERE NOT SURVEY_ID IN ( 57,59 );
ON TABLE HOLD AS projlist_hold FORMAT ALPHA
END
-RUN

The SURVEY table is keyed by the Survey_ID but all I'm interested in is a list of SURVEYNAMEs that are active and start with PRJ. I write this to an ALPHA file. Just the Survey name, seems to work just fine.

Now I'd like to use it to filter on the fact table (SURVEYDATA) which has a gazillion records and I only want the ones that are in the lookup table above.

I do something like this;

quote:

-* Usage/FACT table
TABLE FILE SURVEYDATA
BY SURVEYNAME
WHERE SURVEYNAME IN FILE projlist_hold;
END
-RUN

I get nothing. So I slap a little SQL TRACE ON IT like this to see what's going on.

quote:

SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=STMTRACE//CLIENT
SET TRACESTAMP=OFF
SET EMGSRV=ON
SET TRACEWRAP = 80
SET XRETRIEVAL = OFF

I expect to see something like this;

0 NUMBER OF RECORDS IN TABLE= 7 LINES= 7
SELECT T1."SurveyName", MAX(T1."SurveyName")
FROM SurveyData T1
WHERE
(T1."SurveyName" IN('PRJ - Mom and Pop's - Transponder Rollout Phase 1 ',
'PRJ - Big Cheese Refresh 2011 ',
'PRJ - Mom and Pop's Toggler Replaces Cobra ',
'PRJ - Nuts and Bolts R US Vision Specialty Drawer Fix ',
'PRJ - Were Screwed Thread Gauge Install ',
'PRJ - Washer World Reset 2011 ',
'PRJ - Nuts and Bolts R US Quick Tag Location '))
GROUP BY T1."SurveyName" ORDER BY T1."SurveyName";
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0


But instead I get this;

0 NUMBER OF RECORDS IN TABLE= 7 LINES= 7
SELECT T1."SurveyName", MAX(T1."SurveyName")
FROM SurveyData T1
WHERE
(T1."SurveyName" IN('000046PRJ - Mom and Pop's - Transponder Rollout Phase 1 ',
'000033PRJ - Big Cheese Refresh 2011 ',
'000035PRJ - Mom and Pop's Toggler Replaces Cobra ',
'000040PRJ - Nuts and Bolts R US Vision Specialty Drawer Fix ',
'000035PRJ - Were Screwed Thread Gauge Install ',
'000026PRJ - Washer World Reset 2011 ',
'000033PRJ - Nuts and Bolts R US Quick Tag Location '))
GROUP BY T1."SurveyName" ORDER BY T1."SurveyName";
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

That's funny, I didn't invite the Survey_ID along, but yet it comes along for the ride.

Has anyone seen this before. Why is the SURVEY_ID added to the front of the SURVEYNAME? It doesn't show up when I output to HTML.

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

Perhaps the DBMS adds an extra, default column because you do not specify a column that is sorted by the BY column. I would change the code to

TABLE FILE SURVEY
SUM SURVEYNAME
BY SURVEYNAME
WHERE ( DATESTART LE DT(&YYMD) ) AND ( DATEEND GE DT(&YYMD) );
WHERE SURVEYNAME LIKE 'PRJ - %';
WHERE NOT SURVEY_ID IN ( 57,59 );
ON TABLE HOLD AS projlist_hold FORMAT ALPHA
END

Then, I'd do this to verify that the HOLD file is OK:

TABLE FILE projlist
PRINT *
END

(By the way, your two snippets of code are the same...)

Hard to say without seeing the SQL trace for the first step that creates the lookup list. If your query cannot be optimized, more data may be coming back to WebFOCUS than necessary (e.g., Survey ID). Try SAVE instead of HOLD. If that doesn't work or you need HOLD, then try adding SET HOLDLIST = PRINTONLY.

I fixed the two snippets in my original post.

Here is the complete fex, I'm running it from the console. I added an extra hold file to isolate the filtering from the final hold file, thought it might make a difference. This one has me completely baffled.

quote:

SET HOLDLIST = PRINTONLY

TABLE FILE SURVEY
PRINT
SURVEYNAME
DATEEND
DATESTART
WHERE NOT SURVEY_ID IN ( 57,59 );
WHERE SURVEYNAME LIKE 'PRJ - %';
WHERE ( DATESTART LE DT(&YYMD) ) AND ( DATEEND GE DT(&YYMD) );
ON TABLE HOLD AS SURVEYHOLD FORMAT ALPHA
END
-RUN

TABLE FILE SURVEYHOLD
BY SURVEYNAME
ON TABLE HOLD AS projlist_hold FORMAT ALPHA
END
-RUN

SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=STMTRACE//CLIENT
SET TRACESTAMP=OFF
SET EMGSRV=ON
SET TRACEWRAP = 80
SET XRETRIEVAL = OFF


TABLE FILE SURVEYDATA
SUM SURVEYNAME
BY SURVEY_ID
WHERE SURVEYNAME IN FILE projlist_hold;
END
-RUN

Here is the output (actual customer names have been changed)


0 NUMBER OF RECORDS IN TABLE= 7 LINES= 7
0 NUMBER OF RECORDS IN TABLE= 7 LINES= 7
SELECT T1."Survey_ID", MAX(T1."SurveyName")
FROM OUT_SurveyData T1

WHERE (T1."SurveyName" IN('000046PRJ - Dog and Pony - Transponder Rollout Phase 1 ',
'000033PRJ - Mutt and Jeff Refresh 2011 ',
'000035PRJ - Lees Famous Recipe Toggler Replaces Cobra ',
'000040PRJ - Connector Vision Specialty Drawer Fix ',
'000035PRJ - Mutt and Jeff Thread Gauge Install ',
'000026PRJ - Mutt and Jeff Reset 2011 ',
'000033PRJ - Proposed Quick Tag Location '))
GROUP BY T1."Survey_ID" ORDER BY T1."Survey_ID";
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

As Dan mentioned, place the trace before the first TABLE FILE.

This is because the field is a variable length field (something like A80V). The length of the field is included in the holdfile ...
Change your code as follows:

TABLE FILE SURVEY
PRINT SURVEYNAME/A80
WHERE NOT SURVEY_ID IN ( 57,59 );
WHERE SURVEYNAME LIKE 'PRJ - %';
WHERE ( DATESTART LE DT(&YYMD) ) AND ( DATEEND GE DT(&YYMD) );
ON TABLE HOLD AS projlist_hold FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN

Change the A80 to whatever length the field can maximally hold.
Hope this helps ...

Nice, that works. And the field is A255V.

Thank you.

Now, why didn't I see that? Good catch GamP