SQL Scratch Pad/Document Report - Custom Query - SQL Problem

[leonhardtk]

I'm trying to create a custom query for a Document Report for a requirements diagram.  I'm having trouble figuring out how to do conditional "WHERE" so that if the "WHERE" is True, display the a value from another table, and if the condition is false to display, "None Specified. 
My query works fine until I attempt this last step and I either only get the value for the true statement and ignores the non-matching rows, OR
I get the the value for the true statement and multiple copies of the the non-matching rows.  Here's my SQL:

Code: [Select]

SELECT   pkg.Name as Package,
diag.Name as Diagram,
org.Name as Organization,
ref.Name as Guidance,
refa.Default as Guidance_Date,
resp.Name as Responsibility,
resp.Alias as Responsibility_Type,
respreq.Requirement as Description,
IIF(resp.Alias = "Responsibility",
     MID(auth.Constraint, INSTR(auth.Constraint, "- ")+2), \\Only if Alias is "Responsibility"
        "None Specified") as Authority   \\If Alias is "Reference Task"
FROM t_package pkg,
t_diagram diag,
t_object org,
t_diagramObjects dorg,
t_object ref,
t_diagramObjects dref,
t_connector cref,
t_attribute refa,
t_object resp,
t_objectrequires respreq,
t_diagramObjects dresp,
t_connector cresp,
t_objectConstraint auth
WHERE pkg.Package_ID = #PACKAGEID# AND        \\ Package
pkg.Package_ID = diag.Package_ID AND \\ Diagram
(dorg.Diagram_ID = diag.Diagram_ID AND  \\ Organization
org.Object_ID = dorg.Object_ID AND
org.Object_Type = "Actor") AND
(dref.Diagram_ID = diag.Diagram_ID AND
dref.Ojbect_ID = ref.Object_ID AND
ref.Object_Type = "Artifact" AND
ref.Alias = "Authoritative Document" AND
cref.Start_Object_ID = org.Object_ID AND
cref.End_Object_ID = ref.Object_ID AND
cref.Connector_Type = "Dependency") AND \\ Guidance Document
(refa.Object_ID = ref.Object_ID AND
ref.Name = "Document Date") AND \\ Guidance Document Date
(dresp.Diagram_ID = diag.Diagram_ID AND
dresp.Object_ID = resp.Object_ID AND
resp.Object_Type = "Artifact" AND
cresp.Start_Object_ID = ref.Object_ID AND
cresp.End_Object_ID = resp.Object_ID AND
cresp.Connector_Type = "Dependency") AND \\ Responsibility ; Identified as either "Responsibility" or "Reference_Task"
(respreq.Object_ID = resp.Object_ID) AND \\ Responsibility Description

((res.Alias = "Responsibility" AND
  resp.Object_ID = auth.Object_ID AND \\ Find "Authority" if a "Responsibility"
  auth.Constraint LIKE "Authority*") OR \\ Extract "Authority" from string, "Authority Reference - MyDocument"
(resp.Alias = "Reference Task") )Ignoring the  "Authority" column and logic, I correctly get the following Results:

Code: [Select]

Package Diagram Organization Guidance Guidance Date Responsibility Responsibility Type Description
------- ------- ------------ -------- ------------- -------------- ------------------- -----------------
PKG_1 Diag_1 My_Org      SOP_10 1/1/2000    Resp_1    Responsibility      "Resp_1 Desc...."
PKG_1 Diag_1 My_Org       SOP_10 1/1/2000    Ref Tsk 2    Reference Task      "Ref Tsk 2 Desc."
PKG_1 Diag_1 My_Org      SOP_10 1/1/2000    Ref Tsk 3    Reference Task      "Ref Tsk 3 Desc."
The problem comes in when I try to add "Authority".  If the Responsibility_Type is "Responsibility", then an row is added to the t_ObjectConstraint table
with a CONSTRAINT = "Authority Reference - MyDocument", but I only want the text AFTER the " - " (e.g., MyDocument).
adding my code to retrieve the Authority document, my expected results are:

Code: [Select]

Package Diagram Organization Guidance Guidance Date Responsibility Responsibility Type Description       Authority
------- ------- ------------ -------- ------------- -------------- ------------------- ----------------- ----------------------
PKG_1 Diag_1 My_Org      SOP_10 1/1/2000    Resp_1    Responsibility      "Resp_1 Desc...." Authority Document 100
PKG_1 Diag_1 My_Org       SOP_10 1/1/2000    Ref Tsk 2    Reference Task      "Ref Tsk 2 Desc." None Specified
PKG_1 Diag_1 My_Org      SOP_10 1/1/2000    Ref Tsk 3    Reference Task      "Ref Tsk 3 Desc." None Specified
but my actual results are:

Code: [Select]

Package Diagram Organization Guidance Guidance Date Responsibility Responsibility Type Description       Authority
------- ------- ------------ -------- ------------- -------------- ------------------- ----------------- ----------------------
PKG_1 Diag_1 My_Org      SOP_10 1/1/2000    Resp_1    Responsibility      "Resp_1 Desc...." Authority Document 100
PKG_1 Diag_1 My_Org       SOP_10 1/1/2000    Ref Tsk 2    Reference Task      "Ref Tsk 2 Desc." None Specified
PKG_1 Diag_1 My_Org      SOP_10 1/1/2000    Ref Tsk 3    Reference Task      "Ref Tsk 3 Desc." None Specified
PKG_1 Diag_1 My_Org       SOP_10 1/1/2000    Ref Tsk 2    Reference Task      "Ref Tsk 2 Desc." None Specified
PKG_1 Diag_1 My_Org      SOP_10 1/1/2000    Ref Tsk 3    Reference Task      "Ref Tsk 3 Desc." None Specified
PKG_1 Diag_1 My_Org       SOP_10 1/1/2000    Ref Tsk 2    Reference Task      "Ref Tsk 2 Desc." None Specified
PKG_1 Diag_1 My_Org      SOP_10 1/1/2000    Ref Tsk 3    Reference Task      "Ref Tsk 3 Desc." None Specified
....Repeats for a couple hundred rows

Note that my data is on a segregated network so data above is notional but representative, and the SQL and results were hand-typed here, so I apologize in advance if there are any misspellings or syntax errors; there are no errors on the segregated networks, other than the logic error I seeking assistance on.

I appreciate any assistance you all might be able to provide.

[Geert Bellekens]

I would start by changing your query to use the join syntax.
You are obviously having a problem with multiplying results. That is usually because you don't have a proper join condition.

The problem with your query now is that your join conditions are very far away from the tables they are supposed to be joining.
I for one don't feel like trying to figure out which is which.

Geert

[leonhardtk]

You do bring up a good point, one that I debated on how to post as the query works fine except for the last part. 
1.  copy the entire query from my other network and add notes to address the relationships (easiest for me) or
2.  Simplify the query for just the broken part (easier for everyone else)

Based on your comments, I'll simplify the query for just the broken part and use the JOIN ON syntax.  I don't do SQL very well, so it'll take me a bit.
Thanks for the response.

[Geert Bellekens]

I strongly recommend too always write your queries using the join syntax.
It will make your life a lot easier.

Geert

[leonhardtk]

Doing a crash course on W3Schools to figure out how to use the JOIN syntax... 

[Geert Bellekens]

Here's a bunch of EA specific examples of queries I use in my scripts:

https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/search?q=sql

Geert

[leonhardtk]

Bingo!  Finally got the "JOIN" syntax to work.  For my trouble column, I changed to a LEFT JOIN to get all results and do the lookup for the Responsibility element and it worked Perfectly.  I've been trying to post my final answer, but keep getting and error:  Clean Talk ***Might be SPAM*** or something like that!  Will try to clean it up again and post.

Thanks again!

[leonhardtk]

Okay, that worked.  I wasn't sure how to use the "JOIN" syntax; but ended with with a working solution.

I Changed the last Join to a "LEFT JOIN" as it seems to make sense on what I'm trying to do:

SELECT org.Name as Org,
      ref.Name as Guidance,
      resp.Name as Responsibility,
      resp.Alias as Responsibility_Type,
      IIF(resp.Alias = "Responsibility", MID(auth.Constraint, INSTR(auth.Constraint, "- ")+2), "None Specified") as Authority
      
FROM ((((((((t_package pkg
INNER JOIN t_diagram diag ON pkg.Package_ID = diag.Package_ID)
INNER JOIN t_diagramObjects dorg ON diag.diagram_ID = dorg.diagram_ID)
INNER JOIN t_object org ON dorg.Object_ID = org.Object_ID)
INNER JOIN t_diagramObjects dref on diag.diagram_ID = dref.diagram_ID)
INNER JOIN t_object ref ON dref.Object_ID = ref.Object_ID)
INNER JOIN t_diagramObjects dresp ON diag.diagram_ID = dresp.diagram_ID)
INNER JOIN t_object resp ON dresp.Object_ID = resp.Object_ID)
LEFT JOIN t_objectConstraint auth ON resp.Object_ID = auth.Object_ID)

WHERE org.Object_Type = "Actor" AND
     (ref.Object_Type = "Artifact" AND
      ref.Alias = "Authoritative Document") AND
     (resp.Alias in ("Responsibility","Reference Task")) AND
    ((resp.Alias = "Responsibility" AND
      resp.Object_ID = auth.Object_ID AND
      auth.Constraint LIKE "Authority*") OR
     (resp.Alias = "Reference Task"))

Which gives the correct (expected) answer:

Org   Guidance   Responsibility                              Responsibility_Type   Authority
======   ==========   ================   ===================   ==========================================================
Actor1   Document 1   Responsibility 1                   Responsibility      Authorized IAW Guidance Document 10  (page 2, para 1.2.1
Actor1   Document 1   Responsibility 2                   Reference Task      Not Specified
Actor1   Document 1   Responsibility 3                   Reference Task      Not Specified



Thanks!