With the following script I only want to extract records from VBAK where they exist in ZTRPST. The matching fields would be [ZORDER] from ZTRPST and [VBELN] from VBAK. I keep getting errors when I use the following stmt : SQL Select * FROM VBAK WHERE exists([%SalesOrderDocument_Key], [ZORDER]);
Thanks Thom
[ZTRPST]: // Time Reports - Spread Time Posted
Load
[ZORDER] & '/' & [ZACTIVITY] as [%SalesOrderDocumentItem_Key],
[ZTICKNO] as [Ticket Number_ZTICKNO],
[ZWDATE] as [Date in the week_ZWDATE],
[ZEMPEQP] as [Indicator for employee or equipment_ZEMPEQP],
[ZHOURS] as [Hours Registered_ZHOURS],
[ZHOMCTR] as [Cost Center_ZHOMCTR],
[ZORDER] as [Order Number_ZORDER],
[ZACTIVITY] as [Activity_ZACTIVITY],
[ZSTATUS] as [Job Status_ZSTATUS],
[ZFLGDMEM] as [Status flag for Debit Memo creation_ZFLGDMEM],
[ZOBJECT] as [Employee&Activity / Equip&Equip Type_ZOBJECT],
[ZORDER1] as [Order Number_ZORDER1],
[ZACTIVITY1] as [Activity_ZACTIVITY1],
[ZFLGSERV] as [Status Flag for Service Order Creation_ZFLGSERV],
[ZFLGCONF] as [Status Flag for Confirmations (IW41)_ZFLGCONF],
[ZSERVORDTYPE] as [_ZSERVORDTYPE],
[ZHOMCTR_SERVORD] as [Cost Center_ZHOMCTR_SERVORD],
[ZSTORM] as [_ZSTORM];
SQL Select ZORDER ZTICKNO ZWDATE ZEMPEQP ZHOURS ZHOMCTR ZSTATUS
ZACTIVITY ZFLGDMEM ZOBJECT ZORDER1 ZACTIVITY1 ZFLGSERV ZFLGCONF
ZSERVORDTYPE ZHOMCTR_SERVORD ZSTORM
from ZTRPST where ZWDATE >= '20090104' and ZEMPEQP = 'P' and
ZORDER between '000040000000' and '000049999999'
;
STORE * FROM [ZTRPST] INTO QVD\ZTRPST.QVD;
//DROP TABLE [ZTRPST];
//**********************************************************************************
// Table: VBAK - Sales Order Document Header
//**********************************************************************************
[VBAK]: // Sales Document: Header Data
Load
[VBELN] as [%SalesOrderDocument_Key],
[VKORG] as [Sales Organization_VKORG],
[VTWEG] as [Distribution Channel_VTWEG],
[SPART] as [Division_SPART],
[VBTYP] as [SD document categ._VBTYP],
[AUART] as [Sales Document Type_AUART],
[KURST] as [Exchange Rate Type_KURST],
[VBELN] as [Sales Document_VBELN],
[KUNNR] as [Sold-to party_KUNNR],
[VKBUR] as [Sales Office_VKBUR],
[VKGRP] as [Sales Group_VKGRP],
[AUFNR] as [Order_AUFNR],
[AUGRU] as [Order reason_AUGRU],
[WAERK] as [Document Currency_WAERK],
[GUEBG] as [Valid-from date_GUEBG],
[GUEEN] as [Valid-to date_GUEEN],
[STWAE] as [Statistics currency_STWAE],
[KVGR1] as [Customer group 1_KVGR1],
[KVGR2] as [Customer group 2_KVGR2],
[KVGR3] as [Customer group 3_KVGR3],
[KVGR4] as [Customer group 4_KVGR4],
[KVGR5] as [Customer group 5_KVGR5];
// [KOSTL] as [Cost Center_KOSTL],
// [NETWR] as [Net value_NETWR],
// [VGBEL] as [Reference document_VGBEL],
// [ERNAM] as [Created by_ERNAM],
// [ERZET] as [Time_ERZET],
// [ERDAT] as [Created on_ERDAT],
// [AEDAT] as [Changed on_AEDAT],
// [KNUMA] as [Agreement_KNUMA],
// [BSTNK] as [Purchase order no._BSTNK],
// [BSTDK] as [Purchase order date_BSTDK],
// [STAFO] as [Update group (stats)_STAFO],
// [ANGDT] as [Quotation valid from_ANGDT],
// [BNDDT] as [Quotation valid to_BNDDT],
// [AUDAT] as [Document Date_AUDAT],
// [TRVOG] as [Transaction group_TRVOG],
// [GWLDT] as [Guarantee_GWLDT],
// [SUBMI] as [Collective number_SUBMI],
// [LIFSK] as [Delivery block_LIFSK],
// [FAKSK] as [Billing block_FAKSK],
// [GSBER] as [Business Area_GSBER],
// [GSKST] as [Business area_GSKST],
// [KNUMV] as [Doc. condition no._KNUMV],
// [VDATU] as [Requested deliv.date_VDATU],
// [VPRGR] as [Prop.date type_VPRGR],
// [AUTLF] as [Complete delivery_AUTLF],
// [VBKLA] as [Original system_VBKLA],
// [VBKLT] as [Indicator_VBKLT],
// [KALSM] as [Pricing procedure_KALSM],
// [VSBED] as [Shipping Conditions_VSBED],
// [FKARA] as [Order-rel.bill.type_FKARA],
// [AWAHR] as [Probability_AWAHR],
// [KTEXT] as [Description_KTEXT],
// [BSARK] as [Purchase order type_BSARK],
// [BSTZD] as [Supplement_BSTZD],
// [IHREZ] as [Your Reference_IHREZ],
// [BNAME] as [Name_BNAME],
// [TELF1] as [Telephone_TELF1],
// [MAHZA] as [Number of contacts_MAHZA],
// [MAHDT] as [Last contact date_MAHDT],
// [KOKRS] as [Controlling Area_KOKRS],
// [PS_PSP_PNR] as [WBS Element_PS_PSP_PNR],
// [KKBER] as [Credit control area_KKBER],
// [KNKLI] as [Credit account_KNKLI],
// [GRUPP] as [Cust.cred.group_GRUPP],
// [SBGRP] as [Credit rep.group_SBGRP],
// [CTLPC] as [Risk category_CTLPC],
// [CMWAE] as [Currency_CMWAE],
// [CMFRE] as [Release date_CMFRE],
// [CMNUP] as [Next check_CMNUP],
// [CMNGV] as [Next date_CMNGV],
// [AMTBL] as [Rel. credit value_AMTBL],
// [HITYP_PR] as [HierarchyTypePricing_HITYP_PR],
// [ABRVW] as [Usage_ABRVW],
// [ABDIS] as [MRP for DlvSchType_ABDIS],
// [OBJNR] as [Object no.header_OBJNR],
// [BUKRS_VF] as [CCode to be billed_BUKRS_VF],
// [TAXK1] as [Alt.tax classific._TAXK1],
// [TAXK2] as [TaxClass2-Cust._TAXK2],
// [TAXK3] as [TaxClass3-Cust._TAXK3],
// [TAXK4] as [TaxClass4-Cust._TAXK4],
// [TAXK5] as [TaxClass5-Cust._TAXK5],
// [TAXK6] as [TaxClass6-Cust._TAXK6],
// [TAXK7] as [TaxClass7-Cust._TAXK7],
// [TAXK8] as [TaxClass8-Cust._TAXK8],
// [TAXK9] as [TaxClass9-Cust._TAXK9],
// [XBLNR] as [Reference_XBLNR],
// [ZUONR] as [Assignment_ZUONR],
// [VGTYP] as [Preceding doc.categ._VGTYP],
// [KALSM_CH] as [Search procedure_KALSM_CH],
// [AGRZR] as [Accrual period_AGRZR],
// [QMNUM] as [Notification_QMNUM],
// [VBELN_GRP] as [Master contract_VBELN_GRP],
// [SCHEME_GRP] as [Group Ref. Procedure_SCHEME_GRP],
// [ABRUF_PART] as [Check partner auth._ABRUF_PART],
// [ABHOD] as [Pick up date_ABHOD],
// [ABHOV] as [Pick up time_ABHOV],
// [ABHOB] as [Pick up time_ABHOB],
// [RPLNR] as [Paym.card plan no._RPLNR],
// [VZEIT] as [Requested dely time_VZEIT],
// [STCEG_L] as [Tax dest. country_STCEG_L],
// [LANDTX] as [Tax depart. country_LANDTX],
// [XEGDR] as [EU triangular deal_XEGDR],
// [ENQUEUE_GRP] as [_ENQUEUE_GRP],
// [DAT_FZAU] as [CmlQtyDate_DAT_FZAU],
// [FMBDAT] as [Material Avail. Date_FMBDAT],
// [VSNMR_V] as [Version_VSNMR_V],
// [CONT_DG] as [Contains DG_CONT_DG],
// [PROLI] as [DG mgmt profile_PROLI],
// [MTLAUR] as [Target Incom_MTLAUR],
// [PHASE] as [Sales Phase_PHASE],
// [HANDLE] as [Int.ID_HANDLE],
// [KALCD] as [Proc. Camp.Determin._KALCD],
// [LOGSYSB] as [Logical system_LOGSYSB],
// [CRM_GUID] as [Char 70_CRM_GUID],
// [SWENR] as [Business entity_SWENR],
// [SMENR] as [Unit Number_SMENR],
// [STAGE] as [Construction stage_STAGE],
// [HB_CONT_REASON] as [Contingency reason_HB_CONT_REASON],
// [HB_EXPDATE] as [Expiration date_HB_EXPDATE],
// [HB_RESDATE] as [Resolution date_HB_RESDATE],
// [MULTI] as [Multiple Promotions_MULTI],
// [ZZDT_DOORHANG] as [Date of Door Hanger_ZZDT_DOORHANG],
// [ZZDT_POSTCARD] as [Date of Post Card Mailing_ZZDT_POSTCARD],
// [ZZDT_MAILED] as [Date Mailed_ZZDT_MAILED],
// [ZZDT_FOLLOWUP] as [Follow-Up Mailing Date_ZZDT_FOLLOWUP]
//SQL Select * FROM VBAK where GUEBG >= '$(vYear)' and AUART = '$(vDocument)';
SQL Select * FROM VBAK WHERE exists([%SalesOrderDocument_Key], [ZORDER]);
store VBAK into qvd\VBAK.qvd;
