sorry i have othe proplem i get error whe i try to exucte this" conversion fail when converting the varchar value '3.0101e' to data type int" i change the val() into convert(int,filed_name)SELECT w_collect.comp_id, ctrl.comp_name, rtrim(lTrim(Str(w_collect.id))) AS inv_no, w_collect.BILLINGRUN_ID, w_collect.ISSUE_MONTH, w_collect.ISSUE_YEAR, w_collect.NO_OF_MONTH, w_collect.SECTION_CODE, locations.location_name AS section_name, w_collect.BRANCH_CODE, locations_1.location_name AS branch_name, w_collect.AREA_CODE, locations_2.location_name AS area_name, CONVERT(int,(SUBSTRING(rtrim(lTrim([location])),1,7))) AS lvl4, CONVERT(int,(SUBSTRING(rtrim(lTrim([location])),1,10))) AS lvl5, CONVERT(int,(SUBSTRING(rtrim(lTrim([location])),1,14))) AS lvl6, w_collect.CUSTOMER_ACCOUNT_ID, activities.activity_code, activities.activity_name, w_collect.FILE_NO, w_collect.CUSTOMER_NAME, w_collect.CUSTOMER_ADDRESS, w_collect.METER_STATUS_CODE, meter_status.status_name, [current_reading]-[previous_reading] AS qty, w_collect.USAGE, w_collect.USAGE_VALUE, w_collect.SANITARY, w_collect.MAINTENANCE, w_collect.REVENUE_STAMP, w_collect.CONTRACT_STAMP, w_collect.RECEIPT_STAMP, w_collect.INSTALLMENT, w_collect.AMOUNT_DUE, w_collect.TOTAL_DUE, w_collect.PAY_STATUS_CODE, w_collect.CAL_METHOD_CODE, w_collect.REVIEWED, w_collect.ACCOUNT_STATUS_CODE, w_collect.PAY_DATE, w_collect.NO_OF_UNITS, w_collect.AVG_USG, w_collect.SANITARY_STATUS_CODE, sewer_status.sewer_descr, w_collect.READER_CODE, w_collect.COLLECTOR_CODE, w_collect.ADD_VAL, w_collect.DEDUCT_VAL, w_collect.NO_OF_ROOM, w_collect.GOV_STAMP, w_collect.INSTALLMENT_ID, w_collect.SANITAX, w_collect.PAY_TYPE, w_collect.METER_SIZE_CODE, meter_size.size_name, w_collect.ISCALC, w_collect.pay_time, w_collect.distribution_date, w_collect.other_val, w_collect.clean_val, w_collect.stamp_date, w_collect.stamp_time, w_collect.stamp_user, w_collect.geha_code, w_collect.start_date, w_collect.end_date, w_collect.start_collector, w_collect.end_collector, w_collect.tafket, w_collect.location, locations_3.location_name, w_collect.METER_MAX, w_collect.CURRENT_READING, w_collect.PREVIOUS_READING, CONVERT(int,(w_collect.geha_code/1000)) AS geha_main, w_collect.inv_date, w_collect.BUILDing_no, w_collect.zone_code, locations_4.location_name AS zone_name, w_collect.block_code, locations_5.location_name AS block_name, CONVERT(int,([CUSTOMER_ACCOUNT_ID]/100)) AS main_act, CONVERT(int,([CUSTOMER_ACCOUNT_ID]/10000)) AS cust_type, w_collect.ID, w_collect.req_qty, w_collect.req_valFROM ((((((((((w_collect LEFT JOIN ctrl ON w_collect.comp_id = ctrl.comp_id) LEFT JOIN locations ON w_collect.SECTION_CODE = locations.location_code) LEFT JOIN locations AS locations_1 ON w_collect.BRANCH_CODE = locations_1.location_code) LEFT JOIN locations AS locations_2 ON w_collect.AREA_CODE = locations_2.location_code) LEFT JOIN locations AS locations_3 ON w_collect.location = locations_3.location_code) LEFT JOIN locations AS locations_4 ON w_collect.zone_code = locations_4.location_code) LEFT JOIN locations AS locations_5 ON w_collect.block_code = locations_5.location_code) LEFT JOIN activities ON w_collect.CUSTOMER_ACCOUNT_ID = activities.activity_code) LEFT JOIN meter_status ON w_collect.METER_STATUS_CODE = meter_status.status_code) LEFT JOIN meter_size ON w_collect.METER_SIZE_CODE = meter_size.size_code) LEFT JOIN sewer_status ON w_collect.SANITARY_STATUS_CODE = sewer_status.sewer_type;
conversion faild
Posted - 2010-06-23 : 06:56:23
