I forgot to add the query and the view.

SELECT
VC.RXCLAIMNBR,VC.CLMSEQNBR,VC.DATESBM,VC.TIMESBM,VC.RVDATESBM,VC.MULTSRC
CDE,VC.BRNDTRDNME,VC.PRODUCTID,VC.SRVPROVID,VC.SRVPROVNME,VC.PROCESSOR,V
C.DAYSSUPPLY,VC.DECIMALQTY,VC.USUALNCUST,VC.LABELNAME,VC.AWPUNITCST,VC.R
XNUMBER,VC.DTEFILLED,VC.PRAUTHNBR,VC.GPINUMBER,VC.PRESCRIBER,VC.PRESLSTN
ME,VC.PRESFSTNME,VC.CALDISPFEE,VC.APPINGRCST,VC.COSTTYPCDE,
VC.CLAIMSTS,COALESCE(CC.ACCOUNTID,VC.ACCOUNTID)
ACCOUNTID,COALESCE(CC.GROUPID,VC.GROUPID)
GROUPID,COALESCE(CC.CTYPEUCOST,VC.CTYPEUCOST)
CTYPEUCOST,COALESCE(CC.CALINGRCST,VC.CALINGRCST)
CALINGRCST,COALESCE(CC.RSPDUEAMT,VC.RSPDUEAMT)
RSPDUEAMT,COALESCE(CC.RSPPATPAY,VC.RSPPATPAY)
RSPPATPAY,COALESCE(CC.RSPDISPFEE,VC.RSPDISPFEE)
RSPDISPFEE,VC.INVOICE,CA.AUDITSTATUS,
PT.CATEGORY,PT.PATIENTTYPE
FROM (CLAIMSPAIDREVERSED VC LEFT JOIN (CLAIMAUDIT CA JOIN
CLAIMCORRECTION CC ON CA.ID=CC.AUDITID)
ON VC.RXCLAIMNBR=CA.RXCLAIMNBR
AND VC.CLMSEQNBR=CA.RXCLAIMSEQ
AND VC.CLAIMSTS=CA.CLAIMSTS)
LEFT JOIN CHCPATIENTTYPES PT
ON (VC.ACCOUNTID=PT.CHCCODE
AND VC.GROUPID=PT.GROUP_ID)
WHERE
VC.RXCLAIMNBR=50397285522003

PLAN JOIN (JOIN (VC INDEX (PK_CLAIMSPAIDREVERSED),JOIN (CC NATURAL,CA
INDEX (PK_CLAIMAUDIT))),PT INDEX (I_CHCPATIENTTYPES_CPR))

SELECT
*
FROM
V_ADJUSTED_CLAIMS
WHERE
RXCLAIMNBR=50397285522003

PLAN JOIN (JOIN (V_ADJUSTED_CLAIMS VC INDEX (PK_CLAIMSPAIDREVERSED),JOIN
(V_ADJUSTED_CLAIMS CC NATURAL,V_ADJUSTED_CLAIMS CA INDEX
(PK_CLAIMAUDIT))),V_ADJUSTED_CLAIMS PT INDEX (I_CHCPATIENTTYPES_CPR))

-----Original Message-----
From: Rick DeBay
Sent: Thursday, February 24, 2005 12:37 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Query works, view doesn't


We have a view that is failing with a 'Cannot perform this operation on
an open dataset' error. Running the query behind the view with the same
WHERE clause works fine. I isolated the problem down to one row in one
table, if the query excludes that row there is no problem. Gfix -v
reports no errors, -l reports no limbo transactions. I ran a sweep and
restarted the database just for grins, but that didn't help.

Our TX info looks good:

Oldest transaction 2900336
Oldest active 2900337
Oldest snapshot 2900337
Next transaction 2900338

I'm stumped. The next step is to delete the offending row and insert it
again.

Rick DeBay
Senior Software Developer
RxStrategies.net



Yahoo! Groups Links