Solved

Crystal Reports Error

Posted on 2014-10-02
10
197 Views
Last Modified: 2014-10-03
Main report with Sub Reports; Sub reports use SQL Expressions to pull 'counts' from tables.  1st Subreport 'fine'; succeeding reports which use different tables; are not working.

(SELECT COUNT(*)
FROM purchase_order
WHERE purchase_order.order_id = "client_"."ord_id"
GROUP BY purchase_order.order_id )

Gives me an error of:
Error in Compiling SQL Expression:
Database Connector Error: '42000: MS SQL Server Native Client 10.0
The Multi-part identifier "client_"."ord_id" Could not be bound (Db vendor code 4104

The 'only' difference between this sql expression and the first sub report's sql expression is: "client_"."ord_id"
the reports are 'all' alike in framework, tables are only difference.
Thoughts?
0
Comment
Question by:Spt_Us
  • 5
  • 3
  • 2
10 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 40357233
I've come across this  error before :)
not a show stopper
The reason its happening is the table that contains that field is not in the query client_"."ord_id"
you need to use a JOIN to include that table in your query and that will resolve it
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40357496
How did you add the field to the SWL Expression?

DId you select it from the field list?

mlmcc
0
 

Author Comment

by:Spt_Us
ID: 40357566
I selected it and even typed it. I am not in front of it to try what plusone3055 is asking but I will.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40357612
Since you get a SQL Server error, I assume you are using SQL Server as the database.

Oracle cannot do correlated SQL Expressions.

mlmcc
0
 

Author Comment

by:Spt_Us
ID: 40359214
I cannot do the inner join. errors out as well
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 22

Expert Comment

by:plusone3055
ID: 40359269
Please elaborate on  not being able to do the join
0
 

Author Comment

by:Spt_Us
ID: 40359293
It's telling me there is more than one return value. I got it to work by changing the field to another table, but now some of my values that are coming back as NULL, Blanks or 0s will not 'hide' a field .....
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 40359314
Are you trying to filter based on a value in the current record in the report?

You can use IsNull to test for NULL values.

mlmcc
0
 

Author Comment

by:Spt_Us
ID: 40359326
Ok; my SQL expression does a "COUNT" of rows based on the SQL SELECt statement. If it returns 1 or more a picture object (like Yes/No) will show. Yes for 1 or greater else NO. However, I am using ISNULL, Blank, "", < 1 and it's still showing the graphic. There are not any records returning and I know that is right for this one Order.
0
 

Author Comment

by:Spt_Us
ID: 40359408
Nevermind; I GOT IT

1. I used another field in my report on the SQL expression
2. I use a "ISNULL" at the section level and not field.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

743 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

12 Experts available now in Live!

Get 1:1 Help Now