Urgent help in converstion of oracle to AS

[hk186002]

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

[hk186002]

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

[SASKiwi · Accepted solution]

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;

[hk186002]

Hi SASKiwi,

thank you very much for your inputs. I t Below is the code I have used as said

proc sql;
connect to oracle  (user=xxxx password=yyyy path='CMI1p' readbuff=100000);
select *
from connection to oracle (
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),.....

[SASKiwi]

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.

[Patrick]

@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           )
          );

[hk186002]

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

[SASKiwi]

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

[hk186002]

Thank you Patrick...it worked:)