Oracle Errors

[impreza276]

Hi,
I am running crystal reports v8.5 with an oracle 8i database. There are two problems I am facing.

1) There is a relatively simpple report I have created that runs fine in Crystal Reports, but it give an ORA-00904 "invalid column name" error when I run it in Crystal Ease 3. The DB statement is

SELECT
"CUSTOMER_committee"."NAME", "COMMITTEE_MEMBER"."COM_POSITION", "COMMITTEE_MEMBER"."END_DATE", "COMMITTEE_MEMBER"."VOTING_STS", "CUSTOMER"."CUSTOMER", "CUSTOMER"."MAIL_NAME", "CUSTOMER"."USER_T1", "PHONE"."PHONE"
FROM
"TIMSS"."CUSTOMER" "CUSTOMER_committee",
"TIMSS"."COMMITTEE_MEMBER" "COMMITTEE_MEMBER",
"TIMSS"."CUSTOMER" "CUSTOMER",
"TIMSS"."PHONE" "PHONE"
WHERE
"CUSTOMER_committee"."CUSTOMER" = "COMMITTEE_MEMBER"."COMMITTEE" AND "COMMITTEE_MEMBER"."CUSTOMER" = "CUSTOMER"."CUSTOMER" AND "CUSTOMER"."CUSTOMER" = "PHONE"."CUSTOMER"(+) AND "CUSTOMER"."CUSTOMER_STATUS" <> 'DECEAS' AND "PHONE"."PHONE_TYPE"(+) = 'EMAIL' AND (("COMMITTEE_MEMBER"."COMMITTEE" = '00000012' OR "COMMITTEE_MEMBER"."COMMITTEE" = '00991878') OR "COMMITTEE_MEMBER"."COM_POSITION" = 'CHAIR')
ORDER BY
"CUSTOMER"."CUSTOMER" ASC


2) There is another even simpler report that I created that worked fine. Recently an upgrade was carried out and the oracle database was restructured (database version is unchanged). Even when I rebuild the report from scratch it gives me an Ora-01007 " variable not in select list" error (in crystal reports 8.5)

Any idea what could be causing these problems?

Thanks

 

[synapsevampire]

You may have to contact somone from Crystal Ease, this is a BO Crystal forum.

I would suspect connectivity as the culprit, but the generic "recently an upgrade was..." doesn't mean much of anything, what was upgraded?

Is this an Oracle service pack?

What connectivity are you using?

Did you run a Database->Verify Database from within Crystal? Even though you don't notice changes, Crystal might, and this will reset the datasource.

-k

 

[impreza276]

Thanks synapsevampire.
The upgrade was done to the system that uses the oracle database, so while the tables were restructured, the database version was not affected (no patches applied either).

For the second problem I did try the 'verify database' but it did not cure the problem. So I build a new report from scratch but it came up with the same error. I find that very strange and was hoping that there is a known issue related to it

 

[synapsevampire]

This may be a known issue, but again, this isn't a Crystal Ease forum.

A shame you can't learn the connectivity being used.

Generally you want to use the CR supplied ODBC driver (if Crystal Ease is not using Oracle native), not the Oracle supplied ODBC error.

The fastest is Oracle native connectivity.

It may be that you have some illegal naming conventions in the database as well, or the connecting driver (again, native vs ODBC types) may be getting confused by some reserved column name - saw a post on that a year or so ago). Have you tried against a very simple table? Also some datatypes, such as a timestamp with timezone will piss Crystal off, but I think that particular data type happened in Oracle 9.

-k

 

[jdemmi]

I think the problem might be in your WHERE clause.

Can you please list the columns in the CUSTOMER table (which you have also aliased as the CUSTOMER_COMMITTEE table)

-- Jason
"It's Just Ones and Zeros

 

[impreza276]

Sorry I forgot to add the connectivity information. It is using the native oracle 8i driver, not ODBC. Here is the structure of the customer table

Thanks!

Name Null? Type
----------------------------------------- -------- ---------
CUSTOMER NOT NULL VARCHAR2(8)
FEDERAL_TAX_NUMBER VARCHAR2(11)
NAME_PREFIX VARCHAR2(20)
FIRST_NAME VARCHAR2(40)
MIDDLE_NAME VARCHAR2(40)
LAST_NAME VARCHAR2(40)
NAME_SUFFIX VARCHAR2(20)
NAME_TITLE VARCHAR2(40)
NAME NOT NULL VARCHAR2(80)
MAIL_NAME NOT NULL VARCHAR2(80)
SEARCH_NAME NOT NULL VARCHAR2(80)
JOB_TITLE VARCHAR2(80)
PHONE_TYPE VARCHAR2(6)
PHONE VARCHAR2(60)
CUSTOMER_CLASS NOT NULL VARCHAR2(6)
CUSTOMER_SUBCLASS VARCHAR2(6)
CUSTOMER_STATUS NOT NULL VARCHAR2(6)
STATUS_DATE DATE
CO_CUSTOMER VARCHAR2(8)
CO_NAME VARCHAR2(80)
CO_MAIL_NAME VARCHAR2(80)
CO_SEARCH_NAME VARCHAR2(80)
CREDIT_TERMS VARCHAR2(6)
CREDIT_LIMIT NOT NULL NUMBER(20,2)
CREDIT_STATUS VARCHAR2(6)
CREDIT_COMMENTS VARCHAR2(120)
TAXABLE VARCHAR2(6)
MEMBER_CLASS VARCHAR2(12)
MEMBER_STATUS VARCHAR2(12)
PAID_THRU_DATE DATE
JOIN_DATE DATE
ADDOPER NOT NULL VARCHAR2(12)
ADDDATE NOT NULL DATE
MODOPER VARCHAR2(12)
MODDATE DATE
ROWVERSION NOT NULL NUMBER(10)
CUSTOMER_STATUS_DATE DATE
GENDER CHAR(1)
LABEL_SALES CHAR(1)
FELLOW_SINCE DATE
BIRTHDATE DATE
PAID_THROUGH DATE
ELECTION_STATUS VARCHAR2(6)
MEMBER_TYPE VARCHAR2(6)
OLD_DEGREE_CODE VARCHAR2(6)
CERTIFICATE_DATE DATE
CERTIFICATE_CODE VARCHAR2(3)
LICENSE VARCHAR2(30)
RECORD_TYPE CHAR(1)
REVENUE NUMBER
STAFF NUMBER
FND_MATCHING CHAR(1)
FND_MATCHING_PCT NUMBER(10,2)
FND_MATCHING_LIMIT NUMBER
FOUNDATION CHAR(1)
CHARACTERISTIC VARCHAR2(6)
SPECIAL_NEEDS VARCHAR2(254)
IMAGE_ID VARCHAR2(10)
STD_HONORARIUM NUMBER(12,2)
PUBLISH_FLAG VARCHAR2(1)
RECORD_FLAG VARCHAR2(1)
PHOTO_FLAG VARCHAR2(1)
INTERVIEW_FLAG VARCHAR2(1)
NICKNAME VARCHAR2(40)
CONGRESSIONAL_DIST VARCHAR2(12)
BYLAW_RECEIVED VARCHAR2(1)
REVISION VARCHAR2(1)
RECEIVED_DATE DATE
REVISION_DATE DATE
FAX VARCHAR2(18)
BUSINESS_PHONE VARCHAR2(18)
CONTACT_ID VARCHAR2(8)
TAX_EXEMPT_ID VARCHAR2(11)
RESUME_PATH VARCHAR2(254)
USER_T1 VARCHAR2(40)
USER_T2 VARCHAR2(40)
USER_T3 VARCHAR2(40)
USER_T4 VARCHAR2(40)
USER_D1 DATE
USER_D2 DATE
USER_D3 DATE
USER_D4 DATE
USER_N1 NUMBER(10,2)
USER_N2 NUMBER(10,2)
USER_N3 NUMBER(10,2)
USER_N4 NUMBER(10,2)
JOB_FUNCTION VARCHAR2(6)
DEFAULT_TO_COMPANY_ADDRESS NOT NULL VARCHAR2(1)
VENDOR_NO VARCHAR2(30)
VENDOR_SITE_ID NUMBER
FORMAL_SALUTATION VARCHAR2(40)
INFORMAL_SALUTATION VARCHAR2(40)
NO_FAX_FLG NOT NULL VARCHAR2(1)
NO_EMAIL_FLG NOT NULL VARCHAR2(1)
VAT_ID VARCHAR2(30)
ADDRESS_ID NUMBER(10)
NO_PROMOS CHAR(1)
LIST_CODE VARCHAR2(12)
USR_PLENARYROL2 VARCHAR2(20)
USR_COURSEROL1 VARCHAR2(20)
USR_COURSEROL2 VARCHAR2(20)
USR_COURSEROL3 VARCHAR2(20)
USR_COURSETITL1 VARCHAR2(30)
USR_COURSETITL2 VARCHAR2(30)
USR_COURSETITL3 VARCHAR2(30)
USR_COURSEACCPT1 VARCHAR2(2)
USR_COURSEACCPT2 VARCHAR2(2)
USR_COURSEACCPT3 VARCHAR2(2)
USR_COURSEDAT1 DATE
USR_COURSEDAT2 DATE
USR_COURSEDAT3 DATE
USR_COURSECOMMENTS VARCHAR2(50)
USR_PLENARYACCPT1 VARCHAR2(2)
USR_PLENARYACCPT2 VARCHAR2(2)
USR_PLENARYACCPT3 VARCHAR2(2)
USR_PLENARYCOMMENTS VARCHAR2(50)
USR_PLENARYDAT1 DATE
USR_PLENARYDAT2 DATE
USR_PLENARYDAT3 DATE
USR_PLENARYROL1 VARCHAR2(20)
USR_PLENARYROL3 VARCHAR2(20)
USR_PLENARYTITL1 VARCHAR2(30)
USR_PLENARYTITL2 VARCHAR2(30)
USR_PLENARYTITL3 VARCHAR2(30)
USR_POSTERACCPT1 VARCHAR2(2)
USR_POSTERACCPT2 VARCHAR2(2)
USR_POSTERACCPT3 VARCHAR2(2)
USR_POSTERCOMMENTS VARCHAR2(50)
USR_POSTERDAT1 DATE
USR_POSTERDAT2 DATE
USR_POSTERDAT3 DATE
USR_POSTERROL1 VARCHAR2(20)
USR_POSTERROL2 VARCHAR2(20)
USR_POSTERROL3 VARCHAR2(20)
USR_POSTERTITL1 VARCHAR2(30)
USR_POSTERTITL2 VARCHAR2(30)
USR_POSTERTITL3 VARCHAR2(30)
USR_MAIN_CONTACT_TITLE VARCHAR2(60)
USR_MEDICAL_SCHOOL_TYPE VARCHAR2(20)
USR_MEDSCHID NUMBER(12)
USR_DIRECTOR_NAME VARCHAR2(60)
USR_DIRECTOR_TITLE VARCHAR2(60)
USR_MAIN_CONTACT_NAME VARCHAR2(60)
SUPPRESS_FROM_DIRECTORY NOT NULL CHAR(1)
VENDOR_FLAG NOT NULL CHAR(1)

 

[jdemmi]

First I have a question. Why did you need to reference the CUSTOMER table twice? (Once as CUSTOMER and again as CUSTOMER_COMMITTEE.

Second -- I think the problem is that in your select you have "CUSTOMER_committee"."NAME" but in your WHERE you have "CUSTOMER_committee"."CUSTOMER" even though you select "CUSTOMER" from "CUSTOMER" not "CUSTOMER_COMMITTEE"

Make sense?

Here is the same query, which I modified....can u try it and let me know?

SELECT
"CUSTOMER"."NAME", "COMMITTEE_MEMBER"."COM_POSITION", "COMMITTEE_MEMBER"."END_DATE", "COMMITTEE_MEMBER"."VOTING_STS", "CUSTOMER"."CUSTOMER", "CUSTOMER"."MAIL_NAME", "CUSTOMER"."USER_T1", "PHONE"."PHONE"
FROM
"TIMSS"."COMMITTEE_MEMBER" "COMMITTEE_MEMBER",
"TIMSS"."CUSTOMER" "CUSTOMER",
"TIMSS"."PHONE" "PHONE"
WHERE
"CUSTOMER"."CUSTOMER" = "COMMITTEE_MEMBER"."COMMITTEE" AND
"COMMITTEE_MEMBER"."CUSTOMER" = "CUSTOMER"."CUSTOMER" AND
"CUSTOMER"."CUSTOMER" = "PHONE"."CUSTOMER"(+) AND
"CUSTOMER"."CUSTOMER_STATUS" <> 'DECEAS' AND
"PHONE"."PHONE_TYPE"(+) = 'EMAIL' AND
(("COMMITTEE_MEMBER"."COMMITTEE" = '00000012' OR "COMMITTEE_MEMBER"."COMMITTEE" = '00991878') OR "COMMITTEE_MEMBER"."COM_POSITION" = 'CHAIR')
ORDER BY
"CUSTOMER"."CUSTOMER" ASC

-- Jason
"It's Just Ones and Zeros

 

[impreza276]

Thanks jdemmi, the reason the customer table is referenced twice is that the customer retrieved in "CUSTOMER_committee" is the committee that the customer in "CUSTOMER" belongs to.

 

[impreza276]

Just a follow-up on the crystal ease problem, I reinstalled the Crystal Ease and set all the oracle options as 'install to disk' and it works now.