RXCLAIMNBR is defined as the domain

CREATE DOMAIN D_CLAIMNBR AS
DECIMAL(15,0)
CHECK ((VALUE IS NULL) OR (VALUE BETWEEN 0 AND +999999999999999))

Although I should change it to NUMERIC as I don't want it to be 16
digits.

I'm using Firebird 1.5.2, and I'm guessing the error is from Delphi.
I dropped and recreated the View, and it's working now. Actually, I had
do that twice as after the first time it was displaying tables in the
plan that weren't in the view. Queries would never return (at least
after 5 minutes they didn't. After the second drop and create the plan
made sense and it took less than a second.


-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, February 24, 2005 9:20 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Query works, view doesn't


At 12:39 PM 24/02/2005 -0500, you wrote:

>I forgot to add the query and the view.
>
>SELECT
>VC.RXCLAIMNBR,VC.CLMSEQNBR,VC.DATESBM,VC.TIMESBM,VC.RVDATESBM,VC.MULTSR

C

>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.PRESLST

N

>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

What is the data type of rxclaimnbr? If it is integer, then this claim
number overflows it by a power of five. If it is BigInt, then it is OK.

If it is a char() or varchar() then it would make sense not to depend on

implicit type conversion but to tidily put this search key in single
quotes. An implicit conversion from BigInt to Char will have
unpredictable
results. However, if it is a character type of less than 14, this
string
will overflow it anyway.

As to the exception that DBW is returning to you - DBW will be reacting
to
an error code sent to it through the API. It is making an inappropriate

reaction to the exception that is occurring - which tells me that the
client connection is reading the wrong message file. This happens when
you
have ad hoc mixtures of server and client artifacts around the place.

You don't mention what make and model of server you are using; but to
eliminate these wrong messages, you need to do some searching and
version
checking, to eliminate all message files except those that match the
version number of the server -- on client workstations as well as the
server.

./heLen





Yahoo! Groups Links