Power query - sql statement

melq64

New Member
Joined
Aug 21, 2017
Messages
49
I'm trying to extract subset of data from Oracle database. I have access and connection successfully setup via Excel Power Query.

The two tables connected to Excel report contains millions of rows of data. To extract 12months of this data is approx 11million rows. My Excel report using PowerPivot and slicers, is linked to these tables. Loading and refreshing the tables will take too long. Looking for more efficient method to overcome this.

So I thought of using the SQL statement in PowerQuery connection to extract smaller subset of this data - 6months data instead of 12months.

SQL is not my strength so I'm looking for an SQL statement that extracts only 6months of data and when report is refreshed, it will only refresh 6months data from present day back 6months i.e. [present date - 6months]


Hope this made sense. Appreciate any help:)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Perhaps like
Code:
SELECT fields you want
FROM table/s
WHERE somedatefield > Date - 183

If you don't need individual records & instead can work with sums, then this could hugely reduce the dataset. Something like
Code:
SELECT some fields, SUM(a field), SUM(another field), SUM(one more)
FROM table/s
WHERE somedatefield > Date - 183
GROUP BY some fields

Maybe you could try
WHERE somedatefield >= DateAdd("m",-6,Date)

or even
WHERE somedatefield >= DateSerial(Year(Date),Month(Date)-6, Day(Date))

or some variation
 
Last edited:
Upvote 0
Thanks for your tips but I need individual rows for further calculations within Powerpivot. However, am getting this error based on your suggestions: (sorry obviously am not familiar with sql)

statement 1:
select * from EXCEPTION_SUMMARY
where ACCEPTANCE_DATE > Date - 183;


statement 2:
select * from EXCEPTION_SUMMARY
where ACCEPTANCE_DATE >=DateSerial(Year(Date),Month(Date)-6,Day(Date));

-------------------------------------------------------------------------------------------
ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action:
Error at Line: 5 Column: 45

-------------------------------------------------------------------------------------------
 
Upvote 0
The solution needs to suit the specifics of Oracle's database/SQL. I can't help with that - I have nil specific knowledge of Oracle.

If no-one else chimes in here (Mr Excel) suggest you search Oracle documentation. Or even start a new thread seeking Oracle specific advice.

regards
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top