Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2011
    Posts
    13

    Unhappy Unanswered: using cursor with "WITH CLAUSE" in pro*c program

    When i declare a cursor as follows along with 'with clause' ---

    EXEC SQL DECLARE PUBS CURSOR FOR
    WITH tmp AS (SELECT distinct pub.PUB_IDT IDT,edt.pub_mth
    FROM PRMSELINCPUBT pub,prmeditiot edt
    WHERE pub.PRM_IDT = 1306 and
    pub.CHP_NUM = 1 and pub.pub_idt=edt.pub_idt
    and edt.pub_mth is not null
    order by edt.pub_mth asc)
    SELECT distinct tmp.idt FROM tmp;

    it results in error like

    PCC-S-02201, Encountered the symbol "tmp" when expecting one of the following:
    ;

    .The same query when i run from DB side,it fetches the result.Please tell me how to resolve this.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Software version(s) might be meaningful in this context.

  3. #3
    Join Date
    Mar 2011
    Posts
    13
    Application server version Oracle 10g version 10.1.2.3.0
    DB version Oracle 10g version 10.2.0.4.0

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK then. Note that I don't speak PRO*C, but I hope that this might help.

    First of all, it seems that you named cursor "FOR". Or is it "PUBS"? I can't tell; if it is "FOR", that is probably not a very good idea as "FOR" indicates a FOR loop. Generally, it is better to avoid Oracle keywords and reserved words for your own names.

    Here's how WITH might be used when declaring a cursor. Once again: not PRO*C, pure PL/SQL:
    Code:
    SQL> declare
      2    cursor cur_r is
      3      with tmp as (select deptno from dept)
      4      select deptno from tmp;
      5  begin
      6    for cur_s in cur_r loop
      7      dbms_output.put_line(cur_s.deptno);
      8    end loop;
      9  end;
     10  /
    10
    20
    30
    40
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Does it make any sense to you?

  5. #5
    Join Date
    Mar 2011
    Posts
    13
    Hi....

    'For' which i have used is not cursor name..CURSOR FOR is used to fetch and process every record in a cursor.The cursor name i have used is 'PUBS' .I have used cursors in Pro*C before, but the query in that would be just select query like

    EXEC SQL DECLARE PUBS CURSOR FOR
    SELECT DISTINCT PUB_IDT
    FROM PRMSELINCPUBT
    WHERE PRM_IDT = rm_idt
    AND CHP_NUM = :chp_num
    ORDER BY PUB_IDT;

    The above things executes fine.But when i use 'With' clause in the cursor it thows the error when compiling..

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see ... sorry, I wouldn't know what is wrong. Maybe PRO*C doesn't support WITH clause.

    But, there's no need for WITH at all - you can still rewrite it the way you *know* is correct:
    Code:
    EXEC SQL DECLARE PUBS CURSOR FOR
      SELECT DISTINCT tmp.idt
      FROM (  SELECT DISTINCT pub.PUB_IDT IDT, edt.pub_mth
                FROM PRMSELINCPUBT pub, prmeditiot edt
               WHERE     pub.PRM_IDT = 1306
                     AND pub.CHP_NUM = 1
                     AND pub.pub_idt = edt.pub_idt
                     AND edt.pub_mth IS NOT NULL
            ORDER BY edt.pub_mth ASC)

  7. #7
    Join Date
    Mar 2011
    Posts
    13
    The purpose of using 'with' clause was ....

    I want to get the list of distinct pub_idt values from prmselincpubt table and the list should be sorted in the ascending order of pub_mth of prmeditiot table...

    I first tried just
    SELECT DISTINCT pub.PUB_IDT IDT, edt.pub_mth
    FROM PRMSELINCPUBT pub, prmeditiot edt
    WHERE pub.PRM_IDT = 1306
    AND pub.CHP_NUM = 1
    AND pub.pub_idt = edt.pub_idt
    AND edt.pub_mth IS NOT NULL
    ORDER BY edt.pub_mth ASC

    but it was returning the unique combinations of pub_idt and pub_mth values ... example: pub_idt=101;pub_mth=2004
    pub_idt=101;pub_mth=2005
    but this is not i want... i want list of single pub_idts alone like pub_idt=101 instead of displaying two times... i even tried out join queries...it is not working

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you test code I posted in a message #6? That's what you tried to do with WITH, what you explained in your previous message ... shortly, that *should* do what you are after.

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    In addition, result of both queries will not be ordered in any way; it may look so, but it is not guaranteed (even result set ordered differently would be correct).
    Your requirement looks like a simple aggregate query and depends on exact definition of "the ascending order of pub_mth of prmeditiot table", as there are multiple PUB_MTHs for one PUB_IDT. Just imagine following data:
    Code:
    PUB_IDT PUB_MTH
          1       5
          1       6
          1       8
          2       4
          2       9
          3       7
    What is the order of PUB_IDTs in this case and why?
    Anyway, the correct query should aggregate and it would look like this:
    Code:
    SELECT pub.PUB_IDT IDT, <appropriate aggregate function on edt.pub_mth>
    FROM PRMSELINCPUBT pub, prmeditiot edt
    WHERE <all join/filter conditions>
    GROUP BY pub.PUB_IDT
    ORDER BY 2

  10. #10
    Join Date
    Mar 2011
    Posts
    13
    thanks for ur reply.... but there is a new requirement [plz check the attachment(sample.bmp)]. In this file, the pub_vol_idt is arranged on the ascending order of seq_num of prmselincpubt table.Now it has to be arranged depending on pub_mth of prmeditiot table. i was able to do it..

    Code:
     SELECT *  FROM    prmvolinft WHERE   pub_idt     = 1234
    AND     pub_vol_idt in (select distinct a.IDT from (SELECT distinct pub.vol_idt
    IDT,pub.pub_idt,edt.pub_mth FROM PRMSELINCPUBT pub,prmeditiot edt
    WHERE pub.PRM_IDT = 1306 pub.pub_idt= 1234
    order by edt.pub_mth asc ) a)
    On top of this,suppose the pub_mth values are same between any pub_vol_idt values then it has to be sorted ,depending on seq_num of prmvolinft table.Is it possible to handle in the same query itself??? else please suggest any other approach....
    Attached Thumbnails Attached Thumbnails sample.bmp  

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Include additional columns into the ORDER BY clause.

  12. #12
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by planet100 View Post
    thanks for ur reply.... but there is a new requirement [plz check the attachment(sample.bmp)]. In this file, the pub_vol_idt is arranged on the ascending order of seq_num of prmselincpubt table.Now it has to be arranged depending on pub_mth of prmeditiot table. i was able to do it..
    Please, next time, include that result set to your post as I did, it is really hard to decipher it. Without clear description, what that result set represents, it is useless. Surely not the query below, as it is invalid (wrong condition in subquery WHERE clause).

    You may get sorted result set only when using ORDER BY clause; documentation states this here: http://download.oracle.com/docs/cd/B...2.htm#i2171079
    expr orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables, views, or materialized views in the FROM clause.
    So, sorting by PUB_MTH is as simple as including
    Code:
    ORDER BY pub_mth
    in the main SELECT statement. If PUB_MTH is not present, you have to join table which contains it into FROM clause; if there are duplicate rows, you have to aggregate them somehow to get only one (you have to have some reason for using DISTINCT, although data in that picture are not duplicate in PUB_VOL_IDT nor PUB_MTH columns). With the description you posted, I am unable to apply these rules on your example, so it is on you to do so.

  13. #13
    Join Date
    Mar 2011
    Posts
    13
    I will explain the entire scenario.
    Say for example the current result fetched is as follows..

    • the result is arranged according to the seq_num ascending order


    Code:
    SEQ_NUM   PUB_VOL_IDT    VOL_NUM                   PUB_MTH
    -------	  -----------    -------                       -------
    130		       30           Vol57,Issues 24-26       201012
    131   		       31 	     Vol58,Issues 3-4          201102
    132 		       32 	     Vol58,Issues 1-2          201101
    133 		       33 	     Vol58,Issue 5 	             201103


    1. seq_num belongs to prmvolinft table
    2. pub_vol_idt and vol_num belongs to prmvolinft table
    3. pub_mth belongs to prmeditiot table



    The previous existing query was

    Code:
    SELECT pub_vol_idt,seq_num,vol_num       
    FROM    prmvolinft
    WHERE   pub_idt     = 1234
    AND     pub_vol_idt IN 
    ( SELECT  DISTINCT vol_idt
    FROM    prmselincpubt 
    WHERE   prm_idt = 001
    AND     pub_idt = 1234
    AND     vol_idt > 0 )
    ORDER BY seq_num, pub_vol_idt ;
    • Now the result has to be sorted based on the pub_mth of prmeditiot table.


    I tried the following query and seems to working.

    Code:
    SELECT pub_vol_idt,seq_num,vol_num       
    FROM    prmvolinft
    WHERE   pub_idt     = 1234
    AND     pub_vol_idt IN 
    (select a.VOLID from
    (SELECT distinct pub.vol_idt VOLID,pub.pub_idt,edt.pub_mth
    FROM PRMSELINCPUBT pub,prmeditiot edt
    WHERE pub.PRM_IDT = 001 and 
    pub.pub_idt=1234 
    order by edt.pub_mth asc ) a)
    On top of this,there is another requirement.

    Suppose the pub_mth of prmeditiot table(the column on which the query is sorted) is same for the vol_idt of prmselincpubt table ,
    then instead of sorting by pub_mth, it has to be sorted based on seq_num of pubvolinft table.Please help me on this.

    Can these two conditions
    i.e., i) sorting based on prmeditiot.pub_mth and
    ii)if pub_mth is same then sorting based on prmvolinft.seq_num
    be done in a single query??

  14. #14
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by planet100 View Post
    Can these two conditions
    i.e., i) sorting based on prmeditiot.pub_mth and
    ii)if pub_mth is same then sorting based on prmvolinft.seq_num
    be done in a single query??
    Yes, it is as simple as adding SEQ_NUM column after PUB_MTH in ORDER BY clause.

    But, wait, your query that "seems to working" does not contain ORDER BY clause, so it is in fact not sorting the resultset. Also, it is hard to tell the relationship between PRMVOLINFT and PRMEDITIOT tables as they are not joined in that query at all. In your first post, it was joined on PUB_IDT column with PRMSELINCPUBT table. In the first query, there is no join of PRMVOLINFT and PRMSELINCPUBT in the exists clause; however PRMSELINCPUBT is filtered on the same PUB_IDT as PRMVOLINFT table in the main SELECT.

    So, supposing from the above paragraph that all tables shall be joined on PUB_IDT column and PRMEDITIOT does not multiply the row count (it should be achieved by primary key or unique constraint on PRMEDITIOT.PUB_IDT column), you may join it to the main query to get the sorting criteria:
    Code:
    SELECT pub_vol_idt, seq_num, vol_num       
    FROM prmvolinft, prmeditiot
    WHERE prmvolinft.pub_idt = prmeditiot.pub_idt
      AND prmvolinft.pub_idt = 1234
      AND prmvolinft.pub_vol_idt IN (
        SELECT prmselincpubt.vol_idt
        FROM prmselincpubt
        WHERE prmselincpubt.pub_idt = prmvolinft.pub_idt
          AND prmselincpubt.prm_idt = 001
          AND prmselincpubt.vol_idt > 0 )
    ORDER BY prmeditiot.pub_mth, prmvolinft.seq_num;
    If there are duplicates in PRMEDITIOT.PUB_IDT, we are back to my question: what shall be the order based on PUB_MTH column if there are multiple values in it (see my sample data in one of my previous posts)?

    If I deduced the joining coditions wrongly (it is possible as the join/filter conditions change with nearly every your post), what about posting them here? Posting ER diagram or stating them as primary key/foreign key constraints would be best option.

  15. #15
    Join Date
    Mar 2011
    Posts
    13
    Thnx a lot....
    There wud'nt be any duplicates.. since it is specific to pub_idt of both prmeditiot and prmvolinft tables and also specific to prmvolinft.pub_vol_idt

    Thans for ur help !!

Posting Permissions

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