Results 1 to 7 of 7

Thread: Query

  1. #1
    Join Date
    Mar 2004
    Posts
    22

    Unanswered: Query

    I have 2 tables

    printing;
    Name Null? Type
    ------------------------------- -------- ----
    JDF_NO VARCHAR2(8)
    ORD_QTY NUMBER(6)
    MC_NO NUMBER(4)
    OPERATOR_NO NUMBER(5)
    SUPERVISER_NO NUMBER(5)
    SECTION VARCHAR2(30)
    SHEET_REC NUMBER(10)
    SHEET_DLV NUMBER(10)
    PROS_DATE NOT NULL DATE
    J_T_IN NOT NULL DATE
    J_T_OUT DATE
    REMARK VARCHAR2(100)
    DEPARTMENT VARCHAR2(25)
    JOB_SPECI VARCHAR2(40)
    UPS NUMBER(2)
    PROS_NO VARCHAR2(7)
    WT_NO VARCHAR2(7)
    GD VARCHAR2(10)
    J_NAME VARCHAR2(40)
    STATUS NUMBER(2)
    F_O VARCHAR2(5)
    C_DATE DATE
    PRINT_STATUS VARCHAR2(2)
    SHIFT NUMBER(2)

    seconed is

    eff_operator_master;
    Name Null? Type
    ------------------------------- -------- ----
    TOKEN_NO NOT NULL NUMBER(5) (SAME AS SUPERVISER_NO,OPERATOR_NO)
    EMP_NAME VARCHAR2(45)
    DESG VARCHAR2(1) (S- SUPERVISER,O-OPERATOR)


    first i fired the query:
    SELECT ALL PRINTING.OPERATOR_NO, EFF_OPERATOR_MASTER.EMP_NAME, PRINTING.SHEET_REC,
    PRINTING.SHEET_DLV
    FROM PRINTING, EFF_OPERATOR_MASTER
    WHERE ((PRINTING.OPERATOR_NO = EFF_OPERATOR_MASTER.TOKEN_NO) AND (PRINTING.PROS_DATE BETWEEN TART_DATE AND :END_DATE AND PRINTING.PRINT_STATUS = 'W'))

    now i require the superviser name also so fired the query

    SELECT ALL PRINTING.OPERATOR_NO, EFF1.EMP_NAME, EFF2.EMP_NAME SUP_NAME, PRINTING.SHEET_REC,
    PRINTING.SHEET_DLV
    FROM PRINTING, EFF_OPERATOR_MASTER EFF1,EFF_OPERATOR_MASTER EFF2
    WHERE ((PRINTING.OPERATOR_NO = EFF1.TOKEN_NO) AND (PRINTING.SUPERVISER_NO = EFF2.TOKEN_NO) AND (PRINTING.PROS_DATE BETWEEN TART_DATE AND :END_DATE AND PRINTING.PRINT_STATUS = 'W'))

    NOW ITS NOT SHOWING ANY RECORDS.

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    22
    Can u provide some data?
    Regards,
    Jigar Bhavsar

  3. #3
    Join Date
    Mar 2004
    Posts
    22
    the above queries are used for a report which is breaking on operator no. with sum of sheet recd. and delivered.

    the data for the operator master is
    TOKEN_NO EMP_NAME D
    ---------- --------------------------------------------- -
    0001 Surendra O
    0002 ram singh O
    0003 Ramakant O
    0004 Rajesh O
    0005 ramesh O
    0006 Mahendra Singh O
    0007 Parshant Kumar O
    0008 Ram narayan O
    0009 Raghuveer O
    0010 harish O
    0011 Pankaj O
    0012 narender O
    0013 lakhim chand S
    0014 dhananjay S
    0015 Kailash chand S


    the data for the other table is just repetition of operator no. and supervisors.
    and in numeric fields u can take any values. in the required fields which we need in query.

  4. #4
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    I don't see anything wrong with the query, so I'd assume it's a data problem. Rerun the query as an outer join, as follows:

    SELECT ALL PRINTING.OPERATOR_NO, EFF1.EMP_NAME, EFF2.EMP_NAME SUP_NAME, PRINTING.SHEET_REC,
    PRINTING.SHEET_DLV
    FROM PRINTING, EFF_OPERATOR_MASTER EFF1,EFF_OPERATOR_MASTER EFF2
    WHERE ((PRINTING.OPERATOR_NO = EFF1.TOKEN_NO(+)) AND (PRINTING.SUPERVISER_NO = EFF2.TOKEN_NO(+)) AND (PRINTING.PROS_DATE BETWEEN TART_DATE AND :END_DATE AND PRINTING.PRINT_STATUS = 'W'))

    If you get results with null emp_names, you might have missing data.

  5. #5
    Join Date
    Mar 2004
    Posts
    22

    Thumbs up

    thanks a lot mr. actuary
    this is working......
    thanks again.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    without an outer join:
    PHP Code:
    SELECT 
      PRINTING
    .OPERATOR_NO,
      
    PRINTING.SUPERVISER_NO,
      
    EFF1.EMP_NAME
      (
    SELECT EFF2.EMP_NAME 
        FROM EFF_OPERATOR_MASTER EFF2 
        WHERE EFF2
    .TOKEN_NO PRINTING.SUPERVISER_NOSUP_NAME
      
    PRINTING.SHEET_REC
      
    PRINTING.SHEET_DLV
    FROM 
      PRINTING

      
    EFF_OPERATOR_MASTER EFF1
    WHERE 
      PRINTING
    .OPERATOR_NO EFF1.TOKEN_NO AND 
      
    PRINTING.PROS_DATE BETWEEN :START_DATE AND :END_DATE AND 
      
    PRINTING.PRINT_STATUS 'W' 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Mar 2004
    Posts
    22

    Thumbs up

    thanx a lot the_duck

Posting Permissions

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