cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Amrit5
‎02-01-2018
  • 2 Posts
  • 0 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Converting Oracle Subquery code in SAS EG

by Calcite | Level 5 in SAS Data Management
‎01-15-2018 01:26 AM
‎01-15-2018 01:26 AM
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.....;    ... View more

Converting Oracle Subquery code in SAS EG

by Calcite | Level 5 in SAS Data Management
‎01-13-2018 05:12 AM
‎01-13-2018 05:12 AM
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; ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎02-01-2018 02:33 AM
Quick links