| Author |
Message |
ursyy25
Newbie
Joined: 02 Feb 2011
Online Status: Offline
Posts: 6
|
   Topic: SQL
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!
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
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
|
|
|
|
IP Logged |
|
ursyy25
Newbie
Joined: 02 Feb 2011
Online Status: Offline
Posts: 6
|
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?
|
|
IP Logged |
|
c16271
Groupie
Joined: 24 Aug 2010
Location: United States
Online Status: Offline
Posts: 48
|
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.
|
|
IP Logged |
|
ursyy25
Newbie
Joined: 02 Feb 2011
Online Status: Offline
Posts: 6
|
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).
Edited by ursyy25 - 03 Feb 2011 at 1:09am
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
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
|
|
|
|
IP Logged |
|
ursyy25
Newbie
Joined: 02 Feb 2011
Online Status: Offline
Posts: 6
|
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'.
Edited by ursyy25 - 03 Feb 2011 at 4:11am
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
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
|
|
|
|
IP Logged |
|
ursyy25
Newbie
Joined: 02 Feb 2011
Online Status: Offline
Posts: 6
|
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)
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
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
|
|
|
|
IP Logged |
|
|
|
