SQL 101 The Basics STEVE ENDOW MCP MCT

SQL 101: The Basics STEVE ENDOW, MCP, MCT, MVP CHRISTINA PHILLIPS, MCP, MCT, MVP

Steve Endow Dynamics GP MVP Solomon IV Pervasive in 1996 SQL Server since 1998, GP since 2004 1 TB database in 2005. NET development with GP SQL query optimization SQL Server troubleshooting

Christina Phillips Dynamics GP User, Trainer, and Project Manager Since 2000 Dynamics GP MVP Director at BKD Technologies (BKD, LLP) SQL Reporting Services Solution and Process Design Implementation Ridiculous Troubleshooting

Foundation http: //bit. ly/GPTables http: //bit. ly/gptables

GP Table Reference

Demo Basic SQL Syntax

Expand your SQL vocabulary Comments: -- or /* */ DISTINCT SELECT RTRIM, DISTINCT CUSTNMBR FROM SOP 30200 for spaces SELECT RTRIM(CUSTNAME) LIKE WHERE LIKE CUSTNMBR LIKE ‘AA%’ Wildcards: % _ [a-f] [^a]

Expand your SQL vocabulary GROUP BY with HAVING GROUP ORDER BY CUSTNMBR HAVING COUNT (*) > 10 BY ORDER BY CUSTNMBR ASC ISNULL(ACTIDX, ‘None’) IN / NOT IN WHERE ACTIDX IN (3, 45, 56, 62)

Demo Expand Your SQL Vocabulary

SQL Views Act as dynamic, virtual tables Combine multiple tables, criteria, refer to from multiple sources Leverage Reports, standard views that come with GP Smart. Lists, etc Can allow split duties (data –vs- report design) Can simplify SQL security

Demo SQL Views

Accessing SQL Views and Tables • • Including native Microsoft Excel Smart. List Designer • • • Views, GP Tables http: //www. eonesolutions. com/Smart. List-Builder/SLB-vs-Smart. List. Designer/ SQL Report Builder • • • Data Source, Data Set Report Builder

Demo Accessing SQL Views and Tables

Questions? @Steve. Endow steveendow@gmail. com Christina Phillips @CPLaughs. ALot cphillips@bkd. com https: //dynamicsgpland. blogspot. com/