Custom SQL query problem

[ocean1]

Hi guys!
I have a problem with document generation in EA. I made custom query that show tagged values and type for attribute.
Code:

Code: [Select]

select a.Name as ATTR, a.Type AS TYPE, a.stereotype AS STEREOTYPE, tv.Property as tagName, tv.Value AS tagValue
from (t_attributetag tv
inner join t_attribute a on tv.ElementID = a.ID)
where a.Object_ID = #OBJECTID#
But it generates like 1st table. But I need the second. Please tell me how should I change query?

[Geert Bellekens]

I think that's more of an SQL question then an Enterprise Architect question, so you might get a better result if you check sites like Stackoverflow.
I found some existing questions that might get you on the way

http://stackoverflow.com/questions/8005846/sql-server-combining-multiple-rows-into-one-row
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

The answer might be different depending on the database you are using.
I think I would probably go for a script fragment at this point.

Geert

[Screwtape]

Geert is right, the only way I can see of doing this is using SQL subqueries (which personally I could write for Oracle but not any other database), or a script fragment.

Sadly, the script fragment approach is also laborious, since you would have to break your join into two queries, and handle the concatenation yourself within the script.

It is a shame that EA doesn't have structure within the custom fragment dataset, even though it is structured in such a way that it would be possible.

Instead of just having the

Custom>
{AField}
<Custom

tags, it would be nice at least to be able to have

Custom>DataSet0>
{AnyField}
<DataSet0
DataSet1>
{AnotherField}
<DataSet1<Custom

given that the XML format would support that, and even better if you could nest datasets (which the current XML format doesn't support).

[ocean1]

All solutions in google based on WM_CONCAT, GROUP_CONCAT, FOR XML, but it impossible to implement in EA.

Script fragment means that I should write script with help of javascript, VB script and etс?

[Geert Bellekens]

Yes,

I have some real world script fragments in the Enterprise Architect VBScript Library you can use as an example.

See the package Template Fragments

Geert

[ocean1]

I am not developer I do not know vb and js.

Could you please say what i must change in this code.

Code: [Select]

option explicit

!INC Local Scripts.EAConstants-VBScript

function MyRtfData (objectID, tagname)

dim xmlDOM
set  xmlDOM = CreateObject( "MSXML2.DOMDocument.4.0" )

xmlDOM.validateOnParse = false
xmlDOM.async = false

dim node
set node = xmlDOM.createProcessingInstruction( "xml", "version='1.0'")
    xmlDOM.appendChild node
'
dim xmlRoot
set xmlRoot = xmlDOM.createElement( "EADATA" )
xmlDOM.appendChild xmlRoot

dim xmlDataSet
set xmlDataSet = xmlDOM.createElement( "Dataset_0" )
xmlRoot.appendChild xmlDataSet

dim xmlData
set xmlData = xmlDOM.createElement( "Data" )
xmlDataSet.appendChild xmlData

'loop the Attributes
dim element as EA.Element
set element = Repository.GetElementByID(objectID)
dim attribute as EA.Attribute

if element.Attributes.Count > 0 then
for each attribute in  element.Attributes
addRow xmlDOM, xmlData, attribute
next
MyRtfData = xmlDOM.xml
else
'no attributes, so return empty string
MyRtfData = ""
end if
end function

function addRow(xmlDOM, xmlData, attribute)

dim xmlRow
set xmlRow = xmlDOM.createElement( "Row" )
xmlData.appendChild xmlRow

dim xmlAttributeName
set xmlAttributeName = xmlDOM.createElement( "Name" )
xmlAttributeName.text = attribute.Name
xmlRow.appendChild xmlAttributeName

dim xmlAttributeType
set xmlAttributeType = xmlDOM.createElement( "Type" )
xmlAttributeType.text = attribute.Type
xmlRow.appendChild xmlAttributeType

dim xmlAttributeNotes
set xmlAttributeType = xmlDOM.createElement( "Notes" )
xmlAttributeName.text = attribute.Note
xmlRow.appendChild xmlAttributeNotes

dim xmlAttributeStereotype
set xmlAttributeStereotype = xmlDOM.createElement( "Stereotype" )
xmlAttributeStereotype.text = attribute.Stereotype
xmlRow.appendChild xmlAttributeStereotype

dim tags as EA.Collection
set tags = attribute.TaggedValues

' List all element tags
for i = 0 to tags.Count - 1
dim currentTag as EA.TaggedValue
set currentTag = tags.GetAt( i )

dim xmlAttributeTag
set xmlattributeTag = xml.DOM.createElement( "Tags")
xmlAttributeTag.text = (currentTag.Name & ": " & currentTag.Value)
xmlRow.appendChild xmlAttributeTags

end function

[Geert Bellekens]

If you send me an email we might be able to work something out, but I'm afraid I won't be able to do it for free, that is what i do for a living

Geert