Postgresql Basics

I’m back to tell you a few basic things about Postgresql for anyone struggling or just looking for answers in general.

The main basics still apply!  SELECT, FROM, JOINs, WHERE, HAVING, GROUP BY, ORDER BY.

SUBQUERIES:  All subqueries need an alias.

COALESCE:  Used like ‘NVL’ or ‘ISNULL’.  Here’s the syntax:  COALESCE(<column name>, <value to be used if it is null>, <next value to be used if that value is null>).  Here’s an example:  COALESCE(Invoice_Amount, 0).  So this example means in the event the ‘Invoice_Amount’ column is null, you would like a zero placed there.  You can also use cascading COALESCE values.  Here’s the syntax:  COALESCE(<column name>, COALESCE(<value to be used if it is null>, <next value to be used if that value is null>)).  Here’s an example:  COALESCE(Trip_Purpose, COALESCE(Exempt_Reason, ‘None’)).  Note:  Don’t enclose the field names in single quotes; only do this with a value you are identifying like ‘None’ in this example.

REPLACE:  Super helpful.  Allows you to replace something in a field with something else.  When to use?  Well, if you’re going to export the query to a csv file and some of your fields have commas, you’ll want to use this handy feature.  Here’s the syntax:  REPLACE(<column name>, ‘<value in field to replace>’, ‘<value to put in its place>’).  Here’s an example:  REPLACE(Job_Title, ‘,’, ‘ ‘).  May be hard to see but in this example we want to replace the comma with a space.  The value to be replaced and the value to replace it with need to be enclosed in single quotes.  An example of a before and after:  Value before REPLACE:  Manager, Operations.  Value after REPLACE:  Manager Operations.

Well that’s all I have time for today.  Stay tuned for more helpful info on various topics including Postgresql, Business Objects, Crystal Reports, Cognos, and Tableau!

BITietje 🙂


Postgresql Got You Down?

I’ve been working with Postgresql now for a few months.  It’s not as scary as I thought!

I wanted to post a couple of things I learned along the way in the hopes that it might help others looking for answers.  One of the things that kind of bugs me about some message boards or help docs is that some of them don’t explain what the syntax means.  If you’re new to sql, this can be a brain crusher because you’ll want to copy and paste into your editor but you will keep getting some type of error and won’t be able to figure out how to fix it.  I hope to explain those things here to make it easier for anyone trying to learn or just stuck on a specific piece of syntax.

Feel free to leave comments or ask questions!

BITietje 🙂


Business Objects 4.0 – Input Control / Select to Display Block

This gallery contains 17 photos.

Business Objects 4.1 – Setting a block to be displayed by selection within Input Controls (To follow this blog, I’m assuming you already know how to navigate within BO and you are familiar with creating and modifying documents). I’m sure you already know how to “hide when” within BO. (In case you don’t, instructions are […]

An image free status indicator – using CSS3 and HTML5 alone.

When dealing with creating web applications, especially dashboards, there are many scenarios when one would like to visually represent the status of something. A nice “light bulb” or glass dot is great for this; a red or green status can easily be communicated this way.

When creating a web app, this can easily be done using an image of a colored dot. However, when dealing with mobile or modern web applications it can sometimes be desirable, or even necessary, to do this without the use of images. Luckily there is a way to accomplish this, and make it look great, without using any images at all; using HTML5 and CSS3 alone:

You can see a demo of this, and download the code, here:

To do this, first place this small amount of HTML on your page or in your app where you would like the status indicator to appear:

   <span class="dot"><span class="dot-inner"></span></span>

Second, copy and paste this CSS into your system’s style sheets:

.dot {
overflow: hidden;
border-width: 2px;
border-color: #666;
padding-top: 6px;
padding-left: 6px;
width: 32px;
height: 32px;
display: inline-block;
border-radius: 50%;
margin: 0.2em 0.4em 0.2em 0;
line-height: 32px;
background: #cccccc;
-webkit-box-shadow: inset 0 -6px 6px rgba(0, 0, 0, 0.5), 0 1px 0 rgba(255, 255, 255, 0.3), inset 0 1px 0 rgba(255, 255, 255, 0.3);
-moz-box-shadow: inset 0 -6px 6px rgba(0, 0, 0, 0.5), 0 1px 0 rgba(255, 255, 255, 0.3), inset 0 1px 0 rgba(255, 255, 255, 0.3);
box-shadow: inset -6px -6px 6px rgba(0, 0, 0, 0.3), inset 3px 3px 6px rgba(255, 255, 255, 0.3);
.dot-inner {
background: white;
-webkit-box-shadow: 0px 0px 6px 3px rgba(255, 255, 255, 1);
-moz-box-shadow: 0px 0px 6px 3px rgba(255, 255, 255, 1);
box-shadow: 0px 0px 6px 3px rgba(255, 255, 255, 1);
border-radius: 50%;
overflow: hidden;
width: 6px;
height: 6px;
display: block;
line-height: 0;
.dot-red {
background: #990000;
.dot-green {
background: #009900;
.dot-yellow {
background: #999900;
/** add more colors here if you need **/

That’s it, you are done. Refresh the page and you should see the glass dot. It will be gray (or “off”) by default. To change its color, simple add an extra class to the outer span as such:

Red - <span class="dot dot-red"><span class="dot-inner"></span></span>
Yellow - <span class="dot dot-yellow"><span class="dot-inner"></span></span>
Green - <span class="dot dot-green"><span class="dot-inner"></span></span>

One reason this may be preferred over images is in the case when a user may be zooming in or out of a page/application. Because this is not an image it will always look crisp and smooth, no image degradation. Also, you have the flexibility of changing the dot’s color simply by adjusting the style sheet, no need to create a new image to represent each new color. You can even do this programmatically using something like jQuery. This can be especially handy if you need to represent a wide range of colors.

I hope you find this helpful. Feel free to let us know if you use this technique in your own application.

Google chairman notes the arrival of big data and machine intelligence everywhere

In a rare interview, Google Chairman Eric Schmidt gives Bloomberg his outlook for 2014 trends ( In this 2 minutes video, Schmidt talks about several obvious trends, like “everyone will have a smart phone… essentially connected to a super computer”. Another important remark Schmidt made is about the “arrival of big data and machine intelligence everywhere”.

It is not about the invention of big data and machine intelligence. It is about the arrival. I find that to be the essence of the big data revolution. The idea that we can leverage computers and technology to process data and derive information and intelligence from thatdata is certainly not new. However, it has been hampered by slow processors, lack of storage capabilities, and inability to amass sufficiently significant amounts of data to deliver this intelligence. Past attempts to overcome the technology shortfalls have been painful and could only deliver meaningful results when applying large investments. Well, all that is changing now. It is now affordable and relatively simple to perform analytical tasks that were considered extremely challenging only a few years ago. My news analytics experiment is one example of that ( IT departments and organizations are finding that barriers that prevented them from delivering google like level of services are quickly disappearing, and the bar set by consumer web sites for level of service, sophistication and relevance can now be met with of enterprise applications. This is the challenge we in the BI industry will have to face and meet over the next few years. I believe that leveraging the knowledge and understanding developed during decades of data warehousing development to overcome technological impossibilities positions us, the BI industry veterans in a unique position to help drive the big data revolution in the corporate world. Modeling, abstraction skills, problem solving and technical know-how are the building blocks needed for any big data related project, and these are the traits of the trade for us in the BI industry.

Cleartelligence BLOG is LIVE!

Welcome to our new Cleartelligence blog.  We launch this blog to foster collaboration and innovation within the Business Intelligence/Analytics industry.   We hope to provide many interesting viewpoints on industry trends, as well as, present technical know-how solutions on some of the best technologies in this fast growing space

At Cleartelligence we have a passion for anything Business Intelligence.  Our expert team focuses on Business Analytics, Data Warehousing, Data Visualization, Interactive Reporting, Mobility, and BI Embedded Applications.    As the industry rapidly evolves so does our expertise and desire to become the best at delivering high-value BI solutions.  Our BLOG’s represent this passion as we do our part to foster industry collaboration and innovation.

We look forward to working with our current and future customers, partners, and community developers.