AS400 Query

I have a simple query - listing all line #'s an all info that goes with each -(pretty much all of the columns are itentical - just the total is diff - I need to get the sum of the invoice - which I have - BUT now I want just the summary of the invoice - all of the columns from the detail with the total - how can I print this?? see example
ATEISO SOLDTO SHIPTO CABBV DABBV TO
007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
TOTAL 46,085.9
i want
007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
From http://www.developmentnow.com/g/118_2005_8_0_24_0/microsoft-public-sqlserver-server.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Of course this is a Microsoft SQL Server newsgroup, but the SQL needed
for this is pretty simple.
SELECT ATEISO, SOLDTO, SHIPTO, CABBV, DABBV,
SUM(TO) as TotalTO
FROM Whatever
GROUP BY ATEISO, SOLDTO, SHIPTO, CABBV, DABBV
That assumes that you know and apply whatever the workaround is in
your dialect of SQL for a column name (TO) that is a SQL reserved
word. Or maybe it is not reserved in your dialect.
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 16:11:53 GMT, Kathy
Goldie<kathy.goldie@.maps-na.com> wrote:

>I have a simple query - listing all line #'s an all info that goes with each -(pretty much all of the columns are itentical - just the total is diff - I need to get the sum of the invoice - which I have - BUT now I want just the summary of the invoice - all of the columns from the detail with the total - how can I print this?? see example
> ATEISO SOLDTO SHIPTO CABBV DABBV TO
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
> TOTAL 46,085.9
>i want
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
>
>From http://www.developmentnow.com/g/118_2005_8_0_24_0/microsoft-public-sqlserver-server.htm
>Posted via DevelopmentNow.com Groups
>http://www.developmentnow.com

AS400 Query

I have a simple query - listing all line #'s an all info that goes with eac
h -(pretty much all of the columns are itentical - just the total is diff -
I need to get the sum of the invoice - which I have - BUT now I want just th
e summary of the invoice -
all of the columns from the detail with the total - how can I print this?
'? see example
ATEISO SOLDTO SHIPTO CABBV DABBV TO
007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
TOTAL 46,085.9
i want
007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
From http://www.developmentnow.com/g/118... />
server.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.comOf course this is a Microsoft SQL Server newsgroup, but the SQL needed
for this is pretty simple.
SELECT ATEISO, SOLDTO, SHIPTO, CABBV, DABBV,
SUM(TO) as TotalTO
FROM Whatever
GROUP BY ATEISO, SOLDTO, SHIPTO, CABBV, DABBV
That assumes that you know and apply whatever the workaround is in
your dialect of SQL for a column name (TO) that is a SQL reserved
word. Or maybe it is not reserved in your dialect.
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 16:11:53 GMT, Kathy
Goldie<kathy.goldie@.maps-na.com> wrote:

>I have a simple query - listing all line #'s an all info that goes with each -(pre
tty much all of the columns are itentical - just the total is diff - I need to get t
he sum of the invoice - which I have - BUT now I want just the summary of the invoic
e -
all of the columns from the detail with the total - how can I print this'' see exampl
e
> ATEISO SOLDTO SHIPTO CABBV DABBV TO
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
> TOTAL 46,085.9
>i want
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
>
>From http://www.developmentnow.com/g/118.../>
-server.htm
>Posted via DevelopmentNow.com Groups
>http://www.developmentnow.com

AS400 Query

I have a simple query - listing all line #'s an all info that goes with each -(pretty much all of the columns are itentical - just the total is diff - I need to get the sum of the invoice - which I have - BUT now I want just the summary of the invoice - all of the columns from the detail with the total - how can I print this'' see example
ATEISO SOLDTO SHIPTO CABBV DABBV TO
007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
TOTAL 46,085.9
i want
007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
From http://www.developmentnow.com/g/118_2005_8_0_24_0/microsoft-public-sqlserver-server.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comOf course this is a Microsoft SQL Server newsgroup, but the SQL needed
for this is pretty simple.
SELECT ATEISO, SOLDTO, SHIPTO, CABBV, DABBV,
SUM(TO) as TotalTO
FROM Whatever
GROUP BY ATEISO, SOLDTO, SHIPTO, CABBV, DABBV
That assumes that you know and apply whatever the workaround is in
your dialect of SQL for a column name (TO) that is a SQL reserved
word. Or maybe it is not reserved in your dialect.
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 16:11:53 GMT, Kathy
Goldie<kathy.goldie@.maps-na.com> wrote:
>I have a simple query - listing all line #'s an all info that goes with each -(pretty much all of the columns are itentical - just the total is diff - I need to get the sum of the invoice - which I have - BUT now I want just the summary of the invoice - all of the columns from the detail with the total - how can I print this'' see example
> ATEISO SOLDTO SHIPTO CABBV DABBV TO
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
> TOTAL 46,085.9
>i want
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
>
>From http://www.developmentnow.com/g/118_2005_8_0_24_0/microsoft-public-sqlserver-server.htm
>Posted via DevelopmentNow.com Groups
>http://www.developmentnow.com

Arrrggh - Linked Server issue

Help! I can't seem to get the permissions right on my linked server. I keep getting this error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSOLAP.3" for linked server "DW" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "MSOLAP.3" for linked server "DW".

Here you go: http://support.microsoft.com/kb/925869

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you. That helped. Now when i query using a SQL Server user (non-windows) I get this response:

OLE DB provider "MSOLAP" for linked server "DW" returned message "An error was encountered in the transport layer.".

OLE DB provider "MSOLAP" for linked server "DW" returned message "The peer prematurely closed the connection.".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "DW".

I noticed in the profiler that the user who is connecting is anonymous logon and I turned the required auth on AS off, but I have no idea what permissions are needed to allow this to work.

Arithmetic overflow error converting expression to data type int.

select convert(bigint,21568194 * 100)
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Why does this happen, How do I fix?select convert(bigint,CAST(21568194 AS bigint) * 100)
OR
select CAST(21568194 AS bigint) * 100
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:BB3435B9-09B1-4260-B03B-5F19E78E084F@.microsoft.com...
> select convert(bigint,21568194 * 100)
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
> Why does this happen, How do I fix?

Arithmetic overflow error ...

Hello,
When i execute one query the following error appear,
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data
type int.
I cant understand what is happening but i check one site,
that talk about ".... NOTE: The overflow fix has been
incorporated into Coefficient version 1.1.6 and later
releases. Please update your software to the current
version. The text for the original 1.1.5 fix follows: "
I dont know what to do, but i send you the query because
its possible that im doing something wrong.
select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
[Avarage Exec Time in Milliseconds],
max(datediff(millisecond,s.StartTime,c.EndTime)) as
[Maximum Exec Time in Milliseconds],
min(datediff(millisecond,s.StartTime,c.EndTime)) as
[Minimum Exec Time in Milliseconds]
from T1 s, T2 c
where s.Textdata like c.Textdata
go
Thanks,
Best regards
datediff produces an INT. So, if the difference between starttime and
endtime is larger than ~2 billion, overflow.
You might take the datediff in minutes, cast to BIGINT, and multiply by 60.
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17cba01c44992$6ea5d0b0$a401280a@.phx.gbl...
> Hello,
> When i execute one query the following error appear,
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data
> type int.
> I cant understand what is happening but i check one site,
> that talk about ".... NOTE: The overflow fix has been
> incorporated into Coefficient version 1.1.6 and later
> releases. Please update your software to the current
> version. The text for the original 1.1.5 fix follows: "
> I dont know what to do, but i send you the query because
> its possible that im doing something wrong.
> select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Avarage Exec Time in Milliseconds],
> max(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Maximum Exec Time in Milliseconds],
> min(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Minimum Exec Time in Milliseconds]
> from T1 s, T2 c
> where s.Textdata like c.Textdata
> go
> Thanks,
> Best regards
|||> You might take the datediff in minutes, cast to BIGINT, and multiply by
60.
After the AVG (which might have the same problem on BIGINT).

Arithmetic overflow error ...

Hello,
When i execute one query the following error appear,
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data
type int.
I cant understand what is happening but i check one site,
that talk about ".... NOTE: The overflow fix has been
incorporated into Coefficient version 1.1.6 and later
releases. Please update your software to the current
version. The text for the original 1.1.5 fix follows: "
I dont know what to do, but i send you the query because
its possible that im doing something wrong.
select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
[Avarage Exec Time in Milliseconds],
max(datediff(millisecond,s.StartTime,c.EndTime)) as
[Maximum Exec Time in Milliseconds],
min(datediff(millisecond,s.StartTime,c.EndTime)) as
[Minimum Exec Time in Milliseconds]
from T1 s, T2 c
where s.Textdata like c.Textdata
go
Thanks,
Best regardsdatediff produces an INT. So, if the difference between starttime and
endtime is larger than ~2 billion, overflow.
You might take the datediff in minutes, cast to BIGINT, and multiply by 60.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17cba01c44992$6ea5d0b0$a401280a@.phx.gbl...
> Hello,
> When i execute one query the following error appear,
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data
> type int.
> I cant understand what is happening but i check one site,
> that talk about ".... NOTE: The overflow fix has been
> incorporated into Coefficient version 1.1.6 and later
> releases. Please update your software to the current
> version. The text for the original 1.1.5 fix follows: "
> I dont know what to do, but i send you the query because
> its possible that im doing something wrong.
> select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Avarage Exec Time in Milliseconds],
> max(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Maximum Exec Time in Milliseconds],
> min(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Minimum Exec Time in Milliseconds]
> from T1 s, T2 c
> where s.Textdata like c.Textdata
> go
> Thanks,
> Best regards|||> You might take the datediff in minutes, cast to BIGINT, and multiply by
60.
After the AVG (which might have the same problem on BIGINT).

Arithmetic overflow error ...

Hello,
When i execute one query the following error appear,
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data
type int.
I cant understand what is happening but i check one site,
that talk about ".... NOTE: The overflow fix has been
incorporated into Coefficient version 1.1.6 and later
releases. Please update your software to the current
version. The text for the original 1.1.5 fix follows: "
I dont know what to do, but i send you the query because
its possible that im doing something wrong.
select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
[Avarage Exec Time in Milliseconds],
max(datediff(millisecond,s.StartTime,c.EndTime)) as
[Maximum Exec Time in Milliseconds],
min(datediff(millisecond,s.StartTime,c.EndTime)) as
[Minimum Exec Time in Milliseconds]
from T1 s, T2 c
where s.Textdata like c.Textdata
go
Thanks,
Best regardsdatediff produces an INT. So, if the difference between starttime and
endtime is larger than ~2 billion, overflow.
You might take the datediff in minutes, cast to BIGINT, and multiply by 60.
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17cba01c44992$6ea5d0b0$a401280a@.phx
.gbl...
> Hello,
> When i execute one query the following error appear,
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data
> type int.
> I cant understand what is happening but i check one site,
> that talk about ".... NOTE: The overflow fix has been
> incorporated into Coefficient version 1.1.6 and later
> releases. Please update your software to the current
> version. The text for the original 1.1.5 fix follows: "
> I dont know what to do, but i send you the query because
> its possible that im doing something wrong.
> select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Avarage Exec Time in Milliseconds],
> max(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Maximum Exec Time in Milliseconds],
> min(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Minimum Exec Time in Milliseconds]
> from T1 s, T2 c
> where s.Textdata like c.Textdata
> go
> Thanks,
> Best regards|||> You might take the datediff in minutes, cast to BIGINT, and multiply by
60.
After the AVG (which might have the same problem on BIGINT).

Arithmetic overflow error

I got the following error when running a SP:

Server: Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting nvarchar to data type numeric.
The statement has been terminated.

The stupid thing is, that there is no data conversion at all. It's an insert into SLQ server table where data is retrieved from an Oracle View (using ADO DB link). I got 4 other SP's, doing the same thing for resp 4 other tables, which works fine. Those :mad: SP won't work. I don't know why. Below I put the table structure, view structure and SP I used:

Table:
Contract_No varchar (20) NOT NULL
Registration_Date_Time datetime NOT NULL
AGC varchar (4) NOT NULL
Salesgroup varchar (4) NOT NULL
Group_ varchar (8) NOT NULL
Activity_Type varchar (4) NULL
Type char (1) NULL
Group_Description varchar (50) NULL
Stock_Um varchar (4) NULL
B_Qty numeric(11, 4) NULL
B_Cost numeric(23, 4) NULL
C_Qty numeric(11, 4) NULL
C_Cost numeric(24, 4) NULL
D_Qty numeric(11, 4) NULL
D_Cost numeric(24, 4) NULL

Oracele view:
CONTRACT_NO VARCHAR2(20)
AGC VARCHAR2(4)
SALESGROUP VARCHAR2(4)
GROUP_ VARCHAR2(8)
ACTIVITY_TYPE VARCHAR2(4)
TYPE CHAR(1)
GROUP_DESCRIPTION VARCHAR2(50)
STOCK_UM VARCHAR2(4)
B_QTY NUMBER
B_COST NUMBER
C_QTY NUMBER
C_COST NUMBER
D_QTY NUMBER
D_COST NUMBER

Stored procedure:
CREATE PROCEDURE mis_Upload_Contract_Kosten
@.strType varchar(10),
@.strDate varchar(19)
AS
declare @.strInsert as varchar(1000);
declare @.strSelect as varchar(1000);
declare @.strWhere as varchar(1000);
declare @.strSql as varchar(3019);

SET @.strWhere = ''

SET @.strInsert = 'INSERT C_Contract_Kosten (
Contract_No
, AGC
, Salesgroup
, Group_
, Activity_Type
, Type
, Group_Description
, Stock_Um
, B_Qty
, B_Cost
, C_Qty
, C_Cost
, D_Qty
, D_Cost
, Registration_Date_Time)'

SET @.strSelect = ' SELECT gLCK.Contract_No
, gLCK.AGC
, gLCK.Salesgroup
, gLCK.Group_
, gLCK.Activity_Type
, gLCK.Type
, gLCK.Group_Description
, gLCK.Stock_Um
, gLCK.B_Qty
, gLCK.B_Cost
, gLCK.C_Qty
, gLCK.C_Cost
, gLCK.D_Qty
, gLCK.D_Cost
, ' + char(39) + @.strDate + char(39) + '
FROM Glovia..LIVE.C_CONTRACT_KOSTEN as gLCK
WHERE gLCK.Contract_No NOT LIKE '' IND*''
AND NOT EXISTS
( SELECT vCC.Contract_No
FROM V_Contracts_Closed as vCC
WHERE vCC.Contract_No = gLCK.Contract_No)
AND EXISTS
( SELECT cc.Contract_No
FROM C_Contracten as cc
WHERE cc.Registration_Date_Time = ' + char(39) + @.strDate + char(39) + '
AND cc.Contract_No = gLCK.Contract_No)'

IF @.strType = 'closed'
BEGIN
SET @.strWhere = ' AND NOT(gLCK.Contract_Close_Date IS NULL)'
END

IF @.strType = 'open'
BEGIN
SET @.strWhere = ' AND gLCK.Contract_Close_Date IS NULL'
END

SET @.strSql = @.strInsert + @.strSelect + @.strWhere

EXEC (@.strSql)
GOWell, at least the problem is solved. There was something wrong in the data itself. The x_QTY en x_COST fields contained sometimes a value with 41 numbers (huge list of numbers after the decimal sign). I changed it in the Oracle views, and now it's all working.

Arithmetic error

When I run a view, I am getting an SQL (ODBC) error "Arithmetic overflow
error converting real to data type numeric." Below is the line of code in
the view that I think is causing the error (dbo.WorkerTimesheets.PayRate is
a real data type and has a value of 730). Can anyone help me fix it?
Thanks.
CONVERT(varchar(6), CONVERT(decimal(4, 2), dbo.WorkerTimesheets.PayRate)) +
'/' + dbo.PayRateCodes.RateName AS txtPayRate
DavidOf course it overflows. You reserve 4 places for the entire numeric, 2 of
which are decimal places.
730 already takes up 3 places. Add 2 for decimals to that and you get 5.
Give it more room. :) I'd suggest using decimal(12, 2). If this is used to
store wages or salaries, 8 places is more than enough. :)
Spend more time reading BOL.
ML|||Geez! When I said 8 I meant 10. Yes, it's late...|||Do not use CONVERT() when you have CAST(). Never use FLOAT or REAL
unless you know what you are doing. There are all kinds rounding
errors and they display with the 'E' format.
Why are you converting numerics to strings? The first rule of a tiered
architecture is that you never do formatting in the database.
Re-do your pay rates with DECIMAL() data type; you do not bill to 16
decimal places, do you? . Read up on floating point numbers.|||You are all correct. The "overflow" was my brain. When I look at it
now, it makes sense.
David
*** Sent via Developersdex http://www.examnotes.net ***