Home » SQL & PL/SQL » SQL & PL/SQL » Filtering data from SQL Resultset output [merged]
Filtering data from SQL Resultset output [merged] [message #402247] Fri, 08 May 2009 03:26 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

First of all , let me apologyze for not giving perfect TITLE,

I have a scenario. I am writing a ETL Validation Script, where in i am comparing Source Column Data and Target Column Data to see whether the data is getting matched or not.

When i execute the Procedure, it will print a SQL Select Statement, from which i can determine whether it is matched or not.

Procedure to generate the Select Statement,

/* Formatted on 2009/05/08 13:46 (Formatter Plus v4.8.7) */
CREATE OR REPLACE PROCEDURE sp_validateetl_tst (
   i_src_table     IN   VARCHAR2,
   i_tgt_table     IN   VARCHAR2,
   i_cal_yyyy_mm   IN   VARCHAR2
)
AS
   v_tic        VARCHAR2 (2)    := ' ';
   v_idx        NUMBER (4)      := 0;
   v_tab        VARCHAR2 (32)   := ' ';
   v_row        VARCHAR2 (1024) := ' ';
   v_sql        VARCHAR2 (8192) := ' ';
   v_sql_case   VARCHAR2 (8192) := ' ';

   CURSOR c_mapping
   IS
      SELECT tgt_schema, tgt_table, tgt_column, src_schema, src_table,
             src_column, src_datatype, etl_rules
        FROM etl_mappings
       WHERE etl_rules IS NOT NULL AND tgt_table = i_tgt_table;

   CURSOR c_source_tables
   IS
      SELECT DISTINCT src_schema, src_table
                 FROM etl_mappings
                WHERE key_column = 'Y' AND tgt_table = i_tgt_table;

   CURSOR c_join (i_src_table VARCHAR2)
   IS
      SELECT tgt_schema, tgt_table, tgt_column, src_schema, src_table,
             src_column
        FROM etl_mappings
       WHERE key_column = 'Y' AND tgt_table = i_tgt_table;
BEGIN
   -- Build Select Clause
   v_sql := 'SELECT ';
   v_tic := 'O';
   -- Write SELECT
   DBMS_OUTPUT.put_line (v_sql);

   FOR v_row IN c_mapping
   LOOP
      IF v_tic = 'O'
      THEN
         v_sql :=
               ' '
            || i_src_table
            || '.'
            || v_row.src_column
            || CHR (10)
            || ','
            || i_tgt_table
            || '.'
            || v_row.tgt_column
            || CHR (10);

         IF UPPER (v_row.src_datatype) LIKE '%CHAR%'
         THEN
            v_sql_case :=
                  ','
               || 'CASE WHEN NVL('
               || i_tgt_table
               || '.'
               || v_row.tgt_column
               || ',''-1'') ='
               || 'NVL('
               || v_row.etl_rules
               || ',''-1'') THEN ''Matched'' else ''Not Matched '' END '
               || 'Matched_Status';                             --Alias Column
         END IF;

         IF    UPPER (v_row.src_datatype) LIKE '%NUM%'
            OR UPPER (v_row.src_datatype) LIKE '%FLOAT%'
         THEN
            v_sql_case :=
                  ','
               || 'CASE WHEN NVL('
               || i_tgt_table
               || '.'
               || v_row.tgt_column
               || ',-1) ='
               || 'NVL('
               || v_row.etl_rules
               || ',-1) THEN ''Matched'' else ''Not Matched '' END '
               || 'Matched_Status';                             --Alias Column
         END IF;

         IF UPPER (v_row.src_datatype) LIKE 'DAT%'
         THEN
            v_sql_case :=
                  ','
               || 'CASE WHEN NVL('
               || i_tgt_table
               || '.'
               || v_row.tgt_column
               || ',sysdate) ='
               || 'NVL('
               || v_row.etl_rules
               || ',sysdate) THEN ''Matched'' else ''Not Matched '' END '
               || 'Matched_Status';                             --Alias Column
         END IF;

         v_tic := 'X';
      ELSE
         v_sql :=
               ', '
            || i_src_table
            || '.'
            || v_row.src_column
            || CHR (10)
            || ','
            || i_tgt_table
            || '.'
            || v_row.tgt_column
            || CHR (10);

         IF UPPER (v_row.src_datatype) LIKE '%CHAR%'
         THEN
            v_sql_case :=
                  ','
               || 'CASE WHEN NVL('
               || i_tgt_table
               || '.'
               || v_row.tgt_column
               || ',''-1'') ='
               || 'NVL('
               || v_row.etl_rules
               || ',''-1'') THEN ''Matched'' else ''Not Matched '' END '
               || 'Matched_Status';                             --Alias Column
         END IF;

         IF    UPPER (v_row.src_datatype) LIKE 'NUM%'
            OR UPPER (v_row.src_datatype) LIKE '%FLOAT%'
         THEN
            v_sql_case :=
                  ','
               || 'CASE WHEN NVL('
               || i_tgt_table
               || '.'
               || v_row.tgt_column
               || ',-1) ='
               || 'NVL('
               || v_row.etl_rules
               || ',-1) THEN ''Matched'' else ''Not Matched '' END '
               || 'Matched_Status';                             --Alias Column
         END IF;

         IF UPPER (v_row.src_datatype) LIKE 'DAT%'
         THEN
            v_sql_case :=
                  ','
               || 'CASE WHEN NVL('
               || i_tgt_table
               || '.'
               || v_row.tgt_column
               || ',sysdate) ='
               || 'NVL('
               || v_row.etl_rules
               || ',sysdate) THEN ''Matched'' else ''Not Matched '' END '
               || 'Matched_Status';                             --Alias Column
         END IF;
      END IF;

      v_sql := v_sql || v_sql_case || CHR (10);
      DBMS_OUTPUT.put_line (v_sql);
   END LOOP;

-- Build FROM Clause
   v_sql := ' FROM ' || i_tgt_table;
   v_tic := 'O';

   FOR v_tab IN c_source_tables
   LOOP
      v_sql :=
            v_sql
         || ' INNER JOIN '
         || v_tab.src_schema
         || '.'
         || i_src_table
         || ' ON ';

      FOR v_row IN c_join (v_tab.src_table)
      LOOP
         IF v_tic = 'O'
         THEN
            v_sql :=
                  v_sql
               || '     '
               || i_tgt_table
               || '.'
               || v_row.tgt_column
               || ' = '
               || i_src_table
               || '.'
               || v_row.src_column;
            v_tic := 'X';
         ELSE
            v_sql :=
                  v_sql
               || ' AND '
               || i_tgt_table
               || '.'
               || v_row.tgt_column
               || ' = '
               || i_src_table
               || '.'
               || v_row.src_column;
         END IF;
      END LOOP;
   END LOOP;

   --Adding Where Clause for CAL_YYYY_MM and CMIPS_EOM_AS_OF_DT column (Common columns in all tables)
   IF i_cal_yyyy_mm IS NOT NULL
   THEN
      v_sql := v_sql || ' AND CAL_YYYY_MM =' || '''' || i_cal_yyyy_mm || '''';
      v_sql :=
            v_sql
         || ' AND CMIPS_EOM_AS_OF_DT = '
         || ''''
         || LAST_DAY (TO_DATE (i_cal_yyyy_mm, 'YYYYMM'))
         || '''';
   END IF;

   -- Write FROM
   DBMS_OUTPUT.put_line (v_sql);
END sp_validateetl_tst;
/


Select Statement,after running the above procedure..

SELECT src_col1, tgt_col1,
       CASE
          WHEN NVL (tgt_col1, '-1') = NVL (src_col1, '-1')
             THEN 'Matched'
          ELSE 'Not Matched '
       END matched_status,
       src_col2, tgt_col2,
       CASE
          WHEN NVL (tgt_col2, '-1') = NVL (src_col2, '-1')
             THEN 'Matched'
          ELSE 'Not Matched '
       END matched_status,
       src_col3, tgt_col3,
       CASE
          WHEN NVL (tgt_col3, '-1') = NVL (src_col3, '-1')
             THEN 'Matched'
          ELSE 'Not Matched '
       END matched_status
  FROM target_table INNER JOIN src_table
       ON recip_svcs_need_assessment.recip_case_number_id =
                                                       relcco43_apr08.rcrecnum
     AND recip_svcs_need_assessment.recip_grid_service_type =
                                                         relcco43_apr08.rcgrid
     AND cal_yyyy_mm = '200804'
     AND cmips_eom_as_of_dt = '30-APR-08'


The output is as follows,

Src_Col1    Tgt_Col1  Matched_Status  Src_Col2      Tgt_col2 Matched_Status1
4306196413 4306196413	Matched      	RR 		RR 	Matched      
4306196413 4306196413	Matched      	SS 		SS 	Matched      
4306197882 4306197882	Matched      	AA 		AA 	Matched      
4306197882 4306197882	Matched      	BB 		BB 	Matched      
4306197882 4306197882	Matched      	CC 		CC 	Matched      
4306197882 4306197888	Not Matched	DD 		DD 	Matched      
4306197882 4306197882	Matched      	EE 		EE 	Matched      
4306197882 4306197882	Matched      	FF 		FF 	Matched      
4306197882 4306197882	Matched      	LL 		II	Not Matched
4306197882 4306197882	Matched      	NN 		NN 	Matched  


My Requirement is, I want to display the Source and Target Columns which has Matched_Status as 'Not Matched'

Since i am generating the above select Statement, i will not be knowing how many columns are there in Source and Taget ( I will load all source and taget columns and mappings in a table called ETL_Mappings).

Output desired,

Src_Col1    Tgt_Col1  Matched_Status  Src_Col2      Tgt_col2 Matched_Status1
 
4306197882 4306197888	Not Matched	DD 		DD 	Matched      
4306197882 4306197882	Matched      	LL 		II	Not Matched


Since i am generating the SQL Select Statement for the output, i will not be having the control to see the above result ( I am not loading this select statement into a Table and check).

Please suggest.

Regards,
Ashoka BL
Re: Dynamic Checking of Data [message #402255 is a reply to message #402247] Fri, 08 May 2009 04:01 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Guyz,

Such a long question, but while writing the question, i got the idea....

If i tweak my procedure to give me only not matched records...It would generate the SQL Statement as following,

SELECT src_col1, tgt_col1,
       CASE
          WHEN NVL (tgt_col1, '-1') <> NVL (src_col1, '-1')
             THEN 'Not Matched '
       END matched_status,
       src_col2, tgt_col2,
       CASE
          WHEN NVL (tgt_col2, '-1') <> NVL (src_col2, '-1')
             THEN 'Not Matched '
       END matched_status,
       src_col3, tgt_col3,
       CASE
          WHEN NVL (tgt_col3, '-1') <> NVL (src_col3, '-1')
             THEN 'Not Matched '
       END matched_status
  FROM target_table INNER JOIN src_table
       ON recip_svcs_need_assessment.recip_case_number_id =
                                                       relcco43_apr08.rcrecnum
     AND recip_svcs_need_assessment.recip_grid_service_type =
                                                         relcco43_apr08.rcgrid
     AND cal_yyyy_mm = '200804'
     AND cmips_eom_as_of_dt = '30-APR-08'


This is what i want Smile

Filtering data from SQL Resultset output [message #402280 is a reply to message #402247] Fri, 08 May 2009 07:19 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I have the following tables,

CREATE TABLE EMP_S
(
  EMPNO  NUMBER(4),
  ENAME  VARCHAR2(10 BYTE),
  SAL    NUMBER(7,2)
)


CREATE TABLE EMP_T
(
  EMPNO  NUMBER(4),
  ENAME  VARCHAR2(10 BYTE),
  SAL    NUMBER(7,2)
)


INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7369, 'SMITH', 800); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7499, 'ALLEN', 1600); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7521, 'WARD', 1250); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7566, 'JONES', 2975); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7654, 'MARTIN', 1250); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7698, 'BLAKE', 2850); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7782, 'CLARK', 2450); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7788, 'SCOTT', 3000); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7839, 'KING', 5000); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7844, 'TURNER', 1500); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7876, 'ADAMS', 1100); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7900, 'JAMES', 950); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7902, 'FORD', 3000); 
INSERT INTO EMP_S ( EMPNO, ENAME, SAL ) VALUES ( 
7934, 'MILLER', 1300); 
COMMIT;


INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7369, 'SMITHA', 800); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7499, 'ALLEN', 1600); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7521, 'WARD', 1250); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7566, 'JONES', 2975); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7654, 'MARTIN', 1250); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7698, 'BLAKE', 2850); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7782, 'CLARK', 6000); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7788, 'SCOTT', 3000); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7839, 'KING', 6000); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7844, 'TURNERS', 1500); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7876, 'ADAMS', 1100); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7900, 'JAMES', 950); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7902, 'FORD', 3000); 
INSERT INTO EMP_T ( EMPNO, ENAME, SAL ) VALUES ( 
7934, 'MILLER', 1300); 
COMMIT;


I ran the following query to get the desired output.

SELECT s.empno sempno, t.empno tempno,
       CASE
          WHEN s.empno <> t.empno
             THEN 'Not Matched'
       END matched_status, s.ename sename, t.ename tename,
       CASE
          WHEN s.ename <> t.ename
             THEN 'Not Matched'
       END matched_status, s.sal ssal, t.sal tsal,
       CASE
          WHEN s.sal <> t.sal
             THEN 'Not Matched'
       END matched_status
  FROM emp_s s, emp_t t
 WHERE s.empno = t.empno


OUTPUT
SEMPNO TEMPNO MATCHED_STATUS SENAME     TENAME     MATCHED_STATUS_1 SSAL    TSAL    MATCHED_STATUS_2 
7369   7369                  SMITH      SMITHA     Not Matched      800     800                      
7499   7499                  ALLEN      ALLEN                       1600    1600                     
7521   7521                  WARD       WARD                        1250    1250                     
7566   7566                  JONES      JONES                       2975    2975                     
7654   7654                  MARTIN     MARTIN                      1250    1250                     
7698   7698                  BLAKE      BLAKE                       2850    2850                     
7782   7782                  CLARK      CLARK                       2450    6000    Not Matched      
7788   7788                  SCOTT      SCOTT                       3000    3000                     
7839   7839                  KING       KING                        5000    6000    Not Matched      
7844   7844                  TURNER     TURNERS    Not Matched      1500    1500                     
7876   7876                  ADAMS      ADAMS                       1100    1100                     
7900   7900                  JAMES      JAMES                       950     950                      
7902   7902                  FORD       FORD                        3000    3000                     
7934   7934                  MILLER     MILLER                      1300    1300   


Now i want the Records which have got the value as 'Not Matched' only.

SEMPNO TEMPNO MATCHED_STATUS SENAME     TENAME     MATCHED_STATUS_1 SSAL    TSAL    MATCHED_STATUS_2 
7369   7369                  SMITH      SMITHA     Not Matched      800     800                      
7782   7782                  CLARK      CLARK                       2450    6000    Not Matched      
7839   7839                  KING       KING                        5000    6000    Not Matched      
7844   7844                  TURNER     TURNERS    Not Matched      1500    1500                     


Also please note that the SQL Select statement is generated from Oracle SP.

Please suggest me

Regards,
Ashoka BL
Re: Filtering data from SQL Resultset output [message #402287 is a reply to message #402280] Fri, 08 May 2009 07:50 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
What is you made the query an inline view and then just used a where clause of
matched_status='Not Matched'
Re: Filtering data from SQL Resultset output [message #402288 is a reply to message #402280] Fri, 08 May 2009 07:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SELECT *
FROM (SELECT s.empno sempno
           , t.empno tempno,
             CASE
                WHEN s.empno <> t.empno
                   THEN 'Not Matched'
             END empno_matched_status
           , s.ename sename
           , t.ename tename,
             CASE
                WHEN s.ename <> t.ename
                   THEN 'Not Matched'
             END ename_matched_status
           , s.sal ssal
           , t.sal tsal,
             CASE
                WHEN s.sal <> t.sal
                   THEN 'Not Matched'
             END sal_matched_status
      FROM emp_s s, emp_t t
      WHERE s.empno = t.empno)
WHERE 'Not Matched' in (empno_matched_status,ename_matched_status,sal_matched_status)
;

[Updated on: Fri, 08 May 2009 07:52]

Report message to a moderator

Re: Filtering data from SQL Resultset output [merged] [message #402467 is a reply to message #402247] Sun, 10 May 2009 23:13 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

Thanks for the replies,

but if you notice my query..

SELECT s.empno sempno, t.empno tempno,
       CASE
          WHEN s.empno <> t.empno
             THEN 'Not Matched'
       END matched_status, s.ename sename, t.ename tename,
       CASE
          WHEN s.ename <> t.ename
             THEN 'Not Matched'
       END matched_status, s.sal ssal, t.sal tsal,
       CASE
          WHEN s.sal <> t.sal
             THEN 'Not Matched'
       END matched_status
  FROM emp_s s, emp_t t
 WHERE s.empno = t.empno


All Case When Statement columns have a common column called matched_status, Can i have LIKE Operation on the column matched_status ?

I am actually generating this SQL from a procedure. I will not be knowing the column names. I will use this select statement to determine whether my source column and target column have correct data ( ETL Validation)

The query that JRowBottom suggested has,

WHERE 'Not Matched' in (empno_matched_status,ename_matched_status,sal_matched_status)


But i will have Matched_Status as a column only.

Please suggest whether it is possible or not,

Regards,
Ashoka BL
Re: Filtering data from SQL Resultset output [merged] [message #402527 is a reply to message #402467] Mon, 11 May 2009 05:49 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Changing your SQL generating query to give the matched_status columns known names would seem to be a good solution at this point.

You could use the default naming and change the query to
WHERE 'Not Matched' in (matched_status,matched_status1,matched_status2)

But if you don't know the column names, then that implies that you don't know how many of them there are, and that means that you've got a problem.
Previous Topic: Performance of the Procedure
Next Topic: BULK INSERT
Goto Forum:
  


Current Time: Tue Dec 06 08:23:51 CST 2016

Total time taken to generate the page: 0.05786 seconds