Record Selection Issue

[agorjest]

Using CR9, ODBC/SQL, with data from 3 tables: Workorder, Labor Transactions, and Materials Transactions. Primary table is the Workorder table, with full outer join links on the indexed workorder number field from the workorder table to both the Lab.Trans. and Mat.Trans. tables.

I'm trying to pull records for all labor transactions and materials transactions that occur between the start date and end date parameters chosen by the user ({?Enter StartDate}, {?Enter EndDate}). These dates should be based on the date fields in the Lab.Trans. and Mat.Trans. tables. However, it doesn't seem to be selecting materials transaction records unless there is also a labor transaction with the same workorder #.

Here is the record selection formula I'm using:
({labtrans.labordate} >= {?Enter Start Date} or {mattrans.transdate} >= {?Enter Start Date}) and
({labtrans.labordate} <= {?Enter End Date} or {mattrans.transdate} <= {?Enter End Date})

Any suggestions? Thanks...

 

[lbass]

What is the report intended to do? You could add one of the tables in a subreport, or you might be best off by using a command as a datasource, where you can specify the date ranges for each set of data in a union all statement like this:

select 'labortrans' as type, workorder.workorderID, labortrans.labordate as date
from workorder inner join labortrans on
workorder.workorderID = labortrans.workorderID
where labortrans.labordate >= {?Start} and
labortrans.labordate < {?End} + 1
union all
select 'mattrans' as type, workorder.workorderID, mattrans.transdate as date
from workorder inner join mattrans on
workorder.workorderID = mattrans.workorderID
where mattrans.transdate >= {?Start} and
mattrans.transdate < {?End} + 1

Not sure of the punctuation/syntax for your datasource. The date fields would appear in one field now called "Date", but you could distinguish these fields as necessary based on the "type" field.

-LB

 

[agorjest]

LB,
Thanks for your advice on setting up a command datasource, I think this might work. I created a command with the proper syntax for my SQL ODBC datasource, and got it to work with the 5 fields in the first SELECT statement below. However, 2 fields from the second SELECT statement are missing from the list of available fields for use on the report: matusetrans.actualdate (Materials Transaction Date) and matusetrans.linecost (Cost of Materials Transaction). Is there something missing from my query?


SELECT "labtrans"."startdate", "labtrans"."regularhrs", "workorder"."wonum", "workorder"."wo14", "workorder"."status"
FROM ("CMG"."dbo"."workorder" "workorder" INNER JOIN
"CMG"."dbo"."labtrans" "labtrans" ON "workorder"."wonum"="labtrans"."wonum")
WHERE "labtrans"."startdate">={?Start Date} AND
"labtrans"."startdate"<{?End Date} + 1
UNION ALL
SELECT "matusetrans"."actualdate", "matusetrans"."linecost","workorder"."wonum", "workorder"."wo14", "workorder"."status"
FROM ("CMG"."dbo"."workorder" "workorder" INNER JOIN "CMG"."dbo"."matusetrans" "matusetrans" ON "workorder"."wonum"="matusetrans"."wonum")
WHERE "matusetrans"."actualdate">={?Start Date} AND "matusetrans"."actualdate"<{?End Date} + 1

 

[lbass]

When you use a union or union all, the fields from the two queries are merged by the order they appear in the select statement. If you have fields in one table that don't have a parallel field in the other, you should use 'null' to hold the position. So you might want to change your query to:

SELECT 'LabTrans' "Table", "labtrans"."startdate" "Date", "labtrans"."regularhrs", null "LineCost", "workorder"."wonum", "workorder"."wo14", "workorder"."status"
FROM ("CMG"."dbo"."workorder" "workorder" INNER JOIN
"CMG"."dbo"."labtrans" "labtrans" ON "workorder"."wonum"="labtrans"."wonum")
WHERE "labtrans"."startdate">={?Start Date} AND
"labtrans"."startdate"<{?End Date} + 1
UNION ALL
SELECT 'Matusetrans' "Table", "matusetrans"."actualdate", null,
"matusetrans"."linecost","workorder"."wonum", "workorder"."wo14", "workorder"."status"
FROM ("CMG"."dbo"."workorder" "workorder" INNER JOIN "CMG"."dbo"."matusetrans" "matusetrans" ON "workorder"."wonum"="matusetrans"."wonum")
WHERE "matusetrans"."actualdate">={?Start Date} AND "matusetrans"."actualdate"<{?End Date} + 1

By adding a field "Table", you can use conditional formulas like:

if {command.Table} = "LabTrans" then {command.date}

...if you want to work independently with a field from one of the merged tables.

-LB