Solved

Syntax error...bummer.

Posted on 2014-02-18
12
676 Views
Last Modified: 2014-02-19
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:
Database selected.
    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

Database closed.

Open in new window

Need your gracious help...

PS: I have no clue what version of informix I'm using.
0
Comment
Question by:MikeOM_DBA
  • 6
  • 6
12 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 39868051
Hi Mike,

If you have a version that supports the MERGE statement, that's probably the easiest!


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;


Try that and see what happens.


Kent
0
 
LVL 29

Author Comment

by:MikeOM_DBA
ID: 39868091
Grrrr...same thing:
Database selected.

	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

Database closed.

Open in new window


I found this in the PATH, perhaps this will give a clue of the version: /informix/v100fc6/bin

I must clarify that this is a VERY OLD database and the creators have long been gone from the company.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39868156
Ok.  It was a nice thought.  :)

One of the supported formats of the DELETE statement is very similar to what you posted.

DELETE bill FROM bill_trn t, bill b
WHERE b.contno = t.contno and b.fieldx = t.fieldx and b.itemno = t.itemno ;


I don't know if it works for this version, but it's worth a shot!


Kent
0
 
LVL 29

Author Comment

by:MikeOM_DBA
ID: 39868201
That was the first statement I tried, copied directly from the IBM Informix Dynamic Server, Version 11 (and also 10) manual.
And I do have privileges because the process I'm trying to replace actually deletes all rows and then loads the whole (bill) table.

I am trying to replace that process with one that loads a transaction table and then deletes/inserts the changed/new rows. I got to the point I load the transaction table and I'm stuck with this delete.

I found an alternative but it only compares on column and I do not know how to code it for 3 columns; this is the pseudo-code:
DELETE FROM bill
 WHERE (contno,fieldx,itemno) IN (SELECT contno, fieldx, itemno  FROM bill_trn) ;

Open in new window

Thanks for your help.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39868537
That particular syntax is used by other vendors so it's quite possible that it is/was acceptable to Informix.  But if you've tried it and it doesn't work, it doesn't help much (except to eliminate things that don't work).  :(

I'm wondering if your version is so old that a join-delete isn't supported.  Everything that we've posted here should work (at some release of Informix).


Do you know if the database structure defines bill as a table or a super_table?  (Cascading deletes may be a problem if it's a super table.)

I found some Informix documentation from 1999.

  http://www.ramm.co.nz/files/resourcesmodule/@random45d4e22d937b8/1171591767_Informix_SQL_Syntax.pdf

Page 425 (CH 2, pg 379) has this example:

DELETE FROM lineitem
USING order o, lineitem l
WHERE o.qty < 1 AND o.order_num = l.order_num

That would suggest that this should work, if your version is compatible with 1999:

DELETE FROM bill USING bill_trn t, bill b
WHERE b.contno = t.contno and b.fieldx = t.fieldx and b.itemno = t.itemno ;


Kent
0
 
LVL 29

Author Comment

by:MikeOM_DBA
ID: 39868654
No deal, that was the first one I tried, I think this database was created by Noah after the flood:
$ dbaccess riot <<eof
> DELETE FROM bill USING bill_trn t, bill b
> WHERE b.contno = t.contno and b.fieldx = t.fieldx and b.itemno = t.itemno ;
> eof

Database selected.

  201: A syntax error has occurred.
Error in line 1
Near character position 18

Database closed.

Open in new window



This is the original create table:
{ TABLE "informix".bill row size = 439 number of columns = 100 index size = 30 }
create table "informix".bill 
  (
    actcod char(1),
   . . .    e t c   . . .
    lsprc char(8)
  )  extent size 32 next size 32 lock mode page;
revoke all on "informix".bill from "public";

create index "informix".idx_cnt2 on "informix".bill (contno) using 
    btree  in datadbs2 ;
create index "informix".idx_itmcnt2 on "informix".bill (itemno,
    contno) using btree  in datadbs2 ;
create index "informix".idx_prdct_itrk2 on "informix".bill (prdcat,
    itrack) using btree  in datadbs2 ;
create index "informix".idx_billpk on "informix".bill (contno,fieldx,itemno) using 
    btree  in datadbs2 ;

Open in new window


INFO - bill:   Columns  Indexes  Privileges  References  Status  cOnstraints  triGgers  Table  Fragments  Exit
Display status information for a table.

----------------------- riot@plprdshm ---------- Press CTRL-W for Help --------

Table Name          bill
Owner               informix
Row Size            441
Number of Rows      1383517
Number of Columns   100
Date Created        20090108


INFO - bill:   Columns  Indexes  Privileges  References  Status  cOnstraints  triGgers  Table  Fragments  Exit
Display user access privileges for a table.

----------------------- riot@plprdshm ---------- Press CTRL-W for Help --------

User         Select             Update             Insert  Delete  Index  Alter

public        All                All                Yes     Yes     Yes    No

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 45

Expert Comment

by:Kdo
ID: 39868687
Try running this command:

  SELECT owner FROM systables WHERE TABNAME= ' VERSION';
0
 
LVL 29

Author Comment

by:MikeOM_DBA
ID: 39868789
WOW! It is old...
$ dbaccess riot <<eof
> SELECT owner FROM systables WHERE TABNAME= ' VERSION';
> eof

Database selected.

owner

9.50C1

1 row(s) retrieved.

Database closed.

Open in new window


Just checked the 9.4 manual and the syntax you have posted is valid.
Does that mean it may have to do with how the table was created?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39868966
I can't imagine that a syntax described in the 9.4 manual would be invalid at 9.5.  That kind of behavior is usually reserved for major releases (value left of the decimal point).

Let me think/research a bit more....
0
 
LVL 29

Author Comment

by:MikeOM_DBA
ID: 39871296
Thanks kdo, anything will help.
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 39871586
Hi Mike,

It's almost as if you've got a mixed system, with some features of different versions.  The system claims to be version 9, but won't accept a version 9 formatted command.  (Then again, I'm not telling you anything new here, just recapping.)

I've found several Informix documents on the internet with different command syntax for the DELETE.  We've tried them all.  :(

I've also found the IBM online documentation for Version 10.  The syntax diagram of the DELETE statement is consistent with what we've tried.  The page date is November 2005.

  http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.dba.doc/dba.htm

I don't know where to go from here.  Do you have a support contract with IBM?  Do you know someone that does that can ask this question for you?  If you have an IBM contact, even sales, you might get them to help run this question by the IBM technical support folks.

Kent
0
 
LVL 29

Author Comment

by:MikeOM_DBA
ID: 39871638
We do have IBM support, but it's only for the servers and the Mainframe DB2.
Anyway I will see what they can do.

Thank you very much Kent, i will assign you the points.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now