Working with SQL
This is an intermediate level course suited to DBAs, system analysts, programmers, or anyone wanting to learn how to use SQL to solve business scenarios. It will help to have an understanding of Structured Query Language syntax and functions and regular expressions, or some experience with script-based programming languages. Our learning objectives for this course are to recognize and explain use cases where Structured Query Language, SQL, can be applied in business situations, explain and apply SQL procedures to import and parse text files, define and apply automation processes to parse and sort data to specific databases. We welcome all comments and feedback. So please feel free to reach out to us at firstname.lastname@example.org with any questions or comments.
- [Instructor] A customer has a denomination configuration set up in a database, based on the type of transaction. The denomination dollar amounts that we have obtained from our VaultNote table type parameter are put into a temporary table, denom amount. We then join that with the tables that hold the currency configuration for the customer to work out the note count. But essentially, it is as simple as dividing the amount by the denomination. We use a cursor to loop through the detail lines dataset. The sequence field that we met to the column numbers variable, is used to determine which count field we will update. We use an FL structure to assign the note count to the correct count field based on a column number variable. Once we have looped through our detailed lines dataset, we close and de-allocate the cursor and we construct our transaction detail insert statement with dynamic SQL. Once again, we use the vault code to determine which database to insert into. We have a parameter definition string, which includes a transaction ID from our header record, plus all account fields. We sum the denomination amounts, within the wall loop to get a total value in the detail amount variable. Just have a look at the complete procedure. You can see that again this is repped in try catch block and the transaction. We create the transaction record header here, dynamically determining which transaction table it should go into. We get our new ID to our transactional ID field. And once we have determined the category to use for our denominations in the SignTool account variables, within this IF statement within a wall loop, then again construct our dominant SQL insert the detail record. Once we finished, for each transaction here, we send an email to tell the cash center of the newly created auto-transaction. Again, this is repped in a catch block here, so if it fails, you roll it back, we de-allocate the cursors, and we log the .
About the Author
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.