Results 1 to 6 of 6

Thread: Multiset

  1. #1
    Join Date
    Apr 2008
    Posts
    4

    Unanswered: Multiset

    Hi,

    I used a query with multiset
    select
    col1,col2,ilx.cnt -NVL((select count(*) from table1 x1
    where x1.col1=x2.col1
    and x1.col2=x2.col2
    and x1.rowid > x2.rowid),0) l_nbr
    from invoice@${srcinstance}:table2 x2
    join table(multiset (select col1,col2,col3,count(*) as cnt
    from table4 group by col1,col2,col3)) as ilx
    on(ilx.col1=il.col1
    and ilx.col1=il.col1
    and ilx.col1=il.col1)
    this same query,i was able to execute in informix version 9.4 with IUNLOAD OPTION
    “INFORMIX-SQL Version 9.40.FC6W1”
    Copyright IBM Corporation 1996, 2004


    but the same query said "Not A Valid Select!!!" error when i executed the query in informix version 10.0
    “INFORMIX-SQL Version 10.00.FC6WM2”
    Copyright IBM Corporation 1996, 2006


    why did this happen and what might be the equivalent query in 10.0 version
    Can i use an inline query with iunload command and use multiset also in the 10.0 version.

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    execute in the dbaccess, it will show at exactly point the syntax error occur...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Apr 2008
    Posts
    4
    Thanks so much for the reply.
    The problem is with the (-) operator.
    In one of the informix version 9.4 the query is running fine with the iunload.
    But the same query with iunload utility is not running fine with 10.0.

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Running in dbaccess, what exactly the error message ?
    What error number?

    If possible, put the create table of the each table here, than we can try simulate the problem;
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    Apr 2008
    Posts
    4
    there is no specific error number it just said "not a valid select!!!" no error number. but i can note that its bcoz of a minus symbol (-)

  6. #6
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    When executed in dbaccess , any error return a message number .
    ALL errors on Informix are classified with a number.
    You can see this with command "finderr" .
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

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