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;
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;
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.....;
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);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.