^ throwing error in SQL formula in EZWriter

Answered

Sara Noonan

March 08, 2024 03:55

Hi - NIHF has a pretty complicated SQL statement in one of their EZWriter reports. It looks like the upgrade to 23.2 broke it. The error message mentions a formatting error around AS, and after investigating in SQL, it looks like it's the last AS. 

Should they use ^ to denote what they're denoting or some other character? Please see the very bottom of the query. I can provide the error log and additional information if needed.

Related to

• ezwriter

• 

  Sara Noonan

  March 13, 2024 23:30

  Hey team - to make this even weirder, the same formatting is in Test, and it's working. Is it possible there's some setting in the database that's different between Test and Prod (probably a cloud question but while I've got you…). Basically, does their formatting look ok in the SQL I have posted? If not, then I can bug another team. 
• 

  Seth Halvaksz

  March 13, 2024 23:59

  Richard Marmura 
• 

  Richard Marmura

  March 20, 2024 19:41

  Sara Noonan The error log would be helpful. 

  Talking with Dustin Banks the ^ParameterName^ is what we use as the marker for a custom parameter defined by the user. So Dustin doesn't think the error is tied to this. 
• 

  Sara Noonan

  March 26, 2024 22:39

  Hi - sorry for the delay getting back to you! The log entry for this is super long - please see below. 

  3/26/2024 5:36:26 PM
  EZWriter Data Source -340 
  Threw error     Index # 0    Class - 16    LineNumber - 1    Message - Conversion failed when converting the varchar value 'Standard' to data type int.    Number  - 245    Procedure  -     Server  - Pallas.USI-VAVPC.com,1683    Source  - .Net SqlClient Data Provider    State   - 1    SQL - EXECUTE As USER = 'EZWriterUser'; SELECT (SELECT SUM(ER101_RES_QTY) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER  JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_ORD_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3000','3001','3003','3080','3070','3071','3060','3300','3301','3302','3303','3305','3081','3900','3021','3902', '3085')) AS REG_COUNT, (SELECT CASE WHEN RIGHT(EV200_EVT_SEARCH,2)<> '21' THEN NULL ELSE CAST(SUM(CAST(CASE WHEN ER110_TXT_03 IN ('M','F','NA') THEN '0' ELSE '1' END AS INT)) AS INT) END FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND EV200_EVT_ID = ER110_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3000','3001','3003','3080','3070','3071','3060','3300','3301','3302','3303','3305','3081','3900','3021','3902', '3085')) AS PENDING, (SELECT CASE WHEN LEFT(EV200_EVT_SEARCH,1) IN ('C') THEN (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3901')) ELSE (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_ORD_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3901')) END) AS LIT_COUNT, (SELECT CASE WHEN LEFT(EV200_EVT_SEARCH,1) IN ('C') THEN (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_RES_CODE = 'EXTEND-CAMP' AND ER101_NEW_RES_TYPE IN ('3004')) ELSE (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_RES_CODE = 'EXTEND-CAMP' AND ER100_ORD_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3004')) END) AS EXT_DAY_COUNT, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH(NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND RIGHT(ER101_DESC,3)='KIT' AND ER101_RES_CODE <> ('NO-EXT-CAMP') AND ER100_NEW_STS IN ('A','C') AND ER100_ORD_TYPE = 'ER' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3406')) AS KIT_COUNT, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3900') AND ER101_RES_CODE ='IPP') AS IN_PERSON, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3900') AND ER101_RES_CODE = 'AHP') AS AT_HOME, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER110_TXT_29 = 'NEW' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_NEW_RES_TYPE IN ('3900') AND ER101_PHASE = '1') AS NEW_COUNT, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER110_TXT_29 = 'ALUMNI' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_NEW_RES_TYPE IN ('3900') AND ER101_PHASE = '1') AS ALUMNI_COUNT, (SELECT CASE WHEN LEFT(EV200_EVT_SEARCH,1) IN ('C') THEN (SELECT CAST(SUM(ER101_FC_EXT_CHRG) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_RES_Code NOT in ('ADJ-BUF-CIT','LIT-ADJ-MISC') AND ER101_DESC <> 'DONATION' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER100_ORD_STS IN ('A','C') AND LEFT(ER101_DESC, 3) NOT IN ('LIT', 'Ext', 'Lea', 'CIT') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3000','3001','3003', '3004', '3080', '3085','3070','3071','3060','3300','3301','3302','3303','3305','3081','3010','3011','3012','3013','3050','3900','3021','3902', '3400','3081', '3020', '3052', '3082','3050')) ELSE (SELECT CAST(SUM(ER101_FC_EXT_CHRG) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC NOT IN ('DONATION', 'Extended Camp', 'Building Use Fee Adjustment - LIT') AND ER100_ORD_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3000','3001','3003', '3004', '3080', '3085','3070','3071','3060','3300','3301','3302','3303','3305','3081','3010','3011','3012','3013','3050','3900','3021','3902', '3400','3081', '3020', '3052', '3082','3050')) END) AS REG_REVENUE, (SELECT SUM(ER101_EXT_CHRG) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH(NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER101_DESC IN ('Extended Camp', 'XDI2', 'XDR1', 'XD2') AND ER100_NEW_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3004', '3011')) AS EXT_DAY_REVENUE, (SELECT SUM(ER101_FC_EXT_CHRG) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH(NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER100_NEW_STS IN ('A','C') AND (ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3090','3091','3051','3901') OR ER101_DESC = 'LIT - Miscellaneous Adjustment' OR ER101_DESC = 'Building Use Fee Adjustment - LIT' OR ER101_DESC = 'LIT Cancellation' OR ER101_DESC = 'LIT Cancellation Insurance' OR ER101_DESC = 'DIL2' OR ER101_DESC = 'PTML1' )) AS LIT_REVENUE, (SELECT SUM(ER101_FC_EXT_CHRG) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH(NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C','X','P') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3085','3080')) AS PROF_DEV_REVENUE, CASE WHEN RIGHT(CR073_TXT_27,2) IN ('19', '20', '21', '22') THEN ac4.EV870_NAME ELSE (SELECT TOP 1 EV870_NAME FROM EV625_EVENT_SERVICES WITH(NOLOCK) INNER JOIN EV870_ACCT_MASTER WITH(NOLOCK) ON EV625_ORG_CODE = EV870_ORG_CODE AND EV625_NG_CNTCT_CODE = EV870_ACCT_CODE WHERE EV200_ORG_CODE = EV625_ORG_CODE AND EV200_EVT_ID = EV625_EVT_ID AND EV625_DEPT = 'DISTRI') END AS SCHOOL_DISTRICT, EV200_EVT_START_DATE, EV200_EVT_END_DATE , CONVERT(VARCHAR, CAST(EV200_EVT_END_TIME AS TIME), 100) AS EV200_EVT_END_TIME, CONVERT(VARCHAR, CAST(EV200_EVT_START_TIME AS TIME), 100) AS EV200_EVT_START_TIME, EV200_EVT_DESC, EV200_EVT_STATUS, EV200_MAJ_GROUP, EV200_EVT_ID, EV200_EVT_SEARCH AS EVT_SEARCH, AC1.EV870_NAME, AC1.EV870_ADDRESS_L1, AC1.EV870_CITY, AC1.EV870_STATE, AC1.EV870_POSTAL_CODE, AC1.EV870_IACVB_CODE AS COUNTY, CR073_TXT_27 AS PROGRAM_CODE, CR073_TXT_01 AS START_GRADE, CR073_TXT_02 AS END_GRADE, CR073_AMT_01 AS PROCESSED, CR073_TXT_23 AS SITE_STATUS, CR073_TXT_48 AS YEARS_PRIOR, CR073_TXT_08 AS TOTAL_PRICE, CR073_TXT_04 AS BUF_AMOUNT, CASE WHEN CR073_TXT_05 = 0 THEN ' ' END AS LOCATION_PREMIUM, CR073_TXT_06 AS STAFF_PREMIUM, CR073_TXT_07 AS CUSTODIAN_FEE, CR073_AMT_02 AS ESTIMATED_FEES, CR073_TXT_35 AS FEE_TYPE, (SELECT TOP 1 V1.MM721_FLD_DESC FROM MM721_UDF_TABLE_DTL AS V1 WITH(NOLOCK) WHERE CR073_ORG_CODE = V1.MM721_ORG_CODE AND CR073_TXT_26 = V1.MM721_FLD_CODE AND V1.MM721_TABLE_ID = 12) AS PROGRAM, (SELECT SUM(CAST(ER103_MAX_QTY AS INT)) FROM ER103_RES_MINMAX WHERE ER103_NEW_RES_TYPE IN ('3900') AND ER103_EVT_ID =EV200_EVT_ID AND EV200_EVT_STATUS IN ('30', '60', '29', '31', '75','34','36')) AS CAPACITY, AC3.EV870_LAST_NAME AS RC_LAST_NAME, SM.EV130_STATUS_DESC AS EVENT_STATUS FROM EV200_EVENT_MASTER WITH(NOLOCK) INNER JOIN EV870_ACCT_MASTER AS AC1 WITH(NOLOCK) ON EV200_ORG_CODE = AC1.EV870_ORG_CODE AND EV200_CUST_NBR = AC1.EV870_ACCT_CODE INNER JOIN EV870_ACCT_MASTER AS AC3 WITH(NOLOCK) ON EV200_ORG_CODE = AC3.EV870_ORG_CODE AND EV200_SLSPER = AC3.EV870_ACCT_CODE INNER JOIN CR073_ISSUES WITH(NOLOCK) ON EV200_ORG_CODE = CR073_ORG_CODE AND EV200_EVT_ID = CR073_EVT_ID INNER JOIN EV130_STATUS_MASTER AS SM WITH (NOLOCK) ON EV200_EVT_STATUS = SM.EV130_STATUS_CODE INNER JOIN EV870_ACCT_MASTER AS AC4 WITH (NOLOCK) ON EV200_ORG_CODE = AC4.EV870_ORG_CODE AND EV200_REQ_ACCT_CODE = AC4.EV870_ACCT_CODE INNER JOIN ER103_RES_MINMAX WITH(NOLOCK) ON EV200_ORG_CODE = ER103_ORG_CODE AND EV200_EVT_ID = ER103_EVT_ID AND ER103_ITEM_DESC IN ('EVENT', NULL) WHERE EV200_EVT_ID > 22000 AND EV200_EVT_STATUS IN ('30','29','75','34','36') AND CR073_EXT_ACCT_CODE = '@'+'EVENT' AND RIGHT(EV200_EVT_SEARCH,2) =CAST('24' AS VARCHAR) ORDER BY EV200_EVT_SEARCH    
  -> Conversion failed when converting the varchar value 'Standard' to data type int. 
  SELECT (SELECT SUM(ER101_RES_QTY) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER  JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_ORD_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3000','3001','3003','3080','3070','3071','3060','3300','3301','3302','3303','3305','3081','3900','3021','3902', '3085')) AS REG_COUNT, (SELECT CASE WHEN RIGHT(EV200_EVT_SEARCH,2)<> '21' THEN NULL ELSE CAST(SUM(CAST(CASE WHEN ER110_TXT_03 IN ('M','F','NA') THEN '0' ELSE '1' END AS INT)) AS INT) END FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND EV200_EVT_ID = ER110_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3000','3001','3003','3080','3070','3071','3060','3300','3301','3302','3303','3305','3081','3900','3021','3902', '3085')) AS PENDING, (SELECT CASE WHEN LEFT(EV200_EVT_SEARCH,1) IN ('C') THEN (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3901')) ELSE (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_ORD_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3901')) END) AS LIT_COUNT, (SELECT CASE WHEN LEFT(EV200_EVT_SEARCH,1) IN ('C') THEN (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_RES_CODE = 'EXTEND-CAMP' AND ER101_NEW_RES_TYPE IN ('3004')) ELSE (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_RES_CODE = 'EXTEND-CAMP' AND ER100_ORD_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3004')) END) AS EXT_DAY_COUNT, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH(NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND RIGHT(ER101_DESC,3)='KIT' AND ER101_RES_CODE <> ('NO-EXT-CAMP') AND ER100_NEW_STS IN ('A','C') AND ER100_ORD_TYPE = 'ER' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3406')) AS KIT_COUNT, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3900') AND ER101_RES_CODE ='IPP') AS IN_PERSON, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3900') AND ER101_RES_CODE = 'AHP') AS AT_HOME, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER110_TXT_29 = 'NEW' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_NEW_RES_TYPE IN ('3900') AND ER101_PHASE = '1') AS NEW_COUNT, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER110_TXT_29 = 'ALUMNI' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_NEW_RES_TYPE IN ('3900') AND ER101_PHASE = '1') AS ALUMNI_COUNT, (SELECT CASE WHEN LEFT(EV200_EVT_SEARCH,1) IN ('C') THEN (SELECT CAST(SUM(ER101_FC_EXT_CHRG) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_RES_Code NOT in ('ADJ-BUF-CIT','LIT-ADJ-MISC') AND ER101_DESC <> 'DONATION' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER100_ORD_STS IN ('A','C') AND LEFT(ER101_DESC, 3) NOT IN ('LIT', 'Ext', 'Lea', 'CIT') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3000','3001','3003', '3004', '3080', '3085','3070','3071','3060','3300','3301','3302','3303','3305','3081','3010','3011','3012','3013','3050','3900','3021','3902', '3400','3081', '3020', '3052', '3082','3050')) ELSE (SELECT CAST(SUM(ER101_FC_EXT_CHRG) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC NOT IN ('DONATION', 'Extended Camp', 'Building Use Fee Adjustment - LIT') AND ER100_ORD_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3000','3001','3003', '3004', '3080', '3085','3070','3071','3060','3300','3301','3302','3303','3305','3081','3010','3011','3012','3013','3050','3900','3021','3902', '3400','3081', '3020', '3052', '3082','3050')) END) AS REG_REVENUE, (SELECT SUM(ER101_EXT_CHRG) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH(NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER101_DESC IN ('Extended Camp', 'XDI2', 'XDR1', 'XD2') AND ER100_NEW_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3004', '3011')) AS EXT_DAY_REVENUE, (SELECT SUM(ER101_FC_EXT_CHRG) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH(NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER100_NEW_STS IN ('A','C') AND (ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3090','3091','3051','3901') OR ER101_DESC = 'LIT - Miscellaneous Adjustment' OR ER101_DESC = 'Building Use Fee Adjustment - LIT' OR ER101_DESC = 'LIT Cancellation' OR ER101_DESC = 'LIT Cancellation Insurance' OR ER101_DESC = 'DIL2' OR ER101_DESC = 'PTML1' )) AS LIT_REVENUE, (SELECT SUM(ER101_FC_EXT_CHRG) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH(NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C','X','P') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3085','3080')) AS PROF_DEV_REVENUE, CASE WHEN RIGHT(CR073_TXT_27,2) IN ('19', '20', '21', '22') THEN ac4.EV870_NAME ELSE (SELECT TOP 1 EV870_NAME FROM EV625_EVENT_SERVICES WITH(NOLOCK) INNER JOIN EV870_ACCT_MASTER WITH(NOLOCK) ON EV625_ORG_CODE = EV870_ORG_CODE AND EV625_NG_CNTCT_CODE = EV870_ACCT_CODE WHERE EV200_ORG_CODE = EV625_ORG_CODE AND EV200_EVT_ID = EV625_EVT_ID AND EV625_DEPT = 'DISTRI') END AS SCHOOL_DISTRICT, EV200_EVT_START_DATE, EV200_EVT_END_DATE , CONVERT(VARCHAR, CAST(EV200_EVT_END_TIME AS TIME), 100) AS EV200_EVT_END_TIME, CONVERT(VARCHAR, CAST(EV200_EVT_START_TIME AS TIME), 100) AS EV200_EVT_START_TIME, EV200_EVT_DESC, EV200_EVT_STATUS, EV200_MAJ_GROUP, EV200_EVT_ID, EV200_EVT_SEARCH AS EVT_SEARCH, AC1.EV870_NAME, AC1.EV870_ADDRESS_L1, AC1.EV870_CITY, AC1.EV870_STATE, AC1.EV870_POSTAL_CODE, AC1.EV870_IACVB_CODE AS COUNTY, CR073_TXT_27 AS PROGRAM_CODE, CR073_TXT_01 AS START_GRADE, CR073_TXT_02 AS END_GRADE, CR073_AMT_01 AS PROCESSED, CR073_TXT_23 AS SITE_STATUS, CR073_TXT_48 AS YEARS_PRIOR, CR073_TXT_08 AS TOTAL_PRICE, CR073_TXT_04 AS BUF_AMOUNT, CASE WHEN CR073_TXT_05 = 0 THEN ' ' END AS LOCATION_PREMIUM, CR073_TXT_06 AS STAFF_PREMIUM, CR073_TXT_07 AS CUSTODIAN_FEE, CR073_AMT_02 AS ESTIMATED_FEES, CR073_TXT_35 AS FEE_TYPE, (SELECT TOP 1 V1.MM721_FLD_DESC FROM MM721_UDF_TABLE_DTL AS V1 WITH(NOLOCK) WHERE CR073_ORG_CODE = V1.MM721_ORG_CODE AND CR073_TXT_26 = V1.MM721_FLD_CODE AND V1.MM721_TABLE_ID = 12) AS PROGRAM, (SELECT SUM(CAST(ER103_MAX_QTY AS INT)) FROM ER103_RES_MINMAX WHERE ER103_NEW_RES_TYPE IN ('3900') AND ER103_EVT_ID =EV200_EVT_ID AND EV200_EVT_STATUS IN ('30', '60', '29', '31', '75','34','36')) AS CAPACITY, AC3.EV870_LAST_NAME AS RC_LAST_NAME, SM.EV130_STATUS_DESC AS EVENT_STATUS FROM EV200_EVENT_MASTER WITH(NOLOCK) INNER JOIN EV870_ACCT_MASTER AS AC1 WITH(NOLOCK) ON EV200_ORG_CODE = AC1.EV870_ORG_CODE AND EV200_CUST_NBR = AC1.EV870_ACCT_CODE INNER JOIN EV870_ACCT_MASTER AS AC3 WITH(NOLOCK) ON EV200_ORG_CODE = AC3.EV870_ORG_CODE AND EV200_SLSPER = AC3.EV870_ACCT_CODE INNER JOIN CR073_ISSUES WITH(NOLOCK) ON EV200_ORG_CODE = CR073_ORG_CODE AND EV200_EVT_ID = CR073_EVT_ID INNER JOIN EV130_STATUS_MASTER AS SM WITH (NOLOCK) ON EV200_EVT_STATUS = SM.EV130_STATUS_CODE INNER JOIN EV870_ACCT_MASTER AS AC4 WITH (NOLOCK) ON EV200_ORG_CODE = AC4.EV870_ORG_CODE AND EV200_REQ_ACCT_CODE = AC4.EV870_ACCT_CODE INNER JOIN ER103_RES_MINMAX WITH(NOLOCK) ON EV200_ORG_CODE = ER103_ORG_CODE AND EV200_EVT_ID = ER103_EVT_ID AND ER103_ITEM_DESC IN ('EVENT', NULL) WHERE EV200_EVT_ID > 22000 AND EV200_EVT_STATUS IN ('30','29','75','34','36') AND CR073_EXT_ACCT_CODE = '@'+'EVENT' AND RIGHT(EV200_EVT_SEARCH,2) =CAST('24' AS VARCHAR) ORDER BY EV200_EVT_SEARCH

  3/26/2024 5:36:26 PM
  System.Exception:     Index # 0    Class - 16    LineNumber - 1    Message - Conversion failed when converting the varchar value 'Standard' to data type int.    Number  - 245    Procedure  -     Server  - Pallas.USI-VAVPC.com,1683    Source  - .Net SqlClient Data Provider    State   - 1    SQL - EXECUTE As USER = 'EZWriterUser'; SELECT (SELECT SUM(ER101_RES_QTY) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER  JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_ORD_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3000','3001','3003','3080','3070','3071','3060','3300','3301','3302','3303','3305','3081','3900','3021','3902', '3085')) AS REG_COUNT, (SELECT CASE WHEN RIGHT(EV200_EVT_SEARCH,2)<> '21' THEN NULL ELSE CAST(SUM(CAST(CASE WHEN ER110_TXT_03 IN ('M','F','NA') THEN '0' ELSE '1' END AS INT)) AS INT) END FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND EV200_EVT_ID = ER110_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3000','3001','3003','3080','3070','3071','3060','3300','3301','3302','3303','3305','3081','3900','3021','3902', '3085')) AS PENDING, (SELECT CASE WHEN LEFT(EV200_EVT_SEARCH,1) IN ('C') THEN (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3901')) ELSE (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_ORD_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3901')) END) AS LIT_COUNT, (SELECT CASE WHEN LEFT(EV200_EVT_SEARCH,1) IN ('C') THEN (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_RES_CODE = 'EXTEND-CAMP' AND ER101_NEW_RES_TYPE IN ('3004')) ELSE (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_RES_CODE = 'EXTEND-CAMP' AND ER100_ORD_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3004')) END) AS EXT_DAY_COUNT, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH(NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND RIGHT(ER101_DESC,3)='KIT' AND ER101_RES_CODE <> ('NO-EXT-CAMP') AND ER100_NEW_STS IN ('A','C') AND ER100_ORD_TYPE = 'ER' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3406')) AS KIT_COUNT, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3900') AND ER101_RES_CODE ='IPP') AS IN_PERSON, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3900') AND ER101_RES_CODE = 'AHP') AS AT_HOME, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER110_TXT_29 = 'NEW' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_NEW_RES_TYPE IN ('3900') AND ER101_PHASE = '1') AS NEW_COUNT, (SELECT CAST(SUM(ER101_RES_QTY) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C') AND ER110_TXT_05 <>' ' AND ER110_TXT_29 = 'ALUMNI' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_NEW_RES_TYPE IN ('3900') AND ER101_PHASE = '1') AS ALUMNI_COUNT, (SELECT CASE WHEN LEFT(EV200_EVT_SEARCH,1) IN ('C') THEN (SELECT CAST(SUM(ER101_FC_EXT_CHRG) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE INNER JOIN ER110_EVT_REGIS WITH (NOLOCK) ON ER101_ORD_NBR = ER110_ORD_NBR AND ER101_REG_SEQ = ER110_REG_SEQ WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_REG_SEQ = ER110_REG_SEQ AND ER101_DESC <> 'PARENT' AND ER101_RES_Code NOT in ('ADJ-BUF-CIT','LIT-ADJ-MISC') AND ER101_DESC <> 'DONATION' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER100_ORD_STS IN ('A','C') AND LEFT(ER101_DESC, 3) NOT IN ('LIT', 'Ext', 'Lea', 'CIT') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3000','3001','3003', '3004', '3080', '3085','3070','3071','3060','3300','3301','3302','3303','3305','3081','3010','3011','3012','3013','3050','3900','3021','3902', '3400','3081', '3020', '3052', '3082','3050')) ELSE (SELECT CAST(SUM(ER101_FC_EXT_CHRG) AS INT) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH (NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC NOT IN ('DONATION', 'Extended Camp', 'Building Use Fee Adjustment - LIT') AND ER100_ORD_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3000','3001','3003', '3004', '3080', '3085','3070','3071','3060','3300','3301','3302','3303','3305','3081','3010','3011','3012','3013','3050','3900','3021','3902', '3400','3081', '3020', '3052', '3082','3050')) END) AS REG_REVENUE, (SELECT SUM(ER101_EXT_CHRG) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH(NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER101_DESC IN ('Extended Camp', 'XDI2', 'XDR1', 'XD2') AND ER100_NEW_STS IN ('A','C') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3004', '3011')) AS EXT_DAY_REVENUE, (SELECT SUM(ER101_FC_EXT_CHRG) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH(NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER101_NEW_ITEM_STS IN ('O','C') AND ER100_NEW_STS IN ('A','C') AND (ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3090','3091','3051','3901') OR ER101_DESC = 'LIT - Miscellaneous Adjustment' OR ER101_DESC = 'Building Use Fee Adjustment - LIT' OR ER101_DESC = 'LIT Cancellation' OR ER101_DESC = 'LIT Cancellation Insurance' OR ER101_DESC = 'DIL2' OR ER101_DESC = 'PTML1' )) AS LIT_REVENUE, (SELECT SUM(ER101_FC_EXT_CHRG) FROM ER101_ACCT_ORDER_DTL WITH (NOLOCK) INNER JOIN ER100_ACCT_ORDER WITH(NOLOCK) ON ER101_ORD_NBR = ER100_ORD_NBR AND ER101_ORG_CODE = ER100_ORG_CODE WHERE EV200_ORG_CODE = ER101_ORG_CODE AND EV200_EVT_ID = ER101_EVT_ID AND ER101_DESC <> 'PARENT' AND ER101_DESC <> 'DONATION' AND ER100_NEW_STS IN ('A','C','X','P') AND ER101_NEW_ITEM_STS IN ('O','C') AND ER101_PHASE = '1' AND ER101_NEW_RES_TYPE IN ('3085','3080')) AS PROF_DEV_REVENUE, CASE WHEN RIGHT(CR073_TXT_27,2) IN ('19', '20', '21', '22') THEN ac4.EV870_NAME ELSE (SELECT TOP 1 EV870_NAME FROM EV625_EVENT_SERVICES WITH(NOLOCK) INNER JOIN EV870_ACCT_MASTER WITH(NOLOCK) ON EV625_ORG_CODE = EV870_ORG_CODE AND EV625_NG_CNTCT_CODE = EV870_ACCT_CODE WHERE EV200_ORG_CODE = EV625_ORG_CODE AND EV200_EVT_ID = EV625_EVT_ID AND EV625_DEPT = 'DISTRI') END AS SCHOOL_DISTRICT, EV200_EVT_START_DATE, EV200_EVT_END_DATE , CONVERT(VARCHAR, CAST(EV200_EVT_END_TIME AS TIME), 100) AS EV200_EVT_END_TIME, CONVERT(VARCHAR, CAST(EV200_EVT_START_TIME AS TIME), 100) AS EV200_EVT_START_TIME, EV200_EVT_DESC, EV200_EVT_STATUS, EV200_MAJ_GROUP, EV200_EVT_ID, EV200_EVT_SEARCH AS EVT_SEARCH, AC1.EV870_NAME, AC1.EV870_ADDRESS_L1, AC1.EV870_CITY, AC1.EV870_STATE, AC1.EV870_POSTAL_CODE, AC1.EV870_IACVB_CODE AS COUNTY, CR073_TXT_27 AS PROGRAM_CODE, CR073_TXT_01 AS START_GRADE, CR073_TXT_02 AS END_GRADE, CR073_AMT_01 AS PROCESSED, CR073_TXT_23 AS SITE_STATUS, CR073_TXT_48 AS YEARS_PRIOR, CR073_TXT_08 AS TOTAL_PRICE, CR073_TXT_04 AS BUF_AMOUNT, CASE WHEN CR073_TXT_05 = 0 THEN ' ' END AS LOCATION_PREMIUM, CR073_TXT_06 AS STAFF_PREMIUM, CR073_TXT_07 AS CUSTODIAN_FEE, CR073_AMT_02 AS ESTIMATED_FEES, CR073_TXT_35 AS FEE_TYPE, (SELECT TOP 1 V1.MM721_FLD_DESC FROM MM721_UDF_TABLE_DTL AS V1 WITH(NOLOCK) WHERE CR073_ORG_CODE = V1.MM721_ORG_CODE AND CR073_TXT_26 = V1.MM721_FLD_CODE AND V1.MM721_TABLE_ID = 12) AS PROGRAM, (SELECT SUM(CAST(ER103_MAX_QTY AS INT)) FROM ER103_RES_MINMAX WHERE ER103_NEW_RES_TYPE IN ('3900') AND ER103_EVT_ID =EV200_EVT_ID AND EV200_EVT_STATUS IN ('30', '60', '29', '31', '75','34','36')) AS CAPACITY, AC3.EV870_LAST_NAME AS RC_LAST_NAME, SM.EV130_STATUS_DESC AS EVENT_STATUS FROM EV200_EVENT_MASTER WITH(NOLOCK) INNER JOIN EV870_ACCT_MASTER AS AC1 WITH(NOLOCK) ON EV200_ORG_CODE = AC1.EV870_ORG_CODE AND EV200_CUST_NBR = AC1.EV870_ACCT_CODE INNER JOIN EV870_ACCT_MASTER AS AC3 WITH(NOLOCK) ON EV200_ORG_CODE = AC3.EV870_ORG_CODE AND EV200_SLSPER = AC3.EV870_ACCT_CODE INNER JOIN CR073_ISSUES WITH(NOLOCK) ON EV200_ORG_CODE = CR073_ORG_CODE AND EV200_EVT_ID = CR073_EVT_ID INNER JOIN EV130_STATUS_MASTER AS SM WITH (NOLOCK) ON EV200_EVT_STATUS = SM.EV130_STATUS_CODE INNER JOIN EV870_ACCT_MASTER AS AC4 WITH (NOLOCK) ON EV200_ORG_CODE = AC4.EV870_ORG_CODE AND EV200_REQ_ACCT_CODE = AC4.EV870_ACCT_CODE INNER JOIN ER103_RES_MINMAX WITH(NOLOCK) ON EV200_ORG_CODE = ER103_ORG_CODE AND EV200_EVT_ID = ER103_EVT_ID AND ER103_ITEM_DESC IN ('EVENT', NULL) WHERE EV200_EVT_ID > 22000 AND EV200_EVT_STATUS IN ('30','29','75','34','36') AND CR073_EXT_ACCT_CODE = '@'+'EVENT' AND RIGHT(EV200_EVT_SEARCH,2) =CAST('24' AS VARCHAR) ORDER BY EV200_EVT_SEARCH    
  ---> System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'Standard' to data type int.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
    at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
    at System.Data.SqlClient.SqlDataReader.Read()
    at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
    at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
    at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
    at Ungerboeck.Common.USIDataAdapter.Fill(DataTable dataTable, Boolean ablnUseBeginLoadData) in C:\AZE1-BLD-PRD-07\_work\134\s\Base Services\Ungerboeck.Base\datafactory.cs:line 851
    --- End of inner exception stack trace ---
    at Ungerboeck.Common.USIDataAdapter.Fill(DataTable dataTable, Boolean ablnUseBeginLoadData) in C:\AZE1-BLD-PRD-07\_work\134\s\Base Services\Ungerboeck.Base\datafactory.cs:line 888
    at Ungerboeck.Common.DataAccess.GetTimeOutDataTable(String astrSQL, SQLOptions aintOptions, ListDictionary aobjParameters, IDbConnection& acn, Int32 aintCommandTimeout, IDbTransaction& atx, DataTable atblData, MissingSchemaAction SchemaAction, Boolean ablnUseBeginLoadData) in C:\AZE1-BLD-PRD-07\_work\134\s\Base Services\Ungerboeck.Base\DataAccess.cs:line 2309
    at Ungerboeck.Common.TemplateUtils.GetDataTable(String astrSQL, Int32 aintDataSource) in C:\AZE1-BLD-PRD-07\_work\134\s\Business Services\USIBUSB0\Reporting\EZWriter\TemplateUtils.vb:line 2855
    at Ungerboeck.Common.TemplateUtils.CreateHTMLText(String astrOrgCode, Int32 aintDatasourceSeq, Int32 aintFormatSeq, List`1 aobjEZWriterParms, DataTable& atblHeader, DataTable& atblDetail, DataTable& atblSummary) in C:\AZE1-BLD-PRD-07\_work\134\s\Business Services\USIBUSB0\Reporting\EZWriter\TemplateUtils.vb:line 1291
    at Ungerboeck.Common.EZWriterTemplateUtil.CreateHTMLTextForEZWriterPageBuilder(String& astrEmailText, DataTable atblDatasourceSQLParms, DataTable& atblHeader, DataTable& atblDetail, DataTable& atblSummary) in C:\AZE1-BLD-PRD-07\_work\134\s\Business Services\USIBUSB0\EZWriterTemplateUtil.vb:line 1943
    at Ungerboeck.IEBMS.wri_p1_display.BuildHTML(XMLDOMWrapper aobjXMLDOM, WebDictionary aobjDict, String astrSessionID, String astrPageHeader, String astrOrgCode) in C:\AZE1-BLD-PRD-07\_work\134\s\iEBMS\wri\wri_p1_display.aspx.vb:line 171
    at Ungerboeck.IEBMS.wri_p1_display.Page_Load(Object sender, EventArgs e) in C:\AZE1-BLD-PRD-07\_work\134\s\iEBMS\wri\wri_p1_display.aspx.vb:line 288
   
• 

  Mike Johnson

  June 12, 2024 19:57

  Sara Noonan - Is this still a problem or can the thread be closed out?
• 

  Sara Noonan

  June 12, 2024 20:40

  Go ahead and close it out.

Please sign in to leave a comment.