Oracle view not working

[jenschmidt]

I have created a very simple view that joins a few tables. I'm using Crystal Reports 9 to report data from the view.

The view works just fine until I put a selection criteria on one specific field. The field is coming from a table I created - it's varchar2(50). If I just display the field on the report, it's fine, but if I try to pick specific elements from the field, the error pops up.

It gives me a "Failed to open rowset" window, when I hit OK it shows the following: "Failed to open rowset. Details: 22018:[Oracle][ODBC][Ora]ORA-01722: invalid number".

I've looked at the data - it all appears to be formatted correctly, no numbers or weird characters. I'm not sure what else could be wrong with it. Any ideas?

Thanks!


jennifer.giemza@uwmf.wisc.edu

 

[BJCooperIT]

Jen,
It sounds as though your attempt to "pick specific elements from the field" is performing a to_number on a non-numeric portion of the field. Could you post the varchar2(50) field values and the SQL you are using to extract your pieces?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000

 

[jenschmidt]

Thanks so much for your help!

The data in the field looks like this:
Kidney
Kidney
PBSC
Kidney Pancreas
Liver
Heart
Heart VAD

There are about 2000 lines total in this table, the field in question is never blank. It will always be a string because it contains the type of transplant for each patient.

When I enter the selection criteria in the report, it looks like this:
{UWMF_VIEW_TRANSPLANT.TABLE_NOTES}="Kidney"

The view I built looks like this: (I didn't reflect most of the fields I'm retreiving in the SELECT statement because there are so many.)
SELECT UWMF_TRANSPLANT.TABLE_NOTES,
FROM
CLARITY.CLARITY_TDL CLARITY_TDL,
CLARITY.CLARITY_EAP CLARITY_EAP,
CLARITY.CLARITY_DEP CLARITY_DEP,
CLARITY.CLARITY_POS CLARITY_POS,
CLARITY.CLARITY_LOC CLARITY_LOC,
CLARITY.CLARITY_SER BILL_SER,
CLARITY.IDENTITY_ID IDENTITY_ID,
CLARITY_DS.UWMF_TRANSPLANT UWMF_TRANSPLANT,
CLARITY.ZC_SER_RPT_GRP_6 ZC_SER_RPT_GRP_6

WHERE
(CLARITY_TDL.PROC_ID=CLARITY_EAP.PROC_ID (+)) AND
(CLARITY_TDL.DEPT_ID=CLARITY_DEP.DEPARTMENT_ID (+)) AND
(CLARITY_TDL.POS_ID=CLARITY_POS.POS_ID (+)) AND
(CLARITY_TDL.LOC_ID=CLARITY_LOC.LOC_ID (+)) AND
(CLARITY_TDL.BILLING_PROVIDER_ID=BILL_SER.PROV_ID (+)) AND
(CLARITY_TDL.INT_PAT_ID=IDENTITY_ID.PAT_ID (+)) AND
(IDENTITY_ID.IDENTITY_ID=UWMF_TRANSPLANT.MED_REC (+)) AND
(BILL_SER.RPT_GRP_SIX=ZC_SER_RPT_GRP_6.RPT_GRP_SIX (+)) AND
CLARITY_TDL.DETAIL_TYPE=1 AND
IDENTITY_ID.IDENTITY_TYPE_ID=30

jennifer.giemza@uwmf.wisc.edu

 

[kokiri]

Hi jenschmidt,

I had similar problems in the past dealing with CR. You might want to check your all of your ID fields that you are joining. I'm sure that they are numeric field(s) and it gives an error when you do outerjoin. If you change to equijoin, you might get rid of error but it does serve your purpose.

Can you crate view?

Kokiri

 

[SantaMufasa]

Jen,

I don't want to interfere with the fine help that BJ always renders, but she (and I) would also benefit from a "describe" of the table columns involved in your query (since Oracle often "casts" data in the WHERE clause based upon the data types of the table columns).

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:07 (11Mar04) UTC (aka "GMT" and "Zulu"), 11:07 (11Mar04) Mountain Time)

 

[jenschmidt]

Thank you all for the help! I really appreciate it!

Kokiri - I checked through the fields I'm linking on in the WHERE statement and there is one link that uses a number and a string: (IDENTITY_ID.IDENTITY_ID=UWMF_TRANSPLANT.MED_REC (+))

Identity_ID is a string. Med_Rec is a number. UWMF_TRANSPLANT is the table I created, so I'll go in and modify the column and see what happens! Thanks for having me check there!!

SantaMufasa - when you say you would like a "describe" of the table columns, do you want a list of the fields I'm pulling into the view? (As I'm sure you can tell, I'm brand new to Oracle, especially to creating views!) Sorry for my mental block!

jennifer.giemza@uwmf.wisc.edu

 

[kokiri]

From your SQL prompt type the following :

Desc view(or tablename) name ;

 

[jenschmidt]

Kokiri - It worked! It worked! (insert happy report writer jumping up and down here) Thanks so much for your help! I still don't understand why that join would have only returned the error when one specific field was used, but not any others. Any ideas? If not, I'm just happy it works and will watch for that next time!!

jennifer.giemza@uwmf.wisc.edu

 

[kokiri]

Hi jenschmidt,

I'm glad that it worked. Unfortunately, I don't know why CR returns error and I just know how to go about it.

 

[kevineva]

This type of error arises in CR 7,8,9 because they are using the Oracle7 OCI programmer's library. We have noticed significant problems with CLOB handling that are due to this problem as well. CR10 allegedly addresses this problem.