Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Question Unanswered: Syntax error...bummer.

    Informix version: 9.50C1
    OS: AIX 5.3.0.0

    Obviously not an informix guy, but need to delete some rows from a table.
    I copied the syntax from IBM Informix Guide to SQL: Syntax but to no avail.
    Here is my error:

    Code:
    delete from bill using bill_trn t, bill b 
          where b.contno = t.contno and b.fieldx = t.fieldx and b.itemno = t.itemno ;
      201: A syntax error has occurred.
    Error in line 1
    Near character position 22
    Need your gracious help...

    PS: Also I have tried the following sql with no effect:
    Code:
    merge into bill b using bill_trn t
         on b.contno = t.contno and b.fieldx = t.fieldx and b.itemno = t.itemno
     when matched then delete;
      201: A syntax error has occurred.
    Error in line 1
    Near character position 2
    Last edited by LKBrwn_DBA; 02-26-14 at 16:45.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Lk,

    I never heard about DELETE FROM with more than one table with Informix. I don't know about other RDBMS.

    You probably want to DELETE from one table WHERE key IN ( subquery with 2 tables if you want )

    I also notice that your Informix version is has out of support for many years now. You implementation is severely at risk in case of bug occurrence (By the way 9.50 does not exist ). We are now at 12.10 xC2 with a lot of very interesting features, including NoSQL/MongoDB driver and sharded queries.

    You should really consider taking a look at it:
    take a look here

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Thanks for your response begooden-it.

    I know this Informix version has been out of support for many years now, but the people that created this database have also been long gone.

    This DB was migrated to Oracle some three years ago, unfortunately there remains some DB2 legacy application that still uses it.

    The syntax is valid, I copied from the v10 manual, but also found it way back in the v9.4 manual.

    The "DELETE from one table WHERE key IN ( subquery with 2 tables)" does not work either:
    Code:
    $ cat m2
    informix -i plprd
    calcperiod
    . /usr/local/informix/etc/.profile.imbseom
    
    sudi dbaccess riot <<eof 2>&1
    delete from bill
          where ( contno, fieldx, itemno) in (
            select b.contno, b.fieldx, b.itemno
              from bill_trn t, bill b
             where b.contno = t.contno and b.fieldx = t.fieldx and b.itemno = t.itemno) ;
    
    eof
    
    exit
    
    # Result:
    Database selected.
    
      201: A syntax error has occurred.
    Error in line 2
    Near character position 20
    
    Database closed.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,

    sorry for the delay. I Have been checking again: the Syntax manual you are referring to is the 11.10 Syntax Manual.
    In the 9.40 manual, as well as 10.0, this syntax is valid for XPS only, not for IDS.
    I doubt the product you have is XPS (which is version 8.X,a MPP special version of IDS dedicated exclusively for Datawarehouse and not for OLTP)

    I also have checked until 12.10 and I cannot see the syntax you want to use as valid :-(
    I have tested 11.50, 11.70 and 12.10 and effectively I get a Syntax error on the word 'using'

    Your statement with Merge work in 11.50 xC5 and above

    Sorry I think I can't help anymore :-(

    Eric

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Thank you,
    I appreciate your effort to help.

    To me it's odd that this database version will not allow the delete statement to be constrained with a join condition.

    Our workaround was to fully load this table once a week instead of daily update.

    Fortunately this motivated the "executives" to order the developers to move all remaining operations from this db to an Oracle db which has the same data.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    I am sorry to hear that.

    Passing this to Oracle will at least simplify your life and garantee you more DBA work.

    Cheers
    Eric

Posting Permissions

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