RE: RUNSQLSTM -- MIDRANGE-L

I have a request for "Crystal Reports".

Once you have learned from the list I think it would be a good review for you and for future learners if you to put the steps for making this small project happen into an email and send in to the list.

I would also be reading the current thread concerning SQL performance and indexes.

Also if you plan on turning others users loose using Crystal Reports I would definitely think about creating views for them to use instead of running directly over the files. Helps to stop three different users from getting three different results when creating reports, etc.

Regards,
Bill Hopkins

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

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.