SQL Statements

If you want to copy all the g/l accounts from feature 2303 into another branch with another extension, like .02. In the example below, I am copying Branch Main's gl sales accounts to Branch SWA02 and adding the .02 extension. SQL statement: INSERT INTO "gl_sales_accounts" ( SELECT 'SWA02' as "branch_id" ,category_id ,round(gl_acct_sales,1) +0.02 as "gl_acct_sales" ,round(gl_acct_cogs_or_purc,1)+0.02 as "gl_acct_cogs_or_purc" ,round(gl_acct_returns,1) +0.02 as "gl_acct_returns" ,round(gl_acct_inv_asset,1) +0.02 as "gl_acct_inv_asset" FROM gl_sales_accounts AS g WHERE g.branch_id = 'MAIN' );

Copying Branch Sales G/L accounts from one branch to another and adding extension
  If you want to copy all the g/l accounts from feature 2303 into another branch with another extension, like .02. In the example below, I am copying Bra...
Thu, 24 Oct, 2013 at 4:45 PM
Finding a value in a pervasive Table and Updating the value
Say you want to search the customer table for sales tax code 1 'AZ' and replace with sales tax code 1 'AZ2'   Here is the SQL statem...
Thu, 24 Oct, 2013 at 4:52 PM
Searching a column/field in Pervasive
To do a simple search type in the following SQL statement.   select * from "table name" where column_name = '  '   For example,...
Thu, 24 Oct, 2013 at 6:30 PM
Can't create packing list (need to update iclotsrsv file)
Run the following SQL statement if you have new data set that won't let you create a packing list:   ALTER TABLE "inventory_reserves"( ...
Fri, 22 Nov, 2013 at 1:04 PM
Runtime 13 on VAT tables - Means a bad copy of the data set was loaded:
If you get a runtime 13 while trying to access the sales order file or customer file after turning on the buyout feature run the following sql:   ALT...
Tue, 11 Feb, 2014 at 12:57 PM
Can't create invoice from shipment detail or doesn't allow you to post the invoice because of insuffient quantity even if there is.
This is due to a bad data set that we sent out, so only Gorilla, Right, Atlas, and Besco should have been affected.   Run this SQL statement:    AL...
Wed, 4 Dec, 2013 at 4:39 PM
If you want to have the customer since date so it's formatted so you can do a range of dates in the customer file in Excel. Do the following: (Was for Hudson)
This command should be put in the edit query section of the Microsoft Query:   Select  cast(  left(customer_since_date,4)  +'-'+ right(left(c...
Fri, 6 Dec, 2013 at 11:53 AM
SQL statement to show any account numbers that have a balance but are not on the financial formats
This sql statement will show any account numbers that have a balance in any period and are not in any of the formats:   select gl_account_no from&...
Wed, 12 Aug, 2015 at 6:18 PM
SQL statement to make a field upper case in the tables
On any field you want to change to upper case in the customer file, run the following sql statement, changing the customer_name to whatever field he wants...
Fri, 10 Jan, 2014 at 4:27 PM
User gets a "Can't Post because another user is posting receivings" on Receiving sreen
If the user gets this error and they know no one else is posting.  Go to the features table and do the following:   UPDATE features SET "value&quo...
Wed, 5 Feb, 2014 at 1:22 PM