LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: johnpelz

**Nodes released into this community are prototypes. They have gone through a minimum set of tests; therefore, we cannot guarantee that they will work as designed nor are they supported by any existing maintenance contracts.**

This prototype graph provides an example of how to dynamically generate a series of SQL Statements to pull in data from a varying set of database tables. The example database has a series of 9 tables with a naming scheme of order_[YYYYMM] -- (ex: orders_201401, orders_201402, etc). The use case is to pull back data from these various tables based on the parameters defined (ex: pull back data from May until Jan or from tables orders_201405 to orders_201401).

The graph has 2 Graph level parameters that you need to set to drive the SQL generation (this is the dynamic part):

• RunMonth : the last month of data you want
• PrevMonth : the x number of months in the past you want to include in the analysis

The attached files include

• TrendingData.zip : compressed TrendingData.mdb Access database file
• DynamicTableQuerying.brg : Lavastorm graph illustrating how to generate dynamic SQL and query the TrendingData database

Instructions :

• Download files
• Extract TrendingData Access database file
• Setup ODBC DNS for the TrendingData database -> set username / password if you want
• Open DynamicTableQuery.brg file -> select Graph -> Edit Parameters:
• -->set RunMonth & PrevMonths parameters
• -->set Database connection details from the ODBC DNS you setup earlier
• Run graph

To download the graph, scroll to the bottom of the thread - enjoy!!
Attachments:
TrendingData.zip
DynamicTableQuerying.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Here's another example. This one takes results from the Data Analyzer node and creates the SQL statement to create a table (in this case, Teradata) with the corresponding fields and data types. The 'Create Table and Write Data' composite utilizes a 'tableName' parameter. A table is created with that name and the converted data is written to that table.

Right-click on the composite and 'Open' to see the good stuff. The Agg Ex node is used to generate the SQL statement. It casts strings as VARCHAR's, ints as INT's, doubles as FLOAT's, etc. Note the use of clocking to ensure that the table creation (DB Execute) executes before the data write (DB Store).
Attachments:
createTable.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

Roger has a good one above.

I'm attaching a new one that converts column names to all uppercase and gives more options.
Attachments:
Test Db Create Table.brg
Db Create Table.brg