Add multiplicity as rows

Question

I use Sparx EA to build a relationship database (MS SQL Server).

The tool has a function to create charts.

I use the query:

SELECT system.Name AS Series, systemElement.Name AS GroupName
FROM t_object systemElement
INNER JOIN t_object system ON system.Object_ID = systemElement.ParentID
INNER JOIN t_object organization ON organization.ParentID = systemElement.Object_ID
INNER JOIN t_object post ON post.ParentID = organization.Object_ID AND post.Stereotype = 'ActualPost'

WHERE system.Name = 'Name of my main system object'

to get

which is great, just for the fact that the chart doesn't account for the multiplicity of the ActualPosts.

I can access the multiplicity with

SELECT system.Name AS Series, systemElement.Name AS GroupName, post.Name AS 'ActualPost', post.Multiplicity AS 'Multiplicity'
FROM t_object systemElement
INNER JOIN t_object system ON system.Object_ID = systemElement.ParentID
INNER JOIN t_object organization ON organization.ParentID = systemElement.Object_ID
INNER JOIN t_object post ON post.ParentID = organization.Object_ID AND post.Stereotype = 'ActualPost'

WHERE system.Name = 'Name of my main system object'

which results in a table like:

Series | GroupName | ActualPost | Multiplicity
Name of my main system element | OperationalPerformer A | Post AA | 1
Name of my main system element | OperationalPerformer A | Post AB | 1
Name of my main system element | OperationalPerformer A | Post AC | 2
Name of my main system element | OperationalPerformer B | Post BA | 1
Name of my main system element | OperationalPerformer B | Post BB | 1
Name of my main system element | OperationalPerformer C | Post CA | 3
Name of my main system element | OperationalPerformer C | Post CB | 2
Name of my main system element | OperationalPerformer C | Post CC | 5

What I'm thinking is to add the multiplicity as rows, so turn a table like this:

Series | GroupName | ActualPost| Multiplicity
Name of my main system element | OperationalPerformer A | Post AA | 1
Name of my main system element | OperationalPerformer A | Post AB | 1
Name of my main system element | OperationalPerformer A | Post AC | 2

into

Series | GroupName
Name of my main system element | OperationalPerformer A
Name of my main system element | OperationalPerformer A
Name of my main system element | OperationalPerformer A
Name of my main system element | OperationalPerformer A

Although I don't know how to do that.

Any thoughts?

Thanks!

UPDATE:

Seems like Sparx EA has some limitations when it comes to SQL-queries... The query HAS TO start with select, see WITH clause within EA Sparx query

Is there a workaround for provided answers?

Do you want to repeat every row so many times as defined in Multiplicity column? — Martin Samek, Apr 09 '21 at 13:20

Annamalai D · Accepted Answer · 2021-04-09T13:57:55.120

This will work if the Multiplicity <= 2047

WITH myTable as (
    SELECT system.Name AS Series, systemElement.Name AS GroupName, post.Name AS 'ActualPost', post.Multiplicity AS 'Multiplicity'
    FROM t_object systemElement
    JOIN t_object system 
      ON system.Object_ID = systemElement.ParentID
    JOIN t_object organization 
      ON organization.ParentID = systemElement.Object_ID
    JOIN t_object post 
      ON post.ParentID = organization.Object_ID AND post.Stereotype = 'ActualPost'
    WHERE system.Name = 'Name of my main system object'
)
SELECT
    Series, GroupName
FROM
    myTable m
    JOIN master.dbo.spt_values v on m.Multiplicity > v.number
WHERE
    Type = 'P'

Modified CTE to Derived Table check this out

SELECT
    Series, GroupName
FROM (
    SELECT system.Name AS Series, systemElement.Name AS GroupName, post.Name AS 'ActualPost', post.Multiplicity AS 'Multiplicity'
    FROM t_object systemElement
    JOIN t_object system 
      ON system.Object_ID = systemElement.ParentID
    JOIN t_object organization 
      ON organization.ParentID = systemElement.Object_ID
    JOIN t_object post 
      ON post.ParentID = organization.Object_ID AND post.Stereotype = 'ActualPost'
    WHERE system.Name = 'Name of my main system object'
) DT
    JOIN MASTER.dbo.spt_values v on DT.Multiplicity > v.number
WHERE
    Type = 'P'

Thank you. Nothing returned.. Seemed strange. See my update in the question. — Robbas, Apr 09 '21 at 13:47

Martin Samek · Answer 2 · 2021-04-09T14:08:33.157

0

;WITH T1 AS (
    SELECT
        system.Name AS Series,
        systemElement.Name AS GroupName,
        post.Multiplicity AS 'Multiplicity',
        1 as Cnt
    FROM t_object systemElement
        INNER JOIN t_object system ON system.Object_ID = systemElement.ParentID
        INNER JOIN t_object organization ON organization.ParentID = systemElement.Object_ID
        INNER JOIN t_object post ON post.ParentID = organization.Object_ID AND post.Stereotype = 'ActualPost'
    WHERE system.Name = 'Name of my main system object'

    UNION ALL

    SELECT
        Series,
        GroupName,
        Multiplicity,
        Cnt + 1 AS Cnt
    FROM T1
    WHERE Cnt < Multiplicity
)
SELECT
    Series,
    GroupName
FROM T1
OPTION (MAXRECURSION 0) -- set recursion limit to undefined

edited Apr 09 '21 at 14:08

answered Apr 09 '21 at 13:32

Martin Samek

173
7

Thank you. Nothing returned.. Seemed strange. See my update in the question. – Robbas Apr 09 '21 at 13:46
Sql returns nothing when Multiplicity is null or < 1 or when original sql in your qeustion doesnt return data. Otherwise it works well – Martin Samek Apr 09 '21 at 14:00
That might be, but apparently the tool I'm using has some limitations, as I wrote in the updated question. So in the tool, this dows nothing. – Robbas Apr 09 '21 at 14:11

Add multiplicity as rows

2 Answers2