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

    Unanswered: how to make a CTE with randomly picked rows self-joinable?

    Hi all,

    I have a CTE "tracks2" with randomly picked rows out of a table

    If i run this query in SqlDeveloper it gives me the right rows, if I do it however in ADO.NET for filling a DataSet it gives me 0 rows

    Code:
    CREATE OR REPLACE PACKAGE FETCH_HISTORY AS 
    	TYPE t_cursor IS REF CURSOR ; 
    	Procedure open_join_cursor1 (mood_id_ IN NUMBER, io_cursor IN OUT t_cursor); 
    END FETCH_HISTORY;
    /
    CREATE OR REPLACE PACKAGE BODY FETCH_HISTORY AS
    Procedure open_join_cursor1 (mood_id_ IN NUMBER, io_cursor IN OUT t_cursor) 
    IS 
    	v_cursor t_cursor; 
      seed BINARY_INTEGER;
    BEGIN 
    	IF mood_id_ is not null 
            THEN
              CURSOR random_tracks IS
                 OPEN v_cursor FOR
                  with tracks2 as(
                  select track_id, -1 as loved
                  from (select id as track_id from tracks where id not in (select track_id from tracks_moods where mood_id = 149) order by dbms_random.value)
                  where rownum<=10
    ),
                 withtags as(
                    select tag_id, loved, t.track_id
                    from tracks2 t
                    join tags_tracks tt on t.track_id=tt.track_id
                 )
                  select w1.tag_id as node1, w2.tag_id as node2, w1.track_id as track_id, w1.loved as loved
                  from withtags w1
                  join withtags w2 on w1.tag_id<w2.tag_id and w1.track_id = w2.track_id
                  END IF;
    	io_cursor := v_cursor; 
    END open_join_cursor1; 
    END FETCH_HISTORY;
    Last edited by Chielus; 05-14-10 at 12:54.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    When all else fails Read The Fine Manual

    SELECT
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2010
    Posts
    14
    you referred to the sample_clause

    The CTE "tracks2" is now without dbms_random, but with the sample_clause:

    Code:
    with tracks2 as(
                  select id as track_id, -1 as loved
                  from tracks
                  sample(0.12)
                  where id not in(select track_id from tracks_moods where mood_id=149)
                 )
    However, this gives the same result. I got the right rows within SqlDeveloper, but 0 rows in my DataSet...

    any other suggestions?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Chielus View Post
    However, this gives the same result. I got the right rows within SqlDeveloper, but 0 rows in my DataSet...

    any other suggestions?
    Are you sure you are connecting to the same database using the same user?
    In most of the cases, when the program does something different compared to "pure" SQL it turns out to be a "wrong" connection.

    Are you handling all errors properly in your code?
    Could it be that the code throws an error but you are swallowing the excpetion?

  5. #5
    Join Date
    Mar 2010
    Posts
    14
    I don't catch any exception.

    It's the same user and tablespace.

    Could it be that the query optimizer fixes something in sqldeveloper, but not when i access it with ADO.NET?

    If i remove the inner join like this:

    Code:
    select w1.tag_id as node1, w1.track_id as track_id, w1.loved as loved
                  from withtags w1
    then the results are OK, also within the DataSet. So it must be something with that join i think

  6. #6
    Join Date
    Mar 2010
    Posts
    14
    I want to test if the random CTE is the problem with a temporary table. Temporary tables are new for me, can somebody help me with it:

    Code:
    CREATE OR REPLACE PACKAGE FETCH_HISTORY AS 
    	TYPE t_cursor IS REF CURSOR ; 
    	Procedure open_join_cursor1 (mood_id_ IN NUMBER, io_cursor IN OUT t_cursor); 
    END FETCH_HISTORY;
    /
    CREATE OR REPLACE PACKAGE BODY FETCH_HISTORY AS
    Procedure open_join_cursor1 (mood_id_ IN NUMBER, io_cursor IN OUT t_cursor) 
    IS 
    	v_cursor t_cursor;
      sqlstr VARCHAR2(2000);
    BEGIN
      sqlstr:='CREATE GLOBAL TEMPORARY TABLE random_tracks AS select id as track_id, -1 as loved from tracks sample(0.12) where id not in(select track_id from tracks_moods where mood_id=149)';
    	IF mood_id_ is not null 
            THEN
            execute immediate sqlstr;
                 open v_cursor for
                 with withtags as(
                    select tag_id, loved, t.track_id
                    from random_tracks t
                    join tags_tracks tt on t.track_id=tt.track_id
                 )
                 ,b as(
                    select w1.tag_id as node1, w2.tag_id as node2, w1.track_id as track_id, w1.loved as loved
                    from withtags w1
                    inner join withtags w2 on w1.tag_id!=w2.tag_id and w1.track_id = w2.track_id
                  )
                  select node1,node2,cast(sum(case loved when -1 then 2 when 1 then 0 when 0 then 5 else 1 end)/count(1) as number(9,7)) score
                  from b
                  group by node1,node2;
                  execute immediate 'drop table random_tracks';
            END IF;
    	io_cursor := v_cursor; 
    END open_join_cursor1; 
    END FETCH_HISTORY;
    /
    Error: Table or view doesn't exist (so the temporary table hasn't been created)

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    When you start with the wrong question, no matter how good an answer you get, it won't matter very much.

    Rather than us trying to debug code when you are not showing us the whole picture,
    perhaps you could tell use the actual business problem you are trying to solve.
    Perhaps the current implementation is suboptimal & an easier solution exists.

    Or you can continue to dig your hole.
    It is your choice.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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