![Page 1: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/1.jpg)
Advanced Third Party Access
Rebecca Pyle
October, 2007
![Page 2: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/2.jpg)
2
Agendum – more of the same
> ISQL
> Microsoft Access
![Page 3: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/3.jpg)
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
![Page 4: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/4.jpg)
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
![Page 5: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/5.jpg)
5
ISQL – Force use of index
> You can “force” the engine to use a specific indexSELECT fields
FROM table
FORCE INDEX (index name)
WHERE criteria
> ExampleSELECT *
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'
![Page 6: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/6.jpg)
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
![Page 7: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/7.jpg)
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’
![Page 8: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/8.jpg)
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
![Page 9: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/9.jpg)
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
![Page 10: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/10.jpg)
10
Access – Invoices missing service fee
> Query what’s potentially missing (fees)
![Page 11: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/11.jpg)
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
![Page 12: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/12.jpg)
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
![Page 13: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/13.jpg)
13
Access – Missing invoice numbers
> Query comment line 115
![Page 14: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/14.jpg)
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
![Page 15: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/15.jpg)
15
Access - Calculated leg fare
> Like commission – sum and divide by number of legs
> Add Fare and TrueODNum to Segment Count query
![Page 16: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/16.jpg)
16
Access - Calculated leg fare
> Join to Segments table on InvPayid and TrueODNum
> Divide fare total from segment count by number of segments
![Page 17: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/17.jpg)
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
![Page 18: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/18.jpg)
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
![Page 19: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/19.jpg)
19
Access - True OD with segment information
![Page 20: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/20.jpg)
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)
![Page 21: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/21.jpg)
21
Access - One-way tickets
![Page 22: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/22.jpg)
22
Access – Last Segment
> For Arrival manifest-type reports
> Query segments for greatest segment number per Payid
![Page 23: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/23.jpg)
23
Access – Last Segment
> Query segments and that query
![Page 24: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/24.jpg)
24
Access - Building import files
> Export from one GW and import into another
> Export from something else (e.g., website) and import into GW
![Page 25: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/25.jpg)
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
![Page 26: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/26.jpg)
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
![Page 27: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/27.jpg)
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
![Page 28: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/28.jpg)
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
![Page 29: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/29.jpg)
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
![Page 30: Advanced Third Party Access Rebecca Pyle October, 2007](https://reader035.vdocuments.us/reader035/viewer/2022081414/5513f6e25503466f748b5eeb/html5/thumbnails/30.jpg)
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