Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Syntax error...bummer.

Posted on 2014-02-18
12
Medium Priority
?
694 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
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 46

Expert Comment

by:Kent Olsen
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 46

Expert Comment

by:Kent Olsen
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
 
LVL 46

Expert Comment

by:Kent Olsen
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 46

Expert Comment

by:Kent Olsen
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 46

Accepted Solution

by:
Kent Olsen earned 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 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