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

This:

WHERE EMCLAS IN :WHERECLAUSE


Isn't valid.

If there's always only one value, try
WHERE EMCLAS IN ( :WHERECLAUSE )

or better yet
WHERE EMCLAS = :WHERECLAUSE


if there's a small finite number of elements:

WHERE EMCLAS IN ( :val1, :val2, :val3)

but you might need to pass the null indicators unless there's some
value you know won't ever appear (or just repeat one of the values
you do want)

WHERE EMCLAS IN ( :val1 val1null, :val2 val2null, :val3 val3null)

If the list can be large, you'll probably want to use dynamic SQL as
there isn't an easy way to have a static statement with a dynamic IN
predicate. (It could be done with a user defined table function that
took a string and parsed it into a results set.)

HTH,
Charles




On Mon, Apr 27, 2009 at 6:16 PM, Scott Schollenberger
<schollenbergers@xxxxxxxxxxxxxxxxxx> wrote:

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.






--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.