Solved

Crystal Reports Error

Posted on 2014-10-02
10
211 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.

751 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