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:
- How to Generate Insert Statements
- How to Generate Update Statements
- How to Generate SQL 'IN' Statements
- How to List the Columns of a Table, Quickly!
- Extracting a list of columns from a block of text
- How to Generate Views The Easy Way
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.
- General help
- Symbols and Keywords
- Built-in Functions
- The 'Where' clause
- Date Time formatting
- Date Time parsing
- Command-Line Automation
- SQL Master Class
- HTML Master Class
- Popular Text Manipulations Made Easy «« bookmark this one
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