4 Replies Latest reply: Feb 5, 2013 6:36 AM by BluShadow RSS

    Hide column based on values

    Burasami
      Hi All,

      I have SQL query which is returning result based on result I need to show particular column which values are different
         select * from (
         select a.DEPENDENT_NO,
         LAG(a.DEPENDENT_NO, 1, a.DEPENDENT_NO) OVER ( ORDER BY a.cust_id asc ) as  old_DEPENDENT_NO,
         a.EDUCATION_ID,
         LAG(a.EDUCATION_ID, 1, a.EDUCATION_ID) OVER ( ORDER BY a.cust_id ) as  old_EDUCATION_ID,
         ETHNIC_GRP_ID,
         LAG(a.ETHNIC_GRP_ID, 1, a.ETHNIC_GRP_ID) OVER ( ORDER BY a.cust_id ) as  old_ETHNIC_GRP_ID,
         MARITAL_ST,
         LAG(a.MARITAL_ST, 1, a.MARITAL_ST) OVER ( ORDER BY a.cust_id ) as  old_MARITAL_ST,
         MOTHERS_MAIDEN_NM,
         LAG(a.MOTHERS_MAIDEN_NM, 1, a.MOTHERS_MAIDEN_NM) OVER ( ORDER BY a.cust_id ) as  old_MOTHERS_MAIDEN_NM,
         SPOUSE_NAME,
         LAG(a.SPOUSE_NAME, 1, a.SPOUSE_NAME) OVER ( ORDER BY a.cust_id ) as  old_SPOUSE_NAME,
         OCCUPATION_ID,
          LAG(a.OCCUPATION_ID, 1, a.OCCUPATION_ID) OVER ( ORDER BY a.cust_id ) as  old_OCCUPATION_ID,
          PROF_QUAL_ID,
          LAG(a.PROF_QUAL_ID, 1, a.PROF_QUAL_ID) OVER ( ORDER BY a.cust_id ) as  old_PROF_QUAL_ID,
          GRAND_FATHERS_NM,
          LAG(a.GRAND_FATHERS_NM, 1, a.GRAND_FATHERS_NM) OVER ( ORDER BY a.cust_id ) as  old_GRAND_FATHERS_NM,
          EMP_ST,
          LAG(a.EMP_ST, 1, a.EMP_ST) OVER ( ORDER BY a.cust_id ) as  old_EMP_ST
                from (
           SELECT CUST_ID,row_ts,EDUCATION_ID ,
                ETHNIC_GRP_ID , 
            
           MARITAL_ST,DEPENDENT_NO,MOTHERS_MAIDEN_NM,
                          SPOUSE_NAME,OCCUPATION_ID,PROF_QUAL_ID,GRAND_FATHERS_NM,EMP_ST,CNTRY_OF_BIRTH_ID,
                          RELIGION_ID,FATHERS_NM FROM CUST_PERSONAL_INFO where rec_st = 'S' AND CUST_ID = 112
                          UNION
           SELECT CUST_ID,null,EDUCATION_ID,ETHNIC_GRP_ID,MARITAL_ST,DEPENDENT_NO,MOTHERS_MAIDEN_NM,
                          SPOUSE_NAME,OCCUPATION_ID,PROF_QUAL_ID,GRAND_FATHERS_NM,EMP_ST,CNTRY_OF_BIRTH_ID,
                          RELIGION_ID,FATHERS_NM FROM PERSON WHERE CUST_ID = 112) a) b
           where b.DEPENDENT_NO<>b.old_DEPENDENT_NO or b.EMP_ST<>b.old_EMP_ST                 
                          ;
      result:
      DEPENDENT_NO     OLD_DEPENDENT_NO     EDUCATION_ID     OLD_EDUCATION_ID     ETHNIC_GRP_ID     OLD_ETHNIC_GRP_ID     EMP_ST     OLD_EMP_ST
      5                     4                             353                     353                             223                      223                             U               E
      excepted result:

      I need to display only when there changes in new value & old value.. If there is no difference then I no those column to be displayed

      excepted output from above example
      DEPENDENT_NO     OLD_DEPENDENT_NO       EMP_ST     OLD_EMP_ST
      5                     4                         U               E
      Kindly let me know your suggestion & comments to view the excepted result.

      Thanks & Regards
      Sami.
        • 1. Re: Hide column based on values
          BluShadow
          Could you post some example input data along with the output so we can see exactly what it is you're trying to achieve.

          From what you posted it looks as though you want just the columns that have changed to come out of the query. That won't be possible without dynamic SQL, because SQL projection requires the columns to be known before any data is actually queried.

          Edit to add:...
          Although the following article talks about pivoting of data, the same principle still applies...
          {thread:id=2309172}

          Edited by: BluShadow on 05-Feb-2013 11:29
          • 2. Re: Hide column based on values
            BluShadow
            Tidied up the query a bit so I can read it...
            select * from (select a.DEPENDENT_NO,
                                  LAG(a.DEPENDENT_NO, 1, a.DEPENDENT_NO) OVER (ORDER BY a.cust_id asc) as old_DEPENDENT_NO,
                                  a.EDUCATION_ID,
                                  LAG(a.EDUCATION_ID, 1, a.EDUCATION_ID) OVER (ORDER BY a.cust_id) as old_EDUCATION_ID,
                                  ETHNIC_GRP_ID,
                                  LAG(a.ETHNIC_GRP_ID, 1, a.ETHNIC_GRP_ID) OVER (ORDER BY a.cust_id) as  old_ETHNIC_GRP_ID,
                                  MARITAL_ST,
                                  LAG(a.MARITAL_ST, 1, a.MARITAL_ST) OVER (ORDER BY a.cust_id) as  old_MARITAL_ST,
                                  MOTHERS_MAIDEN_NM,
                                  LAG(a.MOTHERS_MAIDEN_NM, 1, a.MOTHERS_MAIDEN_NM) OVER (ORDER BY a.cust_id) as old_MOTHERS_MAIDEN_NM,
                                  SPOUSE_NAME,
                                  LAG(a.SPOUSE_NAME, 1, a.SPOUSE_NAME) OVER (ORDER BY a.cust_id) as old_SPOUSE_NAME,
                                  OCCUPATION_ID,
                                  LAG(a.OCCUPATION_ID, 1, a.OCCUPATION_ID) OVER (ORDER BY a.cust_id) as old_OCCUPATION_ID,
                                  PROF_QUAL_ID,
                                  LAG(a.PROF_QUAL_ID, 1, a.PROF_QUAL_ID) OVER (ORDER BY a.cust_id) as old_PROF_QUAL_ID,
                                  GRAND_FATHERS_NM,
                                  LAG(a.GRAND_FATHERS_NM, 1, a.GRAND_FATHERS_NM) OVER (ORDER BY a.cust_id) as old_GRAND_FATHERS_NM,
                                  EMP_ST,
                                  LAG(a.EMP_ST, 1, a.EMP_ST) OVER (ORDER BY a.cust_id) as old_EMP_ST
                           from (SELECT CUST_ID
                                       ,row_ts
                                       ,EDUCATION_ID
                                       ,ETHNIC_GRP_ID
                                       ,MARITAL_ST
                                       ,DEPENDENT_NO
                                       ,MOTHERS_MAIDEN_NM
                                       ,SPOUSE_NAME
                                       ,OCCUPATION_ID
                                       ,PROF_QUAL_ID
                                       ,GRAND_FATHERS_NM
                                       ,EMP_ST
                                       ,CNTRY_OF_BIRTH_ID
                                       ,RELIGION_ID
                                       ,FATHERS_NM
                                 FROM   CUST_PERSONAL_INFO
                                 where  rec_st = 'S'
                                 AND    CUST_ID = 112
                                 UNION
                                 SELECT CUST_ID
                                       ,null
                                       ,EDUCATION_ID
                                       ,ETHNIC_GRP_ID
                                       ,MARITAL_ST
                                       ,DEPENDENT_NO
                                       ,MOTHERS_MAIDEN_NM
                                       ,SPOUSE_NAME
                                       ,OCCUPATION_ID
                                       ,PROF_QUAL_ID
                                       ,GRAND_FATHERS_NM
                                       ,EMP_ST
                                       ,CNTRY_OF_BIRTH_ID
                                       ,RELIGION_ID
                                       ,FATHERS_NM
                                 FROM   PERSON
                                 WHERE  CUST_ID = 112
                                ) a
                          ) b
            where b.DEPENDENT_NO != b.old_DEPENDENT_NO
            or    b.EMP_ST != b.old_EMP_ST
            ;
            Not sure how you are expecting your LAG analytical functions to work correctly. Although they will pick up where there is a 'difference' in the values, the "new" and "old" are not determined correctly as there is nothing in the ordering to determine which of the two records for a cust_id is the "new" and which is the "old".
            • 3. Re: Hide column based on values
              Burasami
              Hi Blu,

              Thanks for your reply.
              the "new" and "old" are not determined correctly as there is nothing in the ordering to determine which of the two records for a cust_id is the "new" and which is the "old".
              I need the difference for only for few columns and also cust_id will always have the same value in every row.

              Inner SQL in the from caluse with alias name 'a' with union always return 2 rows.


              (SELECT CUST_ID
                                         ,row_ts
                                         ,EDUCATION_ID
                                         ,ETHNIC_GRP_ID
                                         ,MARITAL_ST
                                         ,DEPENDENT_NO
                                         ,MOTHERS_MAIDEN_NM
                                         ,SPOUSE_NAME
                                         ,OCCUPATION_ID
                                         ,PROF_QUAL_ID
                                         ,GRAND_FATHERS_NM
                                         ,EMP_ST
                                         ,CNTRY_OF_BIRTH_ID
                                         ,RELIGION_ID
                                         ,FATHERS_NM
                                   FROM   CUST_PERSONAL_INFO
                                   where  rec_st = 'S'
                                   AND    CUST_ID = 112
                                   UNION
                                   SELECT CUST_ID
                                         ,null
                                         ,EDUCATION_ID
                                         ,ETHNIC_GRP_ID
                                         ,MARITAL_ST
                                         ,DEPENDENT_NO
                                         ,MOTHERS_MAIDEN_NM
                                         ,SPOUSE_NAME
                                         ,OCCUPATION_ID
                                         ,PROF_QUAL_ID
                                         ,GRAND_FATHERS_NM
                                         ,EMP_ST
                                         ,CNTRY_OF_BIRTH_ID
                                         ,RELIGION_ID
                                         ,FATHERS_NM
                                   FROM   PERSON
                                   WHERE  CUST_ID = 112
                                  ) a
              from the result I need to find old(previous row) and new (current row) value of column by using LAG function.
              select a.DEPENDENT_NO,
                                    LAG(a.DEPENDENT_NO, 1, a.DEPENDENT_NO) OVER (ORDER BY a.cust_id asc) as old_DEPENDENT_NO,
                                    a.EDUCATION_ID,
                                    LAG(a.EDUCATION_ID, 1, a.EDUCATION_ID) OVER (ORDER BY a.cust_id) as old_EDUCATION_ID,
                                    ETHNIC_GRP_ID,
                                    LAG(a.ETHNIC_GRP_ID, 1, a.ETHNIC_GRP_ID) OVER (ORDER BY a.cust_id) as  old_ETHNIC_GRP_ID,
                                    MARITAL_ST,
                                    LAG(a.MARITAL_ST, 1, a.MARITAL_ST) OVER (ORDER BY a.cust_id) as  old_MARITAL_ST,
                                    MOTHERS_MAIDEN_NM,
                                    LAG(a.MOTHERS_MAIDEN_NM, 1, a.MOTHERS_MAIDEN_NM) OVER (ORDER BY a.cust_id) as old_MOTHERS_MAIDEN_NM,
                                    SPOUSE_NAME,
                                    LAG(a.SPOUSE_NAME, 1, a.SPOUSE_NAME) OVER (ORDER BY a.cust_id) as old_SPOUSE_NAME,
                                    OCCUPATION_ID,
                                    LAG(a.OCCUPATION_ID, 1, a.OCCUPATION_ID) OVER (ORDER BY a.cust_id) as old_OCCUPATION_ID,
                                    PROF_QUAL_ID,
                                    LAG(a.PROF_QUAL_ID, 1, a.PROF_QUAL_ID) OVER (ORDER BY a.cust_id) as old_PROF_QUAL_ID,
                                    GRAND_FATHERS_NM,
                                    LAG(a.GRAND_FATHERS_NM, 1, a.GRAND_FATHERS_NM) OVER (ORDER BY a.cust_id) as old_GRAND_FATHERS_NM,
                                    EMP_ST,
                                    LAG(a.EMP_ST, 1, a.EMP_ST) OVER (ORDER BY a.cust_id) as old_EMP_ST
                             from (SELECT CUST_ID
                                         ,row_ts
                                         ,EDUCATION_ID
                                         ,ETHNIC_GRP_ID
                                         ,MARITAL_ST
                                         ,DEPENDENT_NO
                                         ,MOTHERS_MAIDEN_NM
                                         ,SPOUSE_NAME
                                         ,OCCUPATION_ID
                                         ,PROF_QUAL_ID
                                         ,GRAND_FATHERS_NM
                                         ,EMP_ST
                                         ,CNTRY_OF_BIRTH_ID
                                         ,RELIGION_ID
                                         ,FATHERS_NM
                                   FROM   CUST_PERSONAL_INFO
                                   where  rec_st = 'S'
                                   AND    CUST_ID = 112
                                   UNION
                                   SELECT CUST_ID
                                         ,null
                                         ,EDUCATION_ID
                                         ,ETHNIC_GRP_ID
                                         ,MARITAL_ST
                                         ,DEPENDENT_NO
                                         ,MOTHERS_MAIDEN_NM
                                         ,SPOUSE_NAME
                                         ,OCCUPATION_ID
                                         ,PROF_QUAL_ID
                                         ,GRAND_FATHERS_NM
                                         ,EMP_ST
                                         ,CNTRY_OF_BIRTH_ID
                                         ,RELIGION_ID
                                         ,FATHERS_NM
                                   FROM   PERSON
                                   WHERE  CUST_ID = 112
                                  ) a
                            ) b
              how to form the excepted output from above SQL.

              Pls refer the excepted output from previous post.

              Thanks & Regards
              Sami
              • 4. Re: Hide column based on values
                BluShadow
                Sami wrote:
                Hi Blu,

                Thanks for your reply.
                the "new" and "old" are not determined correctly as there is nothing in the ordering to determine which of the two records for a cust_id is the "new" and which is the "old".
                I need the difference for only for few columns and also cust_id will always have the same value in every row.
                Yes, I understood that, but SQL projection will not let you dynamically query a different number of columns using static SQL statements. That's the way SQL is designed. You cannot say "return me all these columns, but exclude any columns that don't have a value in them". SQL projection means that the cursor (all queries are cursors) needs to know the number of columns, their names and their datatypes before any data is actually fetched through the cursor. You cannot change the number of columns once the cursor starts fetching data.

                SQL is the wrong tool to try and do that. You need to use SQL to fetch all the possible columns and then use other code to only 'display' those that you want to display based on whatever conditions you have (in your case you just want to display the ones that have changed)
                Inner SQL in the from caluse with alias name 'a' with union always return 2 rows.
                That's good, at least you have a known factor in your query.
                (SELECT CUST_ID
                ,row_ts
                ,EDUCATION_ID
                ,ETHNIC_GRP_ID
                ,MARITAL_ST
                ,DEPENDENT_NO
                ,MOTHERS_MAIDEN_NM
                ,SPOUSE_NAME
                ,OCCUPATION_ID
                ,PROF_QUAL_ID
                ,GRAND_FATHERS_NM
                ,EMP_ST
                ,CNTRY_OF_BIRTH_ID
                ,RELIGION_ID
                ,FATHERS_NM
                FROM   CUST_PERSONAL_INFO
                where  rec_st = 'S'
                AND    CUST_ID = 112
                UNION
                SELECT CUST_ID
                ,null
                ,EDUCATION_ID
                ,ETHNIC_GRP_ID
                ,MARITAL_ST
                ,DEPENDENT_NO
                ,MOTHERS_MAIDEN_NM
                ,SPOUSE_NAME
                ,OCCUPATION_ID
                ,PROF_QUAL_ID
                ,GRAND_FATHERS_NM
                ,EMP_ST
                ,CNTRY_OF_BIRTH_ID
                ,RELIGION_ID
                ,FATHERS_NM
                FROM   PERSON
                WHERE  CUST_ID = 112
                ) a
                So, which of those two unioned queries is providing the "new" record and which is providing the "old" record. How is the outer query that is fetching the 2 rows supposed to identify which is the "new" and which is the "old"?

                >
                from the result I need to find old(previous row) and new (current row) value of column by using LAG function.
                If the outer query can identify which of the two rows is "new" and which is "old" then the LAG function can be used. At the moment you are only ordering within your LAG function based on the cust_id, so you could get either of the two rows first and either of the two rows second.

                If you know, for example that "old" records are the ones coming from the CUST_PERSONAL_INFO table, and the "new" records are coming from the PERSON table, then you should change your query so that it's not UNIONing the rows together, but rather JOINing the rows together. That way you won't need to use the LAG function at all...
                SELECT c.CUST_ID
                      ,c.row_ts
                      ,case when c.dependent_no != p.dependent_no then p.dependent_no else null end as new_dependent_no
                      ,case when c.education_id != p.education_id then p.education_id else null end as new_education_id
                      ,case when c.ethnic_grp_id != p.ethnic_grp_id then p.ethnic_grp_id else null end as new_ethnic_grp_id
                      ,case when c.marital_st != p.marital_st then p.marital_st else null end as new_marital_st
                      ,case when c.mothers_maiden_nm != p.mothers_maiden_nm then p.mothers_maiden_nm else null end as new_mothers_maiden_nm
                      ,case when c.spouse_name != p.spouse_name then p.spouse_name else null end as new_spouse_name
                      ,case when c.occupation_id != p.occupation_id then p.occupation_id else null end as new_occupation_id
                      ,case when c.prof_qual_id != p.prof_qual_id then p.prof_qual_id else null end as new_prof_qual_id
                      ,case when c.grand_fathers_nm != p.grand_fathers_nm the p.grand_fathers_nm else null end as new_grand_fathers_nm
                      ,case when c.emp_st != p.emp_st then p.emp_st else null end as new_emp_st
                      ,case when c.cntry_of_birth_id != p.cntry_of_birth_id then p.cntry_of_birth_id else null end as new_cntry_of_birth_id
                      ,case when c.religion_id != p.religion_id then p.religion_id else null end as new_religion_id
                      ,case when c.fathers_nm != p.fathers_nm then p.fathers_nm else null end as new_fathers_nm
                FROM   CUST_PERSONAL_INFO c
                       join PERSON p on (p.cust_id = c.cust_id)
                where  c.rec_st = 'S'
                AND    c.CUST_ID = 112
                This still won't reduce the projection of the SQL just to those columns that have changed, but it certainly simplifies the testing of old an new and removes the ambiguity of what is "old" and what is "new".