Attached a sample of my code
[font="Courier New"]-- 43. Validate English Name Address 1
-- 43.1 NAMADDR1 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,43
,1
,NAMADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(NAMADDR1,'')='') AND ADDRTYPE='ME'
-- 43.2 NAMADDR1 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,43
,2
,NAMADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.NAMADDR1) > 40 AND ADDRTYPE='ME'
-- 43.3 NAMADDR1 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,43
,3
,NAMADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),NAMADDR1)<>NAMADDR1
AND ADDRTYPE='ME'
-- 44. Validate English Name Address 2
-- 44.1 NAMADDR1 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,44
,1
,NAMADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(NAMADDR1,'')='') AND ADDRTYPE='ME'
-- 44.2 NAMADDR2 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,44
,2
,NAMADDR2
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.NAMADDR2) > 40
-- 44.3 NAMADDR2 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,44
,3
,NAMADDR2
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),NAMADDR2)<>NAMADDR2
AND ADDRTYPE='ME'
-- 45. Validate English Name Address Line 3
-- 45.1 Validate Null Or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,45
,1
,NAMADDR3
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(NAMADDR3,'')='') AND ADDRTYPE='ME'
-- 45.2 NAMADDR1 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,45
,2
,NAMADDR3
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.NAMADDR3) > 40
-- 45.3 NAMADDR3 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,45
,3
,NAMADDR3
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),NAMADDR3)<>NAMADDR3
AND ADDRTYPE='ME'
-- 46. Validate Name Address 4
-- 46.1 NAMADDR4 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,46
,1
,NAMADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(NAMADDR1,'')='') AND ADDRTYPE='ME'
-- 46.2 NAMADDR4 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,46
,2
,NAMADDR4
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.NAMADDR4) > 40
-- 46.3 NAMADDR4 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,46
,3
,NAMADDR4
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),NAMADDR4)<>NAMADDR4
AND ADDRTYPE='ME'
-- 47. Validate Work Address Line 1-4 exeeds 90 charactors
INSERT INTO CustomerReport
SELECT CUSNO
,47
,1
,LTRIM(RTRIM(WORKADDR1))+LTRIM(RTRIM(WORKADDR2))+LTRIM(RTRIM(WORKADDR3))+LTRIM(RTRIM(WORKADDR4))
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN (LTRIM(RTRIM(WORKADDR1))+LTRIM(RTRIM(WORKADDR2))+LTRIM(RTRIM(WORKADDR3))+LTRIM(RTRIM(WORKADDR4)))>90
AND ADDRTYPE='ME'
-- 48. Validate English Work Address1
-- 48.1 WORKADDR1 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,48
,1
,WORKADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(WORKADDR1,'')='') AND ADDRTYPE='ME'
-- 48.2 WORKADDR1 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,48
,2
,WORKADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.WORKADDR1) > 40 AND ADDRTYPE='ME'
-- 48.3 WORKADDR1 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,48
,3
,WORKADDR1
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),WORKADDR1)<>WORKADDR1
AND ADDRTYPE='ME'
-- 49. Validate English Work Address2
-- 49.1 WORKADDR2 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,49
,1
,WORKADDR9
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(WORKADDR9,'')='') AND ADDRTYPE='ME'
-- 49.2 WORKADDR2 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,49
,2
,WORKADDR2
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.WORKADDR2) > 40 AND ADDRTYPE='ME'
-- 49.3 WORKADDR2 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,49
,3
,WORKADDR2
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),WORKADDR2)<>WORKADDR2
AND ADDRTYPE='ME'
-- 50. Validate English Work Address3
-- 50.1 WORKADDR3 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,50
,1
,WORKADDR3
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(WORKADDR3,'')='') AND ADDRTYPE='ME'
-- 50.2 WORKADDR3 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,50
,2
,WORKADDR3
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.WORKADDR3) > 40 AND ADDRTYPE='ME'
-- 50.3 WORKADDR3 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,50
,3
,WORKADDR3
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),WORKADDR3)<>WORKADDR3
AND ADDRTYPE='ME'
-- 51. Validate English Work Address4
-- 51.1 WORKADDR4 NULL or Blank
INSERT INTO CustomerReport
SELECT CUSNO
,51
,1
,WORKADDR4
FROM dbo.RETDCUST_CUSTADDR
WHERE (COALESCE(WORKADDR4,'')='') AND ADDRTYPE='ME'
-- 51.2 WORKADDR4 exceeds 40 Characters
INSERT INTO CustomerReport
SELECT CUSNO
,51
,2
,WORKADDR4
FROM dbo.RETDCUST_CUSTADDR
WHERE LEN(dbo.RETDCUST_CUSTADDR.WORKADDR4) > 40 AND ADDRTYPE='ME'
-- 51.3 WORKADDR4 contains Arabic characters
INSERT INTO CustomerReport
SELECT CUSNO
,51
,3
,WORKADDR4
FROM dbo.RETDCUST_CUSTADDR
WHERE CONVERT(CHAR(66),WORKADDR4)<>WORKADDR4
AND ADDRTYPE='ME' [/font]
- Zahran -