Select Expert 1

[rwn]

Im suspecting my Select Expert is not restricting the data as I require based on my Sales Order and Job status. The report objective is to ONLY see Material.Material based on the WHERE. However, Im seeing Material.Material being displayed when there are no Sales Orders or Job Orders associated to Material.Material.

My Crystal Reports SQL query is as follows:
SELECT "Material"."Material", "Material"."On_Order_Qty", "Material_Location"."On_Hand_Qty", "Material"."Description", "Job"."Status", "SO_Header"."Status", "Material"."Status", "Material"."Type", "Customer_Part"."Customer"
FROM (((("TRAINING"."dbo"."Material" "Material" LEFT OUTER JOIN "TRAINING"."dbo"."Material_Location" "Material_Location" ON "Material"."Material"="Material_Location"."Material") LEFT OUTER JOIN "TRAINING"."dbo"."Customer_Part" "Customer_Part" ON "Material"."Material"="Customer_Part"."Material") LEFT OUTER JOIN "TRAINING"."dbo"."SO_Detail" "SO_Detail" ON "Material"."Material"="SO_Detail"."Material") LEFT OUTER JOIN "TRAINING"."dbo"."Job" "Job" ON "Material"."Material"="Job"."Part_Number") LEFT OUTER JOIN "TRAINING"."dbo"."SO_Header" "SO_Header" ON "SO_Detail"."Sales_Order"="SO_Header"."Sales_Order"
WHERE ("Material"."Status"='Active' AND ("Material"."Type"='F' OR "Material"."Type"='H' OR "Material"."Type"='S') AND ("Job"."Status"='Active' OR "Job"."Status"='Hold' OR "Job"."Status"='Pending') OR ("SO_Header"."Status"='Hold' OR "SO_Header"."Status"='Open') AND ("Customer_Part"."Customer">='0' AND "Customer_Part"."Customer"<='z'))
ORDER BY "Material"."Material"

Thank you!!!!

 

[lbass]

(
"Material"."Status"='Active' AND
(
"Material"."Type"='F' OR
"Material"."Type"='H' OR
"Material"."Type"='S'
) AND
(
"Job"."Status"='Active' OR
"Job"."Status"='Hold' OR
"Job"."Status"='Pending'
) [red]OR //not sure what clauses are alternatives to this "or"[/red]
(
"SO_Header"."Status"='Hold' OR
"SO_Header"."Status"='Open'
) AND
(
"Customer_Part"."Customer">='0' AND
"Customer_Part"."Customer"<='z'
)
)

Can you explain how the "or" is to be interpreted? What criteria are supposed to "go together" and what criteria are alternatives to that cluster? Can't tell from this. Also not sure how you got an "or" in there like that using the select expert.

-LB

 

[rwn]

Thank you!! I changed it a bit when I entered it in the Select Expert. The prior was copied right from the Crystal reports Database-Show SQL Query. Do I have the parentheses in the right sections?

{Material.Status}="Active" AND
({Material.Type} = {?Material Type}) AND
({Customer_Part.Customer} in {?Begin Cust ID} to {?End Cust ID}) and
({Job.Status}="Active" OR
{Job.Status}="Hold" OR
{Job.Status}="Pending") OR
({SO_Detail.Status} ="Hold" OR
{SO_Detail.Status}="Open" OR
{SO_Detail.Status} = "Backorder")

 

[rwn]

I have fount the above restricts data that should be shown. Any thoughts?

 

[lbass]

You need to explain what should be shown,referencing actual field names.

-LB

 

[rwn]

SELECT "Material"."Material", "Material"."On_Order_Qty", "Material_Location"."On_Hand_Qty", "Material"."Description", "Job"."Status", "Material"."Status", "Material"."Type", "Customer_Part"."Customer", "SO_Detail"."Status", "Job"."Job", "SO_Detail"."Sales_Order"
FROM ((("TRAINING"."dbo"."Material" "Material" LEFT OUTER JOIN "TRAINING"."dbo"."Material_Location" "Material_Location" ON "Material"."Material"="Material_Location"."Material") LEFT OUTER JOIN "TRAINING"."dbo"."Customer_Part" "Customer_Part" ON "Material"."Material"="Customer_Part"."Material") LEFT OUTER JOIN "TRAINING"."dbo"."SO_Detail" "SO_Detail" ON "Material"."Material"="SO_Detail"."Material") LEFT OUTER JOIN "TRAINING"."dbo"."Job" "Job" ON "Material"."Material"="Job"."Part_Number"
WHERE ("Material"."Status"='Active' AND "Material"."Type"='F' AND "Job"."Status"='Active' OR ("Job"."Status"='Hold' OR "Job"."Status"='Pending') OR ("SO_Detail"."Status"='Hold' OR "SO_Detail"."Status"='Open') OR "SO_Detail"."Status"='Backorder' AND ("Customer_Part"."Customer">='0' AND "Customer_Part"."Customer"<='z'))
ORDER BY "Material"."Material"

 

[lbass]

This does not answer my question.

-LB