Skip to main content
Question

904 error - Crystal

  • October 7, 2020
  • 4 replies
  • 423 views
P
Do Gooder (Customer)
Forum|alt.badge.img+3

I’m pulling a phone number from Apps8 in Crystal Reports using the following code on an SQL Expession Field

(
Select Value from prin1app.comm_Method Where
Company_ID ="CUST_STMT_ACCT_REP"."COMPANY" AND 
Party_type = 'Company' AND
Method_ID = 'Phone' AND
Method_Default = 'TRUE'
)
 

I get an ORA-00904 invalid identifier for the reference "CUST_STMT_ACCT_REP"."COMPANY" even though this has been selected from the list of fields in the view.

 

The statement works perfectly from different views or if I use a value 

Company_ID = ‘7’

    4 replies

    Z
    Do Gooder (Customer)
    Forum|alt.badge.img+2
    • zxmaximus
    • Do Gooder (Customer)
    • October 7, 2020

    I think you need to prefix the field identifier with the database schema. So instead of 

    Select Value from prin1app.comm_Method Where
    Company_ID ="CUST_STMT_ACCT_REP"."COMPANY" AND 
    Party_type = 'Company' AND
    Method_ID = 'Phone' AND
    Method_Default = 'TRUE'

     

    It would be :

    Select Value from ifsapp.prin1app.comm_Method Where
    Company_ID ="IFSAPP.CUST_STMT_ACCT_REP"."COMPANY" AND 
    Party_type = 'Company' AND
    Method_ID = 'Phone' AND
    Method_Default = 'TRUE'

     

    P
    Do Gooder (Customer)
    Forum|alt.badge.img+3
    • pknight
    • Author
    • Do Gooder (Customer)
    • October 7, 2020

    Thanks for the reply but still exactly the same.

    P
    Do Gooder (Customer)
    Forum|alt.badge.img+3
    • pknight
    • Author
    • Do Gooder (Customer)
    • October 7, 2020

    Our applications owner has been changed from IFSAPP to PRIN1APP

    Z
    Do Gooder (Customer)
    Forum|alt.badge.img+2
    • zxmaximus
    • Do Gooder (Customer)
    • October 7, 2020

    Looking at the company field in the view you are joining, the field length may be around 2000 for the field company. If this is the case I am sure expressions are limited to specific maximum field length, so this might be the issue.

    Terms & ConditionsCookie settingsAccessibility statement