Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2013
    Posts
    16

    Unanswered: How to get unique field

    I am having an problem where I am getting 2 rows (It is not really a duplicate but how can i get rid of it)

    This is the query I am using
    SELECT DISTINCT C.RPTID, CASE WHEN LENGTH(A.EMPNO) < 7 THEN LPAD (A.EMPNO,7,'0') ELSE SUBSTR(A.EMPNO,1,7) END,AMOUNT,C.EXPKEY, MAX (C.SEQNO) OVER (PARTITION BY C.RPTID,C.EXPKEY),c.ctyid
    FROM TRA A, TAT C, EVT E WHERE A.RPTKEY=C.RPTID AND A.RPTID = E.RPTID AND C.EXPCODE IN ('LUINHP') AND A.PARKEY = C.EXPKEY AND A.PAYID='001' AND A.AMOUNT = A.AMOUNT AND A.REIMBURSECODE = 'REIM'
    AND E.ACTION='LEDGER EXTRACT' and e.EVTDATE >= '2013-05-22' and E.EVTDATE <= '2013-05-22'
    GROUP BY a.empno,c.rptkey,c.expkey,amount,c.seqno,c.ctyid

    When I run the query this is what the results
    rpt2985 0000000 38.0000 1 2 788
    rpt2985 0000000 38.0000 1 2 840
    rpt2985 0000000 68.0000 1 2 788
    rpt2985 0000000 68.0000 1 2 840


    select expkey,ctyid,empno,rptid,expcode from att where rptkey='TEA000002985'
    1 840 00000001 rpt2985 LUINHP
    1 788 78800002 rot2985 LUINHP

    select parkey,amount,ctyid,rptkid,expcode from cof_acctdist where rptkey='TEA000002985'
    1 68.0000 840 rpt2985 LUINHP
    1 38.0000 840 rpt2985 LUINHP


    What I want is this. I have to get a report on the attendees and the total number. I have 2 attendees not 4. It seems to be linking the extra fields by the ctyid.

    rpt2985 0000000 38.0000 1 2 840
    rpt2985 0000000 68.0000 1 2 788

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please publish sample data and your expected results from the sample data.

    There are some inconsistencies in your example.
    (1) Your query include
    FROM TRA A, TAT C, EVT E
    But, you didn't show the data of these tables.
    You wrote the results of...
    select expkey,ctyid,empno,rptid,expcode from att where rptkey='TEA000002985'
    select parkey,amount,ctyid,rptkid,expcode from cof_acctdist where rptkey='TEA000002985'

    (2) SELECT DISTINCT C.RPTID, ...
    C.RPTID is not included in GROUP BY a.empno,c.rptkey,c.expkey,amount,c.seqno,c.ctyid

    so on...

  3. #3
    Join Date
    Jan 2013
    Posts
    16
    Tonkuma,
    Thanks for taking the time to help. I did have some inconsistencies


    This is the query I am using
    SELECT DISTINCT C.RPTID, CASE WHEN LENGTH(A.EMPNO) < 7 THEN LPAD (A.EMPNO,7,'0') ELSE SUBSTR(A.EMPNO,1,7) END,AMOUNT,C.EXPKEY, MAX (C.SEQNO) OVER (PARTITION BY C.RPTID,C.EXPKEY),c.ctyid
    FROM TRA A, TAT C, EVT E WHERE A.RPTID=C.RPTID AND A.RPTID = E.RPTID AND C.EXPCODE IN ('LUINHP') AND A.PARKEY = C.EXPKEY AND A.PAYID='001' AND A.AMOUNT = A.AMOUNT AND A.REIMBURSECODE = 'REIM'
    AND E.ACTION='LEDGER EXTRACT' and e.EVTDATE >= '2013-05-22' and E.EVTDATE <= '2013-05-22'
    GROUP BY a.empno,c.rptid,c.expkey,amount,c.seqno,c.ctyid

    When I run the query this is what the results

    rpt2985 0000000 38.0000 1 2 788
    rpt2985 0000000 38.0000 1 2 840
    rpt2985 0000000 68.0000 1 2 788
    rpt2985 0000000 68.0000 1 2 840


    select expkey,ctyid,empno,rptid,expcode from TAT where rptid='rpt2985'
    1 840 00000001 rpt2985 LUINHP
    1 788 78800002 rot2985 LUINHP

    select parkey,amount,ctyid,rptid,expcode from TRA where rptid='rpt2985'
    1 68.0000 840 rpt2985 LUINHP
    1 38.0000 840 rpt2985 LUINHP


    SELECT * FROM EVT where rptid='RPT2985'
    rptid seqno date action batchno
    RPT2985 11 2013-05-21 AVAILEXT 0
    RPT2985 12 2013-05-22 LEDGER EXTRACT 72


    I would like my final result to look like the sample below. I have 2 attendees not 4. I need to write the report based on the number of attendees. It seems to be linking the extra fields by the ctyid.
    This is the result I get if I don't add the ctyid but unfortunately I need that field.

    rpt2985 0000000 38.0000 1 2 840
    rpt2985 0000000 68.0000 1 2 788

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Looking in your query
    ...
    FROM TRA A, TAT C, EVT E ...
    ...
    ...
    GROUP BY a.empno,c.rptid,c.expkey,amount,c.seqno,c.ctyid
    The table TAT must have a column seqno.
    But, you didn't show the values of the column, like...
    select expkey,ctyid,empno,rptid,expcode from TAT where rptid='rpt2985'
    I guessed a possibility that the result rows which you got might have different seqno.
    When I run the query this is what the results

    rpt2985 0000000 38.0000 1 2 788
    rpt2985 0000000 38.0000 1 2 840
    rpt2985 0000000 68.0000 1 2 788
    rpt2985 0000000 68.0000 1 2 840

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This is the result I get if I don't add the ctyid but unfortunately I need that field.

    rpt2985 0000000 38.0000 1 2 840
    rpt2985 0000000 68.0000 1 2 788
    That is very srange for me.
    Because, you wrote
    When I run the query this is what the results

    rpt2985 0000000 38.0000 1 2 788
    rpt2985 0000000 38.0000 1 2 840
    rpt2985 0000000 68.0000 1 2 788
    rpt2985 0000000 68.0000 1 2 840
    Both("if I don't add the ctyid ..." and "When I run the query ...") had same number of result columns.

    If you removed a column from your final SELECT list, like ...
    This is the result I get if I don't add the ctyid
    you must get the result of one less column.

  6. #6
    Join Date
    Jan 2013
    Posts
    16
    Here is the TAT field with the seqno - there is no amount field on the TAT table
    select seqno,expkey,ctyid,empno,rptid,expcode from TAT where rptid='rpt2985'
    1 1 840 00000001 rpt2985 LUINHP
    2 1 788 78800002 rot2985 LUINHP



    If I don't use the ctyid the results look like this but I need the ctyid
    rptid empno amount expkey max(seqno)
    rpt2985 0000000 38.0000 1 2
    rpt2985 0000000 68.0000 1 2


    If I don't use the amount the results look like this but I also need the amount

    rptid empno expkey max(seqno) ctyid
    rpt2985 0000000 1 2 788
    rpt2985 0000000 1 2 840

    If I could somehow get the query to just work so that my results would not have those 4 fields but instead 2 with the ctyid and the amount.
    Thanks a lot for your assistance. I really appreciate it.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If I don't use the amount the results look like this but I also need the amount

    rptid empno expkey max(seqno) ctyid
    rpt2985 0000000 1 2 788
    rpt2985 0000000 1 2 840
    only ctyid = 840 was related to empno = 00000001 in TAT table.
    ctyid = 788 was related to empno = 78800002.

    Do you want (1) or (2)?
    (1)
    rptid empno expkey max(seqno) ctyid
    rpt2985 0000000 1 2 840

    (2)
    rptid empno expkey max(seqno) ctyid
    rpt2985 0000000 1 2 788
    rpt2985 7880000 1 2 840

    If you want (2), how to relate each row in (2) with row in TRA?
    select parkey,amount,ctyid,rptid,expcode from TRA where rptid='rpt2985'
    1 68.0000 840 rpt2985 LUINHP
    1 38.0000 840 rpt2985 LUINHP
    Last edited by tonkuma; 06-14-13 at 23:16.

  8. #8
    Join Date
    Jan 2013
    Posts
    16
    I would like number2.
    TRA and TAT are join this way.
    tra.rptid=tat.rptid
    tra.parkey = tat.expkey

    both have an empno field.
    TaT empno field is more like the vistors empno, TRA empno is what I want, the employee field
    Tat has no amount field while TRA does have one.
    Tat has a seqno that keeps a count of the number of visitor per trip
    TRA has no seqno

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    TRA and TAT are join this way.
    tra.rptid=tat.rptid
    tra.parkey = tat.expkey
    You showed data like...
    select seqno,expkey,ctyid,empno,rptid,expcode from TAT where rptid='rpt2985'
    1 1 840 00000001 rpt2985 LUINHP
    2 1 788 78800002 rot2985rpt2985 LUINHP
    Note: I thought rot2985 should be rpt2985.
    and
    select parkey,amount,ctyid,rptid,expcode from TRA where rptid='rpt2985'
    1 68.0000 840 rpt2985 LUINHP
    1 38.0000 840 rpt2985 LUINHP
    Both rows of TAT had same value of rptid and same value of expkey.
    Both rows of TRA had same value of rptid and same value of parkey, and they are same values in TAT.

    As a consequence,
    the results of "TAT join TRA on tra.rptid=tat.rptid and tra.parkey = tat.expkey" might be
    Code:
    TAT  TRA
    ----- -----
    row 1 row 1
    row 1 row 2
    row 2 row 1
    row 2 row 2
    both have an empno field.
    You didn't show empno column in TRA.

  10. #10
    Join Date
    Jan 2013
    Posts
    16
    The empno on TRA is

    select parkey,amount,ctyid,rptid,expcode,empno from TRA where rptid='rpt2985'
    1 68.0000 840 rpt2985 LUINHP 0000000
    1 38.0000 840 rpt2985 LUINHP 0000000

    Thanks

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Both rows of TAT had same value of rptid and same value of expkey.
    Both rows of TRA had same value of rptid and same value of parkey, and they are same values in TAT.

    As a consequence,
    the results of "TAT join TRA on tra.rptid=tat.rptid and tra.parkey = tat.expkey" might be

    Code:
    TAT  TRA
    ----- -----
    row 1 row 1
    row 1 row 2
    row 2 row 1
    row 2 row 2
    Let's see concrete example using the data you provided...

    If data from TAT were like
    (2)
    rptid empno expkey max(seqno) ctyid
    rpt2985 0000000 1 2 788
    rpt2985 7880000 1 2 840

    how to relate each row in (2) with row in TRA?

    select parkey,amount,ctyid,rptid,expcode,empno from TRA where rptid='rpt2985'
    1 68.0000 840 rpt2985 LUINHP 0000000
    1 38.0000 840 rpt2985 LUINHP 0000000

    If "TAT join TRA on tra.rptid = tat.rptid and tra.parkey = tat.expkey" was used,
    tra.rptid = tat.rptid = rpt2985
    tra.parkey = tat.expkey = 1
    then the result might be
    Code:
            (TAT)                           (TRA)
    rptid   empno   expkey max(seqno) ctyid empno   amount
    ------- ------- ------ ---------- ----- ------- -------
    rpt2985 0000000 1      2          788   0000000 68.0000
    rpt2985 0000000 1      2          788   0000000 38.0000
    rpt2985 7880000 1      2          840   0000000 68.0000
    rpt2985 7880000 1      2          840   0000000 38.0000
    So, you sould add some more join conditions, if you want to get only two result rows.

  12. #12
    Join Date
    Jan 2013
    Posts
    16
    Tonkuma,

    Thanks for the assistance. I am going to play around with the query.
    Thanks

Posting Permissions

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