Transcript Using SQL with Vantage

Queries and Reports and Apps
(in SQL we love and-s)

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
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)




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…

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

One time question and answers:
◦ Writing a query:
 What is a query?

How many Quotes have been created this
year?
Often faster than writing a
Crystal Report
SELECT count(Q.QuoteNum) If you can click faster than you
can type – learn how to type
FROM PUB.QuoteHed Q
WHERE
Q.Company = '10' and
Q.EntryDate > to_date('01/01/2012', 'mm/dd/yyyy')
How:
Free and Commercial Tools
Not sure where something
is or even if it exists?
See a
table list
Browse
contents


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?
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.


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
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.AvgMtlBurCost AvgCost
FROM
PUB.PartCost PC
Connect to
database via ODBC
Created views
appear and can be
added to Crystal
Reports as if they
are proper tables




Re-arranging the data
Simplifying the problem
Summaries and groupings
Adding data
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
Added data
union all
Taking data from another view
SELECT JH.Company, JH.PartNum, 0.00 FROM
PUB.JobHead JH

Ever need to change one little thing, but the
client (Vantage / Epicor / Vista) won’t let you?
Use SQL!
Phantom Pack check box
Chicken/Egg scenario
UPDATE
MFGSYS.PUB.ShipHead
SET
PhantomPack = 0
WHERE
Company='10' and
PackNum=34286

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

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!
Data queried from database using SQL
UD fields updated
via SQL
Free and commercial tools
Microsoft Visual Studio
SQL query
Connect to
database via ODBC
◦ 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
◦ Tends to use database users (versus client users)
 sysprogress
◦ You can create your own users
◦ Create security of reports via using Windows
Security
ODBC Admin
Connection entries
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
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"
32 bit windows – merge these
keys with your registry
[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"
Make sure these paths match
your file structure (the \bin
directory you copied
Hostname will be different as
well
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
* Special thanks to internet artists