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
Solved

Syntax error...bummer.

Posted on 2014-02-18
12
682 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: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 45

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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: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 45

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 45

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 45

Accepted Solution

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

860 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