Query combine all charges
I have a table with an order and many details, and a second table with all the items on the order.
The order number ties them together.
Right now I have to run the order, with the total of all items charges.
I must separately run a detail report for itemization.
What I would like to do is combine for one query to run shipment details, and have all items included on that line.
Order #, Shipment date, Total Weight, Item #1, Item #1 cost, Item #2, Item #2 cost, etc.
The items count would vary. Is this possible??
It is teradata.
The order number ties them together.
Right now I have to run the order, with the total of all items charges.
I must separately run a detail report for itemization.
What I would like to do is combine for one query to run shipment details, and have all items included on that line.
Order #, Shipment date, Total Weight, Item #1, Item #1 cost, Item #2, Item #2 cost, etc.
The items count would vary. Is this possible??
It is teradata.
Sharath S
Post table structure, sample data and expected result.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Table Orders
Order # VARCHAR()
Shipper_Name VARCHAR()
Ship_date date()
Item_Charge DECIMAL(12,2)
Table Items
Order # VARCHAR()
Item_desc VARCHAR()
Item_type VARCHAR()
Item_Charge DECIMAL(12,2)
Results
Order #, Shipper_Name, Ship_date, Item_desc#1, Item_Charge#1, Item_desc#2, Item_Charge#2, etc.
Order # VARCHAR()
Shipper_Name VARCHAR()
Ship_date date()
Item_Charge DECIMAL(12,2)
Table Items
Order # VARCHAR()
Item_desc VARCHAR()
Item_type VARCHAR()
Item_Charge DECIMAL(12,2)
Results
Order #, Shipper_Name, Ship_date, Item_desc#1, Item_Charge#1, Item_desc#2, Item_Charge#2, etc.
Do you know how many max items exist per an order?
ASKER
Sharath,
No I don't, but I could limit if needed to 5.
No I don't, but I could limit if needed to 5.
It doesn't matter how many items there are -- the report will expand as needed. You can use the various grouping properties to determine whether a group should stay together, or the first item with the header, etc. Here is a brief listing of where these properties are:
Report property sheet
Grp Keep Together
Detail Section
Keep Together
Force New Page
Group Header
Keep Together
Force New Page
Group Footer
Keep Together
Force New Page
Report property sheet
Grp Keep Together
Detail Section
Keep Together
Force New Page
Group Header
Keep Together
Force New Page
Group Footer
Keep Together
Force New Page