Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Crystal Reports Error

Posted on 2014-10-02
10
Medium Priority
?
230 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Starting up a Project

670 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