[Download-List] DOWNLOAD AND BY.EXP problems
Phil Walker
phil.walker at infocusp.co.nz
Fri Apr 2 00:14:01 EST 2004
I tried to do this using LIST, SQL and have now tried DOWNLOAD. Alas, I am
having trouble producing some output for the following query in that the
data appears to get duplicated.
I have a paragraph that says the following:
SELECT LFX_PICK_DETAIL WITH COMPLETED_DATE = "<<FROM_DATE>>" AND WITH
STORER_CODE = "<<STORER_CODE>>"
SSELECT LFX_PICK_DETAIL BY STORER_CODE BY COMPLETED_DATE BY CUSTOMER_NAME BY
SALES_ORDER_NUMBER BY STOCK_CODE BY.EXP LOT_NUMBER REQUIRE.SELECT TO 1
DOWNLOAD _
LFX_PICK_DETAIL _
BY.EXP LOT_NUMBER _
LITERAL '0' _
BREAK.ON STORER_CODE _
BREAK.ON COMPLETED_DATE _
BREAK.ON CUSTOMER_NAME _
BREAK.ON SALES_ORDER_NUMBER _
BREAK.ON STOCK_CODE _
BREAK.ON LOT_NUMBER _
BATCH_QTY _
FOOTING.ON FINAL LITERAL '7' LITERAL '*' LITERAL '*' LITERAL '*' LITERAL '*'
LITERAL '*' LITERAL '*' TOTAL BATCH_QTY _
FOOTING.ON STORER_CODE LITERAL '6' STORER_CODE LITERAL '*' LITERAL '*'
LITERAL '*' LITERAL '*' LITERAL '*' TOTAL BATCH_QTY _
FOOTING.ON COMPLETED_DATE LITERAL '5' STORER_CODE COMPLETED_DATE LITERAL '*'
LITERAL '*' LITERAL '*' LITERAL '*' TOTAL BATCH_QTY _
FOOTING.ON CUSTOMER_NAME LITERAL '4' STORER_CODE COMPLETED_DATE
CUSTOMER_NAME LITERAL '*' LITERAL '*' LITERAL '*' TOTAL BATCH_QTY _
FOOTING.ON SALES_ORDER_NUMBER LITERAL '3' STORER_CODE COMPLETED_DATE
CUSTOMER_NAME SALES_ORDER_NUMBER LITERAL '*' LITERAL '*' TOTAL BATCH_QTY _
FOOTING.ON STOCK_CODE LITERAL '2' STORER_CODE COMPLETED_DATE CUSTOMER_NAME
SALES_ORDER_NUMBER STOCK_CODE LITERAL '*' TOTAL BATCH_QTY _
FOOTING.ON LOT_NUMBER LITERAL '1' STORER_CODE COMPLETED_DATE CUSTOMER_NAME
SALES_ORDER_NUMBER STOCK_CODE LOT_NUMBER TOTAL BATCH_QTY _
HEADING _
LITERAL 'Level' _
LITERAL 'Client' _
LITERAL 'Completed Date' _
LITERAL 'Customer Name' _
LITERAL 'Order Number' _
LITERAL 'Product' _
LITERAL 'Lot Number' _
LITERAL 'Quantity' _
DET.SUP _
FILE LFX_EXPORT <<CURRENT_EXTRACT_FILENAME>> _
OVERWRITING _
FROM 1
The data is
GET.LIST PVW.1
SELECT LFX_PICK_DETAIL BY.EXP LOT_NUMBER
LIST LFX_PICK_DETAIL LOT_NUMBER BATCH_QTY 05:00:05pm 02 Apr 2004 PAGE 1
ORDER_LINE_CODE..... LOT_NUMBER.......... BATCH_QTY
0249085500529#1 0117764 128
0249085500529#1 0118489 128
LOT_NUMBER and BATCH_QTY are in the same ASSOCIATION BATCH_DATA. They are
both multi-valued I-TYPEs WHICH call a SUBROUTINE which includes named
column as the logic is quite complex. The subroutine returns an array from
the named column cache.
This products the following output – see duplicates with Level 0.
Level
Client
Completed Date
Customer Name
Order Number
Product
Lot Number
Quantity
0
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
117764
128
0
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
118489
128
0
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
117764
128
0
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
118489
128
1
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
118489
512
2
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
*
512
3
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
*
*
512
4
2490
31/03/2004
AUCKLAND GENERAL OFFICE
*
*
*
512
5
2490
31/03/2004
*
*
*
*
512
6
2490
*
*
*
*
*
512
7
*
*
*
*
*
*
512
With DET.SUP produces
Level
Client
Completed Date
Customer Name
Order Number
Product
Lot Number
Quantity
1
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
118489
512
2
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
*
512
3
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
*
*
512
4
2490
31/03/2004
AUCKLAND GENERAL OFFICE
*
*
*
512
5
2490
31/03/2004
*
*
*
*
512
6
2490
*
*
*
*
*
512
7
*
*
*
*
*
*
512
Without the BY.EXP it produces
Level
Client
Completed Date
Customer Name
Order Number
Product
Lot Number
Quantity
1
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
117764
256
2
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
*
256
3
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
*
*
256
4
2490
31/03/2004
AUCKLAND GENERAL OFFICE
*
*
*
256
5
2490
31/03/2004
*
*
*
*
256
6
2490
*
*
*
*
*
256
7
*
*
*
*
*
*
256
Whereas I want it to produce
Level
Client
Completed Date
Customer Name
Order Number
Product
Lot Number
Quantity
1
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
117764
128
1
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
118489
128
2
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
RB1921
*
256
3
2490
31/03/2004
AUCKLAND GENERAL OFFICE
85500529
*
*
256
4
2490
31/03/2004
AUCKLAND GENERAL OFFICE
*
*
*
256
5
2490
31/03/2004
*
*
*
*
256
6
2490
*
*
*
*
*
256
7
*
*
*
*
*
*
256
The actual data using LIST is
GET.LIST PVW.1
SELECT LFX_PICK_DETAIL BY.EXP LOT_NUMBER
LIST LFX_PICK_DETAIL LOT_NUMBER BATCH_QTY 05:00:05pm 02 Apr 2004 PAGE 1
ORDER_LINE_CODE..... LOT_NUMBER.......... BATCH_QTY
0249085500529#1 0117764 128
0249085500529#1 0118489 128
Something screwy is happening, I have tried various combination and nothing
works. I would use LIST but you cannot get the output in anything like
resembing the desired output. I have tried SQL and can get something like it
but only at a group level, not the hierarchy.
I am running DOWNLOAD versions 7.00
Phil Walker
+64 21 336294
phil.walker at infocusp.co.nz
infocusp limited
\\ PO Box 77032, Auckland New Zealand \ www.infocusp.co.nz
<http://www.infocusp.co.nz/>
DISCLAIMER: This electronic message together with any attachments is
confidential. If you are not the intended recipient, do not copy, disclose
or use the contents in any way. Please also advise us by return e-mail that
you have received the message and then please destroy. infocusp limited is
not responsible for any changes made to this message and / or any
attachments after sending by infocusp limited. We use virus scanning
software but exclude all liability for viruses or anything similar in this
email or any attachment
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.cedarville.edu/pipermail/download-list/attachments/20040402/2f4da644/attachment.html>
More information about the download-list
mailing list