Solved

Crystal Reports Error

Posted on 2014-10-02
10
216 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 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
In this post we will learn different types of Android Layout and some basics of an Android App.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Simple Linear Regression

636 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