Limit Results in SQL Server Using Row_Number

Here is a sample query that limits the number of results in a query to 500. The first bold item is the column the results need to be ordered by, and the second bold item is the table that your are pulling the data from. Everything else can be used as is to pull all columns from a table and limit the results to 500 rows.


SELECT * FROM
( SELECT
    ROW_NUMBER() OVER (ORDER BY ikeyid ASC) AS ROW_NUMBER,
    *
  FROM A_Table
) foo
WHERE ROW_NUMBER <= 500


You can also adjust the where statement in order to grab a range of records. I had to do this in order to pull out chunks of a huge table rather than pulling them all at once.

WHERE ROW_NUMBER > 0 AND ROW_NUMBER <= 50000

WHERE ROW_NUMBER > 50000 AND ROW_NUMBER <= 100000

WHERE ROW_NUMBER > 100000

Running this query 3 times with the 3 different where statements listed above will result in 3 results sets that have 50,000 rows a piece and the final query pulls in any row over the first 100,000.

Truncating Text In a Trigger Using APEX

Recently I was asked to put a trigger together that copied a Long Text Area value that can hold up to 32,000 characters to a Text Area field that can only hold up to 255 characters. Naturally when you try to copy a value larger than 255 characters to a Text Area field an error will be thrown. So I had to figure out how to basically truncate a string value to 255 characters in order to assign it to a Text Area field.

The string method 'substring' does the trick! What is does is much like in excel using the left(), mid() and right() formulas you give it a starting place and tell it how many characters to grab. Salesforce's basic example is as follow:

'hamburger'.substring(4, 8); 
// Returns "urge" 
    

'smiles'.substring(1, 5);
// Returns "mile" 

My problem was a bit more complex as I had to include logic that would say only perform this truncation when the Long Text Area value is larger than 255 characters. If you try to truncate a string value using substring to a length longer than the string itself you will get an error. Here is the code I used for this instance.

   string EX1 = Sample.LongTextArea1__c;
   string EX2 = Sample.LongTextArea2__c;
   
   //Get sizes of Goal and Strategy in the event they are longer than 255 char and need to be truncated
   Integer size1 = EX1.length();
   Integer size2 = EX2.length();
   
   //If Goal or Strategy are longer than 255 characters they need to be truncated
   if(size1> 255){
         EX1= EX1.substring(0, 255);
   }
   if(size2 > 255){
         EX2 = EX2.substring(0, 255);
   }

I retrieve the length of the strings that were assigned the value of the Long Text Area (EX1 and EX2) and if those lengths are more than 255 characters then I truncated it with the substring method. If they are shorter than the maximum length of a Text Area (255) I do not adjust them at all.

Below is a link to the String Methods as defined by Salesforce.com.

http://www.salesforce.com/us/developer/docs/apexcode/index.htm 

Setting Default Values to Record - Using Visualforce to Override New Button

The goal is to set some default values to a record, there are times that you can not set the proper default value to fields when using the 'Set Default Value' option on a field. A lookup value can be a prime example and that is why I had to research this.

You need 2 things, an apex class that puts together the URL for the page, and a Visualforce page that basically outputs the URL that is generated in the class.

Below are my two items, the Visualforce page code as well as the class that it invokes.

Visualforce Page Code:

<apex:page standardController="Account_Child__c" extensions="DefaultAccountController" action="{!putDefault}">

</apex:page>

APEX Class Code:

 public class DefaultAccountController {
public DefaultAccountController(ApexPages.StandardController controller) {
    }
 
    public PageReference putDefault() {  
String tester = getAccount().Name;
//creating a page reference
PageReference pageRef;      
//Variables to hold the URL parameters  
String retURL =  ApexPages.currentPage().getParameters().get('retURL');
String retIDName =     ApexPages.currentPage().getParameters().get('CF00NC00000053NfO');      
String retAcctID =     ApexPages.currentPage().getParameters().get('CF00NC00000053NfO_lkid');
//Variables from the parent account
List <Account> acct = [select id, name, phone,industry from Account where id =:retAcctID];
string acctNum = acct[0].phone;
string acctInd = acct[0].industry;
//The URL that will be used in the new VF page
pageRef = new PageReference('/a0A/e?CF00NC00000053NfO='+retIDName+'&CF00NC00000053NfO_lkid='+retAcctID+'&Name='+acctNum+'&00NC00000053NfY='+acctInd+'&retUrl='+retURL);  
//adding nooverride to prevent infinite loop  
pageRef.getParameters().put('nooverride', '1');          
pageRef.setRedirect(true);          
//Item that is returned when this class is invoked is the PageRef URL
return pageRef;
}
} 

The highlighted items below should appear on a single line of code.

This is a link to the developer.force.com post that was helpful in creating this.

http://boards.developerforce.com/t5/General-Development/How-to-set-standard-field-to-a-Default-value/m-p/333595/highlight/true#M60358

Exporting Queries from SQL to Excel

The easiest way to export a table from SQL is just running the query then selecting all of the results and copying it with headers then pasting it to excel. Sometimes that does not work though, and here is a workaround I have found if that does not work.

After writing the query, lets say "SELECT * from Contact.dbf" in SQL Server Management Studio (SSMS) follow these steps.

• Create a text file on your computer, lets say output.txt
• In SSMS Query > Results To... > Results to File
• In SSMS Tools > Options... > Query Results > SQL Server > Results to Text
• Next to output format select 'Custom Delimiter'
• Next to custom delimiter you can put anything, but I have found that the pipe symbol works well, so I enter this symbol: |
• Click OK
• In SSMS right click on your database and select 'New Query' the write your query and select Run Query
• IMPORTANT: the steps above must be completed before you open up the new query window.
• Locate your file on your computer and select it then press ok (You will have to change the option to all files rather than the .rpt file it wants)
• Finally open up excel and open up the text file, you will be prompted with a screen where you can set the | as the delimiter, and that's it!

Below is a link to the thread that assisted my in this.

http://stackoverflow.com/questions/6354130/is-there-a-select-into-outfile-equivalent-in-sql-server-management-studio

Mass Exporting Tables from Access to Excel

Recently I have been working with some Access databases that have large amounts of tables in them. Reviewing data in numerous Access tables can be burdensome and it would be easier to review them is they were in Excel.

I found this post that runs some code that does exactly what I need, here are the steps.

• In Access under the 'Database Tools' option click 'Visual Basic'
• In the new window click 'Run' then 'Run Macro'
• Type a name in like "AccessMacro" then click 'Create'
• In the new screen that appears copy and paste the code below replacing anything that was originally in the new window

Sub AccessMacro()

Dim tbl As TableDef
For Each tbl In CurrentDb.TableDefs
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tbl.Name, "C:\Users\Josh\Desktop\NexGen\Test2.xls", True, tbl.Name
Next

End Sub

You will need to adjust the area that is in bold above to the proper path on your computer when the file will be exported to.

Next click Run, then run macro and that's it!

If you hit an error that says 'User Defined Type Not Defined' simply do the following:

Tools > Reference... > (Check) Microsoft DAO 3.6 Object Library > OK

Below is the link to the original site that I found this information:

http://stackoverflow.com/questions/4036772/how-to-export-all-tables-from-an-access-database-into-excel-a-sheet-for-each-t