1 / 33

Using SQL with Vantage

Using SQL with Vantage. Queries and Reports and Apps (in SQL we love and-s). What is SQL?. Structured Query Language Ask for data – get result/data Modify data Another tool for you to use.

hien
Download Presentation

Using SQL with Vantage

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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?

  10. 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.

  11. 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

  12. 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

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

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

  15. 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

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

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

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

  19. 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

  20. 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!

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

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

  23. Bonus round:

  24. 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

  25. 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

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

  27. Connecting to the Database via ODBC:

  28. 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

  29. 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

  30. 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

  31. What if you are on Microsoft SQL Server?

  32. Questions? * Special thanks to internet artists

More Related