sql statement
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: sql statement

  1. #1
    Join Date
    Jun 2003
    Posts
    47

    sql statement

    Hi,

    I have this code which in SQL*Plus runs ok but in PL/SQL it doesn't.
    How can I come around with it?
    SQL*PLUS
    --------
    delete from tblworkingcontent
    where idworkingcontent in
    (select idworkingcontent
    from tblworkingcontent wc1
    where wc1.metatags='a'
    and (select count(*)
    from tblworkingcontent wc2
    where wc2.idworkingcontent and wc2.metatags='a') <
    (select count(*)
    from tblworkingcontent wc3
    where wc3.metatags='a'-1))


    In PL/SQL instead it doesn't execute

    declare
    v_metatags tblworkingcontent.metatags%type;
    v_numerorighe int;
    begin
    delete from tblworkingcontent
    where idworkingcontent in
    (select idworkingcontent
    from tblworkingcontent wc1
    where wc1.metatags=v_metatags
    and (select count(*)
    from tblworkingcontent wc2
    where wc2.idworkingcontent and wc2.metatags=v_metatags) <
    (select count(*)
    from tblworkingcontent wc3
    where wc3.metatags=v_metatags-v_numerorighe))
    end;
    /
    ERROR at line 10:
    ORA-06550: line 10, column 14:
    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
    ( - + mod not null others
    avg
    count current exists max min prior sql stddev sum variance
    execute forall time timestamp interval date


    ORA-06550: line 13, column 46:
    PLS-00103: Encountered the symbol "<" when expecting one of the following:
    ) with and or group having intersect minus start union where
    connect
    ORA-06550: line 16, column 62:
    PLS-00103: Encountered the symbol ")" when expecting one of the following:
    ; return returning and or

    Thank you in advance

  2. #2
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    From metalink:
    A scalar subquery is a subquery that returns a single row, this is a
    new feature in Oracle8i which can be used in the SET clause of an UPDATE
    statement and the VALUES clause of an INSERT statement - but such a statement
    gives an error if placed in a PLSQL block

    In versions below Oracle9i 9.0.1 the SQL and PL/SQL use different parsers, so
    everything that work OK and is supported in SQL will not automatically work OK
    and be supported in PL/SQL
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  3. #3
    Join Date
    Jun 2003
    Posts
    47
    yes, but there is no way I can come around that?

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I think you'd best start by cleaningup the logic. The section

    "and (select count(*)
    from tblworkingcontent wc2
    where wc2.idworkingcontent and wc2.metatags=v_metatags) <
    (select count(*)
    from tblworkingcontent wc3
    where wc3.metatags=v_metatags-v_numerorighe)"

    depends on the table as a whole and not on any row selected by the

    "in (select idworkingcontent
    from tblworkingcontent wc1
    where wc1.metatags=v_metatags . . . ."

    so, as far as I can see, it just switches the whole delete on and off, so it should not be in the sub-query.

    . . and I don't see why you need the IN clause . . . and there is a ; missing after the final )), isn't there?


    How about:

    Code:
    delete from tblworkingcontent
    where metatags=v_metatags
    and 
    (select count(*)
     from tblworkingcontent wc2
     where wc2.idworkingcontent and wc2.metatags=v_metatags) <
    (select count(*)
     from tblworkingcontent wc3
     where wc3.metatags=v_metatags-v_numerorighe)
    ;

  5. #5
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    And, if you want pl/sql, since your scalar subquery expressions
    Originally posted by DaPi
    depends on the table as a whole and not on any row selected by the
    outer query, you can execute them separately:
    Code:
    declare
    v_metatags tblworkingcontent.metatags%type;
    v_numerorighe int;
    v_cnt1 number;
    v_cnt2 number;
    begin
      select count(*) into v_cnt1
        from tblworkingcontent wc2
       where wc2.idworkingcontent and wc2.metatags=v_metatags;
      select count(*) into v_cnt2
        from tblworkingcontent wc3
       where wc3.metatags=v_metatags-v_numerorighe;
      delete from tblworkingcontent
       where metatags=v_metatags
         and v_cnt1 < v_cnt2;
    end;
    /
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  6. #6
    Join Date
    Jun 2003
    Posts
    47
    delete from tblworkingcontent
    where idworkingcontent in
    (select idworkingcontent
    from tblworkingcontent wc1
    where wc1.metatags='a'
    and (select count(*)
    from tblworkingcontent wc2
    where wc2.idworkingcontent (select count(*)
    from tblworkingcontent wc3
    where wc3.metatags='a'-1))


    well

    wc2.idworkingcontent

    makes difference and I hadn't written it.Sorry.

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    EVERYBODY STOP ! ! ! ! !

    we lost this in the formating of the originalpost:

    Code:
     where wc2.idworkingcontent < wc1.idworkingcontent
    back to the drawing board . . . .

  8. #8
    Join Date
    Jun 2003
    Posts
    47
    "delete from tblworkingcontent
    where idworkingcontent in
    (select idworkingcontent
    from tblworkingcontent wc1
    where wc1.metatags=v_metatags
    and (select count(*)
    from tblworkingcontent wc2
    where wc2.idworkingcontent and wc2.metatags=v_metatags) <
    (select count(*)
    from tblworkingcontent wc3
    where wc3.metatags=v_metatags-v_numerorighe))
    end;"
    /


    "wc2.idworkingcontent
    It eliminates it it seems.
    but this makes a difference.
    excuse the mess.

  9. #9
    Join Date
    Jun 2003
    Posts
    47
    yess.
    That's it.

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by edli
    wc2.idworkingcontent < wc1.idworkingcontent

    makes difference and I hadn't written it.Sorry.
    The < to end of line gets lost unless you put spaces round the < . . . . Bizzare!

Posting Permissions

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


Click Here to Expand Forum to Full Width