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);
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.