SQL
Description
Sets or returns the SQL string associated with the query.
If you assign a value to this property, the QueryDef object uses your string instead of the terms you have built using other methods of the QueryDef object.
If you get the value of this property, the QueryDef object returns the SQL string that will be executed when the query is run. If you had assigned a SQL string to this property earlier, that string is returned; otherwise, this method generates a SQL string from the terms that have been added to the QueryDef object so far.
Note: When using
SQL statements in HCL Compass queries,
the SQL statements must include database column names and not field
names. Corresponding database column names and field names may not
be identical to each other if the field name:
- Contains words that are reserved by SQL.
- Is renamed after a schema revision is applied to at least one user database.
Syntax
VBScript
querydef.SQL
querydef.SQL string_of_SQL_statements
Perl
$querydef->GetSQL();
$querydef->SetSQL(string_of_SQL_statements);
- Identifier
- Description
- querydef
- A QueryDef object.
- string_of_SQL_statements
- A String containing the individual SQL statements.
- Return value
- For the Get, returns a String containing the SQL
that will be executed when the query is run.
For the Set, no return value. Returns an exception if user does not have SQL writer privilege.
Examples
VBScript
set session = GetSession
set workspace = session.GetWorkSpace
'Get the QueryDef by supplying a query name
set querydef = workspace.GetQueryDef "Public Queries\Defects"
'Provide a string of SQL statements to set SQL
querydef.SQL "select distinct T1.dbid,T1.id,T1.headline from Defect
T1,statedef T2 where T1.state = T2.id and (T1.dbid <> 0 and (T2.name =
'Submitted'))"
Perl
$workspace = $session->GetWorkSpace();
$querydef = $workspace->GetQueryDef(queryName);
$querydef->SetSQL("select distinct T1.dbid,T1.id,T1.headline from Defect
T1,statedef T2 where T1.state = T2.id and (T1.dbid <> 0 and (T2.name =
'Submitted'))");