Prepare Trial Balance using Oracle Managed Cloud data
We have migrated Oracle Managed Cloud data to DOMO and now trying to develop a Trial balance report.
I have added an attached query to create Dataset. When I run the data set it is running continuously and no completion.
I tried creating a small query to run but still the same issue. I am using MYSQL for the query. The same query is working fine from the Oracle database quickly.
We have approx 3.3. M records
select
gb.`LEDGER_ID`
, date_format(gb.`PERIOD_NAME`,'%b-%d') period_nm
, gb.`PERIOD_YEAR`
, gb.`PERIOD_NUM`
, gb.`CODE_COMBINATION_ID`
, gc.`SEGMENT1`
, gc.`SEGMENT2`
, gc.`SEGMENT3`
, gc.`SEGMENT4`
, gc.`SEGMENT5`
, gc.`SEGMENT6`
, gc.`SEGMENT7`
, gc.`SEGMENT8`
, sum(gb.`PERIOD_NET_DR`) p_net_dr
, sum(gb.`PERIOD_NET_CR`) p_net_cr
, (SELECT sum(`BEGIN_BALANCE_DR`-`BEGIN_BALANCE_CR`)+(`PERIOD_NET_DR`-`PERIOD_NET_CR`)
from `fmc_gl_balances`
where ledger_id = gb.`LEDGER_ID`
and `ACTUAL_FLAG` = 'A'
and `PERIOD_YEAR` = gb.`PERIOD_YEAR`
and `CODE_COMBINATION_ID` = gb.`CODE_COMBINATION_ID`
and `PERIOD_NUM` = gb.`PERIOD_NUM`-1
group by ledger_id,
period_year,
period_num,
code_combination_id) begin_bal
from
`fmc_gl_balances` gb
LEFT JOIN `fmc_gl_code_combinations` gc ON gb.`CODE_COMBINATION_ID` = gc.`CODE_COMBINATION_ID`
and gb.`ACTUAL_FLAG` = 'A'
and gb.`PERIOD_NUM` between 2 and 12
group BY
gb.`LEDGER_ID`
, date_format(gb.`PERIOD_NAME`,'%b-%d')
, gb.`PERIOD_YEAR`
, gb.`PERIOD_NUM`
, gb.`CODE_COMBINATION_ID`
, gc.`SEGMENT1`
, gc.`SEGMENT2`
, gc.`SEGMENT3`
, gc.`SEGMENT4`
, gc.`SEGMENT5`
, gc.`SEGMENT6`
, gc.`SEGMENT7`
, gc.`SEGMENT8`
UNION
select
gb.`LEDGER_ID`
, date_format(gb.`PERIOD_NAME`,'%b-%d') period_nm
, gb.`PERIOD_YEAR`
, gb.`PERIOD_NUM`
, gb.`CODE_COMBINATION_ID`
, gc.`SEGMENT1`
, gc.`SEGMENT2`
, gc.`SEGMENT3`
, gc.`SEGMENT4`
, gc.`SEGMENT5`
, gc.`SEGMENT6`
, gc.`SEGMENT7`
, gc.`SEGMENT8`
, sum(gb.`PERIOD_NET_DR`) p_net_dr
, sum(gb.`PERIOD_NET_CR`) p_net_cr
, (SELECT sum(`BEGIN_BALANCE_DR`-`BEGIN_BALANCE_CR`)+(`PERIOD_NET_DR`-`PERIOD_NET_CR`)
from `fmc_gl_balances`
where ledger_id = gb.`LEDGER_ID`
and `ACTUAL_FLAG` = 'A'
and `PERIOD_YEAR` = gb.`PERIOD_YEAR`-1
and `CODE_COMBINATION_ID` = gb.`CODE_COMBINATION_ID`
and `PERIOD_NUM` = 12
group by ledger_id,
period_year,
period_num,
code_combination_id) begin_bal
from
`fmc_gl_balances` gb
LEFT JOIN `fmc_gl_code_combinations` gc ON gb.`CODE_COMBINATION_ID` = gc.`CODE_COMBINATION_ID`
and gb.`ACTUAL_FLAG` = 'A'
and gb.`PERIOD_NUM` = 1
and substring(gc.`SEGMENT2`,1,1) in ('1','2','3')
group BY
gb.`LEDGER_ID`
, date_format(gb.`PERIOD_NAME`,'%b-%d')
, gb.`PERIOD_YEAR`
, gb.`PERIOD_NUM`
, gb.`CODE_COMBINATION_ID`
, gc.`SEGMENT1`
, gc.`SEGMENT2`
, gc.`SEGMENT3`
, gc.`SEGMENT4`
, gc.`SEGMENT5`
, gc.`SEGMENT6`
, gc.`SEGMENT7`
, gc.`SEGMENT8`
UNION
select
gb.`LEDGER_ID`
, date_format(gb.`PERIOD_NAME`,'%b-%d') period_nm
, gb.`PERIOD_YEAR`
, gb.`PERIOD_NUM`
, gb.`CODE_COMBINATION_ID`
, gc.`SEGMENT1`
, gc.`SEGMENT2`
, gc.`SEGMENT3`
, gc.`SEGMENT4`
, gc.`SEGMENT5`
, gc.`SEGMENT6`
, gc.`SEGMENT7`
, gc.`SEGMENT8`
, sum(gb.`PERIOD_NET_DR`) p_net_dr
, sum(gb.`PERIOD_NET_CR`) p_net_cr
, 0 begin_bal
from
`fmc_gl_balances` gb
LEFT JOIN `fmc_gl_code_combinations` gc ON gb.`CODE_COMBINATION_ID` = gc.`CODE_COMBINATION_ID`
and gb.`ACTUAL_FLAG` = 'A'
and gb.`PERIOD_NUM` = 1
and substring(gc.`SEGMENT2`,1,1) in ('4','5','6','7','8')
group BY
gb.`LEDGER_ID`
, date_format(gb.`PERIOD_NAME`,'%b-%d')
, gb.`PERIOD_YEAR`
, gb.`PERIOD_NUM`
, gb.`CODE_COMBINATION_ID`
, gc.`SEGMENT1`
, gc.`SEGMENT2`
, gc.`SEGMENT3`
, gc.`SEGMENT4`
, gc.`SEGMENT5`
, gc.`SEGMENT6`
, gc.`SEGMENT7`
, gc.`SEGMENT8`
Comments
-
This is not a Domo problem, this looks like a SQL optimization problem.
Do you have any indexes? Have you tried paring down your query until it runs?
If you're not good at optimizing SQL, have you considered using MagicETL? There's nothing you're doing in your SQL that you can't do in Magic, and Magic will ultimately probably be more performant given the transforms you're doing.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 303 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 637 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 763 Beast Mode
- 65 App Studio
- 42 Variables
- 709 Automate
- 184 Apps
- 460 APIs & Domo Developer
- 54 Workflows
- 11 DomoAI
- 39 Predict
- 16 Jupyter Workspaces
- 23 R & Python Tiles
- 401 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 8 Software Integrations
- 134 Manage
- 131 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive