Using SQL with Vantage

Document Sample
Using SQL with Vantage Powered By Docstoc
					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
FROM PUB.QuoteHed Q         can type – learn how to type
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,                    POHeader linked to
   PUB.RCvHead RH,
   PUB.RcvDtl RD,
                                    PODetail via
   PUB.Vendor V                     Company
                                    PONum
WHERE
   PH.Company = PD.Company AND
   PH.PONum = PD.PONum AND         PODetail linked to PORel
   PD.Company = PR.Company AND     via Company
   PD.PONum = PR.PONum AND
   PD.POLine = PR.POLine AND
                                   PONum
   PR.Company = RD.Company AND     POLine
   PR.PONum = RD.PONum AND
   PR.POLine = RD.POLine AND
                                       Which method do you prefer? Old school
   PR.PORelNum = RD.PORelNum AND
   RH.Company = RD.Company AND
                                       typing or new school visual? Visual
   RH.PONum = RD.PONum AND             complexity can sometimes be an issue.
   RH.PackSlip = RD.PackSlip AND
   PH.Company = V.Company AND          Problem solving is making the problem
   PH.VendorNum = V.VendorNum          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"


                                                                                 32 bit windows – merge these
"FileUsage"="0"
"SQLLevel"="1"
"UsageCount"="1"
"Driver"="C:\\OE_10_ODBC_DRIVER\\bin\\pgoe1022.dll"
"Setup"="C:\\OE_10_ODBC_DRIVER\\bin\\pgoe1022.dll"
                                                                                 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"


                                                                                 Make sure these paths match
"PortNumber"="8350"
"DatabaseName"="mfgsys"


                                                                                 your file structure (the \bin
"LogonID"="sysprogress"
"StaticCursorLongColBuffLen"="4096"
"UseWideCharacterTypes"="0"
"EnableTimestampWithTimezone"="1"
"DefaultIsolationLevel"="READ UNCOMMITTED"
                                                                                 directory you copied
"ArraySize"="50"
"DefaultLongDataBuffLen"="2048"                                                  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"
                                                                   TIP: Put file contents into .reg
"DatabaseName"="mfgsys"
                                                                   file (ordinary text file, just
                                                                   with a .reg extension) then
"LogonID"="sysprogress"
"StaticCursorLongColBuffLen"="4096"


                                                                   double click to merge with
"UseWideCharacterTypes"="0"
"EnableTimestampWithTimezone"="1"
"DefaultIsolationLevel"="READ UNCOMMITTED"
"ArraySize"="50"                                                   your own registry
"DefaultLongDataBuffLen"="2048"
* Special thanks to internet artists

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:13
posted:11/4/2012
language:Unknown
pages:33