Technical Questions: find value that does not exist in same table

Print Page | Close Window

find value that does not exist in same table

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22826
Printed Date: 25 Apr 2024 at 11:46am

Topic: find value that does not exist in same table

Posted By: Tonyak74
Subject: find value that does not exist in same table
Date Posted: 16 Oct 2019 at 9:57am

I have a report that I am working on.

It has a table in the report called Equipment.

The Equipment table has a column for Control # and a column for Parent Tag.

I am trying to find away to show All Parent Tag data that does not exist anywhere in the Control # data.

Note: Parent Tag column may have empties and NULLS

I was easily able to pull the data in SQL with the following:

Select * from Equipment where ParentTag not in (Select ControlNo from Equipment) and ParentTag <>''.

I tried adding the Equipment table to the report a second time so I had Equipment and Equipment_1. I then did a left outer join from Equipment.ControlNo to Equipment_1.ParentTag, then in Select expert went to formula and added isnull({Equipment_1.ParentTag}). Did not work.

Example of what I have:

__________________________________________________

ControlNo ParentTag

1111

1222

1333 9999

1444

1555

1666 1111

1777 1333

1888 2222

Example of what I want the end result to be:

___________________________________________________

ControlNo ParentTag

1333 9999

1888 2222

So I only want to show the data from ParentTag that does not exist any where in the ControlNo data.

Replies:

Posted By: hilfy
Date Posted: 17 Oct 2019 at 7:29am

Instead of using just IsNull(), use not IsNull().

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Tonyak74
Date Posted: 17 Oct 2019 at 12:04pm

Thank you for the response..

I gave that a try and it is still pulling all records.

I think because the Data does not have Nulls it is just empty.

Posted By: DBlank
Date Posted: 24 Oct 2019 at 7:49am

IN the select set it to use defaults for NULLS and set it to ="" but also make sure you enforce your join or use fields from both instances of the tables to make the join enforced via data selection.
Or use the SQL you wrote as a Command.

Posted By: SNelson
Date Posted: 25 Oct 2019 at 3:45am

For your select did you try either of these:
{fieldname} <> ""
{fieldname} <> "**"

I've had luck with this when fields are blank but not Null

I've also sometimes been able to suppress rows by going to the Section Expert and in the Common tab entering a formula there to suppress certain conditions
Examples:
{fieldname} = ""
{fieldname} <> "**"
IsNull([fieldname}

The downside is this only suppresses the rows instead of not selecting them but would still work for export or viewing

-------------
SNelson

Posted By: Tonyak74
Date Posted: 25 Oct 2019 at 8:58am

Thank you all..
I was still having issues with the linking on the tables for some reason, so I took DBlanks advise and started a new report adding a Command using my SQL Statement.
I had tried adding the command to my original report, but had no luck with the linking.
Thank you for the help!

Print Page | Close Window