This discussion is archived
6 Replies Latest reply: May 3, 2013 5:41 AM by Peter vd Zwan RSS

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

851268 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points