Solved

Crystal Reports 2008 - Problem with NULL in SQL query

Posted on 2014-03-27
7
1,894 Views
Last Modified: 2014-03-27
I am using Crystal Reports 2008 and trying to run a SQL expression query on an Oracle database. However, the query does not behave as I expected it to.
The database keeps returning NULL values for the field when I am trying to filter them out.

When I use:
(select "TAGENT"."CODE" from "TAGENT"
where "TAGENT"."REFERENCE" = "TJOBS"."AGENT_REF"
AND ("TAGENT"."REFERENCE")IS NOT NULL)

The query returns all the correct fields as well as the one containing blank values.
(12869 NULL values and all the other fields which total 408) = 13277 fields

If I change to look at only NULL values:
(select "TAGENT"."CODE" from "TAGENT"
where "TAGENT"."REFERENCE" = "TJOBS"."AGENT_REF"
AND ("TAGENT"."REFERENCE")IS NULL)

The query then returns one filed containing 13277 fields showing as blank value.

"TAGENT".”CODE” is a String (10)
"TAGENT".”REFERENCE” is a Number

I have created a Sub Report in Crystal which when I use selection formulas and look at the SQL query it creates I get (The dates are for the lastfullmonth):
 SELECT "TJOBS"."PICKUP_DATE", "TJOBS"."REFERENCE", (select "TAGENT"."CODE" from "TAGENT"
where "TAGENT"."REFERENCE" = "TJOBS"."AGENT_REF")
 FROM   "FREEDOM_LIVE"."TJOBS" "TJOBS"
 WHERE  ("TJOBS"."PICKUP_DATE">=TO_DATE ('01-02-2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "TJOBS"."PICKUP_DATE"<TO_DATE ('01-03-2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) AND (select "TAGENT"."CODE" from "TAGENT"
where "TAGENT"."REFERENCE" = "TJOBS"."AGENT_REF") IS  NOT  NULL

This query works and I get just the 408 records I am expecting.

Can someone please shed some light on how I get a single SQL expression Field to work the as the expression works in the sub report when using selection formulas.

Thanks
0
Comment
Question by:Bizatanium
  • 3
  • 2
  • 2
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39958772
WHere are you using the queries?

I notice the first 2 refer to a second table that isn't included in the query.

At the end you use the term SQL Expression.  Are you trying to build a Crystal SQL Expression or are you just using that in a generic sense?

mlmcc
0
 
LVL 1

Author Comment

by:Bizatanium
ID: 39958788
I am using these queries in Crystal Reports 2008 as a Crystal SQL Expression.

The 1st 2 SQL queries shown are Crystal SQL expression formulas.

the last one is the the SQL query in the sub report that you get when you select Show SQL Query from the database menu in Crystal.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39958876
I asked about the SQL Expression because a Crystal SQL expression can only return a SINGLE value so it can't be returning that many rows or fields.

Why do you think it is returning that many values?

mlmcc
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 1

Author Comment

by:Bizatanium
ID: 39959010
I am trying to get just one value from the SQL expression
"TAGENT".”CODE”

Based on the the parameters:
"TAGENT"."REFERENCE" = "TJOBS"."AGENT_REF"
"TAGENT".”REFERENCE" IS NOT NULL

"TAGENT".”CODE” is then returned, but it also returns the values where "TAGENT".”CODE” has no value (which means "TAGENT".”REFERENCE” has no value)

I then try and use this in a Cross Tab, but I get all the correct rows as well as the NULL rows.

To get around this I can write a Crystal formula called Worth Reporting where it checks if the result of the Crystal SQL Expression is Null:
Not Isnull({%Subbed})

Then by adding another column & filtering out the False values my table will look correct.

I had hoped to be able to get the correct data from the original Crystal SQL expression without then having to filter out the data even more.

Thanks
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 500 total points
ID: 39959549
The database keeps returning NULL values for the field when I am trying to filter them out.

The problem is you're NOT filtering them out of the report.  A SQL Expression will return a single distinct value when the conditions of the expression are met.  in this case, you want want records that aren't NULl and you're getting them when that condition is met.  Otherwise, you're getting NULLs because the expression is still running against every detail record in you report.

If you really want to filter out records where the value is NULL from appearing in your report at all, the next step is to use the SQL Expression in your record selection criteria as something like:

Not IsNull({%Code})

This would ensure your report efficiently filters out all records where there is no code.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39959554
On a side note, what version of Oracle are you using and what driver?  In the past, I haven't ever been able to get a full SELECT Oracle SQL Expression to correlate to a field in the report.
0
 
LVL 1

Author Closing Comment

by:Bizatanium
ID: 39959782
I had thought this might be the case, as you’re stated the expression is running against every detail in the report.

Using a SQL expression in my record selection criteria is what I’ve done in the sub report that works, I had just hoped it was possible to combine that into a single Crystal SQL expression.

The work around I have ended up choosing is to use the Not IsNull({%Code}) as the 1st Row in my Cross-Tab and then only showing TRUE values and having the detail in Row 2, which does the trick without filtering out data that will be needed in the rest of the report.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now