|
midrange.com
Ah, doesn't matter anyway. This is a syntax thing: how do you identify a
temporary colunm as part of your selection criteria.
I guess that is part of the question. Can I do this? I have another program
where I used a substringed field in the order by statement, and that seemed
to work OK. Below is the entire program. Some SQL is * out because that
was the original. I started pulling fields and files to get the bare
minimum to work. Thanks for all the suggestions!
I know I can get this to work without SQL by creating a work file, then
reading it in the sequence I need, it just seemed like this should work.
<===================================================>
Terri Harteau
****************
"There's no point in being grown up if you can't be childish sometimes."
- Dr. Who
****************
0001.00 H OPTION(*NODEBUGIO)
0002.00 F*
0003.00 F* Name: INV269 - Sanitaire Weight Report
0004.00 F* Author: Terri Harteau
0005.00 F* Date: 31 May 2002
0006.00 F*
0007.00 F* Purpose: This program will print a list of all standard
Sanitaire
0008.00 F* items (from the price book) with their weight. The
report
0009.00 F* is sorted & breaks on item class, but it is not
printed.
0010.00 F*
0011.00 F* 31May02 Original program. trh
0012.00 F*
0013.00
F**************************************************************************
0014.00 FINV269O O E PRINTER OFLIND(*IN40)
0015.00
F**************************************************************************
0016.00 * Status Data Structure
0017.00 D STATDS SDS
0018.00 D USER 254 263
0019.00 D*
0020.00 D OverFlowP S * inz(%addr (*in40))
0021.00 D OverFlow S N based(OverFlowP)
0022.00 D*
0023.00 D MorESP S 3 INZ('YES')
0024.00 D First S 3 INZ('YES')
0025.00 D*
0026.00 D CmpCls S 2 INZ('99')
0027.00 D*
0028.00 D SQLRec DS
0029.00 D PRKEY 21 INZ(' ')
0030.00 D IPROD 15 INZ(' ')
0031.00 D IDESC 30 INZ(' ')
0032.00 D ICLAS 2 INZ(' ')
0033.00 D IPFDV 4 INZ(' ')
0034.00 D IWGHT 7 3 INZ(0)
0035.00 D IECWGT 15 5 INZ(0)
0036.00 D ITEM 15 INZ(' ')
0037.00 D CUST 6 INZ(' ')
0038.00 C*
0039.00 C* *** Mainline ***
0040.00 C*
0041.00 C******************************
0042.00 C/EXEC SQL DECLARE A CURSOR FOR
0043.00 C+ SELECT PRKEY,IDESC,
0044.00 C+ SUBSTR(PRKEY,1,15) as ITEM,
0045.00 C+ SUBSTR(PRKEY,16,6) as cust,PMETH
0046.00 C+ FROM ESP, IIM
0047.00 C+ WHERE PMETH = 'A'
0048.00 C+ AND :CUST IN (' ', '770440')
0049.00 C+ AND :ITEM = IPROD
0050.00 C+ ORDER BY prkey
0051.00 C/END-EXEC
0052.00 C/EXEC SQL
0053.00 C+ OPEN A
0054.00 C/END-EXEC
0055.00 C******************************
0056.00 C*/EXEC SQL DECLARE A CURSOR FOR
0057.00 C*+ SELECT PRKEY,IPROD,IDESC,ICLAS,IPFDV,IWGHT,IECWGT,
0058.00 C*+ SUBSTR(PRKEY,1,15) as ITEM,
0059.00 C*+ SUBSTR(PRKEY,16,6) as cust#
0060.00 C*+ FROM ESP,IIM,IMX
0061.00 C*+ WHERE :ITEM = IPROD
0062.00 C*+ AND :ITEM = MXPROD
0063.00 C*+ AND :CUST# = '770440'
0064.00 C*+ AND PMETH = 'A'
0065.00 C*+ AND IPFDV <> 'LABR'
0066.00 C*+ ORDER BY ICLAS,IPROD
0067.00 C*/END-EXEC
0068.00 C*/EXEC SQL
0069.00 C*+ OPEN A
0070.00 C*/END-EXEC
0071.00 C******************************
0072.00 C MorESP DOUEQ 'NO '
0073.00 C*
0074.00 C/EXEC SQL
0075.00 C+ FETCH NEXT FROM A INTO :PRKEY, :idesc, :item, :cust
0076.00 C/END-EXEC
0077.00 C*/EXEC SQL
0078.00 C*+ FETCH NEXT FROM A INTO :PRKEY, :IPROD, :IDESC, :ICLAS, :IPFDV,
0079.00 C*+ :IWGHT, :IECWGT, :ITEM, :CUST#
0080.00 C*/END-EXEC
0081.00 C*
0082.00 C IF SQLCOD <> 0
0083.00 C EVAL MorESP = 'NO '
0084.00 C ELSE
0085.00 C*
0086.00 C IF First = 'YES'
0087.00 C EVAL CMPCLS = ICLAS
0088.00 C EVAL First = 'NO '
0089.00 C WRITE INV269O1
OF
0090.00 C ENDIF
0091.00 C*
0092.00 C* Check for item class break.
0093.00 C IF ICLAS <> CmpCls
0094.00 C IF OverFlow
0095.00 C WRITE INV269O1
OF
0096.00 C EVAL OverFlow = *off
0097.00 C ENDIF
0098.00 C WRITE BLKLIN
OF
0099.00 C EVAL CmpCls = ICLAS
0100.00 C ENDIF
0101.00 C*
0102.00 C IF IECWGT = 0
0103.00 C* EVAL PrtWgt = IWGHT
0104.00 C* ELSE
0105.00 C* EVAL PrtWgt = IECWGT
0106.00 C ENDIF
0107.00 C*
0108.00 C IF OverFlow
0109.00 C WRITE INV269O1
OF
0110.00 C EVAL OverFlow = *off
0111.00 C ENDIF
0112.00 C WRITE INV269O2
OF
0113.00 C*
0114.00 C ENDIF
0115.00 C ENDDO
0116.00 C*
0117.00 C IF OverFlow
0118.00 C WRITE INV269O1
OF
0119.00 C EVAL OverFlow = *off
0120.00 C ENDIF
0121.00 C WRITE INV269O9
OF
0122.00 C*
0123.00 C EVAL *INLR = *ON
0124.00 C/EXEC SQL
0125.00 C+ CLOSE A
0126.00 C/END-EXEC
0127.00 C*
As an Amazon Associate we earn from qualifying purchases.
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.
