Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

INSERT INTO problem!

2 posts in General Discussion Last posting was on 2003-09-22 10:37:58.0Z
M. Ravari Posted on 2003-09-22 02:44:27.0Z
From: "M. Ravari" <majid@lablogics.com>
Newsgroups: ianywhere.public.general
Subject: INSERT INTO problem!
Lines: 53
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: adsl-67-127-109-33.dsl.irvnca.pacbell.net
Message-ID: <3f6e6298@forums-2-dub>
X-Original-Trace: 21 Sep 2003 19:46:48 -0700, adsl-67-127-109-33.dsl.irvnca.pacbell.net
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 21 Sep 2003 19:40:29 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 21 Sep 2003 19:44:27 -0700
X-Trace: forums-1-dub 1064198667 10.22.108.75 (21 Sep 2003 19:44:27 -0700)
X-Original-Trace: 21 Sep 2003 19:44:27 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1783
Article PK: 4070

Hello there,

I'm using iAnywhere 7.0

I have created the following table:

/*==============================================================*/
/* Domain: ID */
/*==============================================================*/
create datatype ID double not null;

/*==============================================================*/
/* Table : INVOICETRANSINFO */
/*==============================================================*/
create table Tableowner.INVOICETRANSINFO
(
INVOICETRANSID ID not null,
INVOICEID integer not null,
GLACCOUNT char(64) not null,
AMOUNT double not null,
primary key (INVOICETRANSID)
);

*** InvoiceTransId is an auto-incrementing varaiable ***

writing a stored procedure, I'm using the following syntax:

INSERT INTO InvoiceTransInfo
SELECT @InvId,GLAcct,Amount
FROM #TempInvoiceTrans

where @InvId is a defined variable with proper data
and #TempInvoiceTrans is a temporary table also with proper data and one or
more records.

The same syntax works fine with MS SQL but when I try to run it I get:

ERROR: Wrong number of values for INSERT (as if it is not recognizing the
auto-incrementing ID column)

Pecular thing is that I can do the following with no problems:

INSERT INTO InvoiceTransInfo (InvoiceId,GLAccount,Amount) VALUES
(5000,'A',10.)

In this case it recognizes the auto-incrementing InvoiceTransId field and
places the correct id in it while inserting properly.

Can you help please?

Majid Ravari


Breck Carter [TeamSybase] Posted on 2003-09-22 10:37:58.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: INSERT INTO problem!
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <6kjtmv0ddmj44lk32d56m3r0ll21ti4jh7@4ax.com>
References: <3f6e6298@forums-2-dub>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 22 Sep 2003 03:40:19 -0700, bcarter.sentex.ca
Lines: 83
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 22 Sep 2003 03:33:58 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 22 Sep 2003 03:37:58 -0700
X-Trace: forums-1-dub 1064227078 10.22.108.75 (22 Sep 2003 03:37:58 -0700)
X-Original-Trace: 22 Sep 2003 03:37:58 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1784
Article PK: 4066

Are you *sure* this works in MSS? When you write this:

INSERT INTO InvoiceTransInfo
SELECT @InvId,GLAcct,Amount
FROM #TempInvoiceTrans

how does the DBMS know which 3 of the 4 columns to fill?

Anyway, you must specify a column name list when you are not providing
explicit values for all columns. If you were using VALUES instead of
SELECT you could use the special keyword DEFAULT; e.g.,

INSERT t1 VALUES ( DEFAULT, col_2_value, col_3_value, etcetera );

BTW, the CREATE TABLE shows that INVOICETRANSID is *not*
auto-incrementing, it needs DEFAULT AUTOINCREMENT or IDENTITY.

Also, the DOUBLE data type is not appropriate for autoincrement
columns, they should be an integer or exact numeric data type; BIGINT
is popular for huge ranges, or UNSIGNED BIGINT for even more.

Breck

On 21 Sep 2003 19:44:27 -0700, "M. Ravari" <majid@lablogics.com>

wrote:

>Hello there,
>
>I'm using iAnywhere 7.0
>
>I have created the following table:
>
>/*==============================================================*/
>/* Domain: ID */
>/*==============================================================*/
>create datatype ID double not null;
>
>/*==============================================================*/
>/* Table : INVOICETRANSINFO */
>/*==============================================================*/
>create table Tableowner.INVOICETRANSINFO
>(
> INVOICETRANSID ID not null,
> INVOICEID integer not null,
> GLACCOUNT char(64) not null,
> AMOUNT double not null,
> primary key (INVOICETRANSID)
>);
>
>*** InvoiceTransId is an auto-incrementing varaiable ***
>
>writing a stored procedure, I'm using the following syntax:
>
>INSERT INTO InvoiceTransInfo
>SELECT @InvId,GLAcct,Amount
>FROM #TempInvoiceTrans
>
>where @InvId is a defined variable with proper data
>and #TempInvoiceTrans is a temporary table also with proper data and one or
>more records.
>
>The same syntax works fine with MS SQL but when I try to run it I get:
>
>ERROR: Wrong number of values for INSERT (as if it is not recognizing the
>auto-incrementing ID column)
>
>Pecular thing is that I can do the following with no problems:
>
>INSERT INTO InvoiceTransInfo (InvoiceId,GLAccount,Amount) VALUES
>(5000,'A',10.)
>
>In this case it recognizes the auto-incrementing InvoiceTransId field and
>places the correct id in it while inserting properly.
>
>Can you help please?
>
>Majid Ravari
>

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com