6 Replies Latest reply: May 3, 2013 7:41 AM by Peter vd Zwan RSS

    How to select specific columns from the table based on the source data

    851268
      Hello folks,

      I have a scenario where in I need to select specific columns to process based on the incoming source data. The reason behind to select specific columns is that I'm having over 40 different columns coming in from the source and 99.99% of the time only 5-6 of them are populated and in order to streamline the source data I have to apply many different functions, some case statements to get the source data in required form before loading into the target. The crude way (which I have done at present !) is to apply those complex functions on all the 40 columns but that is hitting the performance. As a result I need to select only the specific columns for processing. If you are thinking to just add a where condition that col1 is not null or col2 is not null .... and so on .... then it cant be done because I'm having different 5-6 columns populated for each type of data coming in from source.

      For example, in the below table t1 there are 10 columns and UID is type. We have 3 different types i.e. phy, chem and maths populated, but based on each individual type I am having specific cols populated. i.e. for
      Phy - f1, f2, f3
      Chem - f4, f5, f6
      Maths - f7, f8, f9
      create table t1
      (type varchar2(10),
      f1 varchar2(50),
      f2 varchar2(50),
      f3 varchar2(50),
      f4 varchar2(50),
      f5 varchar2(50),
      f6 varchar2(50),
      f7 varchar2(50),
      f8 varchar2(50),
      f9 varchar2(50),
      f10 varchar2(50));
      
      insert into t1 values ('PHY','1','1','1',,,,,,,,);
      insert into t1 values ('PHY','2','2','2',,,,,,,,);
      insert into t1 values ('PHY','3','3','3',,,,,,,,);
      insert into t1 values ('CHEM',,,,'1','1','1',,,,,);
      insert into t1 values ('CHEM',,,,'2','2','2',,,,,);
      insert into t1 values ('CHEM',,,,'3','3','3',,,,,);
      insert into t1 values ('MATHS',,,,,,,'1','1','1',,);
      insert into t1 values ('MATHS',,,,,,,'2','2','2',,);
      insert into t1 values ('MATHS',,,,,,,'3','3','3',,);
      Now, is there any way that I can select only populated cols based on each type and then once those columns are selected then I can pass them through those functions, case statements which eventually will run only for the populated cols (which would be 5-6 at max) instead of running them for all 40 different columns.

      Many Thanks.
        • 1. Re: How to select specific columns from the table based on the source data
          Manik
          The crude way (which I have done at present !) is to apply those complex functions on all the 40 columns but that is hitting the performance
          Just want to see those complex functions you are talking about..
          Just dont apply those complex functions if you have null as input to them. That can solve some purpose isnt it?

          Cheers,
          Manik.
          • 2. Re: How to select specific columns from the table based on the source data
            pollywog
            could you just make some views?
            create  or replace view phy as select f1,f2,f3 from t1;
            create or replace view chem as select f4,f5,f6 from t1;
            create or replace view maths as select f7,f8,f9 from t1;
            • 3. Re: How to select specific columns from the table based on the source data
              851268
              Manik

              The complex transformations are to streamline the source data. The incoming source data is a text field and based on the reference table I have to do some instr, substr and case statements to fetch the correct data based on the reference table from that text field. For example, you can think of a particular source column like 'A,B,C,D,E,F' (this is one of the f1, f2 ... cols which would be populated) and now based on the reference data I might just need only E to populate into my target. I hope you got some picture of it. In my crude way, the first thing which I am checking is if the column is NOT NULL and if it is NULL then don't do anything and skip to next column. The problem in that is I still have to write the logic throughout the 40 cols coming from the source (which is already implemented in my so called crude method!). And though majority of them are going to be NULL but still it has to traverse through each decode statement of all the 40 cols. So, I was just looking for a smarter way to identify the ONLY populated cols and then work on it.

              Thanks.
              • 4. Re: How to select specific columns from the table based on the source data
                BluShadow
                You cannot base an SQL projection on the data that will be fetched.

                {thread:id=2309172}
                • 5. Re: How to select specific columns from the table based on the source data
                  851268
                  Polly,

                  Thanks for the advise, but I cant do that as it will create so many views as per each type (which are over 100) and that wont be accepted from the maintenance purposes.

                  Thanks.
                  • 6. Re: How to select specific columns from the table based on the source data
                    Peter vd Zwan
                    Hi,

                    Do you want something like this?
                    create table t1
                    (type varchar2(10),
                    f1 varchar2(50),
                    f2 varchar2(50),
                    f3 varchar2(50),
                    f4 varchar2(50),
                    f5 varchar2(50),
                    f6 varchar2(50),
                    f7 varchar2(50),
                    f8 varchar2(50),
                    f9 varchar2(50),
                    f10 varchar2(50));
                     
                    insert into t1 values ('PHY'    ,'1'  ,'1'  ,'1'  ,null ,null ,null ,null ,null ,null ,null);
                    insert into t1 values ('PHY'    ,'2'  ,'2'  ,'2'  ,null ,null ,null ,null ,null ,null ,null);
                    insert into t1 values ('PHY'    ,'3'  ,'3'  ,'3'  ,null ,null ,null ,null ,null ,null ,null);
                    insert into t1 values ('CHEM'   ,null ,null ,null ,'1'  ,'1'  ,'1'  ,null ,null ,null ,null);
                    insert into t1 values ('CHEM'   ,null ,null ,null ,'2'  ,'2'  ,'2'  ,null ,null ,null ,null);
                    insert into t1 values ('CHEM'   ,null ,null ,null ,'3'  ,'3'  ,'3'  ,null ,null ,null ,null);
                    insert into t1 values ('MATHS'  ,null ,null ,null ,null ,null ,null ,'1'  ,'1'  ,'1'  ,null);
                    insert into t1 values ('MATHS'  ,null ,null ,null ,null ,null ,null ,'2'  ,'2'  ,'2'  ,null);
                    insert into t1 values ('MATHS'  ,null ,null ,null ,null ,null ,null ,'3'  ,'3'  ,'3'  ,null);
                    
                    commit;
                    
                    select
                      case type
                        when 'PHY'    then f1
                        when 'CHEM'   then f4
                        when 'MATHS'  then f7
                      end c1
                      ,case type
                        when 'PHY'    then f2
                        when 'CHEM'   then f5
                        when 'MATHS'  then f8
                      end c2
                      ,case type
                        when 'PHY'    then f3
                        when 'CHEM'   then f6
                        when 'MATHS'  then f9
                      end c3
                    
                    from
                      t1
                    ;
                    
                    C1                                                 C2                                                 C3                                               
                    -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
                    1                                                  1                                                  1                                                  
                    2                                                  2                                                  2                                                  
                    3                                                  3                                                  3                                                  
                    1                                                  1                                                  1                                                  
                    2                                                  2                                                  2                                                  
                    3                                                  3                                                  3                                                  
                    1                                                  1                                                  1                                                  
                    2                                                  2                                                  2                                                  
                    3                                                  3                                                  3                                                  
                    
                     9 rows selected 
                    Regards,

                    Peter