Embedded curosr gives "No Records" where interactive SELECT shows records

I am working on a program that I decided to use embedded SQL in to
handle the grouping functions in the database.

I created a view call PASUPPER that groups certain employee information
and returns a count.



CREATE VIEW PASUPPER (EMENT,EMCLAS,EMFTPT,EMSEX,EMRACE,EMPCOUNT) AS


SELECT EMENT,EMCLAS,EMFTPT,EMSEX,EMRACE,COUNT(*) FROM XPDB.EMPMST

JOIN XPDB.HRDATA ON EMENT=HDENT AND EMSSN=HDSSN

GROUP BY EMENT,EMCLAS,EMFTPT,EMSEX,EMRACE




The program declares a cursor that selects some of these fields and has
a host variable containing the variable portion of the WHERE clause.



C/EXEC SQL

C+ DECLARE C1 CURSOR FOR

C+ SELECT EMFTPT,EMSEX,EMRACE,EMPCOUNT

C+ FROM PASUPPER

C+ WHERE EMCLAS IN :WHERECLAUSE

C+ ORDER BY EMFTPT,EMSEX DESC,EMRACE

C/END-EXEC



C/EXEC SQL

C+ OPEN C1

C/END-EXEC



The OPEN cursor returns an SQL State of 00000.



Next I do a FETCH.



C*

C* // Fetch rows

C*

C/EXEC SQL

C+ FETCH C1 INTO :DS_EMFTPT, :DS_EMSEX, :DS_EMRACE, :NumVal

C/END-EXEC



The first FETCH always returns a SQL State of 02000.

Yet if I run a comparable interactive SELECT statement.



SELECT EMFTPT,EMSEX,EMRACE,EMPCOUNT

FROM PASUPPER

WHERE EMCLAS IN ('AID')

ORDER BY EMFTPT,EMSEX DESC,EMRACE



I get two records returned.



TIME STATUS SEX (M/F) RACE EMPCOUNT

F M 05 3

F F 05 1

******** End of data ********



So my questions is why does the FETCH fail to return these records.



Any help would be appreciated.





________________________________

Scott A. Schollenberger

Vice President, Research & Development

Harris School Solutions







A division of Harris Computer Systems



Phone: (610) 239-9988 ext. 305

Fax: (610) 239-9995
Email: sschollenberger@xxxxxxxxxxxxxxxxxx
<mailto:sschollenberger@xxxxxxxxxxxxxxxxxx>
2011 Renaissance Blvd., Suite 100
King of Prussia, PA 19406

This message and any attachments are confidential to the ordinary user
of the e-mail address to which it was addressed and may also be
privileged. If you are not the addressee you may not copy, forward,
disclose or use any part of the message or its attachments and if you
have received this message in error, please notify the sender
immediately by return e-mail and delete it from your system. The sender
does not accept liability for any errors or omissions in the context of
this message that arise as a result of Internet transmission. Any
opinions contained in this message are those of the author and are not
given or endorsed by the Harris company or office through which this
message is sent unless otherwise clearly indicated in this message and
the authority of the author to so bind the Harris entity referred to is
duly verified.