Alex Vinall

  • Home
  • Photography
    • Flora & Fauna
    • Landscape
    • Architecture
  • Blog
  • Home
  • Photography
    • Flora & Fauna
    • Landscape
    • Architecture
  • Blog

How to SELECT a comma-separated list of column names for all tables in an Oracle schema

22/4/2016

2 Comments

 
Recently I needed to perform a merge of two Oracle database schema. Both schema held the same tables, with the same columns, but due to the different ways that these schema had been created, there was no guarantee that the columns of a table in one schema were in the same order as the same columns in the same table in the other schema.

You'd maybe hope that when performing an INSERT INTO SELECT * FROM that Oracle would cleverly perform some wizardry to map column names explicitly by name. Sadly, this isn't the case, and it would be pretty catastrophic for the Amount column to end up in the VatAmount column of the merged schema.

So, in order to do this I needed to quickly generate INSERT statements that mapped the columns explicitly. Thankfully, the LISTAGG function in Oracle since 11gR2 allows us to do this in combination with the USER_TAB_COLS table that holds this data, with very few lines of code. Here's how to use SQL with an Oracle database schema to get a list of columns per table.

    
Alex
2 Comments

How to unmerge cells and fill values in Excel

12/4/2016

0 Comments

 
View post on imgur.com
I've needed to do this quite a few times, but couldn't easily find a clear example online. Here's how to unmerge cells and fill values in Excel.

For the simple case where the range only contains one distinct value, it's trivial to copy the value to all cells. However, where there are many distinct values in the range, there are a couple more steps involved. This still only takes a few seconds to do.

  1. Highlight the range that you want to unmerge and fill.
  2. Click the "Merge & Center" button to unmerge. For each of the distinct values in the range, this will leave all but the first cell blank.
  3. Click on the first blank cell, and use a formula to copy the cell above. For example, press equals, up, enter.
  4. Copy this cell.
  5. Highlight the range again, and press F5 to access to Go To menu.
  6. Click Special... and select Blanks. Press OK.
  7. Paste.

If you want to keep just values and remove the formula, simply highlight the range again, copy, paste, and paste values only. E.g. Ctrl + C, Ctrl + V, Ctrl, V

Alex
0 Comments

A couple of examples

10/4/2016

0 Comments

 
There are a lot of people posting their EDC on the internet nowadays. A lot of this content is posted through everydaycarry.com or the EDC subreddit, others have their own blogs. Here are a couple of posts I think are worth reading.

Matt Mullenweg

Matt is one of the founding developers of WordPress, and also founded Automattic, a platform that powers a lot of impactful web products including Wordpress itself. His impressive career history is worth a read. He’s into photography and is a professional developer, so I share a lot of his requirements in an EDC.

Matt’s 2016 EDC post is here.

The Urban Prepper

The Urban Prepper is a Seattle-based EDCer who focuses on emergency-situation urban preparedness. He puts an impressive amount effort into carrying a lot of gear in as little space as possible.

His YouTube channel is here.

​Alex
0 Comments

The minimum set of efficient tools for any realistic situation

2/4/2016

0 Comments

 
In the previous post I said that my EDC motto was to carry the minimum set of efficient tools to prepare for any realistic situation. I’ll explain a bit more about what that means.

The minimum set: don’t carry it if it doesn’t serve a purpose. Don’t carry more than two of anything that does serve a purpose, and only carry more than one if one of them might break. This is known as “two is one, one is none” (which was ostensibly practiced by Abraham Lincoln, and some claim that the phrase originated from the U.S. Navy SEALs).

Efficient tools: if one tool can replace two, carry the one rather than the two. However, if that one tool can’t do both jobs effectively, it’s not a suitable replacement for the two. Whilst you can open boxes and cut paper both with a knife, knives can rip paper and damage your cutting surface. Knives aren’t as efficient as scissors at the job. An efficient tool in this case would be one that contains both a knife and scissors without adding much weight or bulk.

Any realistic situation:  I will carry items that I don’t use every day, but only where they would efficiently serve a purpose in a realistic situation. Most of the time I’ll carry some basic first aid equipment, but only include anti-allergy medication in summer, as it won’t realistically be needed in winter. I’ll carry a mini-DisplayPort to VGA adapter to connect my laptop to a projector, because realistically I might need to give a presentation at work and they won’t be set up with DP or HDMI. Living in the UK, realistically I don’t need to carry sunglasses very often.

When thinking about what to pack for a trip, work or photography, this motto helps me to be prepared, and to minimise the risk of something going wrong that can't easily be sorted. If you’re prepared for every realistic situation, when something goes wrong it can be righted in a few minutes, rather than ruining the whole trip.

Alex
0 Comments

    Blog

    Tech, quotes and EDC.

    Archive

    November 2016
    July 2016
    May 2016
    April 2016
    March 2016

    Tags

    All
    EDC
    Excel
    Oracle
    Travel

    RSS Feed

Powered by Create your own unique website with customizable templates.