NimbleText

Manipulate text and data with light-weight patterns

How to Generate Views The Easy Way

So you want to create a view. Let's say you already know the columns you want to use (or if you don't, get that sorted out first, then come back here. Ready? Good.)

Let's say we want a simple view based on all the columns of an existing table. First we can collect the list of columns from our existing table, using any of the methods described in this article 'How do you list the columns of a table, quickly!'

Imagine our Contacts table has these columns:

FirstName
LastName
Company
Address1
Address2
CitySuburb
State
ZIPCode
WorkPhone
BusinessPhone
Mobile
Fax
Email
Web

A very simple create view statement that wraps up this table would look like this:

Create View ContactsView AS
Select
  FirstName,
  LastName,
  Company,
  Address1,
  Address2,
  CitySuburb,
  State,
  ZIPCode,
  WorkPhone,
  BusinessPhone,
  Mobile,
  Fax,
  Email,
  Web
from Contacts

The central component is a comma-separated list of columns. We can turn our basic list of columns into a comma separated list of columns very easily, avoiding all repetitive paw-work, by using the simplest pattern imaginable in NimbleText: 

$0,

This will turn our list of columns into a comma-separated list of columns.

Now say we want something a little trickier: we want to rename the columns so that they are friendly for the reader. If this is what we're after:

FirstName as [First Name],
LastName as [Last Name],
Company as [Company],
Address1 as [Address1],
Address2 as [Address2],
CitySuburb as [City Suburb],
State as [State],
ZIPCode as [ZIPCode],
WorkPhone as [Work Phone],
BusinessPhone as [Business Phone],
Mobile as [Mobile],
Fax as [Fax],
Email as [Email],
Web as [Web],

We can generate this in NimbleText by using a simple built in function 'to words' that splits a PascalCased name into its constituent words. Try this pattern:

try it → 
$0 as [<% $0.toWords()%>],

Again, we've avoided all the repetitive paw-work.

For extra credit (but not a lot of benefit) we can let NimbleText generate the rest of the view definition. Here's a pattern in NimbleText that does it all:

try it → 
$once
Create View ContactsView AS
Select
$each
  $0 as <% $0.toWords()%> <% if ($rowNumOne != $numRows) {','} %>
$once
from Contacts

Now in the click of a button we get our complete view:

Create View ContactsView AS
Select
  FirstName as [First Name],
  LastName as [Last Name],
  Company as [Company],
  Address1 as [Address1],
  Address2 as [Address2],
  CitySuburb as [City Suburb],
  State as [State],
  ZIPCode as [ZIPCode],
  WorkPhone as [Work Phone],
  BusinessPhone as [Business Phone],
  Mobile as [Mobile],
  Fax as [Fax],
  Email as [Email],
  Web as [Web]
from Contacts

Pulling that pattern apart we see use of the '$once' and '$each' keywords, use of embedded javascript, and use of the keywords $rowNumOne and $numRows.

But remember that simple patterns like our first example, '$0,' are useful all by themselves, and give you plenty of bang for your buck (it's zero dollars after all!). You can slowly build up to more complex examples, always getting more value along the way.

And there we have it, a custom user-friendly View generator, and most of the wisdom you need to tailor your own custom views.

Of course this is just one type of SQL statement that NimbleText will help you generate. There's no limit to what types of text you can produce with it.

We've also written guides on many other ad-hoc queries you can generate:

Further help

You can also get general help, help on all the symbols and keywords, or on the built-in functions, filtering with a where clause, help with the powerful command-line automation, or applying custom formats to your dates and times.

You need to purchase a license to unlock all the features in NimbleText.

If you haven't downloaded NimbleText yet, then for added power, privacy and versatility I sincerely think you should download it now.

Download NimbleText