Concatenating Different Records

NewP

New Member
Hi I am new to this. Can you please help me:

I have a database and I need to "concatenate records" based on fields in a table.

Table: Order
Field: Order-Number
Field: Previous-Order-Number
Field: Create-Date

I need to be able to find the first "Order ".
Then be able to find all the other orders that are related via the "Previous-Order-Number" field and put them into a variable delimited by ",".

For example:

Record 1:
- Order-Number is: 123456
- Previous-Order-Number is: 999999

Record 2:
- Order-Number is 999999
- Previous-Order-Number is 111111

Record 3:
- Order-Number is: 111111
- Previous-Order-Number is: 777777

and so on and so on.

I want to put it in a variable, so it will have display/output like this: (Order Number + "|" + Create-date, N) i.e. "123456|01/01/22, 999999|03/03/22, 111111|04/04/22"

Please let me know if you need more information

Thank you
 
Last edited:

Rob Fitzpatrick

ProgressTalk.com Sponsor
I need to be able to find the first "Order ".
Then be able to find all the other orders that are related via the "Previous-Order-Number" field and put them into a variable delimited by ",".
I want to put it in a variable, so it will have display/output like this: (Order Number + "|" + Create-date, N) i.e. "123456|01/01/22, 999999|03/03/22, 111111|04/04/22"

Is that part of an algorithm that someone gave you to use, or a solution you have chosen to accomplish some task? You will have a problem if a client has had 2000 orders and now you're trying to write 2000 * 15 bytes into a character variable. This concatenation approach will be slow and doesn't scale.

Moreover, what is the point of this long string? Are you going to iterate over it and do something with that data? If so, why not just iterate over the records in the table and then do whatever it is you want to do, without building the long string. Or are you going to pass these orders to some other process or system? Or write them to a file?

It might be easier to provide help if we knew your actual business requirement.

P.S.:
Table: Order
Field: Order-Number
Field: Previous-Order-Number
Field: Create-Date
It may not be your doing, but this is bad table design. Each order should be independent of any others. "Previous-Order-Number" is not an attribute of an order in the real world; it shouldn't be in the table either. If you want to filter and order records in a particular way in your code, then you should have an index on the table that lets you do that efficiently, for each kind of ordering or filtering you intend to implement.
 

NewP

New Member
Thanks Rob,

Like you said, not my design. I agree with you.

I am looking for the code that is the right combination of FOR EACH, FIND or IF Statement that will concatenate the data in the way I explained.

I understand your concern but there is only a maximum of of 5 or 6 orders not 2k. No higher than single digit.

Thank you
 
Hi I am new to this. Can you please help me:

I have a database and I need to "concatenate records" based on fields in a table.

Table: Order
Field: Order-Number
Field: Previous-Order-Number
Field: Create-Date

I need to be able to find the first "Order ".
Then be able to find all the other orders that are related via the "Previous-Order-Number" field and put them into a variable delimited by ",".

For example:

Record 1:
- Order-Number is: 123456
- Previous-Order-Number is: 999999

Record 2:
- Order-Number is 999999
- Previous-Order-Number is 111111

Record 3:
- Order-Number is: 111111
- Previous-Order-Number is: 777777

and so on and so on.

I want to put it in a variable, so it will have display/output like this: (Order Number + "|" + Create-date, N) i.e. "123456|01/01/22, 999999|03/03/22, 111111|04/04/22"

Please let me know if you need more information

Thank you

Code:
/* not tested, something like this ... */
ordernbr = "123456".
find Order no-lock where Order-Number = ordernbr no-error.
do while available Order:
  /*** code your concatenate stuff here ***/
  ordernbr = Previous-Order-Number.
  find Order no-lock where Order-Number = ordernbr no-error.
end.
 

NewP

New Member
Thank you Marco,

You mean like this?


Code:
DEFINE BUFFER order2 FOR order.

DEFINE VARIABLE iOrderDetail as INTEGER INITIAL 0.

DEFINE VARIABLE cCombinedOrder as CHAR INITIAL "".



FOR EACH order NO-LOCK:

    ASSIGN

 iOrderDetail = Order.Number.

FIND order2 NO-LOCK WHERE Order.Number = iOrderDetail no-error.

DO WHILE AVAILABLE(order2):

    ASSIGN

    cCombinedOrder  = cCombinedOrder + "," + string(iOrderDetail)

    iOrderDetail    = Previous-Order-Number.

    FIND order2 NO-LOCK WHERE Order.Number = iOrderDetail NO-ERROR.

END.

I tried this and it doesn't work? IS there something I am missing?

Also, what do you mean with "Infinite loop" How do I exit it, if I encounter it with this problem?
Thank you!!
 
Last edited:
HI
You have a bug In this line "FIND order2 NO-LOCK WHERE Order.Number = iOrderDetail NO-ERROR."
You should have "FIND order2 NO-LOCK WHERE Order2.Number = iOrderDetail NO-ERROR. "
Patrice
 

NewP

New Member
Thank you, that hass given me an output.

There is an issue though, it is picking up ALL orders, regardless of if they are related. i.,e Order.Number and Previous Order Number.

It is just picking up Order.Number + Order Number + Order Number.

Ideally it needs to:

Finds Order Number -> find all related Order Numbers (Based on Previous Order Number field0, then when there are no more related Order Numbers exit the procedure.

THEN pick up the unrelated NEXT Order Number and continue the loop.

I hope that makes sense.

Thank you Patrice
 
Hi,
You should store/record the cCombinedOrder and set it to "" after each "Do while "

<code>
DEFINE BUFFER order2 FOR order.

DEFINE VARIABLE iOrderDetail as INTEGER INITIAL 0.

DEFINE VARIABLE cCombinedOrder as CHAR INITIAL "".

define temp-table tt No-undo
field first-order as INTEGER
field CombinedOrder as CHARACTER
index i1 first-order .


FOR EACH order NO-LOCK:


ASSIGN

iOrderDetail = Order.Number.

FIND order2 NO-LOCK WHERE Order2.Number = iOrderDetail no-error.

DO WHILE AVAILABLE(order2):

ASSIGN

cCombinedOrder = cCombinedOrder + "," + string(iOrderDetail)

iOrderDetail = Previous-Order-Number.

FIND order2 NO-LOCK WHERE Order2.Number = iOrderDetail NO-ERROR.

END.
CREATE tt.
assign tt.first-order = Order.Number
tt.CombinedOrder = cCombinedOrder
cCombinedOrder = '' .
END.
for each tt :
/*DO what you want*/

END.
</code>
 
Last edited:
Hi
You could have a look at source code attached .
I am not able to insert it in the post as code .

Patrice
 

Attachments

  • test-pgm.p
    936 bytes · Views: 3

NewP

New Member
Thank you Patrice - the temp table is something I haven't considered. I am reading the documentation on this right now.

I have a question though, what do you mean with

Code:
for each tt :
/*DO what you want*/

END.

What goes in there? The display / output?
Thank you
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
The issue with your data model, and with this requirement/approach of constructing these order chains, is that some orders are treated as special, as if they are parent records, and the previous orders are like their children. You will end up having orders in multiple partial chains.

Consider a set of records with the following order numbers for customer A: 10 -> 9 -> 5 -> 3 -> 1
i.e. order 10's previous order is 9; order 9's previous order is 5, etc.
(Of course I am assuming there is more than one customer, each with its own orders. Perhaps this is a valid assumption? Or perhaps not and this is just an academic exercise. That isn't clear. But given "there is only a maximum of of 5 or 6 orders not 2k", it seems like this is a homework assignment.)

As you iterate over the order table (by order number perhaps?) with a for each, you will construct the following chains of orders:
1
3 -> 1
5 -> 3 -> 1
9 -> 5 -> 3 -> 1
10 -> 9 -> 5 -> 3 -> 1

One of these might be deemed complete, at least at a particular point in time, while the others could be deemed partial/duplicate; they are subsets of the complete one. Do you need to cull the list, eliminating the partial chains and keeping only the complete one(s)?
 
Rob is correct.
It could be interresting for you to use the Temp-table to avoid this side effect.
have a look at the source code attached ,
I did not test it ,
but it could be a clue / starting point
 

Attachments

  • test-pgm-2.p
    1.9 KB · Views: 3
Thank you Marco,

You mean like this?




I tried this and it doesn't work? IS there something I am missing?

Also, what do you mean with "Infinite loop" How do I exit it, if I encounter it with this problem?
Thank you!!
Code:
With "infinite loop" I mean this, what happen with this case?

Record 1:
- Order-Number is: 123456
- Previous-Order-Number is: 999999

Record 2:
- Order-Number is 999999
- Previous-Order-Number is 111111

Record 3:
- Order-Number is: 111111
- Previous-Order-Number is: 123456

Is clear logically that can't happen, but, what if?
So, I would add a counter and quit of the loop if the counter is greater than N value. Or much better, save the order numbers on a temp-table and exit if already exist,
 
You can also test if the new order find (order2 in the do while) is in the list of the previous order (cCombinedOrder).
The Marco's N value is still interresting to avoid the error on 32K.
 

NewP

New Member
The issue with your data model, and with this requirement/approach of constructing these order chains, is that some orders are treated as special, as if they are parent records, and the previous orders are like their children. You will end up having orders in multiple partial chains.

Consider a set of records with the following order numbers for customer A: 10 -> 9 -> 5 -> 3 -> 1
i.e. order 10's previous order is 9; order 9's previous order is 5, etc.
(Of course I am assuming there is more than one customer, each with its own orders. Perhaps this is a valid assumption? Or perhaps not and this is just an academic exercise. That isn't clear. But given "there is only a maximum of of 5 or 6 orders not 2k", it seems like this is a homework assignment.)

As you iterate over the order table (by order number perhaps?) with a for each, you will construct the following chains of orders:
1
3 -> 1
5 -> 3 -> 1
9 -> 5 -> 3 -> 1
10 -> 9 -> 5 -> 3 -> 1

One of these might be deemed complete, at least at a particular point in time, while the others could be deemed partial/duplicate; they are subsets of the complete one. Do you need to cull the list, eliminating the partial chains and keeping only the complete one(s)?
Thank you. That issue did cross my mind, the idea was to cheese it by using excel to cull the duplicate records. I am aware of my limitations in progress
 
Top