create view q_w_clct

as

SELECT     dbo.w_collect.comp_id, dbo.ctrl.comp_name, RTrim(LTrim(STR(dbo.w_collect.ID))) AS inv_no, dbo.w_collect.BILLINGRUN_ID, dbo.w_collect.ISSUE_MONTH, 
                      dbo.w_collect.ISSUE_YEAR, dbo.w_collect.NO_OF_MONTH, dbo.w_collect.SECTION_CODE, dbo.locations.location_name AS section_name, 
                      dbo.w_collect.BRANCH_CODE, locations_1.location_name AS branch_name, dbo.w_collect.AREA_CODE, locations_2.location_name AS area_name, 
                      Val(substring(rtrim(lTrim(dbo.w_collect.location))), 1, 7) AS lvl4, Val(substring(rtrim(lTrim(dbo.w_collect.location)), 1, 10)) AS lvl5, Val(Mid(rtrim(lTrim(dbo.w_collect.location)), 1, 14)) 
                      AS lvl6, dbo.w_collect.CUSTOMER_ACCOUNT_ID, dbo.activities.activity_code, dbo.activities.activity_name, dbo.w_collect.FILE_NO, 
                      dbo.w_collect.CUSTOMER_NAME, dbo.w_collect.CUSTOMER_ADDRESS, dbo.w_collect.METER_STATUS_CODE, dbo.meter_status.status_name, 
                      dbo.w_collect.CURRENT_READING - dbo.w_collect.PREVIOUS_READING AS qty, dbo.w_collect.USAGE, dbo.w_collect.USAGE_VALUE, 
                      dbo.w_collect.SANITARY, dbo.w_collect.MAINTENANCE, dbo.w_collect.REVENUE_STAMP, dbo.w_collect.CONTRACT_STAMP, 
                      dbo.w_collect.RECEIPT_STAMP, dbo.w_collect.INSTALLMENT, dbo.w_collect.AMOUNT_DUE, dbo.w_collect.TOTAL_DUE, 
                      dbo.w_collect.PAY_STATUS_CODE, dbo.w_collect.CAL_METHOD_CODE, dbo.w_collect.REVIEWED, dbo.w_collect.ACCOUNT_STATUS_CODE, 
                      dbo.w_collect.PAY_DATE, dbo.w_collect.NO_OF_UNITS, dbo.w_collect.AVG_USG, dbo.w_collect.SANITARY_STATUS_CODE, 
                      dbo.sewer_status.sewer_descr, dbo.w_collect.READER_CODE, dbo.w_collect.COLLECTOR_CODE, dbo.w_collect.ADD_VAL, 
                      dbo.w_collect.DEDUCT_VAL, dbo.w_collect.NO_OF_ROOM, dbo.w_collect.GOV_STAMP, dbo.w_collect.INSTALLMENT_ID, dbo.w_collect.SANITAX, 
                      dbo.w_collect.PAY_TYPE, dbo.w_collect.METER_SIZE_CODE, dbo.meter_size.size_name, dbo.w_collect.ISCALC, dbo.w_collect.pay_time, 
                      dbo.w_collect.distribution_date, dbo.w_collect.other_val, dbo.w_collect.clean_val, dbo.w_collect.stamp_date, dbo.w_collect.stamp_time, 
                      dbo.w_collect.stamp_user, dbo.w_collect.geha_code, dbo.w_collect.start_date, dbo.w_collect.end_date, dbo.w_collect.start_collector, 
                      dbo.w_collect.end_collector, dbo.w_collect.tafket, dbo.w_collect.location, locations_3.location_name, dbo.w_collect.METER_MAX, 
                      dbo.w_collect.CURRENT_READING, dbo.w_collect.PREVIOUS_READING, Int(dbo.w_collect.geha_code / 1000) AS geha_main, dbo.w_collect.inv_date, 
                      dbo.w_collect.BUILDing_no, dbo.w_collect.zone_code, locations_4.location_name AS zone_name, dbo.w_collect.block_code, 
                      locations_5.location_name AS block_name, Int(dbo.w_collect.CUSTOMER_ACCOUNT_ID / 100) AS main_act, 
                      Int(dbo.w_collect.CUSTOMER_ACCOUNT_ID / 10000) AS cust_type, dbo.w_collect.ID, dbo.w_collect.req_qty, dbo.w_collect.req_val
FROM         dbo.w_collect LEFT OUTER JOIN
                      dbo.ctrl ON dbo.w_collect.comp_id = dbo.ctrl.comp_id LEFT OUTER JOIN
                      dbo.locations ON dbo.w_collect.SECTION_CODE = dbo.locations.location_code LEFT OUTER JOIN
                      dbo.locations AS locations_1 ON dbo.w_collect.BRANCH_CODE = locations_1.location_code LEFT OUTER JOIN
                      dbo.locations AS locations_2 ON dbo.w_collect.AREA_CODE = locations_2.location_code LEFT OUTER JOIN
                      dbo.locations AS locations_3 ON dbo.w_collect.location = locations_3.location_code LEFT OUTER JOIN
                      dbo.locations AS locations_4 ON dbo.w_collect.zone_code = locations_4.location_code LEFT OUTER JOIN
                      dbo.locations AS locations_5 ON dbo.w_collect.block_code = locations_5.location_code LEFT OUTER JOIN
                      dbo.activities ON dbo.w_collect.CUSTOMER_ACCOUNT_ID = dbo.activities.activity_code LEFT OUTER JOIN
                      dbo.meter_status ON dbo.w_collect.METER_STATUS_CODE = dbo.meter_status.status_code LEFT OUTER JOIN
                      dbo.meter_size ON dbo.w_collect.METER_SIZE_CODE = dbo.meter_size.size_code LEFT OUTER JOIN
                      dbo.sewer_status ON dbo.w_collect.SANITARY_STATUS_CODE = dbo.sewer_status.sewer_type

quote:

---

Originally posted by BrOkEn_iCe

i want any one to till me any part of the code have the error and by the way there is an error near substring and i don't know why

---