SQL Expression (Crystal Reports) help

[Reeco]

Hi, hope someone can help.

I have a Crystal report that is basically a list of route cards (sometimes known as works orders or job cards). I need to do some analysis on which customers these route cards were for. Usually, these route cards are linked to a contract which makes it easy to return the customer code, however where they are not linked I would like to use an SQL expression to dig into the database and return the customer code from the first contract associated with the part number/issue combination on the route card.

This involves three tables. I need to link the route_cards table to the contract_parts table and return records where the part number and issue matches. I then need to link to the contracts table, return the cust_code field and select the first of these. Here's what I have so far:

(
Select FIRST ("cust_code")
From
"radandba"."contracts" "contracts"
INNER JOIN "radandba"."contract_parts" "contract_parts" ON ("contract_parts"."contract_no" = "contracts"."contract_no")
INNER JOIN "radandba"."route_cards" "route_cards" On ("contract_parts"."part_num" = "route_cards"."part_num" AND "contract_parts"."issue" = "route_cards"."issue")
Where ("contract_parts"."contract_no" = "contracts"."contract_no" AND
"contract_parts"."part_num" = "route_cards"."part_num" AND
"contract_parts"."issue" = "route_cards"."issue")
)

The above is returning the first cust_code from the entire contracts table, not the subset that I need.

Any help would be appreciated.

 

[Zax63]

I'm not 100% clear on what you are trying to do but I don't think you need the Where clause as it looks like that is just repeating your INNER JOINs. With INNER JOINs only the records that match up will be returned.
Then you need to add a GROUP BY clause. Maybe something like this:

(
Select "contract_parts"."contract_no", FIRST ("cust_code")
From
"radandba"."contracts" "contracts"
INNER JOIN "radandba"."contract_parts" "contract_parts" ON ("contract_parts"."contract_no" = "contracts"."contract_no")
INNER JOIN "radandba"."route_cards" "route_cards" On ("contract_parts"."part_num" = "route_cards"."part_num" AND "contract_parts"."issue" = "route_cards"."issue")
GROUP BY "contract_parts"."contract_no"
)

That should give the cust_code for each contact_no.

 

[Reeco]

Thanks Zax63. I'm basically trying to return data from a third table, using a middle 'linking' table - contract_parts. I have to do this as the table that contains the cust_code field I am trying to return does not contain part_num and issue fields.

Each row in my report contains part_num and issue fields. I want to tell Crystal 'go away and find all instances of that part_num and issue combination in the contract_parts table. When you have found them, return all cust_codes from the (linked) contracts table and select the first of them'. I could quite easily do this with a subreport, but that means returning a variable to the main report and Crystal has various (horrible) limitations with variables.

I wasn't sure about the where clause, but Crystal wouldn't compile the expression if I omitted it. I'll give your solution a go, thanks again.

 

[CriticalThanking]

I normally only use GROUP BY to do aggregation (SUM, COUNT, MIN, MAX, etc). If you simply want each customer once no matter how many times it appears, I normall use SELECT DISTINCT CUSTOMER_NAME FROM CUSTOMERS...

Zax is correct. When using INNER/LEFT/RIGHT JOIN, the ON clause says how to walk from table to table. You would then use the WHERE clause just to refine specific criteria within a row in a single, such as WHERE CUSTOMERS.STATE_ABBRV = 'TX'.

 

[varwoche]

I normally only use GROUP BY to do aggregation (SUM, COUNT, MIN, MAX, etc). If you simply want each customer once no matter how many times it appears, I normall use SELECT DISTINCT CUSTOMER_NAME FROM CUSTOMERS...

Yeah, but check out that weird FIRST aggregate function. [quick google...] Access/Jet I take it? Doesn't make much sense without an ORDER BY. [more google...] Oh, how lame. Jet that is.

 

[rjh01]

You are doing an inner join. Would all rows on the first table (contract_parts) also have corresponding rows on both of the other tables (route_cards and contracts)?

Edit. I also think varwoche is right. You need to get rid of the FIRST parameter. Use DISTINCT instead. But get rid of it until you know it is producing results.

 

[varwoche]

Maybe something like so, except I don't know if Jet supports Top N. Need to specify the ORDER BY that defines which comes first:

SELECT distinct
contract_no
,(select TOP 1 cust_code
from route_cards rc
where rc.part_num = cp.part_num
and rc.issue = cp.issue
ORDER BY <whatever> )
AS cust_code
FROM contracts c
INNER JOIN contract_parts cp ON
cp.contract_no = c.contract_no

(I suck when I don't have the DB at hand.)

(Damn, it hosed my friendly indentation.)


ADD: Maybe you can use your query with an added ORDER BY clause.

 

[Reeco]

Gah, can't get any of the above to work. Thanks anyway.

I think I need to work on my basic SQL concepts. I can write queries that return data from a table linked directly to the report data, but am struggling when there is an intermediary table, as in this case.

 

[varwoche]

Is the db Access/Jet?

What about this...?

Select FIRST ("cust_code")
From
"radandba"."contracts" "contracts"
INNER JOIN "radandba"."contract_parts" "contract_parts" ON ("contract_parts"."contract_no" = "contracts"."contract_no")
INNER JOIN "radandba"."route_cards" "route_cards" On ("contract_parts"."part_num" = "route_cards"."part_num" AND "contract_parts"."issue" = "route_cards"."issue")
Where ("contract_parts"."contract_no" = "contracts"."contract_no" AND
"contract_parts"."part_num" = "route_cards"."part_num" AND
"contract_parts"."issue" = "route_cards"."issue")
ORDER BY <something>

 

[Reeco]

Thanks again varwoche. Your solution looks like it should work but unfortunately it didn't.

However, I have sorted it:

(
Select FIRST "cust_code"
FROM "radandba"."contract_parts" "contract_parts", "radandba"."contracts" "contracts_1"
WHERE (("route_cards"."part_num"="contract_parts"."part_num") AND ("route_cards"."issue"="contract_parts"."issue")) AND ("contract_parts"."contract_no"="contracts_1"."contract_no")
)

I made an alias of the contracts table as this exists in the report elsewhere - not sure if this is needed or not. Also, because the route_cards.part_num and route_cards.issue fields are the fields I am linking to in the report (i.e. the report record selection formula deals with these) I omitted them from any joins in the SQL expression.

The entire report's SQL query is now as follows:


SELECT "route_card_ops"."seq", "route_card_ops"."op_code", "route_card_ops"."route_no", "route_card_ops"."est_run_time", "route_card_ops"."manufacture_method", "route_cards"."qty_finished", "route_cards"."completed_date", "route_cards"."route_status", "contracts"."cust_code", "route_cards"."part_num", "route_cards"."issue", (
Select FIRST "cust_code"
FROM "radandba"."contract_parts" "contract_parts", "radandba"."contracts" "contracts_1"
WHERE (("route_cards"."part_num"="contract_parts"."part_num") AND ("route_cards"."issue"="contract_parts"."issue")) AND ("contract_parts"."contract_no"="contracts_1"."contract_no")
)
FROM "radandba"."route_card_ops" "route_card_ops", ("radandba"."route_cards" "route_cards" LEFT OUTER JOIN "radandba"."contracts" "contracts" ON "route_cards"."contract_no"="contracts"."contract_no")
WHERE ("route_card_ops"."route_no"="route_cards"."route_no") AND "route_card_ops"."manufacture_method"='INH' AND NOT ("route_card_ops"."op_code"='DYEPEN' OR "route_card_ops"."op_code"='FLAME' OR "route_card_ops"."op_code"='ISIR' OR "route_card_ops"."op_code"='MACHINE' OR "route_card_ops"."op_code"='PICKLE' OR "route_card_ops"."op_code"='PLATE' OR "route_card_ops"."op_code"='POWDER' OR "route_card_ops"."op_code"='PROG' OR "route_card_ops"."op_code"='SUBCON' OR "route_card_ops"."op_code"='SUBMACH' OR "route_card_ops"."op_code"='SUBPAINT' OR "route_card_ops"."op_code"='WET') AND "route_cards"."route_status"='CMP'