RE: Rational ClearQuest and SQL Server

I am not familiar with this tool, but the SQL keyword for concatenation is
CONCAT. Try that instead of +.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Rational ClearQuest and SQL Server

Hi

I'm using Rational ClearQuest which has an interface to create queries but
is very limited. It also has the option to edit the SQL once the query has
been created.
I tried to create the query closest of what i want and then i tried to edit
it via SQL.

I can't find the way to concatenate fields (the + didn't work)
I don't know how to change the date format to dd/mmm/yyyy
I find that to use functions i have to enclose the sentence in brackets and
put the letters fn...

Anyone knows if there is a special syntaxs to write SQL sentences in this
tool?

Thanks in advance and I hope the question is clear

This is a query that already works (i have to fix the identation):

select T1.dbid
,T2.oldid AS 'ID ESCENARIO'
,T1.legacyfailpoints AS 'ORDEN EJECUCION'
,T1.oldid AS 'ID EJECUCION'
,T1.legacytotalpoints AS 'ID PROCESO'
,T1.scriptoptions_1 AS 'PROCESO'
,T2.headline AS 'NOMBRE ESCENARIO'
,T1.legacyfiles AS 'NOMBRE TEST CASE'
,T1.notes_log AS 'DESCRIPCION'
,T1.flag_importance AS 'TIPO DE PRUEBA'
,T1.legacycustom3 AS 'APLICACION'
,T1.fldcolumn AS 'MODULO'
,T1.legacyuid AS 'AREA DE NEGOCIO'
,T7.login_name AS 'TESTER'
,T1.start_date AS 'FECHA EJECUCION'
,{fn year(T1.start_date)} AS 'FECHA CORTA' /*OK IT WORKS*/
/*,T16.name + T1.legacyinputs AS 'CONCATENADO'*/ /*DIDN'T WORK*/
,T1.end_date AS 'FECHA TERMINACION'
,T1.baseline_date AS 'FECHA BASELINE'
,T4.endtime AS 'FECHA REGISTRO'
,T1.legacyattemptedpoints AS 'NUMERO DE EJECUCIONES'
,T4.verdict AS 'VEREDICTO'
,T16.name AS 'ESTATUS'
,T1.legacyinputs AS 'REGION'
,T1.legacypostconditions AS 'B3 - ID ORIGEN'
,T1.legacycustom1 AS 'TIPO DE CUENTA'
,T1.scriptoptions AS 'B1 - FASE'
,T1.reprogram_justify AS 'DELIVERY DATE'
,T1.legacypasspoints AS 'TIPO DE SERIE (1-CPP 2-MPP)'
,T1.priority AS 'CRITICIDAD'
,T1.legacyacceptancecriteria AS 'B4 - MODIFICACION'
,T1.legacycustom2 AS 'B2 - GRUPO'
,T1.blocking_pn AS 'PN'
,T1.blocking_issue AS 'ISSUE'
,T1.legacypreconditions AS 'PRE-REQUISITO TECNICO'
,T1.id AS 'ID Rational'
from ( ( ( ( ( ( ( ( tmconfiguredtestcase T1
INNER JOIN users T7 ON T1.owner = T7.dbid )
INNER JOIN statedef T16 ON T1.state = T16.id )
INNER JOIN tmtestcase T5 ON T1.testcase = T5.dbid )
INNER JOIN tmtestplan T6 ON T5.parentplan = T6.dbid )
INNER JOIN tmassetregistry T21 ON T6.assetregistry = T21.dbid )
LEFT OUTER JOIN parent_child_links T2mm ON T1.dbid = T2mm.child_dbid and
16777696 = T2mm.child_fielddef_id )
LEFT OUTER JOIN tmtestsuite T2 ON T2mm.parent_dbid = T2.dbid )
LEFT OUTER JOIN tmtestlog T4 ON T1.dbid = T4.configuredtestcase )
where T1.dbid <> 0
and (( T21.name = 'NEXTEL_DM3'
and ({fn convert(T4.dbid,sql_varchar)} is NULL or T4.latest = 'TRUE')
)
)
order by T2.oldid ASC
, T1.legacyfailpoints ASC