RE: SQL query processor error

Eric,

Thanks for the input. I've coded it using RPG due to time constraints
but I'll give this a try later today and see if it helps.

Rick

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Wednesday, November 28, 2007 5:11 PM
To: Midrange Systems Technical Discussion
Subject: 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.