This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Oct 11, 2012 3:58 AM by chris227 Go to original post RSS
  • 15. Re: group by returning more number of rows
    Frank Kulash Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points