1 2 Previous Next 16 Replies Latest reply: Oct 11, 2012 5:58 AM by chris227 Go to original post RSS
      • 15. Re: group by returning more number of rows
        Frank Kulash
        Hi,
        makdutakdu wrote:
        hi

        the date 12/9/2012 is the invoice date for materials alone
        the date 12/19/2012 is the invoice date for services alone
        The question is: How do you know this? What is there in the tables that tells you that 12/9/2012 is the invoice date for materials, and not the invoice date for services? Explain, step by step, how the 'Materials' row in the gen table is related to the 12/9/2012 row in the rac table.
        • 16. Re: group by returning more number of rows
          chris227
          I dont see the need to use group by.
          You should relate the tran_type to the customer via a separate column (best extra table tran_type and foreign keys) rather than via the substrings.
          SELECT
           opn_job_desc
          ,seq
          ,tran_type
          ,customer_name
          ,customer_number
          ,trx_date 
          FROM
           napesco_log_generic_sum gen
          ,per_people_x eng
          ,per_people_x cell
          ,ra_customer_trx_all cust
          WHERE
           gen.engineer1 = eng.employee_number(+)  
           AND gen.cell_leader = cell.employee_number(+)  
           and gen.opn_job_desc=cust.attribute1(+)
           and substr(gen.tran_type,1,1) =  substr(cust.attribute7(+),length(cust.attribute7(+)),1)
           and gen.opn_job_desc='J/D/UD102/SF31/958/D/1211/1'
          
          OPN_JOB_DESC SEQ TRAN_TYPE CUSTOMER_NAME CUSTOMER_NUMBER TRX_DATE 
          J/D/UD102/SF31/958/D/1211/1 1112083 Services Joint Operations (KGOC / SAC) 1002 12/19/2011 
          J/D/UD102/SF31/958/D/1211/1 1112082 Material Joint Operations (KGOC / SAC) 1002 12/09/2011 
          Edited by: chris227 on 11.10.2012 03:57
          1 2 Previous Next