locked
Query question?? RRS feed

  • Question

  • Experts,

    CREATE TABLE #TEMP
    (
    	ID		VARCHAR(12)		NULL,
    	LName	VARCHAR(35)		NULL,
    	FName	VARCHAR(15)		NULL,
    	SSN		VARCHAR(9)		NULL
    )
    
    INSERT INTO #TEMP VALUES ( '1700000020', '', 'Joseph', '371745' )
    INSERT INTO #TEMP VALUES ( '1700000115', 'Agular', 'Henry', '136007586' )
    INSERT INTO #TEMP VALUES ( '1700001135', 'Castano', '', '341520212' )
    
    SELECT	ID, LName, FName, SSN, '' AS ERR_ID, '' AS ERR_Code, '' AS ERR_Msg
    FROM	#TEMP
    ORDER BY	ID
    
    SELECT	*, '' AS ERR_ID, '' AS ERR_Code, '' AS ERR_Msg
    FROM	#TEMP a
    WHERE	NOT EXISTS
    		(
    			SELECT	*
    			FROM	#TEMP b
    			WHERE	b.ID = a.ID
    						AND
    						(
    							LName = ''
    								OR FName = ''
    								OR LEN(SSN) <> 9
    						)
    		)
    
    UNION ALL
    
    SELECT	ID, LName, FName, SSN,
    		CASE
    			WHEN LName = ''		THEN ID
    			WHEN FName = ''		THEN ID
    			WHEN LEN(SSN) <> 9	THEN ID
    		END AS ERR_ID,
    		CASE
    			WHEN LName = ''		THEN '001'
    			WHEN FName = ''		THEN '002'
    			WHEN LEN(SSN) <> 9	THEN '003'
    		END AS ERR_Code,
    		CASE
    			WHEN LName = ''		THEN 'Lname missing'
    			WHEN FName = ''		THEN 'Fname missing'
    			WHEN LEN(SSN) <> 9	THEN 'SSN lenght is not nine digits'
    		END AS ERR_Msg
    FROM	#TEMP
    WHERE	LName = ''
    			OR FName = ''
    			OR LEN(SSN) <> 9
    ORDER BY	ID
    
    DROP TABLE #TEMP

    Expected o/p:-

    SELECT	*, '' AS ERR_ID, '' AS ERR_Code, '' AS ERR_Msg
    FROM	#TEMP a
    WHERE	NOT EXISTS
    		(
    			SELECT	*
    			FROM	#TEMP b
    			WHERE	b.ID = a.ID
    						AND
    						(
    							LName = ''
    								OR FName = ''
    								OR LEN(SSN) <> 9
    						)
    		)
    
    UNION ALL
    
    SELECT	ID, LName, FName, SSN,
    		CASE
    			WHEN LName = ''		THEN ID
    			WHEN FName = ''		THEN ID
    			WHEN LEN(SSN) <> 9	THEN ID
    		END AS ERR_ID,
    		CASE
    			WHEN LName = ''		THEN '001'
    			WHEN FName = ''		THEN '002'
    			WHEN LEN(SSN) <> 9	THEN '003'
    		END AS ERR_Code,
    		CASE
    			WHEN LName = ''		THEN 'Lname missing'
    			WHEN FName = ''		THEN 'Fname missing'
    			WHEN LEN(SSN) <> 9	THEN 'SSN lenght is not nine digits'
    		END AS ERR_Msg
    FROM	#TEMP
    WHERE	LName = ''
    			OR FName = ''
    			OR LEN(SSN) <> 9
    
    UNION ALL
    
    SELECT	'1700000020' AS 'ID', '' AS 'LName', 'Joseph' AS 'FName', '371745' AS 'SSN', '1700000020' AS 'ERR_ID', '004' AS 'ERR_Code', 'SSN lenght is not nine digits' AS 'ERR_Msg'
    
    ORDER BY	ID

    I want to display all the errors for each record if exists.

    Any help please?

    Thanks in advance

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS

    Tuesday, April 15, 2014 9:52 PM

Answers

  • Deleted
    • Proposed as answer by Fanny Liu Wednesday, April 16, 2014 11:57 AM
    • Marked as answer by Fanny Liu Monday, April 28, 2014 3:14 PM
    Tuesday, April 15, 2014 11:10 PM
  • Try

    ;WITH cteErr001 AS 
    (
    	SELECT [ERR_ID] = [ID]
    	      ,[ERR_Code] = '001'
    		  ,[ERR_Msg] = 'Lname missing'
    	  FROM #TEMP
    	 WHERE LName = ''
    ), cteErr002 AS 
    (
    	SELECT [ERR_ID] = [ID]
    	      ,[ERR_Code] = '002'
    		  ,[ERR_Msg] = 'Fname missing'
    	  FROM #TEMP
    	 WHERE FName = ''
    ), cteErr004 AS 
    (
    	SELECT [ERR_ID] = [ID]
    	      ,[ERR_Code] = '004'
    		  ,[ERR_Msg] = 'SSN lenght is not nine digits'
    	  FROM #TEMP
    	 WHERE LEN(SSN) <> 9
    )
    SELECT *
      FROM #TEMP
      LEFT JOIN (
    	SELECT *
    	  FROM cteErr001
    	 UNION
    	SELECT *
    	  FROM cteErr002
    	 UNION
    	SELECT *
    	  FROM cteErr004
      ) A
      ON [ID] = A.[ERR_ID]

    Hope this helps.
    ~J.
    • Proposed as answer by Fanny Liu Wednesday, April 16, 2014 11:57 AM
    • Marked as answer by Fanny Liu Monday, April 28, 2014 3:14 PM
    Tuesday, April 15, 2014 11:18 PM

All replies

  • Deleted
    • Proposed as answer by Fanny Liu Wednesday, April 16, 2014 11:57 AM
    • Marked as answer by Fanny Liu Monday, April 28, 2014 3:14 PM
    Tuesday, April 15, 2014 11:10 PM
  • Try

    ;WITH cteErr001 AS 
    (
    	SELECT [ERR_ID] = [ID]
    	      ,[ERR_Code] = '001'
    		  ,[ERR_Msg] = 'Lname missing'
    	  FROM #TEMP
    	 WHERE LName = ''
    ), cteErr002 AS 
    (
    	SELECT [ERR_ID] = [ID]
    	      ,[ERR_Code] = '002'
    		  ,[ERR_Msg] = 'Fname missing'
    	  FROM #TEMP
    	 WHERE FName = ''
    ), cteErr004 AS 
    (
    	SELECT [ERR_ID] = [ID]
    	      ,[ERR_Code] = '004'
    		  ,[ERR_Msg] = 'SSN lenght is not nine digits'
    	  FROM #TEMP
    	 WHERE LEN(SSN) <> 9
    )
    SELECT *
      FROM #TEMP
      LEFT JOIN (
    	SELECT *
    	  FROM cteErr001
    	 UNION
    	SELECT *
    	  FROM cteErr002
    	 UNION
    	SELECT *
    	  FROM cteErr004
      ) A
      ON [ID] = A.[ERR_ID]

    Hope this helps.
    ~J.
    • Proposed as answer by Fanny Liu Wednesday, April 16, 2014 11:57 AM
    • Marked as answer by Fanny Liu Monday, April 28, 2014 3:14 PM
    Tuesday, April 15, 2014 11:18 PM