Solved

How do we combine the results of two SQL statements in Crystal Reports - Basically we are wanting to AND two result sets

Posted on 2008-10-28
11
350 Views
Last Modified: 2008-11-01
We have two separate SQL statements or Commands in a Crystal Report and we want to AND them together.  What is the syntax to do so.  We are looking for a third SQL statement that allows us to AND Query 1 with Query 2 and the results returned are only those records that appear in both query 1 and query2 result sets.
0
Comment
Question by:rporter45
[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
11 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22827227
UNION ALL
0
 

Author Comment

by:rporter45
ID: 22827241
Could you please expand on this a little more?
0
 

Author Comment

by:rporter45
ID: 22827248
We are not looking to output both results.  Both queries return a unique ID.  We want to output unique ID's that are present in query 1 and query 2.
0
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

 

Author Comment

by:rporter45
ID: 22827346
How do you call the results from a SQL Command in Crystal to use in another SQL Command?
0
 

Author Comment

by:rporter45
ID: 22827405
Query 1 returns a list of unique ID's.  Query 2 returns a list of unique ID's.  Query 1 and 2 are represented in Crystal as separate Commands.

How do I create a third Command or Formula in Crystal that returns a list of unique ID's that are both in Query 1 and Query 2.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22827520
Let's try this, if I understand correctly.
 

SELECT DISTINCT x.ID
FROM
(
SELECT DISTINCT a.ID
FROM TableA a
UNION ALL
SELECT DISTINCT b.ID
FROM TableB b
) AS x

Open in new window

0
 

Author Comment

by:rporter45
ID: 22827967
This still doesn't work...

It doesn't seem to be referencing the Command (or SQL statements) properly.

SELECT DISTINCT x.ID
FROM
(
SELECT DISTINCT a.ID
FROM Command.0607 a
UNION ALL
SELECT DISTINCT b.ID
FROM Command_1.0708 b
) AS x;
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22828216
Are you trying to build this like referenced in the following?
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21292344.html
Whare are the two SQL statements used in the existing two querys? Perhaps we can tweak the syntax. Can you paste here?
0
 
LVL 82

Expert Comment

by:hielo
ID: 22829190
>>We have two separate SQL statements
You mean something like:
Q1: Select id FROM table1
Q2: Select id FROM table2

Q3: (SELECT id FROM table1) UNION ALL (SELECT id FROM table2)
0
 

Author Comment

by:rporter45
ID: 22829890
I am creating the SQL like this:

When you start to select a datasource you pick the AddCommand option rather than any of the tables.

You have to write the sql as it would be in an Access saved query.

When I add my SQL, it creates a Command for each one I create.  I want to use multiple Command's together in another SQL Command.
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 500 total points
ID: 22855775
I've answered this in your other open question:

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_23858113.html

In a nutshell, you can't do what you're asking.  Likewise, it's a really, really bad idea to link commands in a report do.
0

Featured Post

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

632 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