Is it possible to gather fields from two tables in SWE? We are trying to use a UNION statement and it will not work. We want vew_cable_ex and vew_manufacturerparts_ex in the same report. Each time I apply and test, the UNION and everything after disappears.
SELECT DISTINCT
bom_manufacturer
,bom_reference
,bom_tra_0_l1
,bom_use_data0
,com_tag
,bun_tag
,com_type
,loc_text
,vcomcom_com_tag
,1 AS %ELEMENT_COUNT%
FROM
vew_manufacturerparts_ex
WHERE
bun_id = %CUR_BUNDLE_ID%
AND bom_assemblyid = -1
AND bom_objecttype != 9
ORDER BY vcomcom_com_tag ASC,com_tag ASC
UNION
SELECT DISTINCT
cab_tag
FROM
vew_cable_ex
WHERE
vew_cable_ex.cab_tag != ]][[
Jeremiah, for a UNION to work, you need to have "2 sides" to the query where ALL aliases match from both cable and part sections of the query have an equal number of variables chosen AND identically named ALIASES....ie find the PART variable for REFERENCE and name it with your alias, and do the same for CABLE keeping the SAME ALIAS. For example (feel free to copy this into a query and test...) With VIEWS this become really doable, note this has some special sauce where anything in "F2" is not in the BOM and the report is broken up by book...for Cables, the ORIGIN component of the cable drives the book (bundle) division.
SELECT
vew_cable_ex.cab_manufacturer AS manufacturer
, vew_cable_ex.cab_reference AS reference
, vew_cable_ex.cab_tra_0_l1 AS tra_0
, vew_cable_ex.cab_tag AS tag
, vew_cable_ex.cab_id AS bom_id
, vew_cable_ex.cab_tagorderno AS com_id
, vew_cable_ex.fun_text AS fun
, vew_cable_ex.vcablocfrom_loc_bun_id AS bookid
, vew_cable_ex.cab_articlename AS articlename
,tew_bundle.bun_tag AS bun_tag
,1 AS %ELEMENT_COUNT%
FROM vew_cable_ex
LEFT JOIN tew_bundle ON tew_bundle.bun_id = vew_cable_ex.vcablocfrom_loc_bun_id
WHERE vew_cable_ex.fun_text != ]]F2[[ AND vew_cable_ex.vcablocfrom_loc_bun_id = %CUR_BUNDLE_ID%
UNION
SELECT DISTINCT
vew_manufacturerparts_ex.bom_manufacturer AS manufacturer
, vew_manufacturerparts_ex.bom_reference AS reference
, vew_manufacturerparts_ex.bom_tra_0_l1 AS tra_0
, vew_manufacturerparts_ex.com_parenttagpath AS tag
, vew_manufacturerparts_ex.bom_orderno AS bom_id
, vew_manufacturerparts_ex.com_id AS com_id
, vew_manufacturerparts_ex.fun_text AS fun
, vew_manufacturerparts_ex.bun_id AS bookid
, vew_manufacturerparts_ex.bom_articlename AS articlename
, vew_manufacturerparts_ex.bun_tag AS bun_tag
,1 AS %ELEMENT_COUNT%
FROM vew_manufacturerparts_ex
WHERE vew_manufacturerparts_ex.fun_text != ]]F2[[ AND vew_manufacturerparts_ex.bun_id = %CUR_BUNDLE_ID% ORDER BY bun_tag ASC,articlename ASC,manufacturer ASC,reference ASC