KCL Software Solutions

CIOC SQL Help

Searching by Field Length

To retrieve records based on the length (number of characters) of a given field, you will use the LEN() function. This can be done on a single field or a combination of fields. For example: LEN(DESCRIPTION) &g...

Read Article

SQL for locating Smart Quote, Em-Dash, Elipsis

Special characters commonly inserted when pasting from formatted documents like Microsoft Word can cause data portability and indexing problems. The following searches help identify most cases of records with a...

Read Article

SQL to Find Records with Feedback

Feedback SQL - Community Information Records with Feedback of any kind: EXISTS(SELECT * FROM GBL_FeedbackEntry WHERE NUM=bt.NUM) Records with Feedback submitted by the public: EXISTS(SEL...

Read Article

Find Public Records with Non-Public/Deleted Equivalent

Note: an "equivalent record" is an associated version of the same record in another language Community Information Records with Mismatched Public Status To find a record that has an equivalent record ...

Read Article

Find Multiple Y Terms in Taxonomy Link

The following Search SQL identifies instances of Linked Taxonomy Terms with more than one Y Term in the link: EXISTS(SELECT * FROM CIC_BT_TAX tl WHERE tl.NUM=bt.NUM AND (SELECT COUNT(*) FROM CIC_BT_TAX_TM tlt...

Read Article

Find Multiple Non-Target (Not Y) Terms in Taxonomy Link

The following Search SQL identifies instances of Linked Taxonomy Terms with more than one non-Y Term in the link: EXISTS(SELECT * FROM CIC_BT_TAX tl WHERE tl.NUM=bt.NUM AND (SELECT COUNT(*) FROM CIC_BT_TAX_TM...

Read Article

Find a Complete Word in Specific Field

The CIOC Custom Field Search on the Advanced Search form allows you to search for a sub-string - an exact match of what you enter that occurs anywhere in the field. The problem with this type of search is that ...

Read Article

Searching Multiple Date Fields

There are multiple ways that a date can be modified in CIOC, and sometimes you want to find out if any one of those dates was set during the period of time you are searching. Although you can search two date fi...

Read Article

Services with Multiple Sites, Sites with Multiple Services

To identity Services with multiple Site locations, use: ((SELECT COUNT(*) FROM dbo.GBL_BT_LOCATION_SERVICE WHERE SERVICE_NUM=bt.NUM) + CASE WHEN EXISTS(SELECT * FROM dbo.GBL_BT_OLS WHERE NUM=b...

Read Article

Identifying Records that only use Taxonomy Terms from a Given Branch

To return records that only contain Taxonomy Terms from a Given Branch, edit the bolded elements in the SQL with the Taxonomy Code representing the branch. Below (example is "PX"): EXISTS(SELECT * FROM CIC_BT_...

Read Article