How to compare two columns using partially match result to display

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)

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

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

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

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

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

Hi Nathan,

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

Thanks

Mohammad

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

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

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