Pasting Crystal Reports query in MS SQL 2008 R2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pasting Crystal Reports query in MS SQL 2008 R2

Author

Topic

kipperz3
Starting Member

2 Posts

Posted - 2012-04-19 : 12:29:08

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 14:17:37

can you post your table structures with sample data and show what you're trying to achieve? that would be much better than posting full query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kipperz3
Starting Member

2 Posts

Posted - 2012-04-20 : 07:55:15

I'm sorry, I was just introduced to Crystal Reports and SQL yesterday after a 15 minute crash course. What are table structures?

I'm trying to export data from a police records management system into a Excel spreadsheet. Each table includes a list of fields that would be included in a police report -- such as case info, offense info and victim/offender info. I'm attempting to link all of those tables by a unique identifier (case number) in order to collect several variables from each case. I also have a few filters such as a date range, type of property stolen and area where crime occurred.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-20 : 14:53:07

see guidelines on how to post a question in below link

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources