| Author |
Message |
carstowal
Groupie
Joined: 31 Jul 2008
Online Status: Offline
Posts: 80
|
  
Topic: matching most recent record from 2nd database
Posted: 31 Jul 2008 at 6:58am |
I have a crystal report, Grouped by PART_ID, where all columns pull from PART database, so I can select on various part codes.
I have a group SUM of PLANNED_ORDER.ORDER_QTY
This is the total quantity for each PART_ID in the PLANNED_ORDER(s) database
PLANNED ORDERS don’t have pricing associated with them.
However, I need to calculate the estimated value of the PLANNED ORDERS
So I need to pull the most recent price from the CLOSED_ORDERS Database.
How can I return the CLOSED_ORDERS.UNIT_PRICE with the matching CLOSED_ORDERS.PART_ID from the highest CLOSED_ORDERS.ROW_ID
Example:
ROW_ID PART_ID UNIT_PRICE
112 ABC 57.90
379 ABC 62.20
525 ABC 60.10
I want 60.10 because it is on the highest Row 525
PS. I am a newbie newbie Edited by carstowal - 31 Jul 2008 at 6:58am
|
 IP Logged |
|
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 02 Aug 2008 at 10:03pm |
Do you know SQL? What type of database are you running?
I would use a command to do this. A command is just a SQL select statement. Depending on your database syntax, it would look something like this:
Select PART_ID, UNIT_PRICE
From CLOSED_ORDERS as co
where co.row_ID =
(Select max(co1.ROW_ID)
from CLOSED_ORDERS as co1
where co1.PART_ID = co.PART_ID)
This SQL should give you just one record per part, containing the unit price that you want. Link to the command from PLANNED_ORDERS.PART_ID using a left outer join so that you have a record even for new parts that don't yet have a closed order.
-Dell
|
|
|
| IP Logged |
|
carstowal
Groupie
Joined: 31 Jul 2008
Online Status: Offline
Posts: 80
|
Posted: 05 Aug 2008 at 1:24pm |
I know even less about SQL than Crystal but apparently am diving in head first!
(two days ago I couldn't spell SQL)
I used the SQL command provided and it returned the desired data. (THANKS)
However I don’t know how to automatically obtain that data in a useful format. So I copied if from the Microsoft SQL Server 2000 query, pasted it into Excel, saved it and linked to the Excel spreadsheet as a new database in Crystal. (NOT someting I want to do everytime).
Is there a way to automate the SQL query via Crystal?
I need to create a Crystal Report that someone can refresh monthly without having to do anything beyond clicking REFRESH.
As a side note, the query returned (2) of the records in duplicate, so I did get an error message regarding multiples, which I used the Advanced Filter, Unique Items in Excel to eliminate. Edited by carstowal - 05 Aug 2008 at 1:25pm
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 05 Aug 2008 at 1:33pm |
If you're using SQL Server, you can do this in Crystal (it's not available for all types of databases...)
In the Database Expert, open the database as if you were going to add a table to your report. Under the connection name and above the tables you should see an option that says "Add Command". Select that, give it a name, and type in your SQL. After you save it, go to the Links tab and link from your Planned_Orders table to the command that you just created. Right-click on the link, select "Link Options" and make it a Left Outer join.
To remove duplicates, add the word "Distinct" right after "Select" when you type in your command.
-Dell
|
|
|
| IP Logged |
|
carstowal
Groupie
Joined: 31 Jul 2008
Online Status: Offline
Posts: 80
|
Posted: 08 Aug 2008 at 8:45am |
Apparently using SQL. This works great. Now I have the same problem compounded.
So I created a second command, changed your code to find the CUST_ORDER_ID on the max ROW
Select Distinct PART_ID, CUST_ORDER_ID
From CUST_ORDER_LINE as co
where co.rowID =
(Select max(co1.ROWID)
from CUST_ORDER_LINE as co1
where co1.PART_ID = co.PART_ID)
but I also need to add, embedded into the above something that takes the Order ID finds it in the CUSTOMER_ORDER database and returns the CUST ID
like:
Select ID
From CUSTOMER_ORDER
Where CUST_ORDER_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID
(PS. I wasn't able to name the commands so they're just command & command1. I'm in Crystal 9)
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 08 Aug 2008 at 9:16am |
This is actually a SQL issue, but based on the info you've given, I can help you with it. If you're going to be doing these types of commands, you need to get yourself a good SQL reference and use it to help you build your own.
In your SQL, it looks like you're using the CUSTOMER_ORDER.ID field as both the Order ID and the Customer ID. So, you'll need to tweak this SQL to work with the right fields from your table:[code]
Select Distinct co.customer_id_field, col.PART_ID, col.CUST_ORDER_ID
From CUSTOMER_ORDER as co
inner join CUST_ORDER_LINE as col on col.CUST_ORDER_ID = co. order_id_field
where
where not exists
(Select 'X'
from CUST_ORDER_LINE as col1
where col1.PART_ID = col.PART_ID) or col.rowID =
(Select max(col1.ROWID)
from CUST_ORDER_LINE as col1
where col1.PART_ID = col.PART_ID)
Note the "Not Exists" query that I added. I'm assuming that you want the information even if there are no prior orders, correct?
-Dell
|
|
|
| IP Logged |
|
carstowal
Groupie
Joined: 31 Jul 2008
Online Status: Offline
Posts: 80
|
Posted: 08 Aug 2008 at 9:37am |
yes my post is confusing,
customer ID & order ID are not the same
it should have said
Select CUSTOMER_ID
From CUSTOMER_ORDER
Where CUST_ORDER_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID
the problem is the Customer ID is not in the CUST_ORDER_LINE db
I'm trying to adjust your code.
Can you recommend any good books? Everything I purchase in books & training are too basic for what I want. But then I do seem to have a problem of a boss who needs something more than the basics right out of the gate.
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 11 Aug 2008 at 8:04am |
For SQL I recommend The Practical SQL Handbook by Bowman, Emerson, and Darnovsky ISBN 0-201-44787-8. It give real world examples and is a good reference book. It also gets into things like sub-queries (which is what you're working with here.)
For Crystal I recommend Brian's books.
-Dell
|
|
|
| IP Logged |
|
|
|
