Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
DROP TABLE WorkGap;
That's a quite lengthy SQL statement.
First step, add an
Exit script;
statement in the line above the STORE statement.
Run your script. Check the table view using CTRL-T if there is a table created called WorkGap. How many records has this table?
That's a quite lengthy SQL statement.
First step, add an
Exit script;
statement in the line above the STORE statement.
Run your script. Check the table view using CTRL-T if there is a table created called WorkGap. How many records has this table?
Thanks for the help the exit statement worked!
Yes, but have you also resolved your issue?
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?
After inserting the exit script statement, have you checked your data model?
Have you seen a table named WorkGap with records?
Or could you post your document log?
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.
//WorkGap:
//Load AsOfDate
//,BrAcct
//,Balance
//,LastName
//,FirstName
//,MiddleName
//,DtLstPd
//,NxtDueDt
//,SecurityType
//,PriorEOMBr22
//,PriorEOMDelq
//,Delq
//,LglFlag
//,JDFlag
//,LglJDFlag
//,BKFlag
//,AccountStatus
//,LstAction
//,DtLstWorked
//,Today
//,Days
//,DaysBucket
//,FollowupDt
//,CollectionDescrip
//FROM [..\QVD\01_Extract\WorkGap.qvd] (qvd);
Mitchell,
I can't see your attachments, could you check?
Sorry about that posted is the log from my reload this morning. Thanks again.