SQL "With ()" Statement is it supported? Work around?

[patrickanderson]

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

[jagan]

Hi,

Try this query like this

Select * from

(Your with Query) AS VPS_EXT

WHERE ------------------;

SQL
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 (

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 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;

Hope this helps you.

Regards,

Jagan.