Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2001
    Location
    fairview, tn
    Posts
    8

    Unanswered: Join 2 SQL statements

    I am having a difficulty joining these two statements. I would like union them and get one set of unique data. Currently, there is overlap in the part_no and eff_dt. Any thoughts?

    SELECT A.FCLTY_NO, A.VIN_PFX_CD||A.VIN_SER_NO AS VIN, A.WO_NO, A.WOL_NO, B.WO_OPEN_DT, C.EFF_DT, B.WO_COMPL_DT,
    A.PART_NO, C.DLR_NET_PRC_AM
    FROM DRBA.SVC_WOL_PART_T A,
    DRBA.SVC_WO_T B,
    DRBA.PRT_PART_PRC_T C
    WHERE A.FCLTY_NO = B.FCLTY_NO
    AND A.VIN_PFX_CD = B.VIN_PFX_CD
    AND A.VIN_SER_NO = B.VIN_SER_NO
    AND A.WO_NO = B.WO_NO
    AND A.PART_NO = C.PART_NO
    AND A.FCLTY_NO = '2261'
    AND B.LAST_SUBM_DT BETWEEN '01NOV2006' AND '30APR2007'
    AND C.EFF_DT BETWEEN B.WO_OPEN_DT AND B.WO_COMPL_DT
    AND A.PART_NO LIKE '%9MP%'
    ORDER BY A.WO_NO, A.WOL_NO, A.PART_NO

    SELECT FCLTY_NO, VIN, WO_NO, WOL_NO, WO_OPEN_DT, EFF_DT, PART_NO, DLR_NET_PRC_AM
    FROM (SELECT DISTINCT A.FCLTY_NO, A.VIN_PFX_CD||A.VIN_SER_NO AS VIN, A.WO_NO, A.WOL_NO, B.WO_OPEN_DT, C.PART_NO, C.DLR_NET_PRC_AM,
    C.EFF_DT
    FROM DRBA.SVC_WOL_PART_T A,
    DRBA.SVC_WO_T B,
    DRBA.PRT_PART_PRC_T C
    WHERE A.FCLTY_NO = B.FCLTY_NO
    AND A.VIN_PFX_CD = B.VIN_PFX_CD
    AND A.VIN_SER_NO = B.VIN_SER_NO
    AND A.WO_NO = B.WO_NO
    AND A.PART_NO = C.PART_NO
    AND A.FCLTY_NO = '2261'
    AND B.LAST_SUBM_DT BETWEEN '01NOV2006' AND '30APR2007'
    AND A.PART_NO LIKE '%9MP%'
    AND C.EFF_DT = (SELECT MAX(EFF_DT)
    FROM DRBA.PRT_PART_PRC_T D
    WHERE C.PART_NO = D.PART_NO
    AND EFF_DT < B.WO_OPEN_DT)
    ORDER BY A.WO_NO, A.WOL_NO, C.PART_NO)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    add a NULL in between EFF_DT and PART_NO in the SELECT clause of the second query


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, and also remove the ORDER BY clauses, and add a new ORDER BY clause at the end, after the two SELECTs

    and of course use UNION between them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2001
    Location
    fairview, tn
    Posts
    8
    Not sure what you mean by add NULL between them....please explain.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Code:
    SELECT ..., CAST(NULL AS INT) AS part_no, ...
    Also, if you know that all records are unique, use UNION ALL. UNION implies a duplicate elimination.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Rudy means, that you don't have the same number of columns in each select statement, and as a result they won't line up on top of each other, hence your error.

    Rule of thumb : For union statements you MUST have the same number of columns in each select statement involved.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Technically, that's not a rule of thumb. Its just a plain-old rule!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I have it written on MY thumb

Posting Permissions

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