Querygen (original)

From KeystoneIntranet
Jump to navigation Jump to search


Querygen (Early Versions)

Setting up Data Query

Formatting external (raw) files

If you will be using data from outside Interbase, it is likely that the data was from an external source. This data will need to be formatted and normalized as much as possible. Microsoft Excel is usually the best tool for this job. Treat the data like just another spreadsheet. Before you get too far into the data, some important tips on preparing the data:

  • Cleanup Column Headings
    • Make sure each column has a heading.
    • No funky characters
    • No field names beginning with a numeric.
    • Preferably shorter than 8 characters.
    • No duplicates


Working with Interbase Files

Connection String:

  • For Local File enter the path and file name of the database (e.g.: c:\program files\givenhansco\cc\cust\data\ccw001.ib)
  • For Remote Files, enter the ipaddress (or computer name) followed by a colon, then the path and file name of the database (e.g. 192.168.0.1:c:\ccgh\data\ccw001.ib)

Query:

  • Create a basic query selecting all necessary fields.


Working with ADO Connections

ADO connections allows you to extract data from any ADO/ODBC source such as EXCEL, SQL Server, Access and Text Files. When working with ADO connections, you can either provide a complete ADO (or ODBC) connection string, or create an ODBC alias.

NOTE: The client computer must have the appropriate ADO/ODBC client drivers installed.


Example - Reading Excel with a Connection String

Source Path: 

Provider=MSDASQL.1;DBQ=f:\DIR\datafile.xls;Data Source=Excel Files

Source Query: 

SELECT * FROM [SHEET_NAME$]

NOTE: You can rename the sheet name in excel as desired. This will be used in the select query as above.

Example - Reading ADO Connection with ODBC Alias

  1. Setup ODBC data Source (Administrtive Tools>Data Sources (ODBC). Each driver/connection type has variations

  1. Enter the ODBC name as the Source Path
  2. Select from the desired table


Text File with ODBC

This has the advantage of selecting the flat text file from the Source Query line (no need to set up a separate ODBC data source for each file). The directory of the text files is specified in the ODBC Data source. The File deliniation is set in the ODBC data source. The default is csv as in this example. It can be changed to Tab Delimited. If changed to Fixed, you need to define all of the columns in the ODBC data source.

  1. Set up ODBC data source designating the directory or the text files are to be put.

  1. The Source Path is the ODBC name (e.g. TEXT)
  2. The Source Query references the text file in the designated directory.

Excel with ODBC Alias

Excel Spreadsheet.

  1. Set up ODBC datasource using Excel driver. The spreadsheet dearborn.xls is named the the datasource dearborn1
  2. The data to be queried must be a Named Range in excel.
    1. Open the spreadsheet
    2. Highlight the range the data is in
    3. Select Insert>Name>Define from the menu and name the range (in this example called “test”)
    4. Multiple names can be Named within a spreadsheet.
    5. The Source Path is the ODBC name
  3. The Source Query references the Named Range in the spreadsheet


Working with dBase Files

NOTE: DBase is the least prefered method for importing data.
Working with dBase files is difficult because of file-locking. If a .DBF file is open in Excel, QueryGen will not be able to open the file. Once a file is open in QueryGen, you will not be able to open it in Excel. For this reason, it is strongly recommended to use true .XLS files with the ADO Connection.

Source Path: Directory 

C:\Path

Source Query: 

Select * from file.dbf

Creating the DBase output

NOTE: This is for Old versions of querygen and requires the BDE (Borland Database Engine - which you will have a hard time finding)

  • For each Column
    • For alpha fields, your Excel columns must be wide enough to view the widest data. If you can only see 7 characters when you save the file as .DBF, you'll only be able to access 7 characters with QueryGen. In Excel: Format | Column | Auto-Fit
    • Make sure to set precision for all values in the column to maximum required.
    • Think in terms of how data is used. While a five-digit zip code is numeric, storing it as such can cause zip codes of 01056 to become 1056. If numeric data is not being used for mathematics, it is probably better off being stored as an alpha.
    • The top (non-header) row is important to how a file is stored. If data is both Alpha and Numeric, make sure a numeric value appears in top row. Hint: Set the first record to X's and zeroes as appropriate.
    • If data is currency, set that field's format to currency in Excel before saving as a .DBF

Formatting Options

  • ADD - Adds a specified value to the field. In the example below, it sets TRANS_NO to a value imported from T_NO incremented by 1000.
UPDATE ARTINV SET TRANS_NO='{T_NO|ADD=1000}'
  • DATETIME - specifies that the data should be in Interbase's DATETIME format. This is a necessity for any time-stamp fields. In the example below, it will set CHANGED field into DATETIME format for the field in ARTCUST.
UPDATE ARTCUST SET LAST_CHANGE_DATE='{CHANGED|DATETIME=0}'


  • DECODEDATE


  • DEFAULT - sets a default value if none is available from the import. In the example below, it will take the credit terms from the TERMS field in the import, and if none is specified, set it to a default value of 30.
UPDATE ARTCUST SET CREDIT_TERMS='{TERMS|DEFAULT=30}'


Example - the format command %.5d means left fill the number with 0s. In the case below the text value 1 appears right before the number. If you had a trans_no of 243 it would be formatted as 00243. With the 1 appearing right before you would get 100243. This is useful if you want to add transactions to an existing session based on data already in that session. 

{SESSION_NO},1{TRANS_NO|FORMATINT=%.5d}


  • NULL - specifies to replace the value with NULL if the value of the imported field matches what is specified. In the eaxmple below, if the GL field contains a 0, it will instead set it to null in the COGS_GL field.
UPDATE ARTPROD SET COGS_GL='{GL|NULL=0}'


  • STRLEN - specifies the maximum number of characters to use from the import field. In the example below, it will select no more than two characters from the A1 field.
UPDATE ARTPROD SET PRODUCT_CLASS='{A1|STRLEN=2}'


Tricks

Conditional Insert:

Sometimes you want to insert data only if it doesn't exist, such as when merging 2 sets of g/l accounts where some overlap exists. Since the INSERT statment has no where clause, we can accomplish this by creating our insert statment using special select statement. In the example below, we use the artloc table (where location=0) because we can count on it returning the values just once, and add the additional conditions appropriate for the situation. 

INSERT INTO gltacct (
  GL_ACCOUNT, DESCRIPTION, GL_GROUP, SEQUENCE, SEGMENT_1, SEGMENT_2, SEGMENT_3, SEGMENT_4, SEGMENT_5, ACCOUNT_CLASS,
  ALLOC_RULE_ID, NRMCA_ACCOUNT, ACCOUNT_TYPE, DR_CR_FLAG, SECURITY_CLASS, IMPORTED_FLAG, VALID_FROM_DATE, VALID_TO_DATE,
  NOTES_1, NOTES_2, MEMO, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG )  
select '{GL_ACCOUNT}', '{DESCRIPTION|NULL=0}', '{GL_GROUP|NULL=0}', '{SEQUENCE|NULL=0}', '{SEGMENT_1|NULL=0}',
  '{SEGMENT_2|NULL=0}', '{SEGMENT_3|NULL=0}', '{SEGMENT_4|NULL=0}', '{SEGMENT_5|NULL=0}', '{ACCOUNT_CLASS|NULL=0}',
  '{ALLOC_RULE_ID|NULL=0}', '{NRMCA_ACCOUNT|NULL=0}', '{ACCOUNT_TYPE|NULL=0}', '{DR_CR_FLAG|NULL=0}',
  '{SECURITY_CLASS|NULL=0}', 'Y', '{VALID_FROM_DATE|NULL=0}', '{VALID_TO_DATE|NULL=0}', '{NOTES_1|NULL=0}',
  '{NOTES_2|NULL=0}', null, cast('today' as date), 'HIT', '{ACTIVE_FLAG|NULL=0}'  
   from artloc where loc_id=0 and not EXISTS (SELECT 'A' FROM GLTACct WHERE GL_ACCOUNT= '{GL_ACCOUNT}'  );

Replicating CC Options: 

UPDATE CCTOPTN
SET OPTION_VALUE='{OPTION_VALUE}' ,LAST_CHANGE_USER='HIT',LAST_CHANGE_DATETIME=CAST('today' AS TIMESTAMP)
 WHERE  USER_GROUP_ID='{USER_GROUP_ID}' AND SECTION_NAME='{SECTION_NAME}' AND  OPTION_NAME='{OPTION_NAME}' ;
INSERT INTO CCTOPTN (
USER_GROUP_ID, SECTION_NAME, OPTION_NAME, OPTION_VALUE, LAST_CHANGE_DATETIME, LAST_CHANGE_USER )  SELECT 
'{USER_GROUP_ID}', '{SECTION_NAME}', '{OPTION_NAME}', '{OPTION_VALUE}', cast('today' as timestamp), 'HIT' 
  from artloc where loc_id=(select max(loc_id) from artloc) and not EXISTS (SELECT 'A' FROM CCTOPTN
 WHERE  USER_GROUP_ID='{USER_GROUP_ID}' AND SECTION_NAME='{SECTION_NAME}' AND  OPTION_NAME='{OPTION_NAME}' );

Note that in this example, it attemps to assign the value if the item exists, then creates it if it doesn't exist.

Inserting GL Groups 

INSERT INTO gltacgrp (
GROUP_ID, HEAD_DESCRIPTION, TOTAL_DESCRIPTION, LINE_DESCRIPTION, PARENT_GROUP_ID, SPECIAL_FUNCTION, DETAIL_LEVEL, SEQUENCE, ALLOC_RULE_ID, SECURITY_CLASS, DR_CR_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG )  
SELECT
'{GROUP_ID}', '{HEAD_DESCRIPTION}', '{TOTAL_DESCRIPTION}', '{LINE_DESCRIPTION}', '{PARENT_GROUP_ID}', '{SPECIAL_FUNCTION}', '{DETAIL_LEVEL}', '{SEQUENCE}', '{ALLOC_RULE_ID}', '{SECURITY_CLASS}', '{DR_CR_FLAG}', '{LAST_CHANGE_DATETIME|DATETIME=0}', '{LAST_CHANGE_USER}', '{ACTIVE_FLAG}' 
 from artloc where loc_id=0 and not EXISTS (SELECT 'A' FROM GLTACGRP WHERE GROUP_ID= '{GROUP_ID}'  );

This will insert only the groups that do not exist. 


INSERT INTO 
artcust
 (CUST_NO, STMT_CUST_NO, CREDIT_LIMIT, HIGH_CREDIT, SALESPERSON, CUST_CLASS, TERMS_CLASS, PRICE_GROUP, LAST_SALE_DATE, LAST_PAY_DATE, PAID_THIS_MONTH, OLDEST_AR_DATE, AR_BALANCE, PHONE_NO, FAX_NO, PAGER_NO, MOBILE_NO, EMAIL, URL, START_DATE, LAST_CONTACT_DATE, MTD_SALES, YTD_SALES, MTD_GROSS_PROFIT, YTD_GROSS_PROFIT, LAST_YEAR_SALES, MTD_DISC_TAKEN, YTD_DISC_TAKEN, DUNNING_FLAG, FIN_CHARGE_CODE, TAX_CODE, TAX_CODE_2, TAX_CODE_3, COD_FLAG, SHOW_WARNING, PROSPECT_FLAG, PO_REQUIRED, ZONE_REQUIRED, PREPAY_REQUIRED, PRINT_PRICES, PRINT_WEIGHTS, NAME, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP, USER_1, USER_2, TAX_AUTHORITY, CONTACT, SEQUENCE_CODE, D_AND_B_RATING, NOTES_1, NOTES_2, NOTES_3, NOTES_4, AR_GL, TAX_ID, UMS_SYSTEM, CARRYING_COST_PCT, CUST_COLOR, MOB_LOGIN_NAME, MOB_LOGIN_PASSWORD, MOB_LOGIN_DATETIME, MOB_LOGOUT_DATETIME, MOB_LAST_COID, MOB_SECURITY_CLASS, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG )  

 SELECT
'{CUST_NO}', '{STMT_CUST_NO}', '{CREDIT_LIMIT}', '{HIGH_CREDIT}', '{SALESPERSON}', '{CUST_CLASS}', '{TERMS_CLASS}', '{PRICE_GROUP}', '{LAST_SALE_DATE}', '{LAST_PAY_DATE}', '{PAID_THIS_MONTH}', '{OLDEST_AR_DATE}', '{AR_BALANCE}', '{PHONE_NO}', '{FAX_NO}', '{PAGER_NO}', '{MOBILE_NO}', '{EMAIL}', '{URL}', '{START_DATE}', '{LAST_CONTACT_DATE}', '{MTD_SALES}', '{YTD_SALES}', '{MTD_GROSS_PROFIT}', '{YTD_GROSS_PROFIT}', '{LAST_YEAR_SALES}', '{MTD_DISC_TAKEN}', '{YTD_DISC_TAKEN}', '{DUNNING_FLAG}', '{FIN_CHARGE_CODE}', '{TAX_CODE}', '{TAX_CODE_2}', '{TAX_CODE_3}', '{COD_FLAG}', '{SHOW_WARNING}', '{PROSPECT_FLAG}', '{PO_REQUIRED}', '{ZONE_REQUIRED}', '{PREPAY_REQUIRED}', '{PRINT_PRICES}', '{PRINT_WEIGHTS}', '{NAME}', '{ADDRESS_1}', '{ADDRESS_2}', '{CITY}', '{STATE}', '{ZIP}', '{USER_1}', '{USER_2}', '{TAX_AUTHORITY}', '{CONTACT}', '{SEQUENCE_CODE}', '{D_AND_B_RATING}', '{NOTES_1}', '{NOTES_2}', '{NOTES_3}', '{NOTES_4}', '{AR_GL}', '{TAX_ID}', '{UMS_SYSTEM}', '{CARRYING_COST_PCT}', '{CUST_COLOR}', '{MOB_LOGIN_NAME}', '{MOB_LOGIN_PASSWORD}', '{MOB_LOGIN_DATETIME}', '{MOB_LOGOUT_DATETIME}', '{MOB_LAST_COID}', '{MOB_SECURITY_CLASS}', '{IMPORTED_FLAG}', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' 
from artloc where loc_id=0 and not EXISTS (SELECT 'A' FROM ARTCUST WHERE CUST_NO= '{CUST_NO}'  );
Retrieved from "http://in.compucrete.com/index.php?title=Querygen_(original)&oldid=546"