Topic: Pasting Crystal Reports query in MS SQL 2008 R2 Posted: 19 Apr 2012 at 6:24am
Hello. I know practically nothing about SQL or queries. Yet, I created a query in Crystal Reports version 9.2.0.448 and I'm trying to run it in Microsoft SQL Server 2008 R2 version 10.50.2500.
The issue that I'm seeing is that the data is being duplicated in several cases. Could it have something to do with the fact that when I copy over to MS SQL, some of the commands (INNER JOIN, OR, AND) do not turn blue, but remain gray? Does that mean that the commands are not working?
Here is the query:
SELECT "INP1"."INP1_CATEGORY", "INC1"."INC1_CASE_NO_1", "INC1"."INC1_CASE_NO_2", "INC1"."INC1_OCC_DATE_1", "INC1"."INC1_OCC_DATE_2", "INC1"."INC1_DOW_1", "INC1"."INC1_STATION_A", "INC1"."INC1_STATION_B", "INC1"."INC1_AT_BETWEEN", "INC1"."INC1_LINE_COLOR", "INC1"."INC1_DIRECTION", "INC1"."INC1_LOC_ST_NO", "INC1"."INC1_LOC_ST_NAM", "INC1"."INC1_LOC_X_ST", "INC1"."INC1_SECTOR", "INC1"."INC1_COMMUNITY", "INC1"."INC1_FORCED", "INC1"."INC1_STATUS_C", "INC1"."INC1_STATUS_DATE", "INC1"."INC1_DISPO_C", "INC1"."INC1_DISPO_DATE", "INC1"."INC1_ENT_BY", "INC1"."INC1_CASE_DATE", "INC1"."INC1_CALL_CODE", "INC1"."INC1_CAD_OFCR_1", "INC1"."INC1_CAD_OFCR_2", "INC2"."INC2_PRIMARY", "INC2"."INC2_SUPP_NO", "INC2"."INC2_DOM", "INC2"."INC2_INV_DRG", "INC2"."INC2_INV_LIQ", "INC2"."INC2_INV_GUN", "INC2"."INC2_INV_JUV", "INC2"."INC2_SCH_RELATED", "INC2"."INC2_OFCR_INIT", "INC2"."INC2_ARR", "INC2"."INC2_WAR", "INO1"."INO1_CLASS", "INO1"."INO1_WEAPON", "INP1"."INP1_BRAND", "INP1"."INP1_MODEL", "INP1"."INP1_COLOR_1", "INP1"."INP1_COLOR_2", "INP1"."INP1_VALUE", "INP1"."INP1_STOLE", "INP1"."INP1_QTY", "INP1"."INP1_SERIAL_NO", "INP1"."INP1_DESC", "INP1"."INP1_OA_A_DATE", "INN1"."INN1_X_INV_1", "INN1"."INN1_X_INV_2", "INN1"."INN1_X_INV_3", "INN1"."INN1_X_INV_4", "INN1"."INN1_X_INV_5", "INN1"."INN1_X_INV_6", "INN1"."INN1_X_INV_7", "INN1"."INN1_X_INV_8", "INN1"."INN1_X_INV_9", "INN1"."INN1_X_INV_10", "INN1"."INN1_NAME_L", "INN1"."INN1_NAME_F", "INN1"."INN1_NAME_M", "INN1"."INN1_SEX", "INN1"."INN1_RACE", "INN1"."INN1_DOB_DATE", "INN1"."INN1_Z_AGE_L", "INN1"."INN1_Z_AGE_H", "INN1"."INN1_HEIGHT_L", "INN1"."INN1_HEIGHT_H", "INN1"."INN1_WEIGHT_L", "INN1"."INN1_WEIGHT_H", "INN1"."INN1_HAIR_COL", "INN1"."INN1_EYE_COL", "INN1"."INN1_BUILD", "INN1"."INN1_SKIN", "INN1"."INN1_OCCUPATION", "INN1"."INN1_SCHOOL", "INN1"."INN1_DESTINATN", "INN1"."INN1_INJURIES", "INN1"."INN1_T_EMP", "INN1"."INN1_REMARKS", "INR1"."INR1_ENT_BY", "INR1"."INR1_ENT_DATE", "INR1"."INR1_SUPP_NO", "INR1"."INR1_SEQ_NO", "INR1"."INR1_NARRATIVE" FROM (((("MBTAPolice"."dbo"."INC1" "INC1" INNER JOIN "MBTAPolice"."dbo"."INC2" "INC2" ON ("INC1"."INC1_CASE_NO_1"="INC2"."INC2_CASE_NO_1") AND ("INC1"."INC1_CASE_NO_2"="INC2"."INC2_CASE_NO_2")) INNER JOIN "MBTAPolice"."dbo"."INN1" "INN1" ON ("INC2"."INC2_CASE_NO_1"="INN1"."INN1_CASE_NO_1") AND ("INC2"."INC2_CASE_NO_2"="INN1"."INN1_CASE_NO_2")) INNER JOIN "MBTAPolice"."dbo"."INP1" "INP1" ON ("INN1"."INN1_CASE_NO_1"="INP1"."INP1_CASE_NO_1") AND ("INN1"."INN1_CASE_NO_2"="INP1"."INP1_CASE_NO_2")) INNER JOIN "MBTAPolice"."dbo"."INO1" "INO1" ON ("INP1"."INP1_CASE_NO_1"="INO1"."INO1_CASE_NO_1") AND ("INP1"."INP1_CASE_NO_2"="INO1"."INO1_CASE_NO_2")) INNER JOIN "MBTAPolice"."dbo"."INR1" "INR1" ON ("INO1"."INO1_CASE_NO_1"="INR1"."INR1_CASE_NO_1") AND ("INO1"."INO1_CASE_NO_2"="INR1"."INR1_CASE_NO_2") WHERE ("INP1"."INP1_CATEGORY"=99 OR "INP1"."INP1_CATEGORY"=100 OR "INP1"."INP1_CATEGORY"=5007 OR "INP1"."INP1_CATEGORY"=5013 OR "INP1"."INP1_CATEGORY"=6002 OR "INP1"."INP1_CATEGORY"=6038 OR "INP1"."INP1_CATEGORY"=6040 OR "INP1"."INP1_CATEGORY"=6045 OR "INP1"."INP1_CATEGORY"=6048 OR "INP1"."INP1_CATEGORY"=6049 OR "INP1"."INP1_CATEGORY"=6051 OR "INP1"."INP1_CATEGORY"=8024 OR "INP1"."INP1_CATEGORY"=12017) AND ("INC1"."INC1_LINE_COLOR"='BLUE' OR "INC1"."INC1_LINE_COLOR"='GREEN' OR "INC1"."INC1_LINE_COLOR"='ORANGE' OR "INC1"."INC1_LINE_COLOR"='RED') AND ("INC1"."INC1_OCC_DATE_1">={ts '2003-08-20 00:00:00'} AND "INC1"."INC1_OCC_DATE_1"<{ts '2011-12-31 00:00:01'})
Any help is greatly appreciated, as I'm both clueless and desperate!
INR1.INR1_ENT_BY, INR1.INR1_ENT_DATE, INR1.INR1_SUPP_NO, INR1.INR1_SEQ_NO, INR1.INR1_NARRATIVE FROM INC1 INNER JOIN INC2 ON INC1_CASE_NO_1 = INC2_CASE_NO_1 AND INC1_CASE_NO_2 = INC2_CASE_NO_2 INNER JOIN INN1 ON INC2_CASE_NO_1 = INN1_CASE_NO_1 AND INC2_CASE_NO_2 = INN1_CASE_NO_2 INNER JOIN INP1 ON INN1_CASE_NO_1 = INP1_CASE_NO_1 AND INN1_CASE_NO_2 = INP1_CASE_NO_2 INNER JOIN INO1 ON INP1_CASE_NO_1 = INO1.INO1_CASE_NO_1 AND INP1_CASE_NO_2 = INO1_CASE_NO_2 INNER JOIN INR1 ON INO1_CASE_NO_1 = INR1_CASE_NO_1 AND INO1_CASE_NO_2 = INR1_CASE_NO_2 WHERE INP1.INP1_CATEGORY IN (99,100,5007, 5013, 6002, 6038, 6040, 6045, 6048, 6049, 6051, 8024, 12017) AND INC1.INC1_LINE_COLOR IN ('BLUE','GREEN','ORANGE','RED') AND INC1.INC1_OCC_DATE_1 BETWEEN {ts '2003-08-20 00:00:00'} AND {ts '2011-12-31 00:00:01'}
It ran, but there are still duplicate rows (sometimes as many as 8) in fields such as: INC_CASE_NO_1 INC_CASE_NO_2 INC_OCC_DATE_1 INC_OCC_DATE_2 INC1_DOW_1 INC1_STATION_A
I know that these should be different for each row. Any idea why this could be happening?
I just had a lightbulb moment: I believe the fields that I was using to link my tables before were overlapping. Maybe that was the problem? I now believe I've used the real primary key.
Here is the unedited query from Crystal Reports:
SELECT "INP1"."INP1_CATEGORY", "INC1"."INC1_CASE_NO_1", "INC1"."INC1_CASE_NO_2", "INC1"."INC1_OCC_DATE_1", "INC1"."INC1_OCC_DATE_2", "INC1"."INC1_DOW_1", "INC1"."INC1_STATION_A", "INC1"."INC1_STATION_B", "INC1"."INC1_AT_BETWEEN", "INC1"."INC1_LINE_COLOR", "INC1"."INC1_DIRECTION", "INC1"."INC1_LOC_ST_NO", "INC1"."INC1_LOC_ST_NAM", "INC1"."INC1_LOC_X_ST", "INC1"."INC1_SECTOR", "INC1"."INC1_COMMUNITY", "INC1"."INC1_FORCED", "INC1"."INC1_STATUS_C", "INC1"."INC1_STATUS_DATE", "INC1"."INC1_DISPO_C", "INC1"."INC1_DISPO_DATE", "INC1"."INC1_ENT_BY", "INC1"."INC1_CASE_DATE", "INC1"."INC1_CALL_CODE", "INC1"."INC1_CAD_OFCR_1", "INC1"."INC1_CAD_OFCR_2", "INC2"."INC2_PRIMARY", "INC2"."INC2_SUPP_NO", "INC2"."INC2_DOM", "INC2"."INC2_INV_DRG", "INC2"."INC2_INV_LIQ", "INC2"."INC2_INV_GUN", "INC2"."INC2_INV_JUV", "INC2"."INC2_SCH_RELATED", "INC2"."INC2_OFCR_INIT", "INC2"."INC2_ARR", "INC2"."INC2_WAR", "INO1"."INO1_CLASS", "INO1"."INO1_WEAPON", "INP1"."INP1_BRAND", "INP1"."INP1_MODEL", "INP1"."INP1_COLOR_1", "INP1"."INP1_COLOR_2", "INP1"."INP1_VALUE", "INP1"."INP1_STOLE", "INP1"."INP1_QTY", "INP1"."INP1_SERIAL_NO", "INP1"."INP1_DESC", "INP1"."INP1_OA_A_DATE", "INN1"."INN1_X_INV_1", "INN1"."INN1_X_INV_2", "INN1"."INN1_X_INV_3", "INN1"."INN1_X_INV_4", "INN1"."INN1_X_INV_5", "INN1"."INN1_X_INV_6", "INN1"."INN1_X_INV_7", "INN1"."INN1_X_INV_8", "INN1"."INN1_X_INV_9", "INN1"."INN1_X_INV_10", "INN1"."INN1_NAME_L", "INN1"."INN1_NAME_F", "INN1"."INN1_NAME_M", "INN1"."INN1_SEX", "INN1"."INN1_RACE", "INN1"."INN1_DOB_DATE", "INN1"."INN1_Z_AGE_L", "INN1"."INN1_Z_AGE_H", "INN1"."INN1_HEIGHT_L", "INN1"."INN1_HEIGHT_H", "INN1"."INN1_WEIGHT_L", "INN1"."INN1_WEIGHT_H", "INN1"."INN1_HAIR_COL", "INN1"."INN1_EYE_COL", "INN1"."INN1_BUILD", "INN1"."INN1_SKIN", "INN1"."INN1_OCCUPATION", "INN1"."INN1_SCHOOL", "INN1"."INN1_DESTINATN", "INN1"."INN1_INJURIES", "INN1"."INN1_T_EMP", "INN1"."INN1_REMARKS", "INR1"."INR1_ENT_BY", "INR1"."INR1_ENT_DATE", "INR1"."INR1_SUPP_NO", "INR1"."INR1_SEQ_NO", "INR1"."INR1_NARRATIVE" FROM (((("MBTAPolice"."dbo"."INC1" "INC1" INNER JOIN "MBTAPolice"."dbo"."INC2" "INC2" ON "INC1"."INC1_CNTL_NO"="INC2"."INC2_CNTL_NO") INNER JOIN "MBTAPolice"."dbo"."INN1" "INN1" ON "INC2"."INC2_CNTL_NO"="INN1"."INN1_CNTL_NO") INNER JOIN "MBTAPolice"."dbo"."INP1" "INP1" ON "INN1"."INN1_CNTL_NO"="INP1"."INP1_CNTL_NO") INNER JOIN "MBTAPolice"."dbo"."INO1" "INO1" ON "INP1"."INP1_CNTL_NO"="INO1"."INO1_CNTL_NO") INNER JOIN "MBTAPolice"."dbo"."INR1" "INR1" ON "INO1"."INO1_CNTL_NO"="INR1"."INR1_CNTL_NO" WHERE ("INP1"."INP1_CATEGORY"=99 OR "INP1"."INP1_CATEGORY"=100 OR "INP1"."INP1_CATEGORY"=5007 OR "INP1"."INP1_CATEGORY"=5013 OR "INP1"."INP1_CATEGORY"=6002 OR "INP1"."INP1_CATEGORY"=6038 OR "INP1"."INP1_CATEGORY"=6040 OR "INP1"."INP1_CATEGORY"=6045 OR "INP1"."INP1_CATEGORY"=6048 OR "INP1"."INP1_CATEGORY"=6049 OR "INP1"."INP1_CATEGORY"=6051 OR "INP1"."INP1_CATEGORY"=8024 OR "INP1"."INP1_CATEGORY"=12017) AND ("INC1"."INC1_LINE_COLOR"='BLUE' OR "INC1"."INC1_LINE_COLOR"='GREEN' OR "INC1"."INC1_LINE_COLOR"='ORANGE' OR "INC1"."INC1_LINE_COLOR"='RED') AND ("INC1"."INC1_OCC_DATE_1">={ts '2003-08-20 00:00:00'} AND "INC1"."INC1_OCC_DATE_1"<{ts '2011-12-31 00:00:01'})
And again, the Select Expert formula:
{INP1.INP1_CATEGORY} in [100, 12017, 5007, 5013, 6002, 6038, 6040, 6045, 6048, 6049, 6051, 8024, 99] and {INC1.INC1_OCC_DATE_1} in DateTime (2003, 08, 20, 0, 0, 0) to DateTime (2011, 12, 31, 0, 0, 0) and {INC1.INC1_LINE_COLOR} in ["BLUE", "GREEN", "ORANGE", "RED"]
Maybe if this can be editied like you did before, everything will check out. Thanks for your help!
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Posted: 19 Apr 2012 at 11:33am
basically, all I did was to remove the " as they make it confusing and I removed most of the aliasing...since it was the same as the table name (seems like a poor code) and I removed the absolute declaration of the table name (I removed MBTAPolice.dbo.)
Last I changed all the OR statements in the WHERE clause to be in an 'IN' statement just because it is easier to read.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
