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
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 659 Datasets
- 116 SQL DataFlows
- 2.2K Magic ETL
- 816 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 82 App Studio
- 45 Variables
- 776 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 82 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive