Using SQL with Vantage

1. Using SQL with Vantage Queries and Reports and Apps (in SQL we love and-s)

2. What is SQL? • Structured Query Language • Ask for data – get result/data • Modify data • Another tool for you to use SQL: sometimes referred to as Structured Query Language) is a special-purpose programming language designed for managing data in relational database management systems (RDBMS). Originally based upon relational algebra and tuple relational calculus, its scope includes data insert, query, update and delete, schema creation and modification, and data access control. - Wikipedia

3. Common Misuse: Are you on Progress or on SQL? More precise question: Are you on Progress or on Microsoft SQL? You can use SQL with Progress (and most databases)

4. What can you do with SQL? • Query the database • Create views for utility and report writing (can create views based on a view) • Modify data without business logic getting in the way • Write applications outside of the client (Vantage / Epicor / Vista) • Save money and its fun Just a few ideas…

5. But wait! Why not use BAQ’s • BAQs are specific to Epicor • What if you change products? Could be a pain… • Are used in the client (Vantage / Epicor / Vista) • A BAQ is not going to help you when you are writing a standalone application outside of Vantage • SQL is industry standard • Your value as an employee goes up – a win for everyone

6. Querying the Database: • One time question and answers: • Writing a query: • What is a query? • How many Quotes have been created this year? SELECT count(Q.QuoteNum) FROM PUB.QuoteHed Q WHERE Q.Company = '10' and Q.EntryDate > to_date('01/01/2012', 'mm/dd/yyyy') Often faster than writing a Crystal Report If you can click faster than you can type – learn how to type

7. Querying the Database: How? How: Free and Commercial Tools

8. Querying the Database: Not sure where something is or even if it exists? See a table list Browse contents

9. Creating views / Report Writing • A SQL view is a “virtual table” based on a query • Can use these views in Crystal Reports instead of using the table linker in Crystal Reports. • Why is using the table linker in Crystal Reports bad?

11. How do you link in SQL? FROM PUB.POHeader PH, PUB.PODetail PD, PUB.PORel PR, PUB.RCvHead RH, PUB.RcvDtl RD, PUB.Vendor V WHERE PH.Company = PD.Company AND PH.PONum = PD.PONum AND PD.Company = PR.Company AND PD.PONum = PR.PONum AND PD.POLine = PR.POLine AND PR.Company = RD.Company AND PR.PONum = RD.PONum AND PR.POLine = RD.POLine AND PR.PORelNum = RD.PORelNum AND RH.Company = RD.Company AND RH.PONum = RD.PONum AND RH.PackSlip = RD.PackSlip AND PH.Company = V.Company AND PH.VendorNum = V.VendorNum POHeader linked to PODetail via Company PONum PODetail linked to PORel via Company PONum POLine Which method do you prefer? Old school typing or new school visual? Visual complexity can sometimes be an issue. Problem solving is making the problem simpler.

12. Creating views – Report Writing • When updating the Crystal Report – just update the view • Ever have Crystal Reports freeze on you or crash? • Write the view in a more stable environment and use Crystal Reports for what it is good for – formatting and prettiness • Re-use views in multiple reports • If switching database – just update the view and Crystal Reports won’t complain

13. Creating views – How to? CREATE VIEW PP.PP_PART_AVG_COST (Company, PartNum, AvgCost) as SELECT PC.Company, PC.PartNum, PC.AvgLaborCost + PC.AvgBurdenCost + PC.AvgMaterialCost + PC.AvgSubContCost + PC.AvgMtlBurCostAvgCost FROM PUB.PartCost PC

14. Creating views – Result Connect to database via ODBC Created views appear and can be added to Crystal Reports as if they are proper tables

15. Creating views – of views • Re-arranging the data • Simplifying the problem • Summaries and groupings • Adding data

16. Sample of adding data / Creating a view of a view: CREATE VIEW PP.PP_PART_AVG_COST2 (Company, PartNum, AvgCost) AS SELECT P.COMPANY, P.PARTNUM, P.AVGCOST FROM PP.PP_PART_AVG_COST P union all SELECT JH.Company, JH.PartNum, 0.00 FROM PUB.JobHead JH Added data Taking data from another view

17. Modify Data: • Ever need to change one little thing, but the client (Vantage / Epicor / Vista) won’t let you? Use SQL!

18. Modifying Data: Phantom Pack check box Chicken/Egg scenario

19. Modifying Data: UPDATE MFGSYS.PUB.ShipHead SET PhantomPack = 0 WHERE Company='10' and PackNum=34286

20. Modifying Data: • Pros and Cons (they are the same): • No arguments what’s so ever • By passes business logic Pro/Bro tips: Don’t add data (as in rows to the table) Modify a check box to bypass business logic – do the change in the client then re-set the field Use UD fields/tables Minimize use Test

21. Using SQL in Applications • Problem: • Crystal Reports can’t do much computations/logic • Epicor clients only let you do certain amounts of customizations – and uses up licenses • Solution: • Create your own ERP!

22. Using SQL in Applications Data queried from database using SQL UD fields updated via SQL

23. How? Free and commercial tools Microsoft Visual Studio SQL query Connect to database via ODBC

24. Bonus round:

25. Learning SQL • Online documents – classes • Schools tend to teach basics of SQL as part of the curriculum (Introduction to Information Systems 101) so you might have to catch up

26. Security and users: • Tends to use database users (versus client users) • sysprogress • You can create your own users • Create security of reports via using Windows Security

27. Connecting to the Database via ODBC: ODBC Admin Connection entries

28. Connecting to the Database via ODBC:

29. The hard part (if you are on Progress): Progress Driver Copy contents of oe101B\bin directory (mostly for the dll files) to the client eg: C:\OE_10_ODBC_DRIVER\bin Note: your version may vary – so the directory may vary

30. The hard part (if you are on Progress): Progress Driver Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Progress OpenEdge 10.1B driver] "APILevel"="1" "ConnectFunctions"="YYY" "CPTimeout"="60" "DriverODBCVer"="5.1" "FileUsage"="0" "SQLLevel"="1" "UsageCount"="1" "Driver"="C:\\OE_10_ODBC_DRIVER\\bin\\pgoe1022.dll" "Setup"="C:\\OE_10_ODBC_DRIVER\\bin\\pgoe1022.dll" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers] "Progress OpenEdge 10.1B driver"="Installed" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\V803Live] "Driver"="C:\\OE_10_ODBC_DRIVER\\bin\\pgoe1022.dll" "Description"="OpenEdge 10B" "HostName"="lib-srv3" "PortNumber"="8350" "DatabaseName"="mfgsys" "LogonID"="sysprogress" "StaticCursorLongColBuffLen"="4096" "UseWideCharacterTypes"="0" "EnableTimestampWithTimezone"="1" "DefaultIsolationLevel"="READ UNCOMMITTED" "ArraySize"="50" "DefaultLongDataBuffLen"="2048" 32 bit windows – merge these keys with your registry Make sure these paths match your file structure (the \bin directory you copied Hostname will be different as well

31. 64 bit registry keys Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\Progress OpenEdge 10.1B driver] "APILevel"="1" "ConnectFunctions"="YYY" "CPTimeout"="60" "DriverODBCVer"="5.1" "FileUsage"="0" "SQLLevel"="1" "UsageCount"="1" "Driver"="C:\\OE_10_ODBC_DRIVER\\bin\\pgoe1022.dll" "Setup"="C:\\OE_10_ODBC_DRIVER\\bin\\pgoe1022.dll" [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\ODBC Drivers] "Progress OpenEdge 10.1B driver"="Installed" [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\V803Live] "Driver"="C:\\OE_10_ODBC_DRIVER\\bin\\pgoe1022.dll" "Description"="OpenEdge 10B" "HostName"="lib-srv3" "PortNumber"="8350" "DatabaseName"="mfgsys" "LogonID"="sysprogress" "StaticCursorLongColBuffLen"="4096" "UseWideCharacterTypes"="0" "EnableTimestampWithTimezone"="1" "DefaultIsolationLevel"="READ UNCOMMITTED" "ArraySize"="50" "DefaultLongDataBuffLen"="2048" TIP: Put file contents into .reg file (ordinary text file, just with a .reg extension) then double click to merge with your own registry

32. What if you are on Microsoft SQL Server?

33. Questions? * Special thanks to internet artists