How to compare two columns using partially match result to display

Mohammad Rehman shared this question 3 years ago
Answered

Experts,

Thanks in advance for your time and help.

I have a requirement to display the report which has 4 columns and two columns partially match. Only need to display the partially matching rows and skip the non-matching.

For example:

col1 col2 col3 col4 compare col1 and col4

xyz-1234 bcd lmn 1234 display

xyl-543 hfhf dkjdk 3543 display

xym- 3333 cccc dfdd 11111 skip


How can we achive this in smart reporting?

Best Answer
photo

Hi Mohammad,

Getting the syntax to work on free hand SQL statements can be difficult. You may need to specify which table each row comes from, as freehand SQL will query the underlying DB and not the report itself (eg: names."CI Name")

I would recommend going into your relevant DBMS GUI (pgAdmin, MySQL Workbench, etc), and getting the select statement to work as desired there, and then copy that statement without the SELECT and FROM clauses into the Freehand builder.

In PgAdmin my select statement looked like: this


  1. SELECT
  2. CASE
  3. WHEN CONCAT('%',"B",'%') LIKE CONCAT('%',"A",'%') THEN 1
  4. WHEN CONCAT('%',"A",'%') LIKE CONCAT('%',"B",'%') THEN 1
  5. ELSE 0
  6. END
  7. FROM public."partMatch";

It will be hard for me to give specific advice without seeing how your database is set up, and what underlying DMBS you are using. If you can provide more specific information on this setup, I can try to give more relevant advice.

Regards,

Nathan

Comments (8)

photo
2

Hi,


Thank you for getting in touch. In a simple example, I was able to achieve your goals by implementing a freehand SQL calculated field at the view level, and then applying a filter to the newly created field.


  1. CASE
  2. WHEN CONCAT('%',"B",'%') LIKE CONCAT('%',"A",'%') THEN 1
  3. WHEN CONCAT('%',"A",'%') LIKE CONCAT('%',"B",'%') THEN 1
  4. ELSE 0
  5. END


This will create a new field where values to be kept are designated as 1, while values to be omitted are designated as 0. This can be seen in the screenshot below (Note that I am using the view editor of 7.3)


a43faf569ccbdad2570c2d46ef848d62


You can then create a pre-defined filter at the report level to show only rows where "A LIKE B" = 1.


7033b16febe0baa9cb1c4a9e08519966


Please let me know if this solution does not meet your needs, or if you have any additional questions relating to this issue.


Regards,

Nathan

photo
1

Hi Nathan,

Appreciate your time and help. When I use the similar Free hand SQL and use my column name, it throws validation error.

CASE

WHEN CONCAT('%',"CI Name",'%') LIKE CONCAT('%',"CI ID",'%') THEN 1

WHEN CONCAT('%',"CI ID",'%') LIKE CONCAT('%',"CI Name",'%') THEN 1

ELSE 0

END


Ambiguous column name ''%FE%FF%00%25''


Thanks

Mohammad

photo
1

FYI - I am using 7.1 (BMC supported version with their ITSM Suite)

photo
2

Hi Mohammad,

Getting the syntax to work on free hand SQL statements can be difficult. You may need to specify which table each row comes from, as freehand SQL will query the underlying DB and not the report itself (eg: names."CI Name")

I would recommend going into your relevant DBMS GUI (pgAdmin, MySQL Workbench, etc), and getting the select statement to work as desired there, and then copy that statement without the SELECT and FROM clauses into the Freehand builder.

In PgAdmin my select statement looked like: this


  1. SELECT
  2. CASE
  3. WHEN CONCAT('%',"B",'%') LIKE CONCAT('%',"A",'%') THEN 1
  4. WHEN CONCAT('%',"A",'%') LIKE CONCAT('%',"B",'%') THEN 1
  5. ELSE 0
  6. END
  7. FROM public."partMatch";

It will be hard for me to give specific advice without seeing how your database is set up, and what underlying DMBS you are using. If you can provide more specific information on this setup, I can try to give more relevant advice.

Regards,

Nathan

photo
1

Hi Nathan,


Appreciate the time and help and thanks for the lead, I will figure out the next.


Thanks

Mohammad

photo
2

Hi Mohammad,


I was just wondering if you have had any luck in resolving this issue? Please let me know if you have any additional questions, or if you would be okay with me closing this ticket.


Regards,


Nathan

photo
1

Hi Nathan,

Highly respect your follow-up but I have given up on this. It does not work for me, part of it could be I am using the customized version provided by BMC and lots of feature disabled. SQL views column names are different then actual table or view column name in the database. I have tried all possible options but all in vain.

Appreciate your knowledge sharing. Please close this ticket.

Thanks

Mohammad

photo
2

Hi Mohammad,


I am sorry to hear that. BMC does limit functionality and unfortunately it is hard for us to know exactly what has been limited, so there is a good chance my suggestions were completely irrelevant. I will close this, but please don't hesitate to reach out if you have any additional questions or concerns.


Regards,

Nathan