Converting Oracle Subquery code in SAS EG

Amrit5 — Sat, 13 Jan 2018 10:12:52 GMT

Dear SAS tech team & blogger greetings for the day.

I want to convert below oracle subquery code into SAS EG code I m novice to EG coding plz guide me how to do it.

SELECT code_combination_id, ledger_id, period_num, NAME ledger_name,
period_name, period_year, unit, unit_name, account_type, ACCOUNT,
account_name, group1, group2, group3, group4, group5, department,
dept_name, group11, group12, branch, branch_name, product_line,
prd_line_name, SUM (opening) opening,
SUM (prov_opening) prov_opening,
SUM (NVL (period_dr, 0)) period_dr,
SUM (NVL (period_cr, 0)) period_cr,
SUM (NVL (net_period, 0)) net_period,
SUM (NVL (prov_net_period, 0)) prov_net_period,
(SELECT SUM (NVL (begin_balance_dr, 0) - NVL (begin_balance_cr, 0)
)
FROM gl_balances x
WHERE x.code_combination_id = gjl.code_combination_id
AND x.ledger_id = gjl.ledger_id
AND x.period_year = gjl.period_year
AND x.period_num = 1) ytd_opening,
(SELECT SUM ( NVL (x.begin_balance_dr_s, 0)
- NVL (x.begin_balance_cr_s, 0)
)
FROM apps.xxcns_provision_stat_gl_bal x
WHERE x.code_combination_id_s = gjl.code_combination_id
AND x.ledger_id_s = gjl.ledger_id
AND x.period_year_s = gjl.period_year
AND x.period_num_s = 1) prov_ytd_opening,
(SELECT SUM (NVL (x.period_net_dr, 0) + NVL (x.period_net_cr, 0)
)
FROM gl_balances x
WHERE x.code_combination_id = gjl.code_combination_id
AND x.ledger_id = gjl.ledger_id
AND x.period_year = gjl.period_year
AND x.period_num <= gjl.period_num) ytd_activity,
(SELECT SUM ( NVL (x.period_net_dr_s, 0)
+ NVL (x.period_net_cr_s, 0)
)
FROM apps.xxcns_provision_stat_gl_bal x
WHERE x.code_combination_id_s = gjl.code_combination_id
AND x.ledger_id_s = gjl.ledger_id
AND x.period_year_s = gjl.period_year
AND x.period_num_s <= gjl.period_num) prov_ytd_activity
--,Sum(YTD) Ytd
FROM (SELECT gl.NAME, gjl.period_name, gp.period_year,
gcc.code_combination_id, gjl.ledger_id, gjl.period_num,
gcc.segment1 unit,
(SELECT description
FROM fnd_flex_values_vl
WHERE flex_value_set_id = 1009895
AND flex_value_meaning = gcc.segment1
AND ROWNUM = 1) unit_name,
gcc.account_type, gcc.segment2 ACCOUNT,
(SELECT description
FROM fnd_flex_values_vl
WHERE flex_value_set_id = 1009896
AND flex_value_meaning = gcc.segment2
AND ROWNUM = 1) account_name,
(SELECT attribute1
FROM fnd_flex_values_vl
WHERE flex_value_set_id = 1009896
AND flex_value_meaning = gcc.segment2
AND ROWNUM = 1) group1,
(SELECT attribute2
FROM fnd_flex_values_vl
WHERE flex_value_set_id = 1009896
AND flex_value_meaning = gcc.segment2
AND ROWNUM = 1) group2,
(SELECT attribute3
FROM fnd_flex_values_vl
WHERE flex_value_set_id = 1009896
AND flex_value_meaning = gcc.segment2
AND ROWNUM = 1) group3,
(SELECT attribute4
FROM fnd_flex_values_vl
WHERE flex_value_set_id = 1009896
AND flex_value_meaning = gcc.segment2
AND ROWNUM = 1) group4,
(SELECT attribute5
FROM fnd_flex_values_vl
WHERE flex_value_set_id = 1009896
AND flex_value_meaning = gcc.segment2
AND ROWNUM = 1) group5,
gcc.segment3 department,
(SELECT description
FROM fnd_flex_values_vl
WHERE flex_value_set_id = 1009897
AND flex_value_meaning = gcc.segment3
AND ROWNUM = 1) dept_name,
(SELECT attribute1
FROM fnd_flex_values_vl
WHERE flex_value_set_id = 1009897
AND flex_value_meaning = gcc.segment3
AND ROWNUM = 1) group11,
(SELECT attribute2
FROM fnd_flex_values_vl
WHERE flex_value_set_id = 1009897
AND flex_value_meaning = gcc.segment3
AND ROWNUM = 1) group12,
gcc.segment4 branch,
(SELECT description
FROM fnd_flex_values_vl
WHERE flex_value_set_id = 1009898
AND flex_value_meaning = gcc.segment4
AND ROWNUM = 1) branch_name,
gcc.segment5 product_line,
(SELECT description
FROM fnd_flex_values_vl
WHERE flex_value_set_id = 1009899
AND flex_value_meaning = gcc.segment5
AND ROWNUM = 1) prd_line_name,
SUM ( NVL (begin_balance_dr, 0)
- NVL (begin_balance_cr, 0)
) opening,
SUM ( NVL (begin_balance_dr_s, 0)
- NVL (begin_balance_cr_s, 0)
) prov_opening,
SUM (NVL (gjl.period_net_dr, 0)) period_dr,
SUM (NVL (gjl.period_net_cr, 0)) period_cr,
SUM (NVL (gjl.period_net_dr, 0) - NVL (period_net_cr, 0)
) net_period,
SUM ( NVL (stat.period_net_dr_s, 0)
- NVL (period_net_cr_s, 0)
) prov_net_period
FROM gl_balances gjl,
apps.xxcns_provision_stat_gl_bal stat,
gl_ledgers gl,
gl_code_combinations gcc,
gl_periods gp
WHERE gjl.code_combination_id = gcc.code_combination_id
AND gjl.ledger_id = gl.ledger_id
AND gjl.period_name = gp.period_name
AND gjl.actual_flag = 'A'
AND gjl.currency_code = 'INR'
AND gjl.code_combination_id = stat.code_combination_id_s(+)
AND gjl.period_name = stat.period_name_s(+)
AND gjl.ledger_id = stat.ledger_id_s(+)
GROUP BY gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gl.NAME,
gcc.account_type,
gjl.period_name,
gcc.code_combination_id,
gjl.ledger_id,
gjl.period_num,
gcc.account_type,
gp.period_year) gjl
WHERE 1 = 1
AND ABS (NVL (opening, 0))
+ ABS (NVL (period_dr, 0))
+ ABS (NVL (period_cr, 0)) != 0
GROUP BY code_combination_id,
ledger_id,
period_num,
NAME,
unit,
unit_name,
ACCOUNT,
account_name,
group1,
group2,
group3,
group4,
group5,
period_name,
period_year,
department,
dept_name,
group11,
group12,
branch,
branch_name,
product_line,
prd_line_name,
account_type;

Re: Converting Oracle Subquery code in SAS EG

LinusH — Sat, 13 Jan 2018 13:56:47 GMT

To EG you mean SAS code and not necessarily implemented in the Query Builder (I hope).
If your data will reside in Oracle the simplest is to wrap your code into an explicit SQL pass through block.
If you really need to translate it you need to identify the differences between the SQL dialects. I can see some Oracle functions in your code, and the (+) whatever that mean.
Also, there is no (supported) notion of ROWNUM in SAS SQL, in some cases the unsupported function monotonic() may work.

Re: Converting Oracle Subquery code in SAS EG

SASKiwi — Sat, 13 Jan 2018 22:04:00 GMT

If you want to run this query in EG against an Oracle database then all you need to do is wrap the query into an SQL passthru step:

proc sql;
  connect to Oracle ...put your connection details here.....;
  create table want as
  select * 
  from connection to Oracle(
  ==> put your Oracle query here
  );
quit;

Re: Converting Oracle Subquery code in SAS EG

Amrit5 — Mon, 15 Jan 2018 06:26:30 GMT

Dear SASKiwi,

Thank you so much for your valuable reply

I already have all the tables used in query in SAS library , do I need oracle connection credentials in that case also .Plz elaborate me what is to be put in place of

connect to Oracle ...put your connection details here.....;

Re: Converting Oracle Subquery code in SAS EG

LinusH — Mon, 15 Jan 2018 07:19:13 GMT

Please be crisp, by SAS library do you mean data is now stored in a Base SAS library, or do you have a library defined in SAS pointing to Oracle?
If the first you need to decide your query.
If the later use the CONNECT USING option.

Re: Converting Oracle Subquery code in SAS EG

SASKiwi — Mon, 15 Jan 2018 19:41:40 GMT

If you are going to use SQL passthru, then you don't need the Oracle tables in a SAS library.

Also SAS/ACCESS to Oracle needs to be installed and licensed on your SAS server. I'm guessing this is the case otherwise you wouldn't have the Oracle tables in a SAS library.

Here are the details you will need in your Oracle connection:

   connect to oracle (user = MyUser password = MyPassword path = MyPath);

topic Re: Converting Oracle Subquery code in SAS EG in SAS Data Management

Converting Oracle Subquery code in SAS EG

Re: Converting Oracle Subquery code in SAS EG

Re: Converting Oracle Subquery code in SAS EG

Re: Converting Oracle Subquery code in SAS EG

Re: Converting Oracle Subquery code in SAS EG

Re: Converting Oracle Subquery code in SAS EG