Desktop productivity for business analysts and programmers

Urgent help in converstion of oracle to AS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Urgent help in converstion of oracle to AS

 

 

Hi All,

I have an urgent requirment where in I have to convert the below oracle query to SAS. I am begnner in SAS, I tried
to paste the below query in PROC SQL under programme block and ran the query but seems that
we ahve few functions which needs to be converted. I am finding challenge in converting the last two lines of the code. Can anyone pls
help me here.



SELECT to_number(OLS_DYN_APPLICATIONS.APPLICATION_ID), OLS_VW_MIN_STATUS_DATE.MIN_DATE_OF_EFFECT, trunc(OLS_VW_APP_SUBMITTED_DATE.MIN_DATE_OF_EFFECT), OLS_DYN_APPLICATIONS.CREATOR_ID, ST_STOP_APP_RSN_TYPES_HIST.DESCRIPTION, ( ORIGINATORS.FAMILY_NAME ) || ', ' ||( ORIGINATORS.FIRST_NAME ) || DECODE(( ORIGINATORS.MIDDLE_NAMES ),NULL,NULL,' ' || ( ORIGINATORS.MIDDLE_NAMES )), ORIGINATOR_ORG_UNITS.COST_CENTRE, OLS_DYN_APPLICATION_PRODUCTS.PROMO_CODE FROM OLS_ST_PRODUCT_STATUSES HISTORY_STATUSES, OLS_DYN_SYSTEM_USERS ORIGINATORS, OLS_DYN_ORGANISATIONAL_UNITS ORIGINATING_ORG_UNITS, OLS_DYN_LEGAL_ENTITIES, OLS_ST_SALES_CHANNEL ORIGINATOR_ST_SALES_CHANNEL, OLS_DYN_LOAN_APPLICATIONS, OLS_DYN_STOPPED_APPLICATIONS DYN_STOPPED_APPS_HIST, OLS_ST_STOP_APP_REASON_TYPES ST_STOP_APP_RSN_TYPES_HIST, OLS_DYN_ORGANISATIONAL_UNITS ORIGINATOR_ORG_UNITS, OLS_DYN_STOPPED_APP_REASONS DYN_STOPPED_APP_RSNS_HIST WHERE ( OLS_DYN_APPLICATIONS.APPLICATION_ID=OLS_DYN_LOAN_APPLICATIONS.APPLICATION_ID ) AND ( OLS_DYN_APPLICATIONS.APPLICATION_ID=OLS_DYN_LEGAL_ENTITIES.APPLICATION_ID ) AND ( OLS_DYN_LOAN_APPLICATIONS.APPLICATION_ID=OLS_DYN_APPLICATION_PRODUCTS.APPLICATION_ID ) AND ( ORIGINATORS.ORGANISATIONAL_UNIT_ID=ORIGINATOR_ORG_UNITS.ORGANISATIONAL_UNIT_ID ) AND ( OLS_DYN_APPLICATIONS.ORIGINATOR_ID=ORIGINATORS.SYSTEM_USER_ID ) AND ( ( ( ORIGINATOR_ORG_UNITS.ORGANISATIONAL_UNIT_NAME ) ne 'OLS Project Team' And ( ORIGINATOR_ORG_UNITS.ORGANISATIONAL_UNIT_NAME ) ne 'Mortgages - OLS/HOLO Support Desk' ) AND ( OLS_DYN_LEGAL_ENTITIES.LEGAL_ENTITY_ID = (select min(DLE2.LEGAL_ENTITY_ID) from OLS_DYN_LEGAL_ENTITIES DLE2 where DLE2.APPLICATION_ID = OLS_DYN_APPLICATIONS.APPLICATION_ID) ) AND ORIGINATOR_ORG_UNITS.COST_CENTRE NOT LIKE '306:143' ) AND OLS_VW_PRODUCT_DETAILS.PRODUCT_NAME NOT IN ('Indicative Reverse Mortgage', 'Indicative Housing Loan', 'Indicative Equity Line') AND (trunc(OLS_DYN_APP_PROD_STATUSES.DATE_OF_EFFECT) BETWEEN trunc(sysdate-30) AND trunc(sysdate-1) OR trunc(OLS_DYN_APPLICATIONS.PRE_TO_FULL_APPROVAL_DATE) BETWEEN trunc(sysdate-30) AND trunc(sysdate-1)) )

thanks for taking your time.
Regards,
krishna

 


Accepted Solutions
Solution
‎05-16-2017 10:18 PM
Super User
Posts: 3,240

Re: Urgent help in converstion of oracle to AS

[ Edited ]

Why to you need to convert it? If you wrap it in an a SAS SQL PASSTHRU query you don't have to change any of your code:

 

proc sql;
  connect to oracle(--- put your Oracle connection stuff here ----);
  create table want as select * 
  from connection to oracle(

---- put your Oracle query here -----

  );
  disconnect from oracle;
quit;

 

View solution in original post


All Replies
Occasional Contributor
Posts: 13

Re: Urgent help in converstion of oracle to AS

[ Edited ]

I tried to write the decode funtion as
select family_name||','||first_name|| case when MIDDLE_NAMES is null then ' ' else ' '|| MIDDLE_NAMES end,

 

 

and for to_number(OLS_DYN_APPLICATIONS.APPLICATION_ID) ----------to informat OLS_DYN_APPLICATIONS.APPLICATION_ID $2;

 

not sure if I am right here

Solution
‎05-16-2017 10:18 PM
Super User
Posts: 3,240

Re: Urgent help in converstion of oracle to AS

[ Edited ]

Why to you need to convert it? If you wrap it in an a SAS SQL PASSTHRU query you don't have to change any of your code:

 

proc sql;
  connect to oracle(--- put your Oracle connection stuff here ----);
  create table want as select * 
  from connection to oracle(

---- put your Oracle query here -----

  );
  disconnect from oracle;
quit;

 

Occasional Contributor
Posts: 13

Re: Urgent help in converstion of oracle to AS

Hi 

 

 

Super User
Posts: 3,240

Re: Urgent help in converstion of oracle to AS

You aren't getting the usual SAS log messages at the end of your SQL step. That suggests an unbalanced quote, bracket or something similar.

 

Copy the Oracle code back into your Oracle query tool (SQL Plus?) and run it there - does it run correctly? If not correct it until it does then paste it back into your SAS program.

 

Respected Advisor
Posts: 4,138

Re: Urgent help in converstion of oracle to AS

@hk186002

I believe you're missing a closing bracket.

 

The Oracle SQL you've posted ends with a closing bracket:

  OR   trunc(OLS_DYN_APPLICATIONS.PRE_TO_FULL_APPROVAL_DATE)  BETWEEN  trunc(sysdate-30) AND trunc(sysdate-1))
  )

When you wrap this into a SAS SQL pass-through block then it should look like what @SASKiwi posted.

proc sql;
  connect to oracle(--- put your Oracle connection stuff here ----);
  create table want as select * 
  from connection to oracle(

---- put your Oracle query here -----

  );
  disconnect from oracle;
quit;

Looking into the log you've posted, the closing bracket for the from connection to oracle (....) is missing.

Line 104 in the log you've posted needs another closing bracket. I'd expect your code to look like:

103          OR   trunc(OLS_DYN_APPLICATIONS.PRE_TO_FULL_APPROVAL_DATE)  BETWEEN  trunc(sysdate-30) AND trunc(sysdate-1))
104           )
          );
Occasional Contributor
Posts: 13

Re: Urgent help in converstion of oracle to AS

Hi All,

 

thank you very much for the help...the passthrough worked and was the issue with brackets.....thanks gurus...bit pressure releaved...now its time to learn some SAS stuff ....thanks again...

Super User
Posts: 3,240

Re: Urgent help in converstion of oracle to AS

Great to hear. In that case please mark your post as answered.

Occasional Contributor
Posts: 13

Re: Urgent help in converstion of oracle to AS

Thank you Patrick...it workedSmiley Happy
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 276 views
  • 2 likes
  • 3 in conversation