Hi Im running the below query and its running more than 3 hrs.but still there is no single records found in the table. the source table has60 million records apprx.Now my question is where the storage is happening temporarily.I believe it is in temporary database (tempdb).if that's the case how to check the table's from it.insert into dbo.Dat_MRC_FAGLFLEXB_FY1333333 ( Account , Company , CostCtr , ProfitCtr , Product , Market , Format , Territory , SourceCode , RollForwardActivity , DocType , ReferenceYear , Customer , FilmDimension , Vendor , InternalOrder , TradingPartner ,Currency , FiscalYr , Period , FTCurr , Turnover , FISCPER ,Count ) SELECT Account , Company , CostCtr , ProfitCtr , Product , Market , Format , Territory , SourceCode , RollForwardActivity , DocType , ReferenceYear , Customer , FilmDimension , Vendor , InternalOrder , TradingPartner -- , SUM( Currency ) AS CURTYPE -- For testing only comment out in production , LEFT( CURRUNITforCURTYPE__LocCcy_DocCcy, 3 ) + CASE SUM( Currency ) WHEN 1 THEN ' DocCcy-s' WHEN 2 THEN ' LocCcy-s' WHEN 4 THEN ' GrCcy-s' WHEN 8 THEN ' GlCcy-s' WHEN 3 THEN ' LocDocCcy-c' WHEN 5 THEN ' GrDocCcy-c' WHEN 6 THEN ' GrLocCcy-c' WHEN 7 THEN ' GRLocDocCcy-c' WHEN 9 THEN ' GlDocCcy-c' WHEN 10 THEN ' GlLocCcy-c' WHEN 11 THEN ' GlLocDocCcy-c' ELSE 'Error - SUM( Currency ) = ' + CAST( SUM( Currency ) AS nvarchar(10) ) + ' should never exist as a possible 1,2,3 way sum of the values 1,2,4,8' END AS Currency , FiscalYr , Period , FTCurr , Turnover , FISCPER -- , CURRUNITforCURTYPE__LocCcy_DocCcy -- For testing only comment out in production , COUNT(*) AS DuplicateCount -- For testing only but DO NOT comment out in production needed for reconcilliation FROM ( SELECT CASE WHEN RACCT = '' OR RACCT IS NULL THEN 'No_Account' ELSE 'AC_' + RACCT + '' END AS Account,CASE WHEN RBUKRS = '' OR RBUKRS IS NULL THEN 'No_CoCode' ELSE 'CO_' + RBUKRS + '' END AS Company,CASE WHEN RCNTR = '' OR RCNTR IS NULL THEN 'No_CostCenter' ELSE 'CC_' + RCNTR + '' END AS CostCtr,CASE WHEN PRCTR = '' OR PRCTR IS NULL THEN 'No_ProfitCenter' ELSE 'PC_' + PRCTR + '' END AS ProfitCtr,CASE WHEN ZZMPMPRD = '' OR ZZMPMPRD IS NULL THEN 'No_Product' ELSE /*'PR_' + */ right(ZZMPMPRD, len(ZZMPMPRD)+1 - patindex('%[^0]%', ZZMPMPRD)) /*+ ''*/ END AS Product,CASE WHEN ZZMARKET = '' OR ZZMARKET IS NULL THEN 'No_Market' ELSE 'MK_' + ZZMARKET + '' END AS Market,CASE WHEN ZZFORMAT = '' OR ZZFORMAT IS NULL THEN 'No_Format' ELSE 'FT_' + ZZFORMAT + '' END AS Format,CASE WHEN ZZTERTRY = '' OR ZZTERTRY IS NULL THEN 'No_Territory' ELSE 'TR_' + ZZTERTRY + '' END AS Territory,CASE WHEN ZZSRCSYS = '' OR ZZSRCSYS IS NULL THEN 'No_System' ELSE 'SY_' + ZZSRCSYS END AS SourceCode,CASE WHEN ZZRFACT = '' OR ZZRFACT IS NULL THEN 'No_RollForwardActivity' ELSE 'RA_' + ZZRFACT + '' END AS RollForwardActivity,CASE WHEN ZZBLART = '' OR ZZBLART IS NULL THEN 'No_DocType' ELSE 'DT_' + ZZBLART END AS DocType,CASE WHEN STR(ZZREFYR,4,0) = ' 0' OR ZZREFYR IS NULL THEN 'No_ReferenceYear' ELSE 'RY_' + STR(ZZREFYR,4,0) + '' END AS ReferenceYear,CASE WHEN ZZSPKUNNR = '' OR ZZSPKUNNR IS NULL THEN 'No_Customer' ELSE 'CN_' + ZZSPKUNNR + '' END AS Customer,CASE WHEN ZZFLMDIM = '' OR ZZFLMDIM IS NULL THEN 'No_FilmDimension' ELSE '' + ZZFLMDIM + '' END AS FilmDimension,CASE WHEN ZZVENDOR = '' OR ZZVENDOR IS NULL THEN 'No_Vendor' ELSE 'VN_' + ZZVENDOR + '' END AS Vendor,CASE WHEN ZZAUFNR = '' OR ZZAUFNR IS NULL THEN 'No_InternalOrder' ELSE 'IO_' + ZZAUFNR + '' END AS InternalOrder,CASE WHEN RASSC = '' OR RASSC IS NULL THEN 'No_TradingPartner' ELSE 'TP_' + right(RASSC, len(RASSC)+1 - patindex('%[^0]%', RASSC)) + '' END AS TradingPartner,CASE WHEN CURTYPE = '' OR CURTYPE IS NULL THEN NULL ELSE CASE CURTYPE WHEN '00' THEN 1 WHEN '10' THEN 2 WHEN '30' THEN 4 WHEN '60' THEN 8 ELSE 1000 + CURTYPE END END AS Currency,CASE WHEN CURTYPE = '' OR CURTYPE IS NULL THEN 'No_CurrencyType' ELSE 'USD-GrCcy, ' + CASE CURTYPE WHEN '00' THEN '' WHEN '10' THEN '' WHEN '30' THEN '' WHEN '60' THEN CURRUNIT + '-GlCcy, ' ELSE CURRUNIT + '-' + CURTYPE + '-Ccy, ' END END + FunctionalCurrency + '-LocCcy, ' + RTCUR + '-DocCcy' AS FTCurr,CASE WHEN FISCPER = 0 OR FISCPER IS NULL THEN 'No_FiscalYr' ELSE 'FY' + substring(str(FISCPER,7,0),3,2) + '' END AS FiscalYr,CASE WHEN FISCPER = 0 OR FISCPER IS NULL THEN 'No_Period' WHEN charindex(RIGHT(str(FISCPER,7,0),2),'01020304050607080910111200')=25 THEN 'BegBalance' ELSE substring( 'OctNovDecJanFebMarAprMayJunJulAugSep' , charindex( '-'+RIGHT(str(FISCPER,7,0),2) , '-01-02-03-04-05-06-07-08-09-10-11-12-00' ), 3 ) END AS Period,FISCPER,sum(TURNOVER) AS Turnover,CURRUNIT + FunctionalCurrency + RTCUR AS CURRUNITforCURTYPE__LocCcy_DocCcy FROM ( select * from Dat_EAI_FAGLFLEXB WITH (NOLOCK)) q LEFT OUTER JOIN DimDetailCompanyVw on RBUKRS = Company WHERE ( (DEBIT IS NOT NULL AND ABS(DEBIT) >.00000001) OR (CREDIT IS NOT NULL AND ABS(CREDIT) >.00000001) OR (TURNOVER IS NOT NULL AND ABS(TURNOVER) >.00000001))AND ( CURTYPE = '00' OR CURTYPE = '10' OR CURTYPE = '30' )AND CURRUNIT IS NOT NULL AND RTCUR IS NOT NULL and CURRUNIT <> '' AND RTCUR <> ''and fiscper like '2013%'Please correct me if i'm wrong..
Temporary Storage
Posted - 2013-07-12 : 13:01:33
