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

patrickanderson — Tue, 03 Apr 2012 17:35:36 GMT

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

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

jagan — Wed, 04 Apr 2012 04:37:00 GMT

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

) ASvpp, "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.

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

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

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