Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Actuate Report Writer

    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.
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jul 2008
    Posts
    4
    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')
    )

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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
    Code:
    SELECT matusetrans.itemnum, matusetrans.transdate, matusetrans.quantity,
    matusetrans.issuetype, matusetrans.description, wpmaterial.itemqty,
    matusetrans.qtyrequested
    FROM Workorder
    join wpmaterail on Workorder.wonum = wpmaterial.wonum
    AND
    join matusetrans on Workorder.wonum = matusetrans.wonum
    WHERE matusetrans.refwo = '108875'
    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
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2008
    Posts
    1
    Quote Originally Posted by Sarah_1985
    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')
    )
    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 ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •