BookmarkSubscribeRSS Feed

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.

12 REPLIES 12
Reeza
Super User

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. 

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

Kurt_Bremser
Super User

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.

 

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

Reeza
Super User

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

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.

Kurt_Bremser
Super User

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.

Kurt_Bremser
Super User

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.

LinusH
Tourmaline | Level 20

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.

Data never sleeps
LinusH
Tourmaline | Level 20

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.

Data never sleeps
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 2459 views
  • 0 likes
  • 5 in conversation