Skip to Content
0
Former Member
Jul 27, 2007 at 10:25 PM

"Show SQL Query" changes

22 Views

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"