Hello,
I would like to create a database view that will take the lifecycle report and merge it with the Software Discovery Model table in order to do better querying and generated some easier to read reports without all the fancy dot walking so that my end users just have a better experience.
Anyways I'm having and issue.
I start with with Lifecycle Report (prefix slr) with no where clause
I then add the Software Discovery Model (prefix sdm) with the where clause = "slr_norm_product = sdm_norm_product && slr_norm_full_version = sdm_norm_full_version && slr_manufacturer = sdm_norm_publisher"
the Problem here is when there is two different editions of the same product, I will get double the record in the final table. Example in the Lifecycle Report it has product A with Edition "Pro" and "Enterprise" and then in the Software Discovery Model you only have product A with Edition "Enterprise", you will then get 2 records one that is "Enterprise/Enterprise" "Enterprise/Pro".
The obvious solution here is to add to the where clause "&& slr_norm_edition = sdm_norm_edition", which I did at first.
The real problem that arise is that if "edition" is blank then the row is not included in the view, and a lot of the records I care about have black (or Null) editions.
with additional clause "&& slr_norm_edition = sdm_norm_edition"
without:
So I want to add the join clause on the edition field but also include when edition is blank (null).
I looked up how a SQL database join works in this situation and this seems like the same behavior as NULL
== NULL evaluates to false. It says for SQL to fix this I need to explicitly state it. Something like:
&& (slr_norm_edition = sdm_norm_edition or (slr_norm_edition is Null and sdm_norm_edition is Null)).
I just don't know the right syntax as every time I submit I get an error. Has anyone really gone deep with database join where clauses that could help me out?
Thanks in advance.
In my PDI. 'norm_product' exists on the 'sam_sw_product_lifecycle_report ' and 'samp_pattern_normalization_rule' tables. However, I'm waiting on the proper plugin to activate to get the 'cmdb_sam_sw_discovery_model' table. Add the Left Join on the table list, set it to true, to get all records from the one table.
