Sunday, November 1, 2015

Sydney OTN Day 2015

Just want to give a shout out for Sydney OTN on 30th November 2015. There are a couple of sessions that caught my eyes. Well, they are mainly on the database development.

Supercharge Your SQL Skillset
JSON Support in Oracle 12c
12c for Developers
Oracle Database 12c New Features for Developers and DBAs
Database as a Service (DBaaS) Cloud: HA Architecture and Consolidation Methods
Optimize OLAP & Business Analytics Performance with Oracle 12c In-Memory Database Option
PaaS4SaaS The Options
What does Digital Disruption Mean To The Oracle Ecosystem?

Check out the speaker profiles.

The ticket is about ~$53 (+fee+gst) and there are coffee break and light lunch.

Update: I have bought the ticket and looking forward to the 30th of November. :D

Update 30/11/2015: I'm surprised the amount of people turned up (about 60-80) and the topic/speakers covered in the event is absolutely valuable. However, I did find out that Oracle Market was rather small (else I would expect those rooms would jam packed) and that rather made me sad. I will probably put Oracle skill development aside for a while as the demand was not as high as I would imagine and focusing on what the majority Australia market required.

I would like to thank the organizer for setting up this event as it proves to be really helpful and would highly recommend people, who still attached to Oracle Technology, to attend. You never know what new ideas you may find.

Friday, August 28, 2015

Excel does not like Access Nz() function

If you happened to hook up an Excel report to retrieve data from Access and one day your Excel refuse to download the data, have look on the queries in Access that are using Nz() function.

To whoever does not know what Nz() function is, it is used for null value checking and if true, the second argument of the function will be returned. This function is great formatting result, calculation and etc.

To overcome this issue, my solution was to replace Nz() with IIF() function (e.g. IIF(field1 is null, 0, field1) means if field1 is null, return 0 else return field1)). Once I got all the Nz() function replaced, everything went back to normal. Amazing...

The version of Excel and Access for my case is 2007. Hope this helps whoever use Access query with Nz() and suddenly, Excel stops retrieve data from the query.

Sunday, March 15, 2015

Beware of MS Access 2007 crosstab

My case here was,  MS Access was used to import live data from an IBM database using ODBC drivers and there are no data corruption happened. Triple checked that.

I was working a report for my company sales manager where the report was about putting all the type of sales rebates in a crosstab/pivot style report (Sales transactions as row, rebates type as column and values of each rebates for each sales made up the cells). Then the sales manager will use Excel to import the data from the Access database and work on his analysis in Excel. However, MS Access 2007 crosstab has failed to show some of the expected value when there are more than 40+ columns as rebate types.

After some investigation, removing 10 columns seems to help restore those values back, however, all the columns are deemed to be crucial for the sales manager. In the end, I finally figured out by export the data to MS SQL Server Express and run a stored procedure to generate a pivot view there. Due to the MS SQL sat at my computer, where I might turn it off occasionally, the result is exported into an XML file so the sales manager able link Excel to the XML file instead.

Therefore, beware if you are using crosstab because someday you may find some of the value  missing due to too many columns created.