1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query returns duplicate columns

Discussion in 'SQL PL/SQL' started by skister77, Jul 13, 2017.

  1. skister77

    skister77 Starter

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    NC
    I have this query:
    Code (SQL):
    SELECT md.PCB_TYPE, sm.STEP_MACHINE, sm.DESCRIPTION AS STATION, md.MODEL_NUMBER AS MODEL, md.MODELID, md.NUM_OF_BOARDS AS PANELS, ct.CYCLE_TIME
    FROM ECU_STEP_MACHINE sm
      LEFT OUTER JOIN ECU_STEP_LINES sl ON sm.LINE_ID = sl.LINE_ID
      LEFT OUTER JOIN ECU_LINE_ASSIGN la ON sl.LINE_NUMBER = la.LINE_NUMBER
      INNER JOIN ECU_MODEL_DEFINITION md ON la.MODEL_NUMBER = md.MODEL_NUMBER
        AND la.MODEL_YEAR = md.MODEL_YEAR
        AND la.PROD_TYPE = md.MODEL_PROD_TYPE
        AND la.DESIGN_LEVEL = md.PROD_DESIGN_LEVEL
      LEFT OUTER JOIN OEE_CYCLETIME ct ON sm.STEP_MACHINE = ct.STEP_MACHINE AND md.MODELID = ct.MODELID
    WHERE sm.STEP_MACHINE > 50000 AND sl.LOCATION_ID = 3 AND md.ACTIVE = 1 AND md.MODEL_PROD_TYPE = 'M'
    GROUP BY md.PCB_TYPE, sm.STEP_MACHINE, sm.DESCRIPTION, md.MODEL_NUMBER, md.MODELID, md.NUM_OF_BOARDS, ct.CYCLE_TIME
    ORDER BY sm.DESCRIPTION;
    returns:
    AC1 75006 AGING6 8161 12781 4
    AC1 75006 AGING6 8307 11380 4
    AC1 75006 AGING6 8335 9219 4
    AC1 75006 AGING6 8336 10231 4
    AC1 75006 AGING6 8337 10763 4
    AC1 75006 AGING6 8339 9231 4
    AC1 75006 AGING6 8348 11386 4
    AC1 75006 AGING6 8349 11393 4
    AC1 75006 AGING6 8350 10778 4
    AC1 75006 AGING6 8351 10784 4
    AH2 75006 AGING6 6469 13038 4
    AH2 75006 AGING6 8168 13136 4
    AH2 75006 AGING6 8342 13674 4

    What I want is all the pcb_types grouped (AC1 AH2, etc.)
    Thanks for any help...
    Mark
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,531
    Likes Received:
    360
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post create table statements and provide sample data so we can work with the same tables/data you're working with. Yon can't get answers without providing that information.
     
  3. skister77

    skister77 Starter

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    NC
    I posted the data in my original post.

    CREATE TABLE ECU_STEP_MACHINE
    (
    STEP_MACHINE NUMBER NOT NULL
    , DESCRIPTION VARCHAR2(30 BYTE)
    )

    CREATE TABLE "ECU_STEP_LINES"
    ( "LINE_ID" NUMBER(2,0),
    "LOCATION_ID" NUMBER(2,0),
    "LINE_NUMBER" NUMBER(2,0),
    "DESCRIPTION" VARCHAR2(50 BYTE)
    )

    CREATE TABLE "KCST"."ECU_LINE_ASSIGN"
    ( "LINE_NUMBER" NUMBER(2,0),
    "MODEL_NUMBER" VARCHAR2(4 BYTE),
    "MODEL_YEAR" NUMBER(4,0),
    "DESIGN_LEVEL" VARCHAR2(10 BYTE),
    "PROD_TYPE" VARCHAR2(1 BYTE),
    "STATUS" NUMBER(1,0),
    "BADGE" NUMBER,
    "CHANGE_DATE_TIME" DATE
    )

    CREATE TABLE FOL.ECU_MODEL_DEFINITION
    (
    MODEL_NUMBER VARCHAR2(4 BYTE) NOT NULL
    , MODEL_YEAR NUMBER NOT NULL
    , MODEL_PROD_TYPE VARCHAR2(1 BYTE) NOT NULL
    , PROD_DESIGN_LEVEL VARCHAR2(10 BYTE) NOT NULL
    , PCB_TYPE VARCHAR2(6 BYTE)
    , NUM_OF_BOARDS NUMBER
    , MODELID NUMBER
    )

    CREATE TABLE "KCST"."OEE_CYCLETIME"
    ( "STEP_MACHINE" NUMBER,
    "MODELID" NUMBER,
    "CYCLE_TIME" NUMBER(5,1)
    )
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,531
    Likes Received:
    360
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your original post shows the RESULTS you want ,not the complete set of data.

    Post insert statements, please, to populate these tables.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    733
    Likes Received:
    143
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    What is the composition of fields in the result need?
    I suppose, need to identify the key by aggregation.
    The values that changing ,need to aggregate : in a string, or in a nested table , or to exclude from result.
     
  6. skister77

    skister77 Starter

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    NC
    I finally figured it out, had to remove 2 columns from the query. Thanks everyone for assisting.