| Author |
Message |
neilsja
Newbie
Joined: 03 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
|
  
Topic: SQL Where Problem
Posted: 06 Oct 2011 at 4:26am |
Hi All Another quick query that is frustrating me. I have a table with the following fields: NAME TAG ORDER I have 'NAME' on my report which is fine. However, I only want to see 'NAMES' that have a 'TAG ORDER' of 2. I cannot see how to specify this? Look forward to your help. James.
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Oct 2011 at 4:59am |
assuming this is a 1:1 ratio,
in the select expert use
table.tag_order=2
|
| IP Logged |
|
neilsja
Newbie
Joined: 03 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
|
Posted: 06 Oct 2011 at 5:14am |
Thank you for your reply. That's what I thought also, yet when I add that comment, it still returns the records I have with TAG_ORDER 2 & 4. However if I copy/paste the SQL that Crystal generates; it returns exactly what it should. Very confused! One thing I have noticed is that on the SQL it says: "IM_INVGRP"."IVG_GRP2"="GL_GRPCODES2"."GRP_CODE"
WHERE "GL_GRPCODES2"."GRPTAG_ORDER"=2 But on the Select Expert it says 2.00. Dont know if this may point to something?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Oct 2011 at 5:18am |
ahhh. you have 2 tables.
Try this.
Go into the Databse Expert
Select Links tab
Double CLick on the link (line)
IN the 'Enforced Join' use "enforced both'
|
| IP Logged |
|
neilsja
Newbie
Joined: 03 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
|
Posted: 07 Oct 2011 at 5:31am |
I thought you may have been on to something then, but alas; still the same result!  I still get two results returned: HOSPITAL 1 which is GRPTAG_ORDER 2 and hospital 1 which is GRPTAG_ORDER 4
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Oct 2011 at 6:24am |
|
Can you post some sample data after the tables are joined and explain how you want it to look.
Your last post confused me.
|
| IP Logged |
|
neilsja
Newbie
Joined: 03 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
|
Posted: 09 Oct 2011 at 10:37pm |
Hi There In its simplest for, my table looks like this: Name GRPTAG_ORDER ----------------------------------------- HOSPITAL 1 2 hospital 1 4 So when I run the report (no matter what I put in the select expert), I am always getting: Report Page 1: Name = HOSPITAL 1 Report Page 2: Name = hospital 1 All I want is for my report to show 'hospital 1' (which is GRPTAG_ORDER 2). If I run the generated SQL into my sql analyzer, then I get the correct result. It is just Crystal not returning the correct output.
Thank you for all of your help to date.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Oct 2011 at 4:49am |
this still leads me to think it has something to do with joins.
are there only 2 tables in the whole report?
are you using at least one field from both tables in the report? if not try adding a field from the missing table onto the canvas?
if that does nothing,
can you expalin each table and the field(s) you are joinng on
|
| IP Logged |
|
neilsja
Newbie
Joined: 03 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
|
Posted: 12 Oct 2011 at 10:04pm |
Hi There Unfortunately there are multiple tables involved, but I thought simplifying the process would point me in the right direction. I am going in for surgery tomorrow, so will get back to you in a few weeks. However I have some further ideas on this problem also. Thank you for all of your help already...
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Oct 2011 at 4:09am |
given multiple tables the join issue is the most likely.
In SQL making a join automatically "enforces" it and limits your data set based on what type of you you do. In Crystal, you have to enforce any/all joins. This happens automatically when you use any field from both tables in the join or if you use the database expert as I suggested earlier.
WAn easy way to test this is to put a total record count on your report and drag and drop any field from each table onto the canvas. If you see your total record number change at any time you know you just 'enforced' a join that hanged your record selection output.
If you don't know this is happening or why it can be quite confusing to wathc your data disppear from your report just because you stuck a new field on the canvas.
Good luck with the surgery.
|
| IP Logged |
|
|
|
