?
Solved

Crystal Reports Error

Posted on 2014-10-02
10
Medium Priority
?
241 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 101

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 101

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 101

Accepted Solution

by:
mlmcc earned 1500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
Introduction to Processes
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

601 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