OTH – SQL Script Examples Used for Troubleshooting
If you create a SQL statement for support or troubleshooting purposes, please feel free to add it to this article if others could benefit from seeing it.
OTH – Running SQL Statements article describes how to run SQL statements using DB Explorer, IBConsole and SQL Server Management Studio.
IMPORTANT NOTE: CRM KB articles do not allow double quotes. In all examples below, assume that the single quotes should be double unless otherwise noted.Basic Record Selection
To select (display) all rows and columns from a table:
- Statement: SELECT * FROM <table name>
- Example: SELECT * FROM CONSUMER
To return a specific set of columns instead of the full set, replace the asterisk with a list of desired columns, separating them with commas:
- Statement: SELECT <column name 1>, <column name 2> FROM TABLE
- Example: SELECT DISPLAY_NAME, GENDER FROM CONSUMER
Finding Records That Meet Specific Conditions
To limit which rows are returned, use a WHERE clause after the table name, then a comparison statement that will display the rows of interest:
- Statement: SELECT * FROM <table name> WHERE <column name> = ”<value>”
- Example: SELECT * FROM CONSUMER WHERE GENDER = ”M”
You can use comparison operators in WHERE statements. The valid comparison operators are:
- = : equal to
- <> : not equal to
- > : greater than
- >= : greater than or equal to
- < : less than
- <= : less than or equal to
- Statement: SELECT * FROM TABLE WHERE COLUMN > ”VALUE”
- Example: SELECT DISPLAY_NAME, BIRTH_DATE FROM CONSUMER WHERE BIRTH_DATE > ”5/31/89”
- Show the name and birth date for consumers born after 5/31/89.
AND and OR
To specify mutiple conditions, string together multiple items in the WHERE clause by using AND when multiple conditions must be true:
- Statement: SELECT * FROM <table name> WHERE <column name 1> > ”<value>” and <column name 2> = ”<value>”
- Example: SELECT * FROM CONSUMER WHERE BIRTH_DATE > ”5/31/89” and GENDER = ”M”
- Show all fields from the Consumers table for men born after 5/31/89.
…or use OR when any one of the conditions can be true:
- Statement: SELECT * FROM <table name> WHERE <column name 1> > ”<value>” or <column name 2> = ”<value>”
- Example: SELECT * FROM CONSUMER WHERE BIRTH_DATE > ”5/31/89” or BIRTH_DATE < ”5/31/42”
- Show all fields from the Consumers table for anyone who was born after 5/31/89 or before 5/31/42.
NOTE: When using AND and OR statements, the field name used in the comparison must always be used as a part of the comparison. For example:
- Right: SELECT * FROM CONSUMER WHERE BIRTH_DATE > ”5/31/89” or BIRTH_DATE < ”5/31/42”
- Wrong: SELECT * FROM CONSUMER WHERE BIRTH_DATE > ”5/31/89” or < ”5/31/42”
BETWEEN
The word BETWEEN can be used to find values that fall within a range.
- Statement: SELECT * FROM <table name> WHERE <column name> BETWEEN ”<value 1>” AND ”<value 2>”
- Example: SELECT * FROM EMPLYEE WHERE EMP_NUMBER BETWEEN ”2000” and ”2999”
This can be especially helpful with date fields:
- Example: SELECT DISPLAY_NAME, GENDER, BIRTH_DATE FROM EMPLOYEE WHERE BIRTH_DATE BETWEEN ”1/1/1970” AND ”12/31/1970”
If you want to limit the rows to returned for just a few column values, you can use the reserved word IN:
Statement: SELECT * FROM TABLE WHERE COLUMN IN (”VALUE1”, ”VALUE2”, etc.)
Example: SELECT DISPLAY_NAME, CONSUMER_CITY FROM CONSUMER WHERE CONSUMER_CITY IN (”Blacksburg”, ”Arlington”)
A very useful technique for setup is to write a statement that will find missing information. You do that by finding NULL values:
Statement: SELECT * FROM TABLE WHERE COLUMN IS NULL
Example: SELECT DISPLAY_NAME, CONSUMER_CITY FROM CONSUMER WHERE CONSUMER_CITY IS NULL
The database tool may distiguish between null and blank values. For instance, a value that has never be filled in might be null, but one that has been filled in then deleted might be blank. To test for nulls and blanks:
Statement: SELECT * FROM TABLE WHERE COLUMN IS NULL or COLUMN = ””
Example: SELECT DISPLAY_NAME, CONSUMER_CITY FROM CONSUMER WHERE CONSUMER_CITY IS NULL OR CONSUMER_CITY = ””
SQL Functions to use with select
Add a count function after the select to count the rows:
Statement: SELECT COUNT (*) FROM TABLE
Example: SELECT COUNT(*) FROM CONSUMER WHERE CONSUMER_CITY IS NULL
To count distinct values within the returned dataset, use Count(Distinct) function:
Statement: SELECT COUNT (DISTINCT COLUMN) FROM TABLE
Example: SELECT COUNT(DISTINCT CONSUMER_CITY) FROM CONSUMER
To find the maximum or minimum value in a column, use the MAX() or MIN () functions
Statement: SELECT MAX(COLUMN) FROM TABLE
Example: SELECT MAX(BIRTH_DATE) FROM CONSUMER
Statement: SELECT MIN(COLUMN) FROM TABLE
Example: SELECT MIN(BIRTH_DATE) FROM CONSUMER
To find a sum of values:
Statement: SELECT SUM(COLUMN) FROM TABLE
Example: SELECT SUM(PT_TIME_EARNINGS) FROM V$PT_RECORD WHERE PT_EMP_NUMBER = ‘30389’
Basic Update Statements
Back up the database before doing an update. There may not be a way to “undo” the statements.
Statement: UPDATE TABLE SET COLUMN = ‘VALUE1’
Example: UPDATE EMPLOYEE SET EMP_JWOD = ‘N’
Limit the scope of the change by adding a where clause:
Statement: UPDATE TABLE SET COLUMN1 = ‘VALUE1’ WHERE COLUMN2 = ‘VALUE2’
Example: UPDATE EMPLOYEE SET EMP_JWOD = ‘Y’ WHERE EMP_NUMBER = ‘00309’
Be sure to test the where clause with a select statement before you run the update! Using Joins
Statement: Select * from TABLE1 JOIN TABLE2 ON TABLE1.FIELD = TABLE2.FIELD
Example: SELECT DISPLAY_NAME, ETHNIC_ORIGIN_ID, ETHNIC_ORIGIN_DSC FROM CONSUMER JOIN ETHNIC_ORIGIN ON CONSUMER.ETHNIC_ORIGIN_ID = ETHNIC_ORIGIN.ETHNIC_ORIGIN_ID
This article contains an ever-expanding list of SQL snippets and examples.
- Working with employee start dates
- Select all employees with a start date that is on or after <selected date>: SELECT * from EMPLOYEE where emp_hire_date >='<selected date>’ To limit the columns included in the set to just employee number and hire date: SELECT emp_number, emp_hire_date from EMPLOYEE where emp_hire_date >='<selected date>’
- Count the number of employees with a start date that is on or after <selected date>: SELECT count(*) from EMPLOYEE where emp_hire_date >='<selected date>’
- Change employee start dates if the employee started on or after <selected date>: UPDATE EMPLOYEE SET emp_hire_date = ‘<desired start date>’ WHERE emp_hire_date >='<selected date>’
- Working with employee termination dates
- Select all employees with a termination date: SELECT * from EMPLOYEE where emp_termination_date is not null To limit the columns included in the set to just employee number and hire date: SELECT emp_number, emp_termination_date from EMPLOYEE where emp_termination_date is not null
- Provide a termination date if the employee is currently inactive: UPDATE EMPLOYEE SET emp_termination_date = ‘<desired termination date>’ WHERE emp_pay_status_id = ‘1002’