1 / 30

Advanced Third Party Access

This guide outlines advanced techniques in utilizing ISQL with Microsoft Access for optimized SQL query execution and data management. Learn essential commands, such as SELECT, FROM, and WHERE clauses, and how to efficiently utilize indexing to improve query performance. Discover the nuances between Access and ISQL syntax, and find out how to execute queries via the command line for batch processing. It also covers specific scenarios like handling missing invoices and calculated fields, ensuring data accuracy and relevance in your database activities.

tavia
Download Presentation

Advanced Third Party Access

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. Advanced Third Party Access Rebecca Pyle October, 2007

  2. Agendum – more of the same • ISQL • Microsoft Access

  3. ISQL – basics • SELECT fields or * for all fields • FROM table • WHERE criteria SELECT * FROM dba.Invoice WHERE InvoiceDate between ′2007-06-01′ and ′2007-06-30′ • Single quotes around text. • Be careful if you copy and paste – curly quotes don’t work

  4. ISQL – statistics window • Shows what and how: table and method of search • How: Index or sequential (“seq”). Seq is table scan (entire table) • Sybase 9: • Scans table unless very limiting criteria • Caches results then subsequent queries on same criteria are fast • GW users tend to see performance suffer in some circumstances

  5. ISQL – Force use of index • You can “force” the engine to use a specific index SELECT fields FROM table FORCE INDEX (index name) WHERE criteria • Example SELECT * FROM dba.Invoice force index (invinvdate) WHERE InvoiceDate between '2007-06-01' and '2007-06-30‘ • Finding index names: select * from sys.sysindexes where tname = 'invoice'

  6. ISQL – Force use of index • Not available in Access GUI • Can put in pass-through query • Query, SQL-specific, Pass-through • Speaking of which, Access and ISQL syntax similar but not exactly the same • Dba_invoice vs. dba.invoice for table names • #...# vs. ‘…’ around date values • Double vs. single quotes around text values

  7. ISQL – query from command line • ISQL queries can be run from the command line • And consequently, in a batch file • “dbisqlc” + connection parameters + select statement (one paragraph) dbisqlc -c "uid=edit; pwd=data; dsn=gblware32" SELECT * from dba.invoice where invoicedate between ‘2007-06-01' and '2007-06-03' and FOP in ('A', 'P'); output to c:\inv.txt; • Can’t use symbols that mean something to DOS. <> (not equal) doesn’t work (because > means something in DOS), Use != e.g., where Status != ‘V’

  8. ISQL – I ♥ LIST • LIST is comma-delimited • You can get a “cross-tab” file with this: select bkagt, list(distinct provider order by provider) as Provsfrom dba.invoicewhere invoicedate = '2007-04-16' and traveltype = 'a' group by bkagt;output to c:\bkagt.txtquote '' • quote '' means no quotes

  9. Access • Invoices missing service fee • Apollo invoice numbers • Original ticket information • Thru-fare calculation • City pair “include returns” • True OD with segment information • One-way tickets • Last segment • Building import files • Add/delete comment lines – Fare savings in Invoice Query

  10. Access – Invoices missing service fee • Query what’s potentially missing (fees)

  11. Access – Invoices missing service fee • Query invoice table and the Fees query • Outer join on branch and invoice number • Criteria “is null” on a field from the Fees query • Group by

  12. Access – Missing invoice numbers • GlobalWare Missing Invoices report is not always accurate because of /QA- and MCOs • These attach to the original invoice and Apollo invoice number is in comment line 115 • Query invoice and comment line 115, and create a field that is line 115 if present, else invoice number

  13. Access – Missing invoice numbers • Query comment line 115

  14. Access – Missing invoice numbers • Query invoice and comment line 115 query, and create a field that is line 115 if present, else invoice number

  15. Access - Calculated leg fare • Like commission – sum and divide by number of legs • Add Fare and TrueODNum to Segment Count query

  16. Access - Calculated leg fare • Join to Segments table on InvPayid and TrueODNum • Divide fare total from segment count by number of segments

  17. Access - City pair “include returns” • Treat AUS-DEN and DEN-AUS as the same city pair • Create field: CityPair: Iif (DepartCity<ArrivalCity, DepartCity & '-' & ArrivalCity, ArrivalCity & '-' & DepartCity) • If logic syntax in ISQL (also concatenate and field alias): IF DepartCity<ArrivalCity THEN DepartCity || '-' || ArrivalCity ELSE ArrivalCity || '-' || DepartCity ENDIF AS CityPair

  18. Access - True OD with segment information • Information that could be different on connecting flights is not necessarily in TrueOD, e.g., fare basis • To get this information, join Segments and TrueOD tables via InvPayId and TrueODNum • To get TrueOD only segments in results, group by and pick an aggregate (min or max) for FareBasis

  19. Access - True OD with segment information

  20. Access - One-way tickets • Segments with only one true OD • Max(trueOD) = 1 • Also • Not void • Not refund • Can get info from invoice also if needed (invoice date for criteria, tkt #, etc.) • Might want to exclude Amtrak (OX not accurate)

  21. Access - One-way tickets

  22. Access – Last Segment • For Arrival manifest-type reports • Query segments for greatest segment number per Payid

  23. Access – Last Segment • Query segments and that query

  24. Access - Building import files • Export from one GW and import into another • Export from something else (e.g., website) and import into GW

  25. Access - Building import files • Account IDs, Invoices (including segments and comments), GL • All are flat files • Account IDs and Invoices files are tab-delimited • GL file is fixed-length fields • Account Id is easy; Invoices tricky particularly if you need segments or comments, GL not too bad once you figure out the number fields

  26. Access - Building import files • Basic steps • Build a table in Access that looks like the file • Query data source (e.g., AccountID table) and append to Access table • Export the Access table to text file • Import into GlobalWare • You can build a macro to automate the query and export steps

  27. Access – Add/delete comment lines • EDIT user has insert and delete rights to the Comments table • Fare savings through Invoice Query • Calculate fare savings • Store in Comment line • Define comment line in Invoice Query

  28. Access – Add/delete comment lines • Calculate fare savings • If MaxFare is zero, savings = zero • Otherwise, MaxFare minus TotalCost • If you do fare savings on exchanges the GW way, add the Exchanged amount back in • Saved: IIf([MaxFare]=0,0,[MaxFare]-[TotalCost]-[Exchange]) • Exchange is a negative value, so you subtract it

  29. Access – Add/delete comment lines • Store in comment line fields • Store PayId in InvPayid • Store a number in LineNum • Store Saved in Data • Append to Comments table

  30. Access – What else? • I know we didn’t cover all of these topics in PHX • If you have questions, please feel free to email me – my email address is in the attendee list

More Related