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.

ON Existing update problem

3 posts in General Discussion Last posting was on 2005-09-01 17:43:58.0Z
Eddie Sizemore Posted on 2005-08-11 17:31:37.0Z
From: "Eddie Sizemore" <esizemoreno@spamqx.net>
Newsgroups: ianywhere.public.general
Subject: ON Existing update problem
Lines: 116
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 12-203-198-23.client.insightbb.com
X-Original-NNTP-Posting-Host: 12-203-198-23.client.insightbb.com
Message-ID: <42fb8b79$1@forums-1-dub>
Date: 11 Aug 2005 10:31:37 -0700
X-Trace: forums-1-dub 1123781497 12.203.198.23 (11 Aug 2005 10:31:37 -0700)
X-Original-Trace: 11 Aug 2005 10:31:37 -0700, 12-203-198-23.client.insightbb.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4681
Article PK: 17348

Adaptive Server Anywhere Network Server Version 9.0.2.3131

I need to get a count (JobLoads) and a sum (JobTons) for each product code
on all tickets with a sitecode of 25 and job code of '00100115' (Job Query)
I also need the same info for a day (DailyLoads, DailyTons). The daily
query range will be from midnight to a time a ticket was created. I have a
temp table to hold the results of the query. If I load the temp table with
the daily query first. I get a table with 5 rows with my test data. Then I
run the job query which inserts rows in the same temp table with ON EXISTING
UPDATE. The job query inserts/updates 17 rows. The inserts are ok but the
updates lost my daily loads and dailytons info for 4 of the rows inserted by
the daily query.

However, if I switch the order of the two queries then the On EXISTING
UPDATE works ok. The order for the two queries should not make any
difference as I am using on existing update and the only column the queries
have in common is the pk productcode.

I need to run the daily query first

I have included the temp table and queries below along with the returned
rows.



CREATE GLOBAL TEMPORARY TABLE "DBA"."tDailyJobTotals" (
"ProductCode" CHAR(10) NOT NULL,
"DailyLoads" INTEGER NULL,
"DailyTons" DECIMAL(30,6) NULL,
"JobLoads" INTEGER NULL,
"JobTons" DECIMAL(30,6) NULL,
PRIMARY KEY ("ProductCode")
) ON COMMIT PRESERVE ROWS ;

-- This gives wrong info
TRUNCATE TABLE tDailyJobTotals;

INSERT INTO DBA.tDailyJobTotals (ProductCode, DailyLoads, DailyTons) SELECT
ProductCode, COUNT(), SUM(NetTons) FROM DBA.Ticket
WHERE SiteCode = '25' AND JobCode = '00100115' AND TimeOut BETWEEN
'2005-08-02 00:00:00.000' AND '2005-08-02 23:59:59.999' GROUP BY ProductCode
ORDER By ProductCode;

INSERT INTO DBA.tDailyJobTotals (ProductCode, JobLoads, JobTons) ON EXISTING
UPDATE SELECT ProductCode, COUNT(), SUM(NetTons) FROM DBA.Ticket
WHERE SiteCode = '25' AND JobCode = '00100115' GROUP BY ProductCode ORDER By
ProductCode;

SELECT * FROM tDailyJobTotals;

ProductCode,DailyLoads,DailyTons,JobLoads,JobTons
'304 02',,,370,4368.310000
'411 02',,,303,1306.415000
'57W',,,2889,14345.055000
'67',3,48.170000,31,328.010000
'8W',,,910,3149.570000
'2',,,226,1995.205000
'4',,,369,2556.910000
'467',,,10,36.310000
'7',,,38,121.750000
'703 05',,,85,359.349000
'703 10',,,327,773.770000
'C 304',,,208,1335.080000
'CGL ',,,2,16.080000
'SALT',,,41,80.320000
'SNT 14',,,37,135.970000
'TYPE B',,,6,34.640000
'TYPE C',,,81,654.960000

-- This works but I need to run them in the other order
TRUNCATE TABLE tDailyJobTotals;

INSERT INTO DBA.tDailyJobTotals (ProductCode, JobLoads, JobTons) SELECT
ProductCode, COUNT(), SUM(NetTons) FROM DBA.Ticket
WHERE SiteCode = '25' AND JobCode = '00100115' GROUP BY ProductCode ORDER By
ProductCode;

INSERT INTO DBA.tDailyJobTotals (ProductCode, DailyLoads, DailyTons) ON
EXISTING UPDATE SELECT ProductCode, COUNT(), SUM(NetTons) FROM DBA.Ticket
WHERE SiteCode = '25' AND JobCode = '00100115' AND TimeOut BETWEEN
'2005-08-02 00:00:00.000' AND '2005-08-02 23:59:59.999' GROUP BY ProductCode
ORDER By ProductCode;

SELECT * FROM tDailyJobTotals;

ProductCode,DailyLoads,DailyTons,JobLoads,JobTons
'2',,,226,1995.205000
'304 02',4,39.080000,370,4368.310000
'4',,,369,2556.910000
'411 02',2,7.680000,303,1306.415000
'467',,,10,36.310000
'57W',9,39.610000,2889,14345.055000
'67',3,48.170000,31,328.010000
'7',,,38,121.750000
'703 05',,,85,359.349000
'703 10',,,327,773.770000
'8W',2,11.130000,910,3149.570000
'C 304',,,208,1335.080000
'CGL ',,,2,16.080000
'SALT',,,41,80.320000
'SNT 14',,,37,135.970000
'TYPE B',,,6,34.640000
'TYPE C',,,81,654.960000



---------------------------------
Eddie Sizemore
Senior Analyst
Global Software, Inc.
Email: esizemore@qx.net
Web: www.globalsoftware-inc.com
Phone: 812.246.2819
---------------------------------


anil k goel Posted on 2005-09-01 16:59:31.0Z
From: "anil k goel" <firstname.no-junk.lastname@spam.ianywhere.com>
Newsgroups: ianywhere.public.general
References: <42fb8b79$1@forums-1-dub>
Subject: Re: ON Existing update problem
Lines: 143
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: iarouter.sybase.com
X-Original-NNTP-Posting-Host: iarouter.sybase.com
Message-ID: <43173373$1@forums-1-dub>
Date: 1 Sep 2005 09:59:31 -0700
X-Trace: forums-1-dub 1125593971 10.25.106.45 (1 Sep 2005 09:59:31 -0700)
X-Original-Trace: 1 Sep 2005 09:59:31 -0700, iarouter.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4740
Article PK: 8517

Is the following still a problem (someone pointed this post out to me
today)? If yes, I will take a look:

Please post the following information:

1. The contents of tDailyJobTotals *before* the INSERT ON EXISTING UPDATE
runs.
2. The result set returned by the SELECT statement used in the INSERT
statement.

The post below already contains the contents of tDailyJobTotals *after* the
INSERT.

--
-anil
Research and Development, Query Processing
iAnywhere Solutions Engineering

-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

"Eddie Sizemore" <esizemoreno@spamqx.net> wrote in message
news:42fb8b79$1@forums-1-dub...
> Adaptive Server Anywhere Network Server Version 9.0.2.3131
>
> I need to get a count (JobLoads) and a sum (JobTons) for each product code
> on all tickets with a sitecode of 25 and job code of '00100115' (Job
> Query) I also need the same info for a day (DailyLoads, DailyTons). The
> daily query range will be from midnight to a time a ticket was created. I
> have a temp table to hold the results of the query. If I load the temp
> table with the daily query first. I get a table with 5 rows with my test
> data. Then I run the job query which inserts rows in the same temp table
> with ON EXISTING UPDATE. The job query inserts/updates 17 rows. The
> inserts are ok but the updates lost my daily loads and dailytons info for
> 4 of the rows inserted by the daily query.
>
> However, if I switch the order of the two queries then the On EXISTING
> UPDATE works ok. The order for the two queries should not make any
> difference as I am using on existing update and the only column the
> queries have in common is the pk productcode.
>
> I need to run the daily query first
>
> I have included the temp table and queries below along with the returned
> rows.
>
>
>
> CREATE GLOBAL TEMPORARY TABLE "DBA"."tDailyJobTotals" (
> "ProductCode" CHAR(10) NOT NULL,
> "DailyLoads" INTEGER NULL,
> "DailyTons" DECIMAL(30,6) NULL,
> "JobLoads" INTEGER NULL,
> "JobTons" DECIMAL(30,6) NULL,
> PRIMARY KEY ("ProductCode")
> ) ON COMMIT PRESERVE ROWS ;
>
> -- This gives wrong info
> TRUNCATE TABLE tDailyJobTotals;
>
> INSERT INTO DBA.tDailyJobTotals (ProductCode, DailyLoads, DailyTons)
> SELECT ProductCode, COUNT(), SUM(NetTons) FROM DBA.Ticket
> WHERE SiteCode = '25' AND JobCode = '00100115' AND TimeOut BETWEEN
> '2005-08-02 00:00:00.000' AND '2005-08-02 23:59:59.999' GROUP BY
> ProductCode ORDER By ProductCode;
>
> INSERT INTO DBA.tDailyJobTotals (ProductCode, JobLoads, JobTons) ON
> EXISTING UPDATE SELECT ProductCode, COUNT(), SUM(NetTons) FROM DBA.Ticket
> WHERE SiteCode = '25' AND JobCode = '00100115' GROUP BY ProductCode ORDER
> By ProductCode;
>
> SELECT * FROM tDailyJobTotals;
>
> ProductCode,DailyLoads,DailyTons,JobLoads,JobTons
> '304 02',,,370,4368.310000
> '411 02',,,303,1306.415000
> '57W',,,2889,14345.055000
> '67',3,48.170000,31,328.010000
> '8W',,,910,3149.570000
> '2',,,226,1995.205000
> '4',,,369,2556.910000
> '467',,,10,36.310000
> '7',,,38,121.750000
> '703 05',,,85,359.349000
> '703 10',,,327,773.770000
> 'C 304',,,208,1335.080000
> 'CGL ',,,2,16.080000
> 'SALT',,,41,80.320000
> 'SNT 14',,,37,135.970000
> 'TYPE B',,,6,34.640000
> 'TYPE C',,,81,654.960000
>
> -- This works but I need to run them in the other order
> TRUNCATE TABLE tDailyJobTotals;
>
> INSERT INTO DBA.tDailyJobTotals (ProductCode, JobLoads, JobTons) SELECT
> ProductCode, COUNT(), SUM(NetTons) FROM DBA.Ticket
> WHERE SiteCode = '25' AND JobCode = '00100115' GROUP BY ProductCode ORDER
> By ProductCode;
>
> INSERT INTO DBA.tDailyJobTotals (ProductCode, DailyLoads, DailyTons) ON
> EXISTING UPDATE SELECT ProductCode, COUNT(), SUM(NetTons) FROM DBA.Ticket
> WHERE SiteCode = '25' AND JobCode = '00100115' AND TimeOut BETWEEN
> '2005-08-02 00:00:00.000' AND '2005-08-02 23:59:59.999' GROUP BY
> ProductCode ORDER By ProductCode;
>
> SELECT * FROM tDailyJobTotals;
>
> ProductCode,DailyLoads,DailyTons,JobLoads,JobTons
> '2',,,226,1995.205000
> '304 02',4,39.080000,370,4368.310000
> '4',,,369,2556.910000
> '411 02',2,7.680000,303,1306.415000
> '467',,,10,36.310000
> '57W',9,39.610000,2889,14345.055000
> '67',3,48.170000,31,328.010000
> '7',,,38,121.750000
> '703 05',,,85,359.349000
> '703 10',,,327,773.770000
> '8W',2,11.130000,910,3149.570000
> 'C 304',,,208,1335.080000
> 'CGL ',,,2,16.080000
> 'SALT',,,41,80.320000
> 'SNT 14',,,37,135.970000
> 'TYPE B',,,6,34.640000
> 'TYPE C',,,81,654.960000
>
>
>
> ---------------------------------
> Eddie Sizemore
> Senior Analyst
> Global Software, Inc.
> Email: esizemore@qx.net
> Web: www.globalsoftware-inc.com
> Phone: 812.246.2819
> ---------------------------------
>
>


Eddie Sizemore Posted on 2005-09-01 17:43:58.0Z
From: "Eddie Sizemore" <esizemoreno@spamqx.net>
Newsgroups: ianywhere.public.general
References: <42fb8b79$1@forums-1-dub> <43173373$1@forums-1-dub>
Subject: Re: ON Existing update problem
Lines: 149
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 12-203-188-187.client.insightbb.com
X-Original-NNTP-Posting-Host: 12-203-188-187.client.insightbb.com
Message-ID: <43173dde$1@forums-1-dub>
Date: 1 Sep 2005 10:43:58 -0700
X-Trace: forums-1-dub 1125596638 12.203.188.187 (1 Sep 2005 10:43:58 -0700)
X-Original-Trace: 1 Sep 2005 10:43:58 -0700, 12-203-188-187.client.insightbb.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4741
Article PK: 17333

I just tested it with Version 9.0.2.3169 and it works fine.

"anil k goel" <firstname.no-junk.lastname@spam.ianywhere.com> wrote in
message news:43173373$1@forums-1-dub...
> Is the following still a problem (someone pointed this post out to me
> today)? If yes, I will take a look:
>
> Please post the following information:
>
> 1. The contents of tDailyJobTotals *before* the INSERT ON EXISTING UPDATE
> runs.
> 2. The result set returned by the SELECT statement used in the INSERT
> statement.
>
> The post below already contains the contents of tDailyJobTotals *after*
> the INSERT.
>
> --
> -anil
> Research and Development, Query Processing
> iAnywhere Solutions Engineering
>
> -------------------------------------------------------------------------
> ** Whitepapers, TechDocs, bug fixes are all available through the **
> ** iAnywhere Developer Community at http://www.ianywhere.com/developer **
> -------------------------------------------------------------------------
>
> "Eddie Sizemore" <esizemoreno@spamqx.net> wrote in message
> news:42fb8b79$1@forums-1-dub...
>> Adaptive Server Anywhere Network Server Version 9.0.2.3131
>>
>> I need to get a count (JobLoads) and a sum (JobTons) for each product
>> code on all tickets with a sitecode of 25 and job code of '00100115' (Job
>> Query) I also need the same info for a day (DailyLoads, DailyTons). The
>> daily query range will be from midnight to a time a ticket was created.
>> I have a temp table to hold the results of the query. If I load the
>> temp table with the daily query first. I get a table with 5 rows with my
>> test data. Then I run the job query which inserts rows in the same temp
>> table with ON EXISTING UPDATE. The job query inserts/updates 17 rows.
>> The inserts are ok but the updates lost my daily loads and dailytons info
>> for 4 of the rows inserted by the daily query.
>>
>> However, if I switch the order of the two queries then the On EXISTING
>> UPDATE works ok. The order for the two queries should not make any
>> difference as I am using on existing update and the only column the
>> queries have in common is the pk productcode.
>>
>> I need to run the daily query first
>>
>> I have included the temp table and queries below along with the returned
>> rows.
>>
>>
>>
>> CREATE GLOBAL TEMPORARY TABLE "DBA"."tDailyJobTotals" (
>> "ProductCode" CHAR(10) NOT NULL,
>> "DailyLoads" INTEGER NULL,
>> "DailyTons" DECIMAL(30,6) NULL,
>> "JobLoads" INTEGER NULL,
>> "JobTons" DECIMAL(30,6) NULL,
>> PRIMARY KEY ("ProductCode")
>> ) ON COMMIT PRESERVE ROWS ;
>>
>> -- This gives wrong info
>> TRUNCATE TABLE tDailyJobTotals;
>>
>> INSERT INTO DBA.tDailyJobTotals (ProductCode, DailyLoads, DailyTons)
>> SELECT ProductCode, COUNT(), SUM(NetTons) FROM DBA.Ticket
>> WHERE SiteCode = '25' AND JobCode = '00100115' AND TimeOut BETWEEN
>> '2005-08-02 00:00:00.000' AND '2005-08-02 23:59:59.999' GROUP BY
>> ProductCode ORDER By ProductCode;
>>
>> INSERT INTO DBA.tDailyJobTotals (ProductCode, JobLoads, JobTons) ON
>> EXISTING UPDATE SELECT ProductCode, COUNT(), SUM(NetTons) FROM DBA.Ticket
>> WHERE SiteCode = '25' AND JobCode = '00100115' GROUP BY ProductCode ORDER
>> By ProductCode;
>>
>> SELECT * FROM tDailyJobTotals;
>>
>> ProductCode,DailyLoads,DailyTons,JobLoads,JobTons
>> '304 02',,,370,4368.310000
>> '411 02',,,303,1306.415000
>> '57W',,,2889,14345.055000
>> '67',3,48.170000,31,328.010000
>> '8W',,,910,3149.570000
>> '2',,,226,1995.205000
>> '4',,,369,2556.910000
>> '467',,,10,36.310000
>> '7',,,38,121.750000
>> '703 05',,,85,359.349000
>> '703 10',,,327,773.770000
>> 'C 304',,,208,1335.080000
>> 'CGL ',,,2,16.080000
>> 'SALT',,,41,80.320000
>> 'SNT 14',,,37,135.970000
>> 'TYPE B',,,6,34.640000
>> 'TYPE C',,,81,654.960000
>>
>> -- This works but I need to run them in the other order
>> TRUNCATE TABLE tDailyJobTotals;
>>
>> INSERT INTO DBA.tDailyJobTotals (ProductCode, JobLoads, JobTons) SELECT
>> ProductCode, COUNT(), SUM(NetTons) FROM DBA.Ticket
>> WHERE SiteCode = '25' AND JobCode = '00100115' GROUP BY ProductCode ORDER
>> By ProductCode;
>>
>> INSERT INTO DBA.tDailyJobTotals (ProductCode, DailyLoads, DailyTons) ON
>> EXISTING UPDATE SELECT ProductCode, COUNT(), SUM(NetTons) FROM DBA.Ticket
>> WHERE SiteCode = '25' AND JobCode = '00100115' AND TimeOut BETWEEN
>> '2005-08-02 00:00:00.000' AND '2005-08-02 23:59:59.999' GROUP BY
>> ProductCode ORDER By ProductCode;
>>
>> SELECT * FROM tDailyJobTotals;
>>
>> ProductCode,DailyLoads,DailyTons,JobLoads,JobTons
>> '2',,,226,1995.205000
>> '304 02',4,39.080000,370,4368.310000
>> '4',,,369,2556.910000
>> '411 02',2,7.680000,303,1306.415000
>> '467',,,10,36.310000
>> '57W',9,39.610000,2889,14345.055000
>> '67',3,48.170000,31,328.010000
>> '7',,,38,121.750000
>> '703 05',,,85,359.349000
>> '703 10',,,327,773.770000
>> '8W',2,11.130000,910,3149.570000
>> 'C 304',,,208,1335.080000
>> 'CGL ',,,2,16.080000
>> 'SALT',,,41,80.320000
>> 'SNT 14',,,37,135.970000
>> 'TYPE B',,,6,34.640000
>> 'TYPE C',,,81,654.960000
>>
>>
>>
>> ---------------------------------
>> Eddie Sizemore
>> Senior Analyst
>> Global Software, Inc.
>> Email: esizemore@qx.net
>> Web: www.globalsoftware-inc.com
>> Phone: 812.246.2819
>> ---------------------------------
>>
>>
>
>