I have an error that I cannot resolve. Below is the outlined error along with my SQL qvd load script. Any help narrowing down how to resolve this error is greatly appreciated.
Error:
Table not found STORE WorkGap into [..\QVD\01_Extract\WorkGap.qvd](qvd)
QVD Script:
WorkGap: SQL IF OBJECT_ID('tempdb..#transfers') IS NOT NULL drop table #transfers select tx.BrAcct ,tx.BrNbr ,tx.OldBrAcct ,case when LEN(tx.OldBrAcct) = 7 then LEFT(tx.OldBrAcct,1) when LEN(tx.OldBrAcct) = 8 then LEFT(tx.OldBrAcct,2) when LEN(tx.OldBrAcct) = 9 then LEFT(tx.OldBrAcct,3) end as OldBrNbr ,tx.ConversionDt ,DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, tx.ConversionDt) + 1, 0)) as ConversionDtEOM
into #transfers
FROM Reports.dbo.Transfers tx with (nolock)
where tx.OldBrAcct is not null and tx.BrNbr = 22 and tx.ConversionDt between DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and GETDATE() IF OBJECT_ID('tempdb..#EOMDelq') IS NOT NULL Drop Table #EOMDelq Select LA.BrAcct ,LA.BrNbr ,CASE WHEN LA.Delq = 0 THEN 'PaidAhead' WHEN LA.Delq IN (1,2) THEN 'CurrentMonth' WHEN LA.Delq = 3 THEN 'P30' WHEN LA.Delq = 6 THEN '30Day' WHEN LA.Delq = 9 THEN '60Day' WHEN LA.Delq = 12 THEN '90Day' WHEN LA.Delq = 15 THEN '120Day' WHEN LA.Delq = 18 THEN '150Day' WHEN LA.Delq = 21 THEN '180Day' ELSE 'PaidAhead' END as Delq Into #EOMDelq From Reports.dbo.LoanArchive LA with(nolock) WHERE LA.MonthEnd = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) IF OBJECT_ID('tempdb..#CollectionComments') IS NOT NULL Drop Table #CollectionComments Select MAX(convert(date,CC.ActionDt,101)) as ActionDate ,CC.BrAcct Into #CollectionComments From Reports.dbo.Collections CC with(nolock) Where CC.ActionCd IN ('NP','PP','F10') Group By CC.BrAcct
Select convert(date,GETDATE(),101) as 'AsOfDate' ,LN.BrAcct ,LD.Balance ,BW.LastName ,BW.FirstName ,BW.MiddleName ,convert(date,LD.DtLstPd,101) as DtLstPd ,convert(date,LD.NxtDueDt,101) as NxtDueDt ,CASE WHEN LN.CollateralCd = 221 THEN 'Secured -- Auto' WHEN LN.CollateralCd = 235 THEN 'Unsecured' WHEN LN.CollateralCd = 240 THEN 'Secured -- Personal Property' WHEN LN.CollateralCd in(260,262) THEN 'Secured -- Boat Motorcycle or RV' ELSE 'Other' END AS SecurityType ,CASE WHEN ED.BrNbr = 22 THEN 'Y' ELSE 'N' END AS 'PriorEOMBr22?' ,ED.Delq as PriorEOMDelq ,CASE WHEN LD.Delq = 0 THEN 'CurrentMonth' WHEN LD.Delq IN (1,2) THEN 'P30' WHEN LD.Delq = 3 THEN '30Day' WHEN LD.Delq = 6 THEN '60Day' WHEN LD.Delq = 9 THEN '90Day' WHEN LD.Delq = 12 THEN '120Day' WHEN LD.Delq = 15 THEN '150Day' WHEN LD.Delq = 18 THEN '180Day' WHEN LD.Delq = 21 THEN '210Day' ELSE 'PaidAhead' END as Delq ,CASE WHEN LD.LegalStat > 0 and LD.JDDate IS NULL THEN 1 ELSE NULL END as LglFlag ,CASE WHEN LD.JDDate IS NOT NULL and LD.LegalStat = 0 THEN 1 ELSE NULL END as JDFlag ,CASE WHEN LD.LegalStat > 0 and LD.JDDate IS NOT NULL THEN 1 ELSE NULL END as LglJDFlag ,CASE WHEN BK.BrAcct IS NOT NULL THEN 1 Else Null END as BKFlag ,CASE WHEN BK.BrAcct IS NOT NULL OR LD.JDDate IS NOT NULL OR AC.BrAcct IS NOT NULL THEN 'Non-Workable' Else 'Workable' END AS 'AccountStatus' ,CC.ActionDate as 'LstAction(PP,NP,F10)' ,convert(Date,FW.QADLCM,101) as DtLstWorked ,convert(date,GETDATE(),101) as Today ,DATEDIFF(dd,FW.QADLCM,GETDATE()) as 'Days' ,CASE WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 8 THEN '1' WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 15 THEN '2' WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 22 THEN '3' WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 29 THEN '4' ELSE '5' END AS DaysBucket ,MAX(CONVERT(DATE,FW.FollowupDate,101)) as FollowupDt ,FW.CollDescr + FW.CollDescr_1 + FW.CollDescr_2 + FW.CollDescr_3 as CollectionDescrip FROM Reports.dbo.Loans LN with(nolock) LEFT OUTER JOIN Reports.dbo.[Type] TP with (nolock) on LN.LoanTyp = TP.[Type] AND LN.Class = TP.Class LEFT OUTER JOIN Reports.dbo.LoanDaily LD with(nolock) on LN.BrAcct = LD.BrAcct LEFT OUTER JOIN Reports.dbo.Bankrupt BK with(nolock) on BK.BrAcct = LN.BrAcct and BK.DtFiled is not null and BK.DtFiled > '01/01/1900' LEFT OUTER JOIN #CollectionComments CC with(nolock) on CC.BrAcct = LN.BrAcct LEFT OUTER JOIN Reports.dbo.Followups FW with(nolock) on FW.BrAcct = LN.BrAcct LEFT OUTER JOIN Reports.dbo.LoansBorrower LB with (nolock) on LN.BrAcct = LB.BrAcct and LB.IRSOwner = 'y' LEFT OUTER JOIN Reports.dbo.Borrower BW with (nolock) on LB.HHNbr = BW.HHNbr and LB.NamSeq = BW.NamSeq LEFT OUTER JOIN #transfers tx with (nolock) on LN.BrAcct = tx.BrAcct LEFT OUTER JOIN #EOMDelq ED with (nolock) on isnull(tx.OldBrAcct, ld.BrAcct) = ED.BrAcct LEFT OUTER JOIN Reports.dbo.ActionCodes AC with (nolock) on AC.BrAcct = LN.BrAcct and AC.Code = 250
WHERE LD.Released = 0 AND LD.Retracted = 0 AND LD.Balance > 0 AND LD.Category < 80 AND LD.BrNbr = 22
Group By LN.BrAcct ,LD.Balance ,BW.LastName ,BW.FirstName ,BW.MiddleName ,convert(date,LD.DtLstPd,101) ,convert(date,LD.NxtDueDt,101) ,CASE WHEN LN.CollateralCd = 221 THEN 'Secured -- Auto' WHEN LN.CollateralCd = 235 THEN 'Unsecured' WHEN LN.CollateralCd = 240 THEN 'Secured -- Personal Property' WHEN LN.CollateralCd in(260,262) THEN 'Secured -- Boat Motorcycle or RV' ELSE 'Other' END ,CASE WHEN ED.BrNbr = 22 THEN 'Y' ELSE 'N' END ,ED.Delq ,CASE WHEN LD.Delq = 0 THEN 'CurrentMonth' WHEN LD.Delq IN (1,2) THEN 'P30' WHEN LD.Delq = 3 THEN '30Day' WHEN LD.Delq = 6 THEN '60Day' WHEN LD.Delq = 9 THEN '90Day' WHEN LD.Delq = 12 THEN '120Day' WHEN LD.Delq = 15 THEN '150Day' WHEN LD.Delq = 18 THEN '180Day' WHEN LD.Delq = 21 THEN '210Day' ELSE 'PaidAhead' END ,CASE WHEN LD.LegalStat > 0 and LD.JDDate IS NULL THEN 1 ELSE NULL END ,CASE WHEN LD.JDDate IS NOT NULL and LD.LegalStat = 0 THEN 1 ELSE NULL END ,CASE WHEN LD.LegalStat > 0 and LD.JDDate IS NOT NULL THEN 1 ELSE NULL END ,CASE WHEN BK.BrAcct IS NOT NULL THEN 1 Else Null END ,CASE WHEN BK.BrAcct IS NOT NULL OR LD.JDDate IS NOT NULL OR AC.BrAcct IS NOT NULL THEN 'Non-Workable' Else 'Workable' END ,CC.ActionDate ,convert(Date,FW.QADLCM,101) ,DATEDIFF(dd,FW.QADLCM,GETDATE()) ,CASE WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 8 THEN '1' WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 15 THEN '2' WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 22 THEN '3' WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 29 THEN '4' ELSE '5' END ,FW.CollDescr + FW.CollDescr_1 + FW.CollDescr_2 + FW.CollDescr_3;
STORE WorkGap into [..\QVD\01_Extract\WorkGap.qvd](qvd); DROPTABLE WorkGap;
Yea, you're right, I probably jumped the gun here.
I'm still having problems getting the data to load.
I solved the problem of getting the .qvd to run, when I added the exit script. But when I put my load statment into my .qvw and ran the script I keep getting "Field not Found" error. But I have lined the naming conventions up and they all match the .qvd naming conventions, so I dont know what the problem is now. Any help?
I can't get the script to model anything in the .qvw, because I keep getting the "Field Not Found" error in the .qvw. I have posted the document log from the .qvd showing that it is running, but I still can't get it to pull over in the load script. I have attached the load script as well.
