Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Technical Questions
Message Icon Topic: find value that does not exist in same table Post Reply Post New Topic
Author Message
Tonyak74
Newbie
Newbie
Avatar

Joined: 24 Apr 2013
Online Status: Offline
Posts: 20
Quote Tonyak74 Replybullet Topic: find value that does not exist in same table
    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.

IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3668
Quote hilfy Replybullet Posted: 17 Oct 2019 at 7:29am
Instead of using just IsNull(), use not IsNull().

-Dell
IP IP Logged
Tonyak74
Newbie
Newbie
Avatar

Joined: 24 Apr 2013
Online Status: Offline
Posts: 20
Quote Tonyak74 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9011
Quote DBlank Replybullet 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.
IP IP Logged
SNelson
Newbie
Newbie
Avatar

Joined: 20 Jan 2017
Online Status: Offline
Posts: 13
Quote SNelson Replybullet 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
IP IP Logged
Tonyak74
Newbie
Newbie
Avatar

Joined: 24 Apr 2013
Online Status: Offline
Posts: 20
Quote Tonyak74 Replybullet 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!
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.063 seconds.