I am passing the values: schema, month and year thru from another focexe file. I did a type to see what value is being passed and does pass the correct values. However, when I do a recordlimit, readlimit, end exit, nothing displays when an individual schema is selected, I get a 0 tables 0 records. However, the "all" Schema does display a table.

-*********************************
-SET &MASTER_SCHEMA = &SCHEMA;
-SET &MASTER_MONTH = &MONTH;
-SET &MASTER_YEAR = &YEAR;
-*
-SET &strSCHEMA= IF &MASTER_SCHEMA EQ 'ALL' THEN ''
- ELSE 'WHERE USERNAME EQ ''&SCHEMA''';
-SET &strMONTH = IF &MONTH EQ 'ALL' THEN ''
- ELSE 'WHERE LOG_MONTH EQ ''&MONTH''';
-SET &strYEAR= IF &MASTER_YEAR EQ 'ALL' THEN ''
- ELSE 'WHERE LOG_YEAR EQ ''&YEAR''';
-*
-SET &ALLSCHEMA_SWITCH = IF &MASTER_SCHEMA EQ 'ALL' THEN 1 ELSE 0;
-SET &ALLMONTH_SWITCH = IF &MASTER_MONTH EQ 'ALL' THEN 1 ELSE 0;
-SET &ALLYEAR_SWITCH = IF &MASTER_YEAR EQ 'ALL' THEN 1 ELSE 0;
-*
-*
-****************************************
-*Values Below: 1 equals On; 0 equals off
-****************************************
-TYPE All schema: &ALLSCHEMA_SWITCH
-TYPE All Months: &ALLMONTH_SWITCH
-TYPE All Years: &ALLYEAR_SWITCH

-******Check values********
-TYPE All Schema: &strSCHEMA.EVAL
-*
-TYPE All Month: &strMONTH.EVAL
-*
-TYPE All Year: &strYEAR.EVAL
-**************************
-*
-*-******************************
-* From main table
-******************************


TABLE FILE EFIP_USER_AUDIT_METRICS

COUNT DST.USERNAME
-*
BY LOG_YEAR
BY LOG_MONTH
-*******
-* EVALUEAT THE SCHEMA VALUE
&strSCHEMA.EVAL
-* EVALUATE THE MONTH VALUE
&strMONTH.EVAL
-* EVALUATE THE YEAR VALUE
&strYEAR.EVAL
-*******
-* DONE EVALUATE
-*
WHERE RECORDLIMIT EQ 100
WHERE READLIMIT EQ 100
END
-EXIT

ON TABLE HOLD AS USR_DATA
END

-**********************

any thoughts?

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

quote:

SET &strSCHEMA= IF &MASTER_SCHEMA EQ 'ALL' THEN ''
- ELSE 'WHERE USERNAME EQ ''&SCHEMA''';
-SET &strMONTH = IF &MONTH EQ 'ALL' THEN ''
- ELSE 'WHERE LOG_MONTH EQ ''&MONTH''';

How about doing the &SCHEMA.EVAL here to get the actual value of the &SCHEMA variable?

I tried doing that well, but no such luck

READLIMIT and RECORDLIMIT do two different things.

READLIMIT reads physical records and if you don't get a hit in the first 100, you will get zero rows.

RECORDLIMIT passes the first 100 to match the screening conditions.

You have both in your request. To get rows, I'd remove the READLIMIT. You don't need both.

I removed the readlimit and I changed the recordlimit to a 1000, then 10,000 just to be sure...haha....but still no such luck.

If you -SET ECHO=ON; and SET XRETRIEVAL=OFF, you can look at your generated code and see if it looks correct. You might want to post it here as well.

Okay, from adding that code in, I got an output result of:

...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
(BEFORE DISTINCT TESTS)

What was the generated code, the TABLE request with the evaluated WHERE tests?

Also, are you sure that you have matching records for your screening conditions?

I figured it out...I needed a LJUST for the username field...was not comparing the exact username, rather one with spaces....Thanks for your help!!