RE: SQL query processor error

Rick,

Not necessarily an answer to your question, but just an observation:

When I use CTE to build a temp result set, I generally declare the field names of each CTE in the table definition rather than using the "AS newname" in the select. I believe I had some unusual errors on V5R3 with this....

As I recall, SQL was having trouble keeping column name correlations as it built the CTE result sets. By declaring them in the CTE table definition, things started working..... Not sure if this is related to your issue, but you never know....



With IntTypes (Account, LoanType, LoanBranch) as (
Select cvLoan, cvIntT,
cast(cvBrch as numeric(3,0))
from tmods.cvcnvdtl
order by cvLoan),

MaxLoan as (MaxAcct, MaxLoan) (
Select AccountNumber, Max(LoanNumber)
from tmods.lbtrupmt
group by AccountNumber
order by AccountNumber),

-- New stuff
Branches (BranchNote, BranchLoan, BranchNumber) as (
select Cast(SubStr(strAccount, 1, 7) as Numeric(7,0)),
intLoan, intBranch
from tmods.newmm
union all
select Cast(substr(lnCstLnID, 1, 10) as Numeric(7,0)),
Cast(SubStr(lnSeqnbr, 1, 4) as Numeric(4,0)),
Cast(SubStr(lnbranch, 1, 4) as Numeric(4,0))
from tmods.lbloans exception join tmods.newmm on
SubStr(lnCstLnID, 1, 7) = SubStr(StrAccount, 1, 7)
order by 1, 2)
-- End new stuff

Select pmBank, pmAcct, pmLoan, pmVoidCd, pmPrcDt, pmEffDt, pmTranCd,
pmPrnAmt, pmIntAmt, pmMscAmt, pmMonTot, pmLnBal, ACFTrnCd1,
Amt1Act, Amt2Act, Amt3Act, Amt4Act, Amt5Act, PrinBal, IntBal,
pmDesc, pmCmt901, pmCmt902, BranchNumber, pmPmtID, pmTranID,
acfLoan, SepTran, ACFTrnCd2, LoanType, memo
from tmods.lbTruPmt join MaxLoan on pmAcct = MaxAcct and pmLoan = MaxLoan
join tmods.cvpmtdef on pmBank = KeyValue1 and pmTranCd = KeyValue2
join tmods.lbacxref on pmacct = xrLBLoan join IntTypes on acfLoan = Account
join Branches on pmAcct = BranchNote and pmLoan = BranchLoan
where pmacct = 1160583
order by pmAcct, pmPrcDt, pmTranID

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of
Rick.Chevalier@xxxxxxxxxxxxxxx
Sent: Wednesday, November 28, 2007 2:55 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL query processor error



I have an SQL statement that was working fine until I added another 'temp' table select. The added piece runs fine by itself. I only get an error when I combine the two pieces.

Running the job in debug I see the following errors in the job log.
*** DBOP open FAILED. Exception from call to SLIC! ***.
Internal failure occurred in query processor.
PROBLEM WITH QDT FOR STMT# 1, QDT# 1, OCLE 1

Trying to Google a solution wasn't any help.

This is the SQL statement. The branches select is the new piece. If anyone has any ideas I'd really appreciate hearing them.

With IntTypes as (
Select cvLoan as Account, cvIntT as LoanType,
cast(cvBrch as numeric(3,0)) as LoanBranch
from tmods.cvcnvdtl
order by cvLoan),

MaxLoan as (
Select AccountNumber as MaxAcct, Max(LoanNumber) as MaxLoan
from tmods.lbtrupmt
group by AccountNumber
order by AccountNumber),

-- New stuff
Branches as (
select Cast(SubStr(strAccount, 1, 7) as Numeric(7,0)) as BranchNote,
intLoan as BranchLoan, intBranch as BranchNumber
from tmods.newmm
union all
select Cast(substr(lnCstLnID, 1, 10) as Numeric(7,0)) as BranchNote,
Cast(SubStr(lnSeqnbr, 1, 4) as Numeric(4,0)) as BranchLoan,
Cast(SubStr(lnbranch, 1, 4) as Numeric(4,0)) as BranchNumber
from tmods.lbloans exception join tmods.newmm on
SubStr(lnCstLnID, 1, 7) = SubStr(StrAccount, 1, 7)
order by 1, 2)
-- End new stuff

Select pmBank, pmAcct, pmLoan, pmVoidCd, pmPrcDt, pmEffDt, pmTranCd,
pmPrnAmt, pmIntAmt, pmMscAmt, pmMonTot, pmLnBal, ACFTrnCd1,
Amt1Act, Amt2Act, Amt3Act, Amt4Act, Amt5Act, PrinBal, IntBal,
pmDesc, pmCmt901, pmCmt902, BranchNumber, pmPmtID, pmTranID,
acfLoan, SepTran, ACFTrnCd2, LoanType, memo
from tmods.lbTruPmt join MaxLoan on pmAcct = MaxAcct and pmLoan = MaxLoan
join tmods.cvpmtdef on pmBank = KeyValue1 and pmTranCd = KeyValue2
join tmods.lbacxref on pmacct = xrLBLoan join IntTypes on acfLoan = Account
join Branches on pmAcct = BranchNote and pmLoan = BranchLoan
where pmacct = 1160583
order by pmAcct, pmPrcDt, pmTranID

Rick Chevalier
<<Chevalier, Rick.vcf>>


Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.