This blog comes from the Vertica Architecture and SQL book by Tom Coffing and David Cook.
Contact Tom.Coffing@CoffingDW.com if you want to purchase the book. Check out the table of contents here. https://www.nexusdataserver.com/services-9.
The blog is below this first video, but did you know you can migrate directly to Vertica in a heartbeat with Nexus? This video shows the many features and options for migrating 19 different databases to Vertica with the click of a button. We also show how we can join tables from 19 different systems with Vertica in a single query (federated query). We also show how to search for column names (i.e., customer_number) across all systems in your enterprise and then join them. Finally, you will see that users can work from home to migrate and join data, but they have the option to execute the migrations from a Nexus Server (located on-premises or on any cloud) for high-speed transfers.
In the example below, we order the data with an ORDER BY grade_pt statement at the end of the SQL. Therefore the answer set sorts by grade_pt in ascending mode because ASC is the default. Stanley Johnson is in the first row with a class_code of null because Vertica sorts nulls first when sorting in ascending mode.
This example uses the Nexus Query Chameleon. Notice all of the systems in the systems tree on the left. Why have query tools for different systems when Nexus queries all systems in your enterprise?
In the example below, we attempt to order the data by class_code, and the data sorts alphabetically. However, when I went to school, you were a freshman, then a sophomore, then a junior, and finally a senior. I have taught many students who did not know how to order the data in that logical order. Therefore, I will show you how to do so.
This example uses the Nexus Query Chameleon in dark mode. The Chameleon changes colors for the environment you desire.
Many people don't know you can use CASE in your ORDER BY statement. The example below uses a CASE statement to logically sort the data as you went to high school or college.
This example uses the Nexus Query Chameleon with four queries running simultaneously (Snowflake, Google BigQuery, Teradata, and Vertica). Each system query window and result gets a tab the same color the user chooses for the system.
You can also use an ORDER BY statement with a DECODE in Vertica to accomplish the same logical order when sorting the answer set.
The DECODE is similar to the CASE but in a different format. We are ordering in the DECODE by CLASS_CODE, and if the value equals 'FR,' then give it a value of one, just like we did with the CASE statement in our previous example. Likewise, if the CLASS_CODE equals a 'SO,' give it a two, and so on. The five at the end is the ELSE statement in a DECODE.
This example uses the Nexus Query in Sherwood dark green with book mode chosen, which places the answer sets to the side of the query window.
Get Your Analytics for Free for all Databases
Analytics on Nexus is the best compliment for Analytics with Vertica, Snowflake, Redshift, and all systems. Imagine if you need to get dozens of analytic reports on a specific data set. Of course, you might have to run different analytic queries on Vertica to get your reports, but one of the best concepts of Nexus is the Garden of Analytics.
The Nexus saves every answer set you run during the day into the Garden of Analytics, which uses your PC's memory. Then, using drag and drop templates, anyone can get over 80 different analytic reports, graphs, charts, pivots, or spreadsheets, and Nexus does all of the calculations for free inside your PC. So, for example, if you need 80 analytic reports, then Nexus saves you 80 queries; you would have to pay a cloud vendor.
Check out the Nexus Garden of Analytics in this YouTube video and save lots of money.
Why Write the SQL When Nexus Writes It For You?
Nexus users can use the Super Join Builder, which shows you tables and views, connects you to which tables join, and writes the SQL automatically. If you want to see the greatest automated join builder worldwide, check out this YouTube Video. And yes, the Nexus Super Join Builder joins data from Vertica with Excel, Microsoft Access, Teradata, Oracle, SQL Server, DB2, Postgres, MySQL, Amazon Redshift, Azure Synapse, Snowflake, BigQuery, or other Vertica warehouses or projects.
Check out the Nexus Super Join Builder in this YouTube video and become super at joins. And yes, you can edit the SQL Nexus creates.
Why Buy a Different Tool for each database when Nexus queries them all?
It took 18 years of development led by Tom Coffing to build the Nexus Query Chameleon. And the best part is that Nexus can query all systems in your enterprise and has a Super Join Builder that builds the SQL automatically as the user's point-and-click. And the most amazing part is that Nexus can easily join Vertica tables with Excel, Access, and tables and views from all other database platforms. Check out just some of the amazing features of Nexus here.