Im trying to get a count of distinct License_no and just cant seem to get it to print out right. It always comes out 0 on the subfoot ""Total Count:
Can anyone tell what im doing wrong?

Thanks.

TABLE FILE SQLOUT
PRINT 
     LEGAL_NAME AS 'Legal,Name'
     Business_Address AS 'Business,Address'
     Mailing_Address AS 'Mailing,Address'
     Phone_Number AS 'Phone,Number'
     LC_Return_Date/HMDYY AS 'Indicator,Effective,Date'
BY License_No AS 'License,Number'
BY Tax_Period AS 'Tax,Period'
BY TRADE_NAME AS 'Trade,Name'
     
ON License_No RECAP 
COUNT1/I9 = IF License_No EQ LAST License_No THEN 0 ELSE 1;
ON License_No SUBFOOT
" "
ON TABLE SUBFOOT
"Total Count: <TOT.COUNT1 "
WHERE Tax_Period EQ '&Tax_Period.(FIND TAX_PERIOD IN DBO_PERIOD).Tax_Period.';
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &FMT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *  

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

WF 7.1.7- Windows XP

COUNT1 Needs to be a COMPUTE

Erney,

I never do a RECAP that way; Use a multi-verb process. Also, ON TABLE will cause problems in later releases(so I have found), so I use DUMMY. Also, I changed your COUNT logic:

  
DEFINE FILE SQLOUT
  DUMMY/A1 = ' ';
END
TABLE FILE SQLOUT
SUM
   COMPUTE COUNT1/I9 = IF License_No EQ LAST License_No THEN COUNT1+1 ELSE 1; NOPRINT
  BY DUMMY NOPRINT
PRINT 
     LEGAL_NAME AS 'Legal,Name'
     Business_Address AS 'Business,Address'
     Mailing_Address AS 'Mailing,Address'
     Phone_Number AS 'Phone,Number'
     LC_Return_Date/HMDYY AS 'Indicator,Effective,Date'

BY DUMMY NOPRINT
BY License_No AS 'License,Number'
BY Tax_Period AS 'Tax,Period'
BY TRADE_NAME AS 'Trade,Name'
     
ON License_No SUBFOOT
" "
ON DUMMY SUBFOOT
"Total Count: <TOT.COUNT1 "

WHERE Tax_Period EQ '&Tax_Period.(FIND TAX_PERIOD IN DBO_PERIOD).Tax_Period.';

ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &FMT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *  

hth

Erney,

Sorry!! Had the COMPUTE backwards!!!!

  
DEFINE FILE SQLOUT
  DUMMY/A1 = ' ';
END
TABLE FILE SQLOUT
SUM
   COMPUTE COUNT1/I9 = IF License_No EQ LAST License_No THEN COUNT1 ELSE COUNT1+1; NOPRINT
  BY DUMMY NOPRINT
PRINT 
     LEGAL_NAME AS 'Legal,Name'
     Business_Address AS 'Business,Address'
     Mailing_Address AS 'Mailing,Address'
     Phone_Number AS 'Phone,Number'
     LC_Return_Date/HMDYY AS 'Indicator,Effective,Date'

BY DUMMY NOPRINT
BY License_No AS 'License,Number'
BY Tax_Period AS 'Tax,Period'
BY TRADE_NAME AS 'Trade,Name'
     
ON License_No SUBFOOT
" "
ON DUMMY SUBFOOT
"Total Count: <TOT.COUNT1 "

WHERE Tax_Period EQ '&Tax_Period.(FIND TAX_PERIOD IN DBO_PERIOD).Tax_Period.';

ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &FMT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE 

Interesting im getting a count of 2 back, even though there are a few hundred results.

Oop nevermind, I got it

thanks a bunch again.

Hi Erney,

You are welcome, I guess? Did you have a typo or something else?

Tom

 DEFINE FILE SQLOUT
DUMMY/A1 = ' ';
END
TABLE FILE SQLOUT
SUM
     COMPUTE COUNT1/I9 = IF License_No EQ LAST License_No THEN COUNT1 ELSE COUNT1 + 1; NOPRINT
BY DUMMY NOPRINT
[B]BY License_No AS 'License,Number'[/B]
PRINT
     Tax_Period AS 'Tax,Period'
     TRADE_NAME AS 'Trade,Name'
     LEGAL_NAME AS 'Legal,Name'
     Business_Address AS 'Business,Address'
     Mailing_Address AS 'Mailing,Address'
     Phone_Number AS 'Phone,Number'
     LC_Return_Date/HMDYY AS 'Indicator,Effective,Date'
BY DUMMY NOPRINT
BY License_No AS 'License,Number'

ON DUMMY SUBFOOT
"Total Count: <COUNT1 "

ON License_No SUBFOOT
" "
WHERE Tax_Period EQ '&Tax_Period.(FIND TAX_PERIOD IN DBO_PERIOD).Tax_Period.';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &FMT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE * 

Had to put
BY License_No AS 'License,Number'

after the BY DUMMY NOPRINT

AHHH!!! Well Done... AND, you changed TOT.COUNT1 to COUNT1..