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

AS2005. Strange MDX behaviour.

Hi, MDX gurus,

I have a unexplainable problem with pretty easy MDX.

Following MDX queries

//
select
Filter([Date].[Date].members,
[Date].[Date].CurrentMember.MemberValue = VBA![dateadd]("yyyy", -2, VBA![Date]()) )
on 0,
{} on 1
from [Adventure Works]

//
select
Filter([Date].[Date].members,
[Date].[Date].CurrentMember.MemberValue = CDate("11.07.2004"))
on 0,
{} on 1
from [Adventure Works]

provide the same result as expected.

This query

//
select
Filter([Date].[Calendar].[Month].members,
[Date].[Date].CurrentMember.MemberValue = CDate("11.07.2004"))
on 0,
{} on 1
from [Adventure Works]

returns as expected one member

but this query

select
Filter([Date].[Calendar].[Month].members,
[Date].[Date].CurrentMember.MemberValue = VBA![dateadd]("yyyy", -2, VBA![Date]()) )
on 0,
{} on 1
from [Adventure Works]

retuns nothing. This is strange, isn't it?
Can anybody explain it?

Thanks in advance,

Vladimir Chtepa

The puzzle for me is not why your fourth query doesn't return anything - I don't think it should - but why the third query does return July 2004. In your third query you're filtering the members on [Date].[Calendar].[Month] and for each one checking the currentmember on [Date].[Date] - but the currentmember should be the All Member on [Date].[Date] in all cases, as the following queries show:

with member measures.test as [Date].[Date].CurrentMember.membervalue

select measures.test on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

and

with member measures.test as [Date].[Date].CurrentMember.membervalue = CDate("11/07/2004")

select measures.test on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Very strange...

Chris

|||

I don't know why 3-d query returns "expected" result.

It will be great If anybody from developer team could explain it.

Article - delete all data property

Hi There

I think this is a pretty straight forward question , just want confirmation.
I am using snapshot replication to a subscriber, it is essentially a re-publisher, these same tables are replicated from the subcriber to another.

If i select the delete all data option on the snapshot articles will it truncate the table or delete all data , i am sure it is delete as it says so but i need to be 100% sure that it does not truncate, as the truncate command is not allowed on tables that are published for replication , as the ones on the re-publisher are.

ThanxOk i have subsequently found out it does truncate.

So i have a problem.

Server A has transactional replication to Server B.

I want to setup snapshot replication from Server C to Server A, some of the articles involved are also replicated from Server A to Server B.

So essentially i want it to snapshot new data from Server C to Server A, it must delete all data present , thereby the articles involved in transactional replication from Server A to Server B must be cleaned out and replicated with new data accordingly.

Hope this makes sense, and i really hope it is possible !

Thanx|||Ok it hink i have figured it out, i have choosen the article property to delete all data matching filtered rows, but i do not filter rows , so presume it simply does not have a where clause but it performs a delete obviously not a truncate.

If some one can just confirm this is he correct way to go about it i would appreciate it.

Thanx|||I havent tried this, but I would guess, you would be better off starting afresh.
First setup snapshot replication from C-->A.
The setup Tran replication from A-->B with only those articles that you need from A to B.

This would be cleaner and easy to troubleshoot. And since you want new data from A-->B anyways, I dont see any harm in removing replication between A-->B and setting it afresh after A is refreshed from C.|||This needs to be automated, and occur on a regular basis.
Deleting subscriptions and re-setting up is not an option needs to integrate into the entire replication structure, including production. So you suggestions are not an option for me unfortunately.

Thanx|||Hi Sean,

I tried your scenario and it seemed to work fine.

I setup Tran replication between A-->B with some data
Then I setup Snapshot replication C-->A and the article had delete all data matching filter criteria but there was no filter and everything seemed to work fine.

Any changes made at C did seem to propagate to A and then to B. However snapshot had to be run at C since it is a snapshot publication. And I had both push subscriptions from C to A and A to B.

Please do try out and let us know if you encounter any issues.|||Thanx Mahesh

I will be going ahead with this on 1 production environemnt today or tomorrow, will update you on the results.

Thank You

Are there any MS SQL operations that are not transactional ?

I pretty new to MS SQL.
Long ago I heard that some MS SQL data definition operations are not
transactional.
Executed in the transaction's boundaries their results persists even if
transaction rolls back.
Could anyone ellaborat on that subject ?
Thank you.Table variables are non-transaction, that is to say, they aren't affected by
transaction scope - there is still logging on the transaction log.
declare @.tb table ( mycol int )
insert @.tb values( 1 )
begin tran
update @.tb set mycol = mycol + 1
rollback tran
select * from @.tb
The above returns 2, if you use a temporary table or permanent table you
will get 1...
create table #tb ( mycol int )
insert #tb values( 1 )
begin tran
update #tb set mycol = mycol + 1
rollback tran
select * from #tb
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Marek" <nospam@.nowhere.com> wrote in message
news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I pretty new to MS SQL.
> Long ago I heard that some MS SQL data definition operations are not
> transactional.
> Executed in the transaction's boundaries their results persists even if
> transaction rolls back.
> Could anyone ellaborat on that subject ?
> Thank you.
>|||TRUNCATE table.
"Marek" <nospam@.nowhere.com> wrote in message
news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I pretty new to MS SQL.
> Long ago I heard that some MS SQL data definition operations are not
> transactional.
> Executed in the transaction's boundaries their results persists even if
> transaction rolls back.
> Could anyone ellaborat on that subject ?
> Thank you.
>|||That's incorrect. TRUNCATE participates in a transaction like any other
DML operation (table variables excepted).
David Portas
SQL Server MVP
--|||> TRUNCATE table.
?
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
ROLLBACK
SELECT Col1 FROM MyTable
DROP TABLE MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <someone@.microsoft.com> wrote in message
news:%23LIQ8RM0FHA.1192@.TK2MSFTNGP10.phx.gbl...
> TRUNCATE table.
> "Marek" <nospam@.nowhere.com> wrote in message
> news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>|||You can have non transaction DDL inside a transaction if you use xp_cmdshell
and osql to execute the DDL. Extended stored procedures are not included in
a transaction. But if you are looking for a bug or "feature" where plain DDL
(table variables excepted as mentioned in the other posts) is not
transactional, there is none I am aware of in SQL Server 2000, although
there might have been in long ago versions like 6.0 or 6.5.
Jacco Schalkwijk
SQL Server MVP
"Marek" <nospam@.nowhere.com> wrote in message
news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I pretty new to MS SQL.
> Long ago I heard that some MS SQL data definition operations are not
> transactional.
> Executed in the transaction's boundaries their results persists even if
> transaction rolls back.
> Could anyone ellaborat on that subject ?
> Thank you.
>|||I stand corrected.
"JT" <someone@.microsoft.com> wrote in message
news:%23LIQ8RM0FHA.1192@.TK2MSFTNGP10.phx.gbl...
> TRUNCATE table.
> "Marek" <nospam@.nowhere.com> wrote in message
> news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>