I have a database that can be described as follows:

Inovice header has 1 to many line items.

Each line item has 1 to many Line Item Descriptions

Also, Each Line Item has 1 to many Line Item Special Instructions.


If I join The Header to he line Item and also to the Line Item Description and Special Instruction tables, when I report the Line Item amount, I'm getting a duplicate/triplicate situation when I have multiple Line Item descriptions and Special Instructions.


What I would like the report to look like is this:

Hdr # LI # LI Amt LI Desc LI Spec Instr
----- ---- ------ ------ ------------
1 1 10.00 Des 1 SI 1
Des 2 SI 2
Des 3
2 20.00 Des 1 SI 1
Des 2
2 1 30.00 Des 1 SI 1
Des 2 SI 2



I hope this makes sense. Anybody have any hints on how to do this? I know I did it a couple years ago, and I can't remember how I did it.


Thanks,

Carl

This message has been edited. Last edited by: Kerry,

Hdr # LI # LI Amt LI Desc LI Spec Instr
----- ---- ------ ------ ------------
1..... 1... 10.00 Des 1.. SI 1
..................Des 2.. SI 2
..................Des 3
.......2... 20.00 Des 1.. SI 1
..................Des 2

2..... 1... 30.00 Des 1.. SI 1
..................Des 2.. SI 2

Carlf, please enclose your sample output using the </> button in your "Post" window.

As for the duplicates, it is a normal behaviour when joining tables in a one-to-many relation.

That should not be a problem with WebFOCUS which does not print "duplicate" values when you display your parent-wise fields as BY fields.

TABLE FILE blah
PRINT LN_SPEC_INSTR
BY HDR_NO
BY LN_N
BY LN_AMT
BY LN_DESC
END

Hope this helps.

- Neftali.

quote:

That should not be a problem with WebFOCUS which does not print "duplicate" values when you display your parent-wise fields as BY fields

The above is of course applicable unless you have SET BYDISPLAY = ON.