| Author |
Message |
ViperSBT
Newbie
Joined: 02 Jul 2009
Location: United States
Online Status: Offline
Posts: 12
|
  
Topic: JOIN question
Posted: 02 Jul 2009 at 5:31am |
I'm not a complete Newbie when it comes to Crystal nor SQL queries, but I am at a loss on the report I am trying to generate. Here is my current query in Crystal: SELECT "Part1"."PartNum", "PartTran1"."TranType"
FROM {oj "MFGSYS"."PUB"."Part" "Part1" LEFT OUTER JOIN "MFGSYS"."PUB"."PartTran" "PartTran1" ON ("Part1"."Company"="PartTran1"."Company") AND ("Part1"."PartNum"="PartTran1"."PartNum")}
WHERE "PartTran1"."TranType"<>'STK-MTL'
Which generates the following output:
| PartNum |
| 000004-0019 |
| 000004-0019 |
| 000004-0019 |
| 000004-0019 |
| 000004-0019 |
| 000004-0019 |
| 000004-0019 |
| 000004-0019 |
| 000004-0029 |
| 000004-0029 |
| 000004-0029 |
| 000004-0029 |
| 000004-0029 |
| 000004-0032 |
| 000004-0032 |
| 000004-0032 |
| 000004-0032 |
| 000004-0032 |
| 000004-0035-00 |
| 000004-0035-00 |
| 000004-0035-00 |
| 000004-0035-00 |
| 000004-0035-00 |
| 000004-0035-00 |
| 000004-0035-00 |
| 000004-0035-00 |
I understand that the reason for this is that each of these PartNums has multiple entries in the PartTran table. But I only want one result per PartNum so the return looks like this:
| PartNum |
| 000004-0019 |
| 000004-0029 |
| 000004-0032 |
| 000004-0035-00 |
What do I need to do different?
|
 IP Logged |
|
|
|
delstar
Newbie
Joined: 11 Jun 2009
Online Status: Offline
Posts: 26
|
Posted: 02 Jul 2009 at 5:43am |
|
SELECT DISTINCT
|
| IP Logged |
|
ViperSBT
Newbie
Joined: 02 Jul 2009
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 02 Jul 2009 at 5:46am |
|
Where in Crystal can I tell it to only provide DISTINCT results? I tried the option under the Database menu to "Select Distinct Records", but that appears to effect the PartNum/PartTran combination and not just the PartNum. So, it removes some of the duplication, but not all.
Edited by ViperSBT - 02 Jul 2009 at 5:50am
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 02 Jul 2009 at 6:14am |
Yes, distinct is going to look at the whole row of data being returned. How do you want to use this? is the big question. If you just want rows of partnums, and not trantype, I would create a group and group it by partnum, then display the part num only in the header or the footer and suppress the rest, but then the question becomes, why is TranType in the query?
HTH
|
| IP Logged |
|
ViperSBT
Newbie
Joined: 02 Jul 2009
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 02 Jul 2009 at 6:24am |
A PartNum will have many Transactions entered against it. And the Transactions can be of different Type (TranType), I need to isolate all PartNums that don't have certain TranType entries. Once I have this report, I will be adding additional information to the PartNum from different Tables so that I can start doing some calculations and figure the value of the items... This is what I am shooting for:
| |
Part Number |
|
Description |
Part Class |
|
G/L Account |
|
|
Cost |
|
Warehou |
|
OnhandQty |
|
Value |
| |
000004-0029 |
|
Meat Box Cold Pan |
Finished Goods |
|
0280000000000 |
|
|
1,475.17 |
|
A51 |
|
2.00 |
|
$2,950.34 |
| |
000004-0032 |
|
Mobile Table 33 x 38 x
22 |
Finished Goods |
|
0280000000000 |
|
|
359.06 |
|
A51 |
|
1.00 |
|
$359.06 |
| |
000004-0041 |
|
Ice Pans |
Finished Goods |
|
0280000000000 |
|
|
244.55 |
|
A52 |
|
2.00 |
|
$489.10 |
| |
000004-0043 |
|
60 Inch Undercounter
Refrigerator |
Finished Goods |
|
0280000000000 |
|
|
2,843.20 |
|
A52 |
|
1.00 |
|
$2,843.20 |
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 02 Jul 2009 at 6:33am |
OK, you need to filter your data, I am assuming that you are joining the tables in the report directly. Report/Selection Formulas/Record is where you want to go next.
since you want to exclude certain trantypes, it would depend on if the types are fixed or dynamic and how these are entered into the report, but for a start with a fixed type
{PartTran1.TranType} <> "excluded tran type"...
whatever records return a true in the formula are displayed, the rest are skipped
HTH
|
| IP Logged |
|
ViperSBT
Newbie
Joined: 02 Jul 2009
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 02 Jul 2009 at 6:39am |
|
This is what I currently have in the Record Selection Formula:
not ({Part.ClassID} in ["COL", "FSP", "FST", "GOS", "JAN", "MNE", "SAF", "SMT", "STS", "TOL", "WLD"]) and
not ({PartTran.TranType} in ["STK-MTL"])
But, since this still isn't accounting for the fact that a TranType of "ADJ-QTY" may occur multiple times in the PartTran table for a PartNum I am still getting multiple returned lines...
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 02 Jul 2009 at 7:18am |
I am assuming that you want adj-qty...and that there are multiple adj-qty in a single partNum...which makes sense.
your report, is it calculating the on-hand amount, or just reading it out of a table? I figure that the cost is read from a table, and the value is just a multiplication.
Ultimately, your report, appears to be a summary report, and I would create a group and display the desired results in the group footer. If you are calculating the on-hand quantity, you can create a running total or a formula to do the calculation and then report on it in the footer. You can suppress the details and the header.
If a desired trancode appears multiple times in the recordset, you are going to get multiple records. More likely than not, some field, that you need, will be different, so a distinct will not filter the multiple rows, as each row IS distinct. A stored proc can help, as it can combine the data and manipulate it in a way that Crystal can't. Crystal does simple joins, and filters from there, but sometimes you need to do so much more for a report.
I know, I am drifting, but how to display only 1 row for a multiple row data, is to apply some sort of grouping, because, there comes a point where you can't filter out duplicating data. You could use a subreport, but I discourage them as they cause more hits to the database, which could impact performance (depends on how many rows and columns are being asked for). Subreports is a way to cut down on duplication as you get rid of columns that cause the duplication to begin with. It is all up to you and how you want the report to run / display / maintainability.
|
| IP Logged |
|
ViperSBT
Newbie
Joined: 02 Jul 2009
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 02 Jul 2009 at 7:46am |
|
The report Cost and Quantity are coming out of other tables and I have a formula figuring the Value. I'll give the grouping a shot and see how I do with it... May have more questions...
|
| IP Logged |
|
ViperSBT
Newbie
Joined: 02 Jul 2009
Location: United States
Online Status: Offline
Posts: 12
|
Posted: 02 Jul 2009 at 8:19am |
|
OK, I think I got the grouping working now, but I just realized a fatal flaw in my Query Criteria...
SELECT DISTINCT "Part1"."PartNum", "Part1"."PartDescription", "PartCost1"."StdMaterialCost", "Part1"."ClassID", "PartClass1"."Description", "PartClass1"."ExpenseChart", "PartBin1"."WarehouseCode", "PartBin1"."OnhandQty", "PartTran1"."TranType"
FROM {oj ((("MFGSYS"."PUB"."Part" "Part1" INNER JOIN "MFGSYS"."PUB"."PartCost" "PartCost1" ON ("Part1"."Company"="PartCost1"."Company") AND ("Part1"."PartNum"="PartCost1"."PartNum")) INNER JOIN "MFGSYS"."PUB"."PartClass" "PartClass1" ON ("Part1"."Company"="PartClass1"."Company") AND ("Part1"."ClassID"="PartClass1"."ClassID")) INNER JOIN "MFGSYS"."PUB"."PartBin" "PartBin1" ON ("Part1"."Company"="PartBin1"."Company") AND ("Part1"."PartNum"="PartBin1"."PartNum")) LEFT OUTER JOIN "MFGSYS"."PUB"."PartTran" "PartTran1" ON ("Part1"."Company"="PartTran1"."Company") AND ("Part1"."PartNum"="PartTran1"."PartNum")}
WHERE NOT ("PartTran1"."TranType"='ADJ-MTL' OR "PartTran1"."TranType"='PUR-MTL' OR "PartTran1"."TranType"='STK-MTL') AND NOT ("Part1"."ClassID"='COL' OR "Part1"."ClassID"='FSP' OR "Part1"."ClassID"='FST' OR "Part1"."ClassID"='GOS' OR "Part1"."ClassID"='JAN' OR "Part1"."ClassID"='MNE' OR "Part1"."ClassID"='SAF' OR "Part1"."ClassID"='SMT' OR "Part1"."ClassID"='STS' OR "Part1"."ClassID"='TOL' OR "Part1"."ClassID"='WLD')
ORDER BY "Part1"."PartNum", "PartBin1"."WarehouseCode"
This is giving me all of the results in PartTran for a PartNum where TranType doesn't equal one of the criteria I have selected. However, what I want is to only see PartNum that don't have any of TranType of the selected criteria...
If I have PartNum 100, 101 and 102
100 has TranTypes A, B, C
101 has TranTypes A, C
102 has TranTypes A, B
If I tell it to show me all PartNum that don't have TranType C I want to see PartNum 102 and only 102. But I am getting all 3 because at some point each on of them has a TranType that isn't C.
Does that make sense?
|
| IP Logged |
|
|
|
