Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    14

    Unanswered: Stored procedure with only an insert

    Hello all,

    I hope you can help me with this problem on which i spend already several hours

    this is the stored procedure i want to create:

    Code:
    create or replace
    PROCEDURE ALGORITHM_PROTOTYPE( mood IN NUMBER) IS
    BEGIN
      with a as(
      select track_id,loved from tracks_moods where mood_id=mood and listened=1
      ), tagscores as(
      select tt.tag_id, sum(case tm.loved when 1 then 0 when 0 then 5 else 1 end) score from tags_tracks tt join a tm on tm.track_id = tt.track_id group by tt.tag_id
      ), rankings as(
      select rank() over(order by score,tag_id) ranking, tag_id, score from tagscores
      ), toptags as(
      select tag_id, ranking from rankings where ranking<=10
      )
      insert into tracks_moods(mood_id,track_id)
      select track_id,mood
      from( --voor random te maken moet dit in een apparte tabel
      select tt.track_id
      from toptags top
      join tags_tracks tt on tt.tag_id = top.tag_id
      where tt.track_id not in(select track_id from tracks_moods where mood_id=mood)
      order by dbms_random.value
      )
      where rownum<=10;
    END ALGORITHM_PROTOTYPE;
    the error i get is this:
    Error(13,3): PL/SQL: ORA-00928: missing SELECT keyword

    what i want is the output from the last select to store into the table tracks_moods

    i already tested the whole select (with cte's) and it gives me the right records. so the problem lays somewhere within the insert or using it within a stored procedure

    thanks in advance,
    Chielus

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You cannot use CTEs with UPDATE or INSERT.

  3. #3
    Join Date
    Mar 2010
    Posts
    14
    i just found that out on myself, the insert must be before the with

    Code:
    create or replace
    PROCEDURE ALGORITHM_PROTOTYPE( mood IN NUMBER) IS
    BEGIN
      insert into tracks_moods(mood_id,track_id)
      with a as(
      select track_id,loved from tracks_moods where mood_id=mood and listened=1
      ), tagscores as(
      select tt.tag_id, sum(case tm.loved when 1 then 0 when 0 then 5 else 1 end) score from tags_tracks tt join a tm on tm.track_id = tt.track_id group by tt.tag_id
      ), rankings as(
      select rank() over(order by score,tag_id) ranking, tag_id, score from tagscores
      ), toptags as(
      select tag_id, ranking from rankings where ranking<=10
      )
      select track_id,mood
      from( --voor random te maken moet dit in een apparte tabel
      select tt.track_id
      from toptags top
      join tags_tracks tt on tt.tag_id = top.tag_id
      where tt.track_id not in(select track_id from tracks_moods where mood_id=mood)
      order by dbms_random.value
      )
      where rownum<=10;
    END ALGORITHM_PROTOTYPE;
    thanks anyway!!

    (clicked some ads )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •