Print Page | Close Window

SQL

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12207
Printed Date: 05 May 2024 at 2:05am


Topic: SQL
Posted By: ursyy25
Subject: SQL
Date Posted: 02 Feb 2011 at 2:56am
Hy!

I have a problem with 'converting' my 'sql sintax' to 'Crystal Reports syntax' (Select Expert/Formula Editor).

So, I have:
...
AND NOT Code1 in
(SELECT Code2
FROM CodeEvidence
WHERE Date BETWEEN '02.10.2010' AND 12.10.2010')

How can I write this 'select' in 'Crystal Reports syntax'?

Thanks for help!





Replies:
Posted By: hilfy
Date Posted: 02 Feb 2011 at 3:51am
You can't do it that way in Crystal syntax.  So, you have two options:
 
1.  If Crystal allows it for your type of database, write a single command to pull in ALL of the data for your report.  A command is just a SQL Select statement in the syntax of your database.  If you can write SQL, and it appears that you can, this may be the easiest route to take.
 
2.  If you are unable to write a command for your type of database or if you chose not to, the alternative is to do the following:
 
- Add the CodeEvidence table to your report.  If it's already there, you'll need to add it a second time, in which case Crystal will alias it with "_1" at the end of the name.
 
- Link from Code1 in your existing data to Code2 in CodeEvidence.  Right-click on the link, select "Link Options" and change the link to a left outer join.
 
- In the Record Selection formula put something like the following:
{CodeEvidence.Date} in Date(2010, 2, 10) to Date(2010, 12, 10) and
IsNull({CodeEvidence.Code2))
 
NOTE:  If the dates can be changed, you may want to use parameters instead of hard coding them.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: ursyy25
Date Posted: 02 Feb 2011 at 4:38am
1. Where can I write that single command in Crystal Reports and how will it know that I'm connecting to my DB?

Posted By: c16271
Date Posted: 02 Feb 2011 at 5:45am
What version of CR do you have?

If you have XI, then:

1. Go to  Database
2. Database Expert
3. Create a DB Connection
4. "Add Command"
5. Right Click on Command, and press "Edit Command"
6. Put your query in there

Hope this helps.

Posted By: ursyy25
Date Posted: 02 Feb 2011 at 7:00pm
I have version 8.5. No option for that there?

I tried with option 2, but I get no results. Without date and Your suggestion the results are the same in Crystal Reports and in my DB, but if I add date in Crystal Reports I get no results.

Why do I need two tables of one table? And with which one do I connect, original or _1? And original and _1 have all the fields connected.

If I'm more clear, I want all the codes1 from that range of date and that are not the same as codes2 (or that are not in that select).

Posted By: hilfy
Date Posted: 03 Feb 2011 at 3:46am
With 8.5, you can actually edit the SQL that Crystal generates for the report - the option is somewhere in the Database menu.  You could potentially create the full SQL for the report there.
 
Do you want all of the records where Code1 <> Code2 in the same record for the date range?  How are you joining the original data without the aliased table?
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: ursyy25
Date Posted: 03 Feb 2011 at 4:08am
Code2 and Date are in Table2, in Table1 I have Code1. I'm trying to compare Code(s)1 with Code(s)2 that are in desired 'date range', and I want to only get those Code(s)1 that are not in Table2 (not equal Code2 in 'date range'). 
 
I used left outer join as You suggested - everything is fine, until I use 'date range'.

Posted By: hilfy
Date Posted: 03 Feb 2011 at 4:26am

Did you left outer join from Table1 to Table2 or the other way around?  It needs to be from 1 to 2.

Could you please post the formula with the date range from the Select Expert?  Thanks!
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: ursyy25
Date Posted: 03 Feb 2011 at 5:37am
Yes, it's from Table1 to Table2.
 
I have:
{CodeEvidence.Date} in DateTime (2010, 10, 02, 00, 00, 00) to DateTime (2010, 10, 12, 00, 00, 00)

Posted By: hilfy
Date Posted: 03 Feb 2011 at 7:48am

Is that all that's in your select formula?  Does the {CodeEvidence.Date} data have timestamps?  If not, just use Date(2010,10, 02) to (2010, 10, 12).  If it does and you want to include all of 10/12, you need to either change the time to 59, 59, 59 or change the date to 10/13.

Also, because of the left outer join, you need to add this to the formula:
 
and IsNull({CodeEvidence.Code2})
 
If that doesn't solve the problem, please explain exactly how it's not working - what data you get vs. what you expect.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: ursyy25
Date Posted: 03 Feb 2011 at 8:39am
Yes, it does (timestamps).
 
I also have  IsNull({CodeEvidence.Code2}).
 
Can I send You some printscreens tomorrow?
 


Print Page | Close Window