I started to explore sql expression fields some weeks ago but I have the following problem :
Our database has 3 main tables : workorder, operations and materials
This workorder table contains the header information but also the more detailed level information.
This header information can be filtered out by a specific column.
WORKORDER
ID DETAIL PART
A 0 PARTNUMBER
A 1 EMPTY
A 2 EMPTY
In the crystal report I put all records from this workorder table but now I want to add this PART information only from ID = 'A' and DETAIL = '0' by using a sql expression fields.
I tried it like this
(
SELECT WORKORDER.PART
FROM WORKORDER
WHERE WORKORDER.ID = WORKORDER.ID AND WORKORDER.DETAIL = 0
)
If I change WORKORDER.ID into 'A' I have off course the correct answer.
If something like this is impossible I have also a view that I can use
(SELECT WORKORDERVIEW.PART
FROM WORKORDERVIEW
WHERE WORKORDERVIEW.ID = WORKORDER.ID
)
=> in this view I filtered out all the records with DETAIL = '0'
But this is also not working.
I keep on getting errors about WORKORDER.ID can't be bound or that this is