10 Replies Latest reply: Nov 1, 2010 7:42 AM by bigmac704 RSS

    Sql Coding Help

    bigmac704
      Hello All,
      I need help on getting the sql code.
      Here is how the example data is laid out in my data table called example.

      name     age     favorite colors
      john doe     15     red:blue:green

      Notice how the data is in column "favorite colors" they are set values selected from a multiple list.
      I need the sql to get it to display like this.

      name     age     favorite colors
      john doe     15     red
      john doe     15     blue
      john doe     15     green

      thanks
        • 1. Re: Sql Coding Help
          773431
          BigMac,

          I think you're going to have to do some PL/SQL to get this. APEX provides a function--apex_util.string_to_table--that can split that value out for you, but it returns as a vc_arr2, which isn't immediately useful.

          Here's something that should help.

          -David
          • 2. Re: Sql Coding Help
            20020
            Hi bigmac704,

            There may be other ways to do this, but one way is to use apex_util.string_to_table along with a pipelined function:

            1) Create a type to define your person-favorite color row:
            CREATE OR REPLACE TYPE  PERSON_COLOR as object (name varchar2(30), age number(3, 0), fav_color varchar2(10));
            2) Create a type to define a "table" of your person-favorite color rows:
            CREATE OR REPLACE TYPE  PERSON_COLOR_TBL AS TABLE OF person_color
            3) Create the following pipelined function:
            CREATE OR REPLACE FUNCTION get_person_colors RETURN person_color_tbl
            PIPELINED IS
              out_rec    person_color := person_color(null, null, null);
              l_vc_arr2  APEX_APPLICATION_GLOBAL.VC_ARR2;
            BEGIN
              FOR r IN (SELECT * FROM person_colors) LOOP
                out_rec.name := r.name;
                out_rec.age := r.age;
                l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(r.fav_colors);
                // for each color, pipe a row
                FOR i IN 1..l_vc_arr2.count LOOP
                  out_rec.fav_color := l_vc_arr2(i);
                  pipe row(out_rec);
                END LOOP;
              END LOOP;
              RETURN;
            END;
            Now you can use this pipelined function just as you would use a table:
            SELECT * FROM TABLE(get_person_colors);
            NAME AGE FAV_COLOR 
            john doe 15 red 
            john doe 15 blue 
            john doe 15 green 
            
            SELECT * FROM TABLE(get_person_colors) WHERE name = 'john doe';
            NAME AGE FAV_COLOR 
            john doe 15 red 
            john doe 15 blue 
            john doe 15 green 
            
            SELECT * FROM TABLE(get_person_colors) WHERE name = 'jim smith';
            no data found
            Hope this helps,
            John

            If you find this information useful, please remember to mark the post "helpful" or "correct" so that others may benefit as well.
            • 3. Re: Sql Coding Help
              bigmac704
              Thanks it worked 2 more question on this though. When the main table is updated with this automatically update this function as well?

              questions 2.
              If I get the reverse of the current question like if my data comes to me like so:
              NAME AGE FAV_COLOR
              john doe 15 red
              john doe 15 blue
              john doe 15 green


              is there a way to upload it into my main table like:
              name age Fav_color
              john doe 15 red:blue:green

              so that everything matches up correctly.
              because I am getting this both ways believe it or not.
              • 4. Re: Sql Coding Help
                fac586
                >
                Here is how the example data is laid out in my data table called example.

                name age favorite colors
                john doe 15 red:blue:green

                Notice how the data is in column "favorite colors" they are set values selected from a multiple list.
                I need the sql to get it to display like this.

                name age favorite colors
                john doe 15 red
                john doe 15 blue
                john doe 15 green
                >
                Storing data in a denormalized form like this usually leads to trouble. Rather than getting into complex programmatic solutions simply to retrieve data, why not use a properly normalized data model that eliminates the problem?
                SQL> create sequence subject_seq;
                
                Sequence created.
                
                SQL> create sequence color_seq;
                
                Sequence created.
                
                SQL> create table subjects (
                  2        subject_id    integer          primary key
                  3    , subject_name  varchar2(30)  not null
                  4    , age            number(3, 0)  not null);
                
                Table created.
                
                SQL> create table colors (
                  2        color_id    integer        primary key
                  3    , color_name  varchar2(20)  not null,
                  4    constraint color_name_uk unique (color_name));
                
                Table created.
                
                SQL> create table favorite_colors (
                  2        subject_id  integer   not null references subjects
                  3    , color_id    integer   not null references colors,
                  4    constraint fav_color_pk primary key (
                  5          subject_id
                  6        , color_id))
                  7  organization index;
                
                Table created.
                
                SQL> create index fav_color_ix2 on favorite_colors (
                  2        color_id
                  3    , subject_id);
                
                Index created.
                Deal with complexity&mdash;like processing multi-value select lists&mdash;in the bits of the application that lend themselves to it: PL/SQL processes; DB packages etc, using features like the aforementioned <tt>apex_util.string_to_table</tt>:
                SQL> declare
                  2  
                  3    l_sub_id     subjects.subject_id%type;
                  4  
                  5    procedure process_fav_colors (
                  6          p_subject_id  in subjects.subject_id%type
                  7        , p_colors_str  in varchar2)
                  8    is
                  9  
                 10        l_fav_colors  apex_application_global.vc_arr2;
                 11  
                 12    begin
                 13  
                 14        l_fav_colors := apex_util.string_to_table(p_colors_str);
                 15  
                 16        delete favorite_colors
                 17        where subject_id = p_subject_id;
                 18  
                 19        forall i in l_fav_colors.first()..l_fav_colors.last()
                 20          insert into favorite_colors
                 21            (subject_id, color_id)
                 22          select
                 23                 p_subject_id
                 24               , col.color_id
                 25          from
                 26                 colors col
                 27          where
                 28                 col.color_name = l_fav_colors(i);
                 29  
                 30    end process_fav_colors;
                 31  
                 32  begin
                 33  
                 34    insert into colors values (color_seq.nextval, 'red');
                 35    insert into colors values (color_seq.nextval, 'green');
                 36    insert into colors values (color_seq.nextval, 'blue');
                 37    insert into colors values (color_seq.nextval, 'rose');
                 38    insert into colors values (color_seq.nextval, 'black');
                 39    insert into colors values (color_seq.nextval, 'white');
                 40  
                 41    insert into subjects
                 42        (subject_id, subject_name, age)
                 43    values
                 44        (subject_seq.nextval, 'john doe', 15)
                 45    returning
                 46        subject_id into l_sub_id;
                 47  
                 48    process_fav_colors(l_sub_id, 'red:green:blue');
                 49  
                 50    insert into subjects
                 51        (subject_id, subject_name, age)
                 52    values
                 53        (subject_seq.nextval, 'man ray', 86)
                 54    returning
                 55        subject_id into l_sub_id;
                 56  
                 57    process_fav_colors(l_sub_id, 'black:white');
                 58  
                 59    insert into subjects
                 60        (subject_id, subject_name, age)
                 61    values
                 62        (subject_seq.nextval, 'pablo picasso', 91)
                 63    returning
                 64        subject_id into l_sub_id;
                 65  
                 66    process_fav_colors(l_sub_id, 'blue:rose');
                 67  
                 68  end;
                 69  /
                
                PL/SQL procedure successfully completed.
                Then getting the data in the required form is trivial:
                SQL> select
                  2              sub.subject_name
                  3            , sub.age
                  4            , col.color_name
                  5  from
                  6              subjects sub
                  7            , favorite_colors fc
                  8            , colors col
                  9  where
                 10              sub.subject_id = fc.subject_id
                 11  and       fc.color_id = col.color_id
                 12  order by
                 13              sub.subject_name
                 14            , col.color_name;
                
                SUBJECT_NAME                     AGE COLOR_NAME
                ------------------------------ ---------- --------------------
                john doe                      15 blue
                john doe                      15 green
                john doe                      15 red
                man ray                       86 black
                man ray                       86 white
                pablo picasso                      91 blue
                pablo picasso                      91 rose
                And what if there's a requirement to query and analyze the data? Answer questions like: Who likes blue? Or How many subjects have more than 2 favorite colors?
                SQL> select
                  2              sub.subject_name
                  3  from
                  4              subjects sub
                  5            , favorite_colors fc
                  6            , colors col
                  7  where
                  8              sub.subject_id = fc.subject_id
                  9  and       fc.color_id = col.color_id
                 10  and       col.color_name = 'blue';
                
                SUBJECT_NAME
                ------------------------------
                john doe
                pablo picasso
                
                SQL> select
                  2              count(*)
                  3  from
                  4              (select
                  5                  count(*)
                  6              from
                  7                  subjects sub
                  8                   , favorite_colors fc
                  9                   , colors col
                 10              where
                 11                  sub.subject_id = fc.subject_id
                 12              and      fc.color_id = col.color_id
                 13              group by
                 14                  sub.subject_id
                 15                   , sub.subject_name
                 16              having
                 17                  count(*) > 2);
                
                  COUNT(*)
                ----------
                      1
                This is impossible to do simply and efficiently with the original model.
                • 5. Re: Sql Coding Help
                  jhammer
                  How would you handle that if p_colors_str were a clob?
                  • 6. Re: Sql Coding Help
                    20020
                    Hi bigmac704,

                    I'm glad to hear that worked for you. Pipelined functions can be extremely useful especially for times when what you need is a "view" but with logic that can't be expressed with plain SQL - definitely worth looking into further.

                    Question 1 - yes - this is just a function reading your data

                    Question 2 - there are ways to do this via a PL/SQL program or perhaps by looking into Oracle SQL's "connect by" and "rollup" for "flattening" query results. But I'd suggest seriously considering faq586's suggestion to normalize your favorite colors to a separate table. I think you'll be happier with the design down the road.

                    Hope this helps,
                    John

                    If you find this information useful, please remember to mark the post "helpful" or "correct" so that others may benefit as well.
                    • 7. Re: Sql Coding Help
                      bigmac704
                      I got this to work for a different table but I not get this to work for another table I have. Here is what I have done

                      step 1>
                      CREATE OR REPLACE TYPE "ACCOM_TYPE" as object (record_number number,school_code number(6,0),student_id number(9,0),last_name VARCHAR2(30),first_name VARCHAR2(30),grade VARCHAR2(2),plan_type VARCHAR2(6),test_code varchar2(5),can_or_oth_description varchar2(60),
                      start_date DATE,end_date DATE,accommodation VARCHAR2(25))
                      /

                      step 2>
                      CREATE OR REPLACE TYPE "ACCOM_TYPE_TBL" AS TABLE OF accom_type
                      /

                      step 3>
                      create or replace FUNCTION get_accom_type RETURN accom_type_tbl
                      PIPELINED IS
                      out_rec accom_type := accom_type(null,null,null);
                      l_vc_arr2 APEX_APPLICATION_GLOBAL.VC_ARR2;
                      BEGIN
                      FOR r IN (SELECT * FROM "new_accommodations") LOOP
                      out_rec.record_number := r.record_number;
                      out_rec.year := r.year;
                      out_rec.school_code := r.school_code;
                      out_rec.student_id := r.student_id;
                      out_rec.last_name := r.last_name;
                      out_rec.grade := r.grade;
                      out_rec.plan_type := r.plan_type;
                      out_rec.test_code := r.test_code;
                      out_rec.can_or_oth_description := r.can_or_oth_description;
                      out_rec.start_date := r.start_date;
                      out_rec.end_date := r.end_date;
                      l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(r.accommodation);
                      FOR i IN 1..l_vc_arr2.count LOOP
                      out_rec.accommodation := l_vc_arr2(i);
                      pipe row(out_rec);
                      END LOOP;
                      END LOOP;
                      RETURN;
                      END;

                      I keep getting this error when I run the or create the function in step 3.
                      PLS-00306: wrong number or types of arguments in call to 'ACCOM_TYPE'Compilation failed,line 3 (11:22:11)

                      can anyone help thanks.
                      • 8. Re: Sql Coding Help
                        515357
                        The issue is with the statement:

                        out_rec accom_type := accom_type(null,null,null);

                        You have included null three times. But, accom_type has 12 columns. Hence, if you include 9 more "null", it should work.

                        out_rec accom_type := accom_type(null,null,null,null,null,null,null,null,null,null,null,null);

                        Ravi
                        • 9. Re: Sql Coding Help
                          bigmac704
                          I tried using 12 nulls but I get these errors for some reason.
                          Compilation failed,line 6 (08:17:37)
                          PL/SQL: ORA-00942: table or view does not existCompilation failed,line 6 (08:17:37)
                          PL/SQL: SQL Statement ignoredCompilation failed,line 7 (08:17:37)
                          PLS-00364: loop index variable 'R' use is invalidCompilation failed,line 7 (08:17:37)
                          PL/SQL: Statement ignoredCompilation failed,line 8 (08:17:37)
                          PLS-00302: component 'YEAR' must be declaredCompilation failed,line 8 (08:17:37)
                          PL/SQL: Statement ignoredCompilation failed,line 9 (08:17:37)
                          PLS-00364: loop index variable 'R' use is invalidCompilation failed,line 9 (08:17:37)
                          PL/SQL: Statement ignoredCompilation failed,line 10 (08:17:37)
                          PLS-00364: loop index variable 'R' use is invalidCompilation failed,line 10 (08:17:37)
                          PL/SQL: Statement ignoredCompilation failed,line 11 (08:17:37)
                          PLS-00364: loop index variable 'R' use is invalidCompilation failed,line 11 (08:17:37)
                          PL/SQL: Statement ignoredCompilation failed,line 12 (08:17:37)
                          PLS-00364: loop index variable 'R' use is invalidCompilation failed,line 12 (08:17:37)
                          PL/SQL: Statement ignoredCompilation failed,line 13 (08:17:37)
                          PLS-00364: loop index variable 'R' use is invalidCompilation failed,line 13 (08:17:37)
                          PL/SQL: Statement ignoredCompilation failed,line 14 (08:17:37)
                          PLS-00364: loop index variable 'R' use is invalidCompilation failed,line 14 (08:17:37)
                          PL/SQL: Statement ignoredCompilation failed,line 15 (08:17:37)
                          PLS-00364: loop index variable 'R' use is invalidCompilation failed,line 15 (08:17:37)
                          PL/SQL: Statement ignored
                          • 10. Re: Sql Coding Help
                            bigmac704
                            I got it working thanks. I had to put 12 nulls like you said and put my talbe name in all caps because that is the way it is in the table title.