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)

add a NULL in between EFF_DT and PART_NO in the SELECT clause of the second query

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

Not sure what you mean by add NULL between them....please explain.

Also, if you know that all records are unique, use UNION ALL. UNION implies a duplicate elimination.

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.

Technically, that's not a rule of thumb. Its just a plain-old rule!

I have it written on MY thumb