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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 712 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 104 Community Announcements
- 4.8K Archive