This discussion is archived
10 Replies Latest reply: Nov 1, 2010 5:42 AM by 762641 RSS

Sql Coding Help

762641 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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
    762641 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    How would you handle that if p_colors_str were a clob?
  • 6. Re: Sql Coding Help
    20020 Pro
    Currently Being Moderated
    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
    762641 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    762641 Newbie
    Currently Being Moderated
    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
    762641 Newbie
    Currently Being Moderated
    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.

Legend

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