Syntax

The syntax for this command is:

select * | all | access-rights | changes | linked-items | changed-files | 
enhanced-links | lifecycle | links | workrecords | 
agile-breakdown | scope-change | differences | historical-revisions | attached-labels | share-tree |
unlabeled-revisions | merge-counts | attachments |connections-log | duplicate-shares | missing-artifacts | non-tip-artifacts | behavior | {propertyName, propertyName,...} | filter = 'filtername'
from 'typeName'  | starteam {history} {deleted} {backlog} {workspace} {rolldown}
{between-labels labelname1 and labelname2}
{attached-to-label labelname }
{at [label = "label" | promotionstate = "promotion state" | datetime = "date" {-pattern "pattern"}]
into fileName { separator 'field separator' } {newline newlineSeparator} {headers 'on' | 'off' | sqlnames} {toExcel}
{-pattern "datetimepattern"} {-locale "localecode"}
where
{{ attached-label = 'labelName' } | { query = 'myquery' } |
propertyName relation value and/or propertyName relation value and/or...}
{for} {folder = 'myfolder' {recurse} or folder = 'myfolderhierarchy' {recurse} or folder = . {recurse}} or ...}
order by {propertyName, propertyName,...} | orderFilter = 'myOrderFilter'
[–epwdfile “passwordfilepath”] [-p "userName:password@hostName:endpoint/projectName/
[viewName/][folderHierarchy/]"] | [-s "userName:password@hostName:endpoint]

Use the select command to invoke StarTeam meta-queries. The combination of options determines the type of query, which could be over a file, folder, change request or etc., and the saved filters for the type. Values that contain spaces should be enclosed in double quotes. This command has been modeled on the standard SQL SELECT syntax. Cross type joins are not supported. If folder identification clauses are not specified, the tool assumes the folderHierarchy is set through the setProject command or the root folder of the view, with recurse ON (that is, all descendants or depth == -1). The WHERE clause is constrained to a query and a possible set of folders. Folders may be combined with an OR, but cannot be joined with an AND. Folders act as a further constraint to a query. Folders potentially reduce the subset of results obtained from the query to the items that reside within the specified folders. When a folder hierarchy is specified in the WHERE clause of a select, update or delete statement, the path must start with the root folder and traverse the folder tree all the way down to the leaf folder of interest. It must be explicitly terminated by a \. However, the root folder path must not start with a \. / and \ are interchangeable.

Simple dynamic queries support either chained OR clauses or chained AND clauses. However, they do not support a mix of OR and AND conditions. Complex queries are supported but only as saved queries, for example: where query = 'mySavedQueryname'.

If a property name in the where clause identifies a text property, the relation is '=' and the value starts with a "*", then the * is treated as a wildcard targeted for expansion. This query returns all items whose property values end with the text of the query value. For example, the following returns all files in the view whose names end in .doc.

Only * is supported as a wildcard, and can exist contextually at the start of, at the end of or surrounding a phrase. In other words, "*.doc", "doc*", or "*.*" will all expand the * out to mean - any set of characters.

No other wildcard characters are supported. "." itself is treated as a literal character, no different than (say) a, b or c.

Parameters

For information about the command parameters, refer to Operation Parameters.

In addition:

Parameter	Description
* (| all)	Specifies all defined properties for the type. (This is a superset of propertyName1, propertyName2, ...) propertyName specifies the subset of properties for the type. relation may be any one of the following: = < <= > >= <> != in (,). Alternative equivalents are eq, epic, lt, lte, gt, gte, neq. in represents a multi-value set relationship. For example, changenumber in (1,2,3,4). All other relationships are single valued. For example, changenumber = 1, changenumber > 2, or changenumber < 5. Both eq and eqic represent explicit equality (=). However, eqic also implies case insensitive queries for text values properties.
access-rights	Overrides properties. If specified, it generates an access rights report. The columns are the set of available permissions. The rows are the Securable’s (or Container’s) for which access rights exist.
agile-breakdown	Overrides properties. If specified, it generates an agile breakdown report. Sprints For sprints, it is a denormalized join or view of all stories, tasks, and workrecords attached to the selected sprints. Each row represents the break down to the workrecord detail. The columns are broken down as: Columns Description 1-10 Sprint details 11-30 Story details 31-55 Task 56-60 WorkRecord Plans For plans, it is a denormalized join or view of all plans and requirements attached to the selected plans. The columns are broken down as: Columns Description 1-6 Plan details 7-30 Requirement details
historical-revisions	historical-revisions is a report that identifies and lists all the revisions at which the items are actually attached to the specified label. If historical-revisions is specified, then the where clause is ignored.
attached-labels	Produces a report combining all historical revisions of the selected item(s) with the labels attached to each of those revisions. The rows of this report match the label tab details in the StarTeam Cross-Platform Client.
share-tree	Produces a report describing the share tree of the selected items, equivalent to the Reference tab on the StarTeam.
unlabelled-revisions	Produces a report which is the converse of attached-labels. It lists only those historical revisions of the selected item(s) which are unlabeled as of the last build.
changes	Overrides properties. If specified, it generates a change package changes report. The columns identify revision details of each attached item to the change package. The rows identify the attached items. The report can span multiple change packages.
changed-files	Overrides properties. If specified, it generates a software lines-of-code count report. The rows identify the attached files. The columns identify revision details of all attached files to the process item. The report can span multiple process items. Each process item may be linked to multiple revisions of the same artifact (file). Each row provides a count of the number of lines added, number of lines changed, or number of lines deleted in the context of the checked in revision.
scope-change	Overrides properties. Generates a scope change report that is targeted at measuring the total scope (cost) of the sprint. It includes the history of the sprint including all active/deleted stories associated with the sprint and the history of active stories, with their estimated points.
differences	Overrides properties. If specified, it generates a differences report for files (spanning all revisions) between two labels. Note: differences can only be used in conjunction with the file type. If the differences keyword is specified, then between-labels'labelname1'and'labelname2' must be specified, (and overrides the where clause)
linked-items	Overrides properties. If specified, it generates a process item report. The columns identify revision details of all attached items to the process item. The rows identify the attached items. The report can span multiple process items. Each process item may be linked to multiple revisions of the same artifact (file, folder).
enhanced-links	Overrides properties. If specified, it generates an enhanced links report, which provides trace visibility across all views in the project for which trace the queried items shares exist. The columns identify revision details of all attached traces to the queried item. The rows identify the attached queried items. The report can span multiple items.
links	Overrides properties. If specified, it generates a links report. The query follows all the traces which lead to or away from the specified item and describes the details of the items at the other end point.
lifecycle	Overrides properties. If specified, it generates a report that tracks the item through its lifecycle (from creation, through moves and shares, to possible deletion). The rows identify all history and revisions per item, covering item changes, moves and deletes, ordered by modification time - from most recent to oldest. The report can span multiple items. The first column identifies the item by item id.
workrecords	Workrecords can only be specified in conjunction with the Task type. When workrecords is specified, a workrecords report will be produced.
merge-counts	Overrides properties and can only be specified in conjunction with the File type. When specified, the resultant report records a count of the number of times a file has been merged from any other view. The counts are generated per view.
attachments	Produces a report identifying all the attachments by name, ID, size and md5 for each item in the select list.
connections-log	Overrides properties. Generates a connections report. It queries the server log, parses each connection out, and identifies the users who have logged in, logged out, their license types (fixed v/s floating) and the total number of consumed licenses. The connections-log report is a metadata query that requires the type to be 'starteam'.
duplicate-shares	Overrides properties. If specified, it generates an exception report of all items which are shares of each other in the selected view.
missing-artifacts	Overrides properties. If specified, it generates an exception report of all items which are not attached to he label (specified by attached-to-label) in the selected view.
non-tip-artifacts	Overrides properties. If specified, it generates an exception report of all items which are attached to the label (specified by attached-to-label) in the selected view, but not at the tip revision.
behavior	Produces a behavior report. This report identifies the branch state and configuration characteristics of the selected items (from the where clause) and matches the StarTeam Cross-Platform Client Advanced Behavior dialog box.
Filter	Overrides properties. It identifies a saved filter (by name) for the specified type, and expands into a subset of properties for the type.
typeName	Specifies the item type. Types are mutually exclusive, and include any one of the stock or custom component (if any) types. Type name 'starteam' is a special case which currently only supports the 'connections-log' query. If 'starteam' is the specified type, then -s is sufficient to identify the server; i.e. -p is not required.
at	describes a rolled back view configuration. It may be one of label, promotion state or datetime. If specified, the query is run at the rolled back configuration. datetime may be further qualified by a pattern. The pattern must match any valid pattern supported by the java JDK in java.text.SimpleDateFormat.applyLocalizedPattern(String) and ensure that embedded date/ time strings strictly adhere to that pattern. If a pattern is specified, a 2 character country code locale may also be optionally specified.
attached-label	Specifies a label to which the items of the specified type have been attached. The items to be selected are the ones attached to the label.
deleted	Specifies the result set. If specified, only deleted items are returned.
history	Specifies a qualifier on the result set. For example, if the result set contains ten items of a given type, history returns all revisions of each item.
backlog	Can only be specified in the context of the Story type. For example, if the result set contains ten items of a given type, history returns all revisions of each item.
workspace	Acts as a constraint on file queries. If specified, it locates not-in-view folders and files from the file system (the view path mapped to working folders on disk) and includes them in the report.
rolldown	Can only be specified for tree item types. When specified, the children of the selected tree items (specifically, the entire tree of descendants) are added to the result set.
Folder	Specifies the StarTeam folder name in the current view. If there are multiple folders with the same name, the command returns all folders with that name. Or specify the folder hierarchy in the "/" format. The folder path musts start and end with "/". Start from the root folder and end in a branch folder. For example: /StarDraw/SourceCode/On-line Help/.
recurse	Designates all descendants from the folder specified.
.	Implies the current working folder, requiring the tool to find StarTeam folders with paths mapping to the current working folder. The Command processor must be running inside the StarTeam folder hierarchy.
into	"fileNameWithPath" specifies a file to which the output of the query will be written. The generated output is tabular in format, each row separated by a carriage return/line feed, specific to the platform the query is run on.
newline	If newline separator is specified, then embedded new lines inside text fields are replaced by the provided separator. If not specified, then embedded new lines are replaced by their character string equivalents, specifically, "\r" and/or "\n".
headers	By default, headers = on. If headers = off, output files are written without headers. A special case of headers turned on is to substitute the property internal names for the display names by using the syntax sqlnames.
toExcel	Produces an output file which is in CSV format. The columns are separated by a comma and embedded new lines in text are replaced by line feeds. This is a special format which allows Microsoft Excel to successfully import these types of files directly into a spreadsheet while retaining the tabular structure of the data.
myQuery	Specifies a saved StarTeam query name for the type. The suported property value types are: Text Literal string. Integer A string in the form of an integer like "1234". Double A string in the form of a double like "1234.5678". Long A string in the form of a long like "1234567890". Boolean The string "true" or "false" - case insensitive. Date String format yyyy-mm-dd, 4 digit year, 1 <= mm <= 12, 1 <= dd <= 31. DateTime If -pattern "pattern" is specified, then it is parsed using java.text.SimpleDateFormat, localized pattern set to "pattern". See http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html. If -pattern is not specified, attempt to match patterns using java.text.DateFormat {SHORT, MEDIUM, LONG, FULL} in that order. If all else fails, the engine will attempt ISO8601 parsing e.g.: yyyy-mm-ddThh:mm:ssZ . DateTime patterns work two ways. When specified, they are used to parse input date/time strings in the query itself. Additionally, if datetime properties are output, they are formatted to fit the specified pattern. TimeSpan String format [ws][-][d. |d]hh:mm:ss[.ff][ws], items in brackets optional. see com.starteam.util.TimeSpan. ws whitespace, d days, ff fractional second, hh hours, mm minutes 0 <= mm <= 59, ss seconds 0 <= ss <= 59. Enumerated String. Enumerated value specified may be internal name, display name, or string representation of integer enumeration code. If the Enumerated property is multi-selectable, two or more enums may be specified. In this case, they must be separated by a period. For example: 101.102.103. Object User Value specified may be user name or string representation of integer user id. Group Value specified may be group name or string representation of integer group id. Label Value specified may be label name or string representation of integer label id. LinkValue A string in the form of an integer like "1234" which represents the viewmemberid of the source or target link of a trace.
order by	Specifies a default sort order for the output result set.
OrderFilter	Specifies the saved StarTeam filter name for the type. It expands into a subset of properties for the type, which is used for sorting. myFilter and myOrderFilter can be different. The properties specified in MyOrderFilter should not be set for grouping. If a set of property names is specified instead of the order filter, the sort criteria default to ascending order, sort by text is set for text properties, and sort by date is set for Date/DateTime Properties. If you need more specific sorting, specify an existing saved filter.

In general, the select grammar is positional. It is best to follow the position of the syntax as outlined in the command description above.

Examples

select * from File where query = "Status = Current" order by
orderfilter = "All Files By Status" -p
"Administrator:Administrator@localhost:49201/StarDraw/Release 1.0
Maintenance"

The following example shows how to use the differences keyword. It selects all properties of all change requests and writes them into a file called QueryOutput.txt.

select differences from file between-labels 14.0.3.21 and 14.0.3.27
into "c:/temp/differencesReport.txt" separator | -p
"username:password@hostname:port/project/view"

select * from changerequest into "c:/temp/QueryOutput.txt"

select linked-items from ChangeRequest where AddressedIn = "Next Build"
or
select linked-items from ChangeRequest where AddressedIn = "-2"

The example below selects three properties, Name, Status, and File Time Stamp at check in, for all files, which satisfies the built in query Files to Checkin.

select Name, Status, Modified from file where query = "Files to CheckIn"

The example below selects all tasks from the Sales Materials folder or the Marketing Materials folder and its descendants. It returns a result set containing only the task properties described by the "By Status and Responsibility" filter. select * from task where filter = "By Status & Responsibility"

select filter = "By Status and Responsibility" from task where folder = 
"Sales Materials" or folder = "Marketing Materials" recurse

The following examples show how to use select with change requests and change packages.

select linked-items from ChangeRequest into fullyQualifiedPathToOutputFile 
where ChangeNumber = 1234 -p "username:password@host:port/project/view"

select changes from ChangePackage where name = "Workspace Changes on 2013-10-15@22-43-00Z"

This example shows how to use the select command with the lifecycle parameter.

select lifecycle from File into fullyQualifiedPathToOutputFile 
where FileName = Server.java -p "username:password@host:port/project/view"

This example shows how to generate a workrecords report:

select workrecords from task where StTaskNumber = 88

The following example shows how to user the select command with the rolldown parameter:

stcmd (ex)   select {propertyname,(s)…} from task rolldown where {…} –p “…”

This examples shows how to generate an agile-breakdown report:

select agile-breakdown from sprint into c:/temp/agileReport.txt 
separator "|" where SprintID = 1234 -p "user:password@host:port/project/view"

The following example shows you how to select rows in a range:

select "*" from changerequest into "c:/temp/stout.txt" where 
changenumber ">" 50000 and changenumber "<" 50100 -p
"user:password@host:port/project/view"

The next example uses a date range on the ModifiedTime property:

select "*" from ChangeRequest into "c:/temp/stout.txt" pattern =
"M/d/y" where modifiedtime ">" 1/1/2014 and modifiedtime "<"
8/30/2014 -p "user:pwd@host:port/project/view"

The following example produces history results similar to the deprecated hist command:

select revisionnumber, viewid, modifieduserid, modifiedtime,
comment from file history
-p "Username:Password@host:port/project/view/[folderhioerarchy]/"
where name = "filename"

The next example shows the use of historical-revisions

select historical-revisions from file attached-to-label "label
name" -p "user:password@host:port/project/view"

The next two examples show the use of wildcards and fully qualified folder paths

select folder, name from file where name = "*.doc" for folder =
"StarDraw\Source Code\External Resources\"

select viewmemberid, name, description from file where name =
"*.doc"

The next few examples show different usage of property relation values:

select * from ChangeRequest pattern = "M/d/y" where ModifiedTime
> 12/14/1999 and modifiedtime < "2/16/1999"

select * from ChangeRequest where ChangeNumber in (10298, 10310,
10316, 10320)");

select * from ChangeRequest where ChangeNumber = 10298
select * from ChangeRequest where ChangeNumber ">=" 10298 and
ChangeNumber "<=" 10320
select * from File where name = "GNUmakefile.build*"
select * from File where name = "*.buildinfo"
select * from File where name = "*ranching release
views.doc*"
select * from ChangeRequest where Responsibility = "Alan
Kucheck"

Note that ">=" can be replaced by gte without the double quotes around it,

= can be replaced by eq, etc.

This example shows the use of date patterns driving the format of output date time property values:

select name, modifiedtime from file -pattern "yyyyMMddHHmmss"
where name = "*.java" order by name -p
"user:pwd@host:port/project/view"

This example queries the path of all files which are exclusively locked by a user:

select Path from file where exclusivelocker <> "" 

select connections-log from starteam into
"c:/temp/connections.log" -s "user:password@host:port"

This example reports on custom properties, and uses them to drive the query.

select changenumber, usr_projecttype, usr_dateclosed from
changerequest into custom.cr.output.txt headers off pattern =
"M/d/y" where USR_DATECLOSED gte 1/1/2015 and USR_DATECLOSED lt
1/1/2016

This same query with greater precision:

select changenumber, usr_projecttype, usr_dateclosed from
changerequest into custom.cr.output.txt headers off pattern =
"M/d/y H:m:s" where USR_DATECLOSED gte "1/1/2015 0:0:0" and
USR_DATECLOSED lt "12/31/2016 23:59:59"