SQL "With ()" Statement is it supported? Work around?
I have the following SQL statement statements work individually but the "with VPS_EXT as ()" Statement breaks it,
is this function supported in QV? or do is there a easy work around besides loading two statements seperately and trying to make key to tie the 2 tables?:
SQL
with VPS_EXT as (
select
vp.ext_promo_nbr,
vp.tap_event_nbr,
eh2.name eh2_name,
vp.description,
vp.product_id1,
vp.pack_name,
mt.short_description,
msc.name msc_name,
vp.tap_event_status_cd,
vp.first_delivery_date,
vp.last_delivery_date,
vp.mop_cd,
vp.allow_amount,
max.unit_value,
vp.case_rate,
vp.forecast_nbr_cases,
vp.xcpn_date,
vp.create_date,
vp.create_user_name,
vp.last_modified_date
from "VISTACPG".vps_promotions_planner vp,
"VISTACPG".tap_xcpns tx,
"VISTACPG".merch_type mt,
"VISTACPG".entity_hierarchy eh2,
"VISTACPG".merchallow_xcpns max,
"VISTACPG".merch_sub_cd msc
where vp.ext_promo_nbr is not null
and tx.tap_event_key = vp.tap_event_key and tx.xcpn_level = 1
and mt.code = vp.merch_type
and mt.code = msc.merch_event_sub_cd
and max.tap_event_key = vp.tap_event_key(+) and max.merch_type = vp.merch_type(+) and max.xcpn_level = 1
and eh2.hierarchy_type = 2 and eh2.level_id in (7,8)
and eh2.entity_id = vp.acct_vista_number
and max.authorized_amount <> 0
)
select tp.year,
vpp.ext_promo_nbr,
tp.initiative_id,
vpp.tap_event_nbr,
tp.tpm_promo_nbr,
tp.group_to_lde,
vpp.eh2_name,
vpp.description,
tp.event_name,
vpp.product_id1,
vpp.pack_name,
vpp.short_description, tp.mf_merch_type,
tp.mf_allow_type,
tp.tpm_merch_type,
vpp.eh2_name,
vpp.tap_event_status_cd,
vpp.first_delivery_date,
tp.delivery_start,
vpp.last_delivery_date,
tp.delivery_end,
tp.xref_type,
tp.method_of_pay,
vpp.mop_cd,
vpp.allow_amount,
tp.planned_amount,
vpp.unit_value,
vpp.case_rate,
tp.allowance_rate,
vpp.forecast_nbr_cases,
tp.total_consumption,
vpp.xcpn_date,
tp.delete_date,
vpp.create_date,
vpp.create_user_name,
vpp.last_modified_date,
tp.load_date,
tp.intf_date
from VPS_EXT vpp, "VISTACPG".tpm_promotions tp
where vpp.ext_promo_nbr = tp.initiative_id
and vpp.short_description = tp.tpm_merch_type
and vpp.product_id1 = tp.pack_id_lde
and tp.delete_date is null
and (not (vpp.first_delivery_date = tp.delivery_start and vpp.last_delivery_date = tp.delivery_end)
OR tp.delete_date is not null and vpp.allow_amount <> 0
OR tp.xref_type = 'C' and vpp.unit_value <> tp.allowance_rate
OR tp.xref_type = 'S' and vpp.case_rate <> tp.allowance_rate
OR tp.xref_type = 'L' and (1 - (vpp.allow_amount / tp.planned_amount)) not between -.2 and .2)
order by vpp.eh2_name, vpp.first_delivery_date, vpp.tap_event_nbr, vpp.pack_name
select vp.ext_promo_nbr, vp.tap_event_nbr, eh2.name eh2_name, vp.description, vp.product_id1, vp.pack_name, mt.short_description, msc.name msc_name, vp.tap_event_status_cd, vp.first_delivery_date, vp.last_delivery_date, vp.mop_cd, vp.allow_amount, max.unit_value, vp.case_rate, vp.forecast_nbr_cases, vp.xcpn_date, vp.create_date, vp.create_user_name, vp.last_modified_date from "VISTACPG".vps_promotions_planner vp, "VISTACPG".tap_xcpns tx, "VISTACPG".merch_type mt, "VISTACPG".entity_hierarchy eh2, "VISTACPG".merchallow_xcpns max, "VISTACPG".merch_sub_cd msc where vp.ext_promo_nbr is not null and tx.tap_event_key = vp.tap_event_key and tx.xcpn_level = 1 and mt.code = vp.merch_type and mt.code = msc.merch_event_sub_cd and max.tap_event_key = vp.tap_event_key(+) and max.merch_type = vp.merch_type(+) and max.xcpn_level = 1 and eh2.hierarchy_type = 2 and eh2.level_id in (7,8) and eh2.entity_id = vp.acct_vista_number and max.authorized_amount <> 0
) AS vpp, "VISTACPG".tpm_promotions tp where vpp.ext_promo_nbr = tp.initiative_id and vpp.short_description = tp.tpm_merch_type and vpp.product_id1 = tp.pack_id_lde and tp.delete_date isnull and (not (vpp.first_delivery_date = tp.delivery_start and vpp.last_delivery_date = tp.delivery_end) OR tp.delete_date isnotnulland vpp.allow_amount <> 0 OR tp.xref_type = 'C' and vpp.unit_value <> tp.allowance_rate OR tp.xref_type = 'S' and vpp.case_rate <> tp.allowance_rate OR tp.xref_type = 'L' and (1 - (vpp.allow_amount / tp.planned_amount)) notbetween -.2 and .2) orderby vpp.eh2_name, vpp.first_delivery_date, vpp.tap_event_nbr, vpp.pack_name;
