OTH - SQL Script Examples Used for Troubleshooting

»

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’

Home

»

Knowledge Base

»