cancel
Showing results for 
Search instead for 
Did you mean: 

"Show SQL Query" changes

Former Member
0 Kudos

Post Author: DavidinMT

CA Forum: Data Connectivity and SQL

I have a .RPT file created on one PC that runs in about 15 seconds. On the Crystal server and on two other PCs, it never finishes. Same version and patch level, same DB2 ODBC driver version.

I noticed a stange behavior. On the original PC the "Show SQL Query" is a large set of joins. As soon as it's brought up on the other PCs, it shows as several discrete sql commands. I suspect it's this behavior that is causing the report to behave differently.

Can anyone tell me why the SQL Command in the "Show SQL Query" changes when opened on different PCs?

    • Non-working ***************************************************************************SELECT "AP03_MASTER_APL"."BF_SSN", "AP03_MASTER_APL"."AN_SEQ", "AP03_MASTER_APL"."AC_STA_ORG_PRC", "AP03_MASTER_APL"."IF_GTR", "AP03_MASTER_APL"."AF_DOE_SCL", "AP03_MASTER_APL"."AF_DOE_LDR", "AP03_MASTER_APL"."IC_LON_PGM", "LK10_LS_CDE_LKP"."PX_DSC_LNG", "LK10_LS_CDE_LKP"."PM_ATR", "AP03_MASTER_APL"."AD_LON_1_DSB", "AP03_MASTER_APL"."AF_APL_ID"FROM "SFWHRM1"."AP03_MASTER_APL" "AP03_MASTER_APL" INNER JOIN "SFWHRM1"."LK10_LS_CDE_LKP" "LK10_LS_CDE_LKP" ON "AP03_MASTER_APL"."AC_STA_ORG_PRC"="LK10_LS_CDE_LKP"."PX_ATR_VAL"WHERE "LK10_LS_CDE_LKP"."PM_ATR"='AC-STA-ORG-PRC'ORDER BY "AP03_MASTER_APL"."AF_DOE_SCL", "AP03_MASTER_APL"."BF_SSN", "AP03_MASTER_APL"."AN_SEQ"SELECT "GU10_GTR"."IM_GTR_SHO", "GU10_GTR"."IF_GTR"FROM "SFWHRM1"."GU10_GTR" "GU10_GTR"SELECT "SC10_SCH_DMO"."IM_SCL_FUL", "SC10_SCH_DMO"."IF_DOE_SCL"FROM "SFWHRM1"."SC10_SCH_DMO" "SC10_SCH_DMO"SELECT "SC25_SCH_DPT"."IC_SCL_DOM_ST", "SC25_SCH_DPT"."IC_SCL_DPT", "SC25_SCH_DPT"."IF_DOE_SCL"FROM "SFWHRM1"."SC25_SCH_DPT" "SC25_SCH_DPT"WHERE "SC25_SCH_DPT"."IC_SCL_DPT"='000' AND "SC25_SCH_DPT"."IC_SCL_DOM_ST"='OR'SELECT "LN15_DSB"."LC_DSB_TYP", "LN15_DSB"."LN_LON_DSB_SEQ", "LN15_DSB"."LA_DSB", "LN15_DSB"."LD_DSB", "LN15_DSB"."LA_DSB_CAN", "LN15_DSB"."AN_SEQ", "LN15_DSB"."BF_SSN"FROM "SFWHRM1"."LN15_DSB" "LN15_DSB"SELECT "PD10_PRS_NME"."DM_PRS_1", "PD10_PRS_NME"."DM_PRS_LST", "PD10_PRS_NME"."DF_PRS_ID"FROM "SFWHRM1"."PD10_PRS_NME" "PD10_PRS_NME"SELECT "AP17_LO_APL_ERR"."PN_LO_APL_STP_ID", "AP17_LO_APL_ERR"."AC_APL_ERR_TYP", "AP17_LO_APL_ERR"."AI_LO_APL_STP_OVR", "AP17_LO_APL_ERR"."AF_APL_ID"FROM "SFWHRM1"."AP17_LO_APL_ERR" "AP17_LO_APL_ERR"* Working ******************************************************************************SELECT "AP03_MASTER_APL"."BF_SSN", "AP03_MASTER_APL"."AN_SEQ", "AP03_MASTER_APL"."AC_STA_ORG_PRC", "AP03_MASTER_APL"."IF_GTR", "AP03_MASTER_APL"."AF_DOE_SCL", "AP03_MASTER_APL"."AF_DOE_LDR", "AP03_MASTER_APL"."IC_LON_PGM", "GU10_GTR"."IM_GTR_SHO", "SC25_SCH_DPT"."IC_SCL_DOM_ST", "LN15_DSB"."LC_DSB_TYP", "LN15_DSB"."LN_LON_DSB_SEQ", "LN15_DSB"."LA_DSB", "LN15_DSB"."LD_DSB", "LN15_DSB"."LA_DSB_CAN", "SC25_SCH_DPT"."IC_SCL_DPT", "PD10_PRS_NME"."DM_PRS_1", "PD10_PRS_NME"."DM_PRS_LST", "LK10_LS_CDE_LKP"."PX_DSC_LNG", "LK10_LS_CDE_LKP"."PM_ATR", "SC10_SCH_DMO"."IM_SCL_FUL", "AP03_MASTER_APL"."AD_LON_1_DSB", "AP17_LO_APL_ERR"."PN_LO_APL_STP_ID", "AP17_LO_APL_ERR"."AC_APL_ERR_TYP", "AP17_LO_APL_ERR"."AI_LO_APL_STP_OVR"FROM (((((("SFWHRM1"."AP03_MASTER_APL" "AP03_MASTER_APL" INNER JOIN "SFWHRM1"."LK10_LS_CDE_LKP" "LK10_LS_CDE_LKP" ON "AP03_MASTER_APL"."AC_STA_ORG_PRC"="LK10_LS_CDE_LKP"."PX_ATR_VAL") LEFT OUTER JOIN "SFWHRM1"."GU10_GTR" "GU10_GTR" ON "AP03_MASTER_APL"."IF_GTR"="GU10_GTR"."IF_GTR") LEFT OUTER JOIN "SFWHRM1"."SC10_SCH_DMO" "SC10_SCH_DMO" ON "AP03_MASTER_APL"."AF_DOE_SCL"="SC10_SCH_DMO"."IF_DOE_SCL") LEFT OUTER JOIN "SFWHRM1"."LN15_DSB" "LN15_DSB" ON ("AP03_MASTER_APL"."BF_SSN"="LN15_DSB"."BF_SSN") AND ("AP03_MASTER_APL"."AN_SEQ"="LN15_DSB"."AN_SEQ")) LEFT OUTER JOIN "SFWHRM1"."PD10_PRS_NME" "PD10_PRS_NME" ON "AP03_MASTER_APL"."BF_SSN"="PD10_PRS_NME"."DF_PRS_ID") LEFT OUTER JOIN "SFWHRM1"."AP17_LO_APL_ERR" "AP17_LO_APL_ERR" ON "AP03_MASTER_APL"."AF_APL_ID"="AP17_LO_APL_ERR"."AF_APL_ID") LEFT OUTER JOIN "SFWHRM1"."SC25_SCH_DPT" "SC25_SCH_DPT" ON "SC10_SCH_DMO"."IF_DOE_SCL"="SC25_SCH_DPT"."IF_DOE_SCL"WHERE "LK10_LS_CDE_LKP"."PM_ATR"='AC-STA-ORG-PRC' AND "SC25_SCH_DPT"."IC_SCL_DPT"='000' AND "SC25_SCH_DPT"."IC_SCL_DOM_ST"='OR' AND (("AP03_MASTER_APL"."AC_STA_ORG_PRC"='0' OR "AP03_MASTER_APL"."AC_STA_ORG_PRC"='11' OR "AP03_MASTER_APL"."AC_STA_ORG_PRC"='51' OR "AP03_MASTER_APL"."AC_STA_ORG_PRC"='52') OR "AP17_LO_APL_ERR"."AC_APL_ERR_TYP" LIKE 'CAN%' AND "AP17_LO_APL_ERR"."PN_LO_APL_STP_ID"=69 AND ("AP17_LO_APL_ERR"."AI_LO_APL_STP_OVR" IS NULL OR "AP17_LO_APL_ERR"."AI_LO_APL_STP_OVR"<'a'))ORDER BY "AP03_MASTER_APL"."AF_DOE_SCL", "AP03_MASTER_APL"."BF_SSN", "AP03_MASTER_APL"."AN_SEQ"

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Post Author: DavidinMT

CA Forum: Data Connectivity and SQL

Now if I can only identify a similar fix for the server...

Former Member
0 Kudos

Post Author: DavidinMT

CA Forum: Data Connectivity and SQL

It seems Service Pack 3 may have introduced a bug. I restored from Crystal Reports Developer Version 11.0.0.2269 back to 11.0.0.1994 and the problem went away.

Former Member
0 Kudos

Post Author: DavidinMT

CA Forum: Data Connectivity and SQL

No command lines are used. It's all table joins. Anything you see in the query syntax is being put there by Crystal.

I have two machines that read the .rpt file correctly (one is the author of the report), and two that read it with the multiple selects. And from the performance, I suspect the Crystal Server is behaving like the second two.

Former Member
0 Kudos

Post Author: yangster

CA Forum: Data Connectivity and SQL

are you doing this via tables and joins are using a command object?why are you using all those parenthesis in your from statement?i don't see any alias being created within any of your joins