I created a union statement in the

[shaleen7]

I created a union statement in the command section of Crystal 9. The results of my SQL command did in fact displayed the correct number. So I then placed the data in a crosstab and got the following data returned:


1st 2nd 3rd 4th Total
Cats 1000 10 20 10 10 1050
Dogs 2000 5 6 8 9 2028


I believe values 1000 and 2000 are results of one the SQL statements from the union query. Is there any way for me to get rid of these values?

 

[synapsevampire]

Post the SQL used and how you laid out the crosstab.

-k

 

[shaleen7]

I simply created a union between the two SQL statements.


SELECT "VRPT_TABANIMALNUMBER"."CODANIMALNUMBER", "VTABBRE"."DATRECEIVED", "VRPT_TABSTATE"."CODSTATE", "VRPT_TABANIMALS"."CODANIMALS", "VTABBRE"."DATDELIVERED","VRPT_TABSTATUS"."CODSTATUS","VRPT_TABTYPE"."CODTYPE"
FROM "TUSBREV32"."VRPT_TABANIMALS" "VRPT_TABANIMALS", "TUSBREV32"."VRPT_TABSTATE" "VRPT_TABSTATE", "TUSBREV32"."VRPT_TABTYPE" "VRPT_TABTYPE", "TUSBREV32"."VRPT_TABANIMALNUMBER" "VRPT_TABANIMALNUMBER", "TUSBREV32"."VTABBRE" "VTABBRE", "TUSBREV32"."VRPT_TABANIMALNUMBER" "VRPT_TABANIMALNUMBER_1","TUSBREV32"."VRPT_TABSTATUS" "VRPT_TABSTATUS"

WHERE ("VRPT_TABANIMALNUMBER"."IDEANIMALNUMBER"="VTABBRE"."IDEANIMALNUMBER&quot AND ("VTABBRE"."IDEANIMALNUMBER"="VRPT_TABANIMALNUMBER_1"."IDEANIMALNUMBER" (+)) AND ("VRPT_TABTYPE"."IDETYPE" (+)="VRPT_TABANIMALNUMBER_1"."IDETYPE&quot AND ("VRPT_TABSTATE"."IDESTATE"="VTABBRE"."IDESTATE&quot AND ("VRPT_TABANIMALS"."IDEANIMALS"="VTABBRE"."IDEANIMALS&quot AND ("VRPT_TABSTATUS"."IDESTATUS" (+)="VTABBRE"."IDESTATUS&quot
AND NOT ("VRPT_TABANIMALS"."CODANIMALS"='DP' OR "VRPT_TABANIMALS"."CODANIMALS"='CAT' OR "VRPT_TABANIMALS"."CODANIMALS"='DOG' OR "VRPT_TABANIMALS"."CODANIMALS"='RD') AND NOT ("VRPT_TABSTATE"."CODSTATE"='NJ' OR "VRPT_TABSTATE"."CODSTATE"='NY') AND "VTABBRE"."DATRECEIVED" IS NOT NULL AND "VTABBRE"."DATDELIVERED" IS NULL

union all

SELECT "VRPT_TABANIMALNUMBER"."CODANIMALNUMBER", "VTABBRE"."DATRECEIVED", "VRPT_TABSTATE"."CODSTATE", "VRPT_TABANIMALS"."CODANIMALS", "VTABBRE"."DATDELIVERED", "VRPT_TABSTATUS"."CODSTATUS","VRPT_TABTYPE".CODTYPE
FROM "TUSBREV32"."VRPT_TABSTATUS" "VRPT_TABSTATUS", "TUSBREV32"."VRPT_TABANIMALS" "VRPT_TABANIMALS", "TUSBREV32"."VRPT_TABSTATE" "VRPT_TABSTATE", "TUSBREV32"."VRPT_TABTYPE" "VRPT_TABTYPE", "TUSBREV32"."VRPT_TABANIMALNUMBER" "VRPT_TABANIMALNUMBER", "TUSBREV32"."VTABBRE" "VTABBRE", "TUSBREV32"."VRPT_TABANIMALNUMBER" "VRPT_TABANIMALNUMBER_1"


WHERE ("VRPT_TABANIMALNUMBER"."IDEANIMALNUMBER"="VTABBRE"."IDEANIMALNUMBER&quot AND ("VTABBRE"."IDEANIMALNUMBER"="VRPT_TABANIMALNUMBER_1"."IDEANIMALNUMBER" (+)) AND ("VRPT_TABTYPE"."IDETYPE" (+)="VRPT_TABANIMALNUMBER_1"."IDETYPE&quot AND ("VRPT_TABSTATE"."IDESTATE"="VTABBRE"."IDESTATE&quot AND ("VRPT_TABANIMALS"."IDEANIMALS"="VTABBRE"."IDEANIMALS&quot AND ("VRPT_TABSTATUS"."IDESTATUS" (+)="VTABBRE"."IDESTATUS&quot AND "VTABBRE"."DATDELIVERED" IS NULL AND NOT ("VRPT_TABANIMALS"."CODANIMALS"='CAT' OR "VRPT_TABANIMALS"."CODANIMALS"='DOG' OR "VRPT_TABANIMALS"."CODANIMALS"='BIRD' OR "VRPT_TABANIMALS"."CODANIMALS"='SNAKE') AND "VTABBRE"."DATRECEIVED" IS NULL AND NOT ("VRPT_TABSTATE"."CODSTATE"='NJ' OR "VRPT_TABSTATE"."CODSTATE"='NY') AND ("VRPT_TABSTATUS"."CODSTATUS"='ILL' OR "VRPT_TABSTATUS"."CODSTATUS"='DEAD')

These are the actually numbers. They are correct but I don't understand why the first row of data is being displayed.

1st 2nd 3rd 4th Total
CAT 4,189 1,016 1,476 1,401 1,813 9,895
DOG 837 253 428 397 408 2,323
BIRD 3,136 846 1,231 880 1,000 7,093
SNAKE 981 477 573 436 559 3,026
TOTAL 9,143 2,592 3,708 3,114 3,780 22,337

Sorry the post is so long

 

[synapsevampire]

Long posts are preferable to those without key information.

This doesn't show me what fields are in the crosstab. What are the row, column and summary fields?

-k

 

[IdoMillet]

The first column is simply those cases where the value in the column field is Null. To get rid of such cases, add a WHERE condition in the SQL (or change from an OUTER join to a regular join if the Outer Join is the cause for the NUll values).

Alternatively add a (NOT IsNull({your_Field})) as a record selection formula in the Crystal report itself.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com