Anybody here use Actuate?

If so, do you know of a forum (like this one) for Acuate users.
The few I've found via google seem to have no active users that are capable of answering my annoying questions.

Hi There

The following sql statement is not giving the right out, could anyone help

I have 3 tables 1) workorder 2) matusetrans 3)wpmaterial

table 1 has 1 record, t2 has 2 record & t3 has 2 record
when i join them i am getting 4 records in which 2 records are duplicated, is there any way i can stop displaying the duplicate record, note i have used DISTINCT but it did not work.


"SELECT matusetrans.itemnum, matusetrans.transdate, matusetrans.quantity,
matusetrans.issuetype, matusetrans.description, wpmaterial.itemqty,
matusetrans.qtyrequested
FROM matusetrans, wpmaterial, workorder.wonum
WHERE ( (matusetrans.refwo = '108875')
AND (Workorder.wonum = wpmaterial.wonum)
AND (Workorder.wonum = wpmaterial.wonum)
AND (matusetrans.issuetype = 'ISSUE')
)

first off you may be better off asking this question in the appropriate forum, which db engine are you using, and would you lime this question moving there? (Im guessing because of the bracketing its probably MS Access)

is their any reason for the duplicate AND (Workorder.wonum = wpmaterial.wonum), or is that a typo?

I don't see how you are expecting the db engine to resolve a link between matusetrans and the other two tables...
should the dupicate AND (Workorder.wonum = wpmaterial.wonum), read
AND (Workorder.wonum = matusetrans.wonum)?

I suggest you use the join clause rather than the where clause
it quite possble that when doing a join youmay get more rows than you first anticipate.. because thee may be more than one match in anyone table

sarah, your sql is wrong. first of all, you are checking twice for wpmaterial wonum. instead, check first for wpmaterial, and then for matusetrans. my guess is that you are using oracle (most maximo instances i have seen run on oracle). let me also stress out that workorder may or may not have materials assigned to it, so let us make our joins according to that:

select matusetrans.itemnum, matusetrans.transdate, matusetrans.quantity,
matusetrans.issuetype, matusetrans.description, wpmaterial.itemqty,
matusetrans.qtyrequested from workorder, matusetrans, wpmaterial
where workorder.wonum = wpmaterial.wonum (+)
and workorder.wonum = matusetrans.refwo (+)
and matusetrans.issuetype = 'ISSUE'

should work just fine ...