Crystal Reports SQL limit criteria question.

[ChrisMcKenzie]

Hi! This is my 1st post and obviously I'm here because I need help. I do plan on contributing though.<br>
<br>
On to my question. I am using Crystal Reports 6 to write a simple report containing 1 group. The details section within this group contains a number of fields. My criteria I wish to impose within SQL before the report is run is to filter each group that contains a certain value within one of these fields. <br>
<br>
It looks something like this:<br>
<br>
(Group #1)<br>
xxx xxx xxxxx xxxxxxxxx<br>
xxx xxx xxxxx xxxxxxxxx<br>
xxx aaa xxxxx xxxxxxxxx<br>
<br>
The (aaa)s would be the key, once it is identified, I need the group either suppressed (within CR, but I know it is impossible to surpress an entire group) or filtered out through the SQL query.(my only solution)<br>
<br>
Here is a clip from my SQL query, I've read through every SQL and Crystal Reports book I could get my hands on. SAP_Transports."SAPServer" being the field that the value is contained in.<br>
<br>
SELECT<br>
Help_Desk."Problem #", Help_Desk."Client ID", Help_Desk."Subject ID", ASAPTRAN_."CLIENTNO", SAP_Transports."Transport No", SAP_Transports."Transport Date", SAP_Transports."Pstatus", AP_Transports."SAPServer", SAP_Transports."CurrStatus"<br>
<br>
FROM<br>
"_SMDBA_"."Help Desk" Help_Desk, "_SMDBA_"."_SAPTRAN_" ASAPTRAN_, "_SMDBA_"."SAP Transports" SAP_Transports<br>
<br>
WHERE<br>
Help_Desk."Problem #" = ASAPTRAN_."CASE" AND ASAPTRAN_."TRANSPNO" = SAP_Transports."Transport No" AND ASAPTRAN_."TRANDATE" = SAP_Transports."Transport Date"<br>
<br>
ORDER BY<br>
Help_Desk."Problem #" DESC, ASAPTRAN_."CLIENTNO" ASC, SAP_Transports."Transport No" ASC, Help_Desk."Subject ID" ASC, Help_Desk."Client ID" ASC<br>
<br>
I know I've got to set an Alias and remove it from there, then compare the two(Union), but I can figure it out on my own with the referneces and lack of help my office is providing me with.<br>
<br>
I'd appreciate any help. Thanks.

 

[calahans]

Hi Chris,<br>
<br>
Your SQL is a bit heavy, but one thought occurs. Have you tried to create a view on a table which restricts rows (or columns), and then query on that.<br>
<br>
Any help?

 

[virgo]

I would create a series of consecutive views, progressively narrowing down the columns(rows) that have to be queried. <br>
<br>
I am having a miserable time trying to do everything at once in this enviroment. 'Divide and conquer' seems to work better.