× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Then again, perhaps what you really want is a view.

In STRSQL, set naming as *SQL then:

CREATE VIEW yourlib.yourview AS
SELECT IDDOCD AS INV_DATE, IDORD# AS ORD_NUM,
IDORDT AS ORD_TYPE, IDPRLC AS PROD_FAMILY,
IDPR$C AS PRICE_CODE,IDCOM#,
MAX(IDPRT#) AS ITEM_REF,IARCC4 AS PROD_TYPE,
IARC11 AS PROD_CLASS,IACMQC AS CUM_CODE,
SUM(IDSHP#) AS NUM_UNITS,
SUM(IDSHP# * IDCSU$) AS TOT_COST,
SUM(IDSHP# * IDNTU$) AS TOT_SLS
FROM OEIND94 INNER JOIN ICPRT1 ON IDPRT# = IAPRT#
WHERE IDCOM#='001' AND
IDDOCD >= 20130401 AND
IDDOCD <= 20130430? AND
IARCC4='FIN'
GROUP BY IDDOCD, IDORD#,IDORDT,IDPRLC,IDPR$C,
IDCOM#,IARCC4,IARC11,IACMQC ORDER BY? IDDOCD,IDORD#,
IDORDT,IDPRLC,IDCOM#;

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan Kimmel
Sent: Wednesday, May 29, 2013 4:46 PM
To: Midrange Systems Technical Discussion
Subject: RE: RUNSQLSTM

In STRSQL, set naming as *SQL then:


CREATE PROCEDURE yourlib.yourproc() LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE C1 CURSOR FOR
SELECT IDDOCD AS INV_DATE, IDORD# AS ORD_NUM,
IDORDT AS ORD_TYPE, IDPRLC AS PROD_FAMILY,
IDPR$C AS PRICE_CODE,IDCOM#,
MAX(IDPRT#) AS ITEM_REF,IARCC4 AS PROD_TYPE,
IARC11 AS PROD_CLASS,IACMQC AS CUM_CODE,
SUM(IDSHP#) AS NUM_UNITS,
SUM(IDSHP# * IDCSU$) AS TOT_COST,
SUM(IDSHP# * IDNTU$) AS TOT_SLS
FROM OEIND94 INNER JOIN ICPRT1 ON IDPRT# = IAPRT#
WHERE IDCOM#='001' AND
IDDOCD >= 20130401 AND
IDDOCD <= 20130430? AND
IARCC4='FIN'
GROUP BY IDDOCD, IDORD#,IDORDT,IDPRLC,IDPR$C,
IDCOM#,IARCC4,IARC11,IACMQC ORDER BY? IDDOCD,IDORD#,
IDORDT,IDPRLC,IDCOM#;
OPEN C1;
RETURN;
END

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Crystal Reports
Sent: Wednesday, May 29, 2013 3:54 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: RUNSQLSTM

 
OK then how do you make this into a stored procedure? SO it can be run from Crystal Reports.
 
I have loaded an sql statements into a text member and this does not work as RUNSQLSTM, yet this does work under STRSQL.
?
SELECT? IDDOCD AS INV_DATE, IDORD# AS ORD_NUM,????????
IDORDT? AS ORD_TYPE, IDPRLC AS PROD_FAMILY,???????????
IDPR$C AS PRICE_CODE,IDCOM#,??????????????????????????
MAX(IDPRT#) AS ITEM_REF,IARCC4 AS PROD_TYPE,??????????
IARC11 AS PROD_CLASS,IACMQC AS CUM_CODE,??????????????
SUM(IDSHP#) AS NUM_UNITS,?????????????????????????????
SUM(IDSHP# * IDCSU$) AS TOT_COST,?????????????????????
SUM(IDSHP# * IDNTU$) AS TOT_SLS???????????????????????
FROM?? OEIND94 INNER JOIN ICPRT1?? ON IDPRT# = IAPRT#?
WHERE? IDCOM#='001' AND???????????????????????????????
IDDOCD >= 20130401??? AND?????????????????????????????
IDDOCD <= 20130430? AND???????????????????????????????
IARCC4='FIN'??????????????????????????????????????????
GROUP BY IDDOCD, IDORD#,IDORDT,IDPRLC,IDPR$C,?????????
IDCOM#,IARCC4,IARC11,IACMQC ORDER BY? IDDOCD,IDORD#,??
IDORDT,IDPRLC,IDCOM#??????????????????????????????????
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.