topic Re: ORACLE prepare error: ORA-00972 in SAS Programming

ORACLE prepare error: ORA-00972

anonymous_user — Wed, 24 Aug 2016 09:47:09 GMT

Hi, I get this error when connecting to Oracle and running the SQL code, any idea why this is please?

SELECT Case when AGENT.Authorisation_Type = 1
then 'AR'
else 'DA'
end as Authorisation,
MORTGAGE_APPLICATION.Transact_Number,
MORTGAGE_APPLICATION.Application_Number,
MORTGAGE_APPLICATION.SAM_Account_Number,
APPLICATION_CASE_DETAILS.Case_Number,
APPLICATION_CASE_DETAILS.Agent_Id,
COMPANY.Business_Type,
COMPANY.Company_FSA_Firm_Reference_Number,
COMPANY.Principal_FSA_Firm_Reference_Number,
APPLICATION_CASE_DETAILS.Loan_Amount,
APPLICATION_CASE_DETAILS.Loan_Type_Id,
MORTGAGE_APPLICATION.Transact_Decision,
MORTGAGE_APPLICATION.Date_AIP_Submitted,
AGENT.First_Name,
AGENT.Last_Name,
APPLICATION_CASE_DETAILS.Main_Applicant_First_Name,
APPLICATION_CASE_DETAILS.Main_Applicant_Last_Name,
PRODUCT.Product_Code,
COMPANY.Registered_Company_Name
FROM APPLICATION_CASE_DETAILS,
APPLICATION_PRODUCT,
MORTGAGE_APPLICATION,
PRODUCT,
AGENT,
COMPANY,
(select min(Product_Order) FirstProd,
Fk_Application_Number
from Application_Product
Group By Fk_Application_Number) OneProduct
WHERE OneProduct.Fk_Application_Number=MORTGAGE_APPLICATION.Application_Number
AND OneProduct.FirstProd = APPLICATION_PRODUCT.Product_Order
AND OneProduct.Fk_Application_Number = APPLICATION_PRODUCT.Fk_Application_Number
AND APPLICATION_PRODUCT.Fk_Product=PRODUCT.Pk
AND APPLICATION_CASE_DETAILS.Case_Number=MORTGAGE_APPLICATION.Case_Number
AND APPLICATION_CASE_DETAILS.Agent_Id=AGENT.Agent_Id
AND AGENT.Company_Id=COMPANY.Company_Id
AND MORTGAGE_APPLICATION.Internal_User=0
AND NOT (MORTGAGE_APPLICATION.Date_AIP_Submitted<{ts '1899-12-30 00:00:00'})
AND NOT (MORTGAGE_APPLICATION.Status_Code='AipCancelledforCNRUseOnly'
OR MORTGAGE_APPLICATION.Status_Code='AipDuplicate'
OR MORTGAGE_APPLICATION.Status_Code='AipError'
OR MORTGAGE_APPLICATION.Status_Code='AipIncomplete'
OR MORTGAGE_APPLICATION.Status_Code='AipSubmitted')
AND (APPLICATION_CASE_DETAILS.Case_Item_Type=1
OR APPLICATION_CASE_DETAILS.Case_Item_Type=2)
AND NOT (COMPANY.Company_Id=2
OR COMPANY.Company_Id=3)
AND MORTGAGE_APPLICATION.Transact_Action='CREDITBUREAUDECISION'
AND COMPANY.Fk_Channel = 1
AND MORTGAGE_APPLICATION.Date_AIP_Submitted >='2014-04-01'
AND COMPANY.Company_Id <> 26203

);

ERROR: ORACLE prepare error: ORA-00972: identifier is too long. SQL statement: SELECT Case when AGENT.Authorisation_Type = 1 then
'AR' else 'DA' end as Authorisation, MORTGAGE_APPLICATION.Transact_Number, MORTGAGE_APPLICATION.Application_Number,
MORTGAGE_APPLICATION.SAM_Account_Number, APPLICATION_CASE_DETAILS.Case_Number, APPLICATION_CASE_DETAILS.Agent_Id,
COMPANY.Business_Type, COMPANY.Company_FSA_Firm_Reference_Number, COMPANY.Principal_FSA_Firm_Reference_Number,
APPLICATION_CASE_DETAILS.Loan_Amount, APPLICATION_CASE_DETAILS.Loan_Type_Id, MORTGAGE_APPLICATION.Transact_Decision,
MORTGAGE_APPLICATION.Date_AIP_Submitted, AGENT.First_Name, AGENT.Last_Name,
APPLICATION_CASE_DETAILS.Main_Applicant_First_Name, APPLICATION_CASE_DETAILS.Main_Applicant_Last_Name, PRODUCT.Product_Code,
COMPANY.Registered_Company_Name FROM APPLICATION_CASE_DETAILS, APPLICATION_PRODUCT, MORTGAGE_APPLICATION, PRODUCT, AGENT,
COMPANY, (select min(Product_Order) FirstProd, Fk_Application_Number from Application_Product Group By
Fk_Application_Number) OneProduct WHERE OneProduct.Fk_Application_Number=MORTGAGE_APPLICATION.Application_Number AND
OneProduct.FirstProd = APPLICATION_PRODUCT.Product_Order AND OneProduct.Fk_Application_Number =
APPLICATION_PRODUCT.Fk_Application_Number AND APPLICATION_PRODUCT.Fk_Product=PRODUCT.Pk AND
APPLICATION_CASE_DETAILS.Case_Number=MORTGAGE_APPLICATION.Case_Number AND APPLICATION_CASE_DETAILS.Agent_Id=AGENT.Agent_Id
AND AGENT.Company_Id=COMPANY.Company_Id AND MORTGAGE_APPLICATION.Internal_User=0 AND NOT
(MORTGAGE_APPLICATION.Date_AIP_Submitted<{ts '1899-12-30 00:00:00'}) AND NOT
(MORTGAGE_APPLICATION.Status_Code='AipCancelledforCNRUseOnly' OR MORTGAGE_APPLICATION.Status_Code='AipDuplicate' OR
MORTGAGE_APPLICATION.Status_Code='AipError' OR MORTGAGE_APPLICATION.Status_Code='AipIncomplete' OR
MORTGAGE_APPLICATION.Status_Code='AipSubmitted') AND (APPLICATION_CASE_DETAILS.Case_Item_Type=1 OR
APPLICATION_CASE_DETAILS.Case_Item_Type=2) AND NOT (COMPANY.Company_Id=2 OR COMPANY.Company_Id=3) AND
MORTGAGE_APPLICATION.Transact_Action='CREDITBUREAUDECISION' AND COMPANY.Fk_Channel = 1 AND
MORTGAGE_APPLICATION.Date_AIP_Submitted >='2014-04-01' AND COMPANY.Company_Id <> 26203.

Re: ORACLE prepare error: ORA-00972

Reeza — Wed, 24 Aug 2016 09:57:52 GMT

That's long...and not all the code.

Delete half of,the code at a time until you find where the error is, then once you have it isolated you can fix it from there.

Re: ORACLE prepare error: ORA-00972

anonymous_user — Wed, 24 Aug 2016 10:20:58 GMT

That is all of the code. I have played around with the code but still keep getting the error

Re: ORACLE prepare error: ORA-00972

Kurt_Bremser — Wed, 24 Aug 2016 10:30:06 GMT

ORA-00972 points to a name that is too long (more than thirty characters).

Company_FSA_Firm_Reference_Number and Principal_FSA_Firm_Reference_Number have more than thirty characters.

Verify that the names are correctly written.

Re: ORACLE prepare error: ORA-00972

RW9 — Wed, 24 Aug 2016 10:36:55 GMT

There is a fair few problems in that code, a couple of examples:
- some of the code is missing

- the final and statement encompasses the whole of the last block of code without a terminating )

- Not sure if this is valid {ts '1899-12-30 00:00:00'}

- There is a date value used, with no indication that its a date: Date_AIP_Submitted >='2014-04-01'

Just some things I noticed when I tried to apply some sort of formatting to that mass of text. Some other tips, you can select from with a where clause in your from statement - called subquerying, avoids a lot of unecessary where parts. Also you can us in() rather than specfying each bit as a where. I have updated your code somewhat below to make it readable, and applied some fixes. I would however start by identifying in each of the incoming datasets what information you want to keep - this then forms each of the sub-queries that go into your main where clause. You also look at how you want to join these tables.

proc sql;
  connect to oracle (...);
  create table WANT as
  select * from connection to oracle (
    select  case  when AGENT.AUTHORISATION_TYPE = 1 then 'AR' 
                  else 'DA' end as AUTHORISATION,
            MORTGAGE_APPLICATION.TRANSACT_NUMBER,
            MORTGAGE_APPLICATION.APPLICATION_NUMBER, 
            MORTGAGE_APPLICATION.SAM_ACCOUNT_NUMBER, 
            APPLICATION_CASE_DETAILS.CASE_NUMBER, 
            APPLICATION_CASE_DETAILS.AGENT_ID, 
            COMPANY.BUSINESS_TYPE, 
            COMPANY.COMPANY_FSA_FIRM_REFERENCE_NUMBER, 
            COMPANY.PRINCIPAL_FSA_FIRM_REFERENCE_NUMBER, 
            APPLICATION_CASE_DETAILS.LOAN_AMOUNT, 
            APPLICATION_CASE_DETAILS.LOAN_TYPE_ID, 
            MORTGAGE_APPLICATION.TRANSACT_DECISION, 
            MORTGAGE_APPLICATION.DATE_AIP_SUBMITTED, 
            AGENT.FIRST_NAME, 
            AGENT.LAST_NAME, 
            APPLICATION_CASE_DETAILS.MAIN_APPLICANT_FIRST_NAME, 
            APPLICATION_CASE_DETAILS.MAIN_APPLICANT_LAST_NAME, 
            PRODUCT.PRODUCT_CODE, 
            COMPANY.REGISTERED_COMPANY_NAME 
    from    (select * from APPLICATION_CASE_DETAILS where CASE_ITEM_TYPE in (1,2)) APPLICATION_CASE_DETAILS, 
            APPLICATION_PRODUCT, 
            (select * from MORTGAGE_APPLICATION
              where MORTGAGE_APPLICATION.DATE_AIP_SUBMITTED > {ts '1899-12-30 00:00:00'}
                and STATUS_CODE not in ('AipCancelledforCNRUseOnly','AipDuplicate','AipError','AipIncomplete','AipSubmitted')
                and TRANSACT_ACTION='CREDITBUREAUDECISION'
                and DATE_AIP_SUBMITTED >='2014-04-01'd
                and INTERNAL_USER=0
            ) MORTGAGE_APPLICATION, 
            PRODUCT, 
            AGENT, 
            (select * from COMPANY where COMPANY_ID not in (2,3,26203) and FK_CHANNEL=1) COMPANY, 
            (select min(PRODUCT_ORDER) FIRSTPROD, 
                    FK_APPLICATION_NUMBER 
             from Application_Product
             Group By Fk_Application_Number) ONEPRODUCT 
    where   ONEPRODUCT.FK_APPLICATION_NUMBER=MORTGAGE_APPLICATION.APPLICATION_NUMBER 
        and ONEPRODUCT.FIRSTPROD = APPLICATION_PRODUCT.PRODUCT_ORDER
        and ONEPRODUCT.FK_APPLICATION_NUMBER = APPLICATION_PRODUCT.FK_APPLICATION_NUMBER
        and APPLICATION_PRODUCT.FK_PRODUCT=PRODUCT.PK 
        and APPLICATION_CASE_DETAILS.CASE_NUMBER=MORTGAGE_APPLICATION.CASE_NUMBER 
        and APPLICATION_CASE_DETAILS.AGENT_ID=AGENT.AGENT_ID 
        and AGENT.COMPANY_ID=COMPANY.COMPANY_ID 
  );
  disconnect from oracle;
quit;

Re: ORACLE prepare error: ORA-00972

anonymous_user — Wed, 24 Aug 2016 10:38:19 GMT

Why would this code run on fine on a Crystal Report but not when connected to Oracle via SAS?

Re: ORACLE prepare error: ORA-00972

Reeza — Wed, 24 Aug 2016 10:44:00 GMT

@Brandon16 wrote:

Why would this code run on fine on a Crystal Report but not when connected to Oracle via SAS?

Are you sure your connecting to Oracle and specifying your query correctly?

As indicated you haven't posted all of your code.

Re: ORACLE prepare error: ORA-00972

Kurt_Bremser — Wed, 24 Aug 2016 11:00:01 GMT

Then look what Crystal Reports actually does and how it connects to Oracle. Oracle does not allow names longer than thirty characters. Just google ORA-00972.

Re: ORACLE prepare error: ORA-00972

Kurt_Bremser — Wed, 24 Aug 2016 11:10:41 GMT

PS I have no idea about Crystal Reports, but it might let you use column labels instead of column names to address columns.

Re: ORACLE prepare error: ORA-00972

LinusH — Wed, 24 Aug 2016 13:46:07 GMT

Any code placed within a SQL pass through block is the headache for Oracle, SAS has no part in it.

So direct any concerns about your SQL to your DBA, or the person who developed it for you.

Re: ORACLE prepare error: ORA-00972

LinusH — Wed, 24 Aug 2016 13:46:34 GMT

Any code placed within a SQL pass through block is the headache for Oracle, SAS has no part in it.

So direct any concerns about your SQL to your DBA, or the person who developed it for you.

Re: ORACLE prepare error: ORA-00972

anonymous_user — Fri, 26 Aug 2016 09:41:58 GMT

Yes, the connection to Oracle is working fine as I have had to run other reports on this connection. It's just this one that is casuing me an issue. This is all of the code from a Crystal report and I have just dumped it into SAS.

Re: ORACLE prepare error: ORA-00972

Kurt_Bremser — Fri, 26 Aug 2016 10:10:30 GMT

Instead of just taking the code from the external tool and copy it into SAS, I'd rather rebuild it there. Look how the tables are represented in the library defined on the Oracle connection (use pro contents etc), and then apply the logic to that.