The following code is suppose to return to me a list of all products a bottler did NOT purchase. The first query returns all the products that a bottler did purchase.
I then CROSS JOIN to the second derived table where a the product is not in the first query.... however I am doing this on teradata and running into spool space issues.
The first query runs fine by itself. However it spools out when ran with CROSS JOIN. ANy other methods I could take to avoid a CROSS JOIN?
SELECT distinct a.BTLR_DLVR_PNT_NO
,Bottler_Name
, b. BrandPack
FROM
(SELECT distinct
inv_corp.Inv_Dt as invoice_date
,snap.BTLR_DLVR_PNT_NO AS BTLR_DLVR_PNT_NO
,btlr_branch_nm as Bottler_Name
,prodet.prd_nm as product
, inv_cat_type_cd as cattype
FROM corp_invoice_views.invoice_corp inv_corp
JOIN CORP_LINES_views.LINE_CORP line_corp
ON inv_corp.Tsr_Inv_Ref_No = line_corp.Tsr_Inv_Ref_No
AND inv_corp.Inv_Dt = line_corp.Inv_Dt
JOIN CHR_VIEWS.DPT_SNAPSHOT_SELECT snap
ON inv_corp.BTLR_DLVR_PNT_NO = snap.BTLR_DLVR_PNT_NO
and inv_corp.OWNRSHP_ID = snap.OWNRSHP_ID
Join NSR_VIEWS.PRODET prodet
ON prodet.cce_material_no = line_corp.cce_material_no
Join CBS_ASR_VIEWS.CBS_MATL_ENTRPRS_PKG_ROLLUP entpkg
ON prodet.CCE_MATERIAL_NO = entpkg.CCE_MATERIAL_NO
Where 1=1
AND prodet.First_Sale_Dt IS NOT NULL
AND line_corp.Inv_DT >= (current_date - 14)-- Looks back 2weeks
and snap.ownrshp_id = 65----Bottler Heartland's ownershipid
) as A
CROSS JOIN
(Select distinct prodet.prd_nm,
brnd.Brnd_Nm||' '||entpkg.ENTRPRS_PKG_DESC AS BrandPack
From NSR_VIEWS.PRODET prodet
JOIN CBS_ASR_VIEWS.CBS_MATL_ENTRPRS_PKG_ROLLUP AS entpkg
ON prodet.CCE_MATERIAL_NO = entpkg.CCE_MATERIAL_NO
JOIN NSR_VIEWS.Brand AS brnd
ON prodet.Cce_Brand_Flvr = brnd.Brnd_No
) as B
where a.product <> b.prd_nm
--and a.BTLR_DLVR_PNT_NO = '9990000005089' ----Bottler Heartland
and a.cattype IN ('D', 'B')
Order by Bottler_Name
;