I NEED HELP WITH A SQL SENTENCE

[charlyprg]

Hi, I try to create a SQL sentence for a template but I have a error because I use  COALESCE and convert(varchar(90)). I use the enterprise 13 bulit 1310

SELECT
RN.Name as [Name],
RN.Alias as [Padrão],
RN.Note as "Note.Formatted", --(Esta me toco cambiarla por "ObjectNotes-Formatted")
RN.Status as [Status],
 COALESCE((SELECT convert(varchar(90), REL.Name) + char(13) AS [text()]
     FROM t_object REL, t_connector CON2, t_connector CON3
     WHERE CON3.Start_Object_ID = #OBJECTID#
     AND REL.Object_ID = CON3.End_Object_ID
    AND REL.Stereotype in ('BusinessProcess',  'FunctionalRequirement' )
    AND REL.Object_ID = CON2.End_Object_ID
    AND CON2.Start_Object_ID = RN.Object_ID
   FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), '') AS [REL]
FROM t_object RN, t_connector CON1
WHERE CON1.Start_Object_ID = #OBJECTID#
AND RN.Object_ID = CON1.End_Object_ID
AND RN.Stereotype = 'BusinessRequirement'
AND RN.Status NOT IN ('StandBy', 'Revisar')

[Geert Bellekens]

Which database? What error?

Most of us are not clairvoyant, so you'll have to give us a bit more info.

Geert

[charlyprg]

Sorry I don´t know a DataBase, how I configure wich database can use?. I don't use a EA in a server, I used only in my computer and it's the first time that I installed the software

[charlyprg]

I Understand, how it's the first time that I installed the software in my computer the default database is Access. Thank you!!!

[charlyprg]

The correct question is: How I can convert this sentence in SQL in a sentence in Access?

SELECT
RF.Name as [Name],
RF.Alias as [Padrão],
RF.Note as "Note.Formatted",
RF.Status as [Status],
 COALESCE((SELECT convert(varchar(90), REL.Name) + char(13) AS [text()]
     FROM t_object REL, t_connector CON2, t_connector CON3
     WHERE CON3.Start_Object_ID = #OBJECTID#
     AND REL.Object_ID = CON3.End_Object_ID
    AND REL.Stereotype in ('BusinessRequirement', 'NonfunctionalRequirement')
    AND REL.Object_ID = CON2.Start_Object_ID
    AND CON2.End_Object_ID = RF.Object_ID
   FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), '') AS [REL]
FROM t_object RF, t_connector CON1
WHERE CON1.Start_Object_ID = #OBJECTID#
AND RF.Object_ID = CON1.End_Object_ID
AND RF.Stereotype = 'FunctionalRequirement'
AND RF.Status NOT IN ('StandBy', 'Revisar')
order by RF.Name

Thanks!!!!

[Geert Bellekens]

I'm afraid you'll have a lot of work, and I'm not even sure you can translate this into Access SQL syntax.
Especially the "FOR XML PATH" part is going to be problematic.

Maybe it is easier to setup a local SQL Server instance and transfer your model to that.

Geert

[qwerty]

Or you use a script in conjunction with some simple SQL to get the output you need.

q.

[Geert Bellekens]

Or you use a script in conjunction with some simple SQL to get the output you need.

q.

I'm pretty sure the query is used in an SQL template fragment (as "Note.Formatted" gave it away).
Converting an SQL fragment to a script fragment is a big step.

Geert