2005 Pivot Question - Not returning data..not sure what i did.

Okay, using examples I found on the internet, I managed to piece this
together. Now the part in the data section, works, and i get the data to
come out. Once I add the pivot to it, it stops producing results.
I am guessing that I have 2 or 3 major problems with this.
1. Where do i send the output params, as far as what equals what.
2. How do i reference the columns in the nested query (defined AS Data) in
the outer query.. (not sure if i even have that part in there right)..but i
t
needs to tilt on the account tag, and sum the quantity.
Dataset example:
Forward Date AcctTag Symbol Strike price
Typecode Quantity
9/1/2005 12:00:00 AM MN501 CL-OTC 0 F -25
9/1/2005 12:00:00 AM SU185 CL-OTC 0 F 85
10/1/2005 12:00:00 AM SU185 CL-OTC 0 F -45
3. Probably something else in there, that i can't figure out..but i figure
theres one unseen problem in there.
Use XXXCAPMAN
Go
sp_dbcmptlevel aaacapman, 90 'forces into SQL2005 mode
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_POSSheets
(
@.inUSERID varchar(20),
@.inCommodities varchar(250),
@.outSU185 int OUTPUT,
@.outBW685 int OUTPUT,
@.outMN053 int OUTPUT,
@.outSU598 int OUTPUT,
@.outSU959 int OUTPUT,
@.outSU897 int OUTPUT,
@.outSU896 int OUTPUT,
@.outSU797 int OUTPUT,
@.outSU524 int OUTPUT,
@.outSU511 int OUTPUT,
@.outSU397 int OUTPUT,
@.outSU960 int OUTPUT,
@.outSU895 int OUTPUT,
@.outSU935 int OUTPUT,
@.out9SU809 int OUTPUT,
@.out9SU885 int OUTPUT,
@.outMN051 int OUTPUT,
@.outSymbol varchar(20) OUTPUT,
@.outForwardDate varchar(20) OUTPUT,
@.outStrikePrice int OUTPUT,
@.outTypeCode int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT
[1] AS 'SU185'
,[2] AS 'MN053'
,[3] AS 'SU598'
,[4] AS 'SU959'
,[5] AS 'SU897'
,[6] AS 'SU896'
,[7] AS 'SU797'
,[8] AS 'SU524'
,[9] AS 'SU511'
,[10] AS 'SU397'
,[11] AS 'SU960'
,[12] AS 'SU895'
,[13] AS 'SU935'
,[14] AS '9SU809'
,[15] AS '9SU885'
,[16] AS 'MN051'
,[17] AS 'BW685'
From (
SELECT TOP (100) PERCENT dbo.Trades.ForwardDate, dbo.Accounts.[Account
Tag], dbo.Commodity.Symbol, dbo.Trades.StrikePrice, dbo.Trades.TypeCode,
dbo.Trades.Quantity
FROM dbo.Users INNER JOIN
dbo.Commodity INNER JOIN dbo.Accounts INNER JOIN dbo.Trades ON
dbo.Accounts.ID = dbo.Trades.AccountCode ON dbo.Commodity.ID =
dbo.Trades.CommodityCode ON dbo.Users.ID = dbo.Trades.Trader
WHERE (dbo.Commodity.Symbol LIKE N'CL%' OR
dbo.Commodity.Symbol LIKE N'CL-OTC%') AND (dbo.Accounts.SheetAccount = 1)
AND (dbo.Trades.ActiveFlag = 1) AND (dbo.Users.UserShortName = N'Carl')
GROUP BY dbo.Trades.ForwardDate, dbo.Commodity.Symbol,
dbo.Trades.StrikePrice, dbo.Trades.TypeCode, dbo.Accounts.[Account Tag],
dbo.Trades.Quantity
ORDER BY dbo.Trades.ForwardDate ) AS Data
PIVOT(
Sum(Data.Quantity)
For Data.[Account Tag]
IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[
10],[11],[12],[13],[14],[15],[16],[1
7])
) AS PVT
END
GO
www.krushradio.com - Internet Radio for the rest of usLet me know if I was not clear on what i am trying to accomplish...
I would really like to understand what parts are incorrect...or how the
dynamics work for the new pivot function. The examples I've found are
without the datasamples, so i can't really use them to pick apart the actual
queries.
Thanks
Dan Regalia
www.krushradio.com - Internet Radio for the rest of us
"Daniel Regalia" wrote:

> Okay, using examples I found on the internet, I managed to piece this
> together. Now the part in the data section, works, and i get the data to
> come out. Once I add the pivot to it, it stops producing results.
> I am guessing that I have 2 or 3 major problems with this.
> 1. Where do i send the output params, as far as what equals what.
> 2. How do i reference the columns in the nested query (defined AS Data) i
n
> the outer query.. (not sure if i even have that part in there right)..but
it
> needs to tilt on the account tag, and sum the quantity.
> Dataset example:
> Forward Date AcctTag Symbol Strike pri
ce
> Typecode Quantity
> 9/1/2005 12:00:00 AM MN501 CL-OTC 0 F -25
> 9/1/2005 12:00:00 AM SU185 CL-OTC 0 F 85
> 10/1/2005 12:00:00 AM SU185 CL-OTC 0 F -45
> 3. Probably something else in there, that i can't figure out..but i figur
e
> theres one unseen problem in there.
>
> Use XXXCAPMAN
> Go
> sp_dbcmptlevel aaacapman, 90 'forces into SQL2005 mode
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE sp_POSSheets
> (
> @.inUSERID varchar(20),
> @.inCommodities varchar(250),
> @.outSU185 int OUTPUT,
> @.outBW685 int OUTPUT,
> @.outMN053 int OUTPUT,
> @.outSU598 int OUTPUT,
> @.outSU959 int OUTPUT,
> @.outSU897 int OUTPUT,
> @.outSU896 int OUTPUT,
> @.outSU797 int OUTPUT,
> @.outSU524 int OUTPUT,
> @.outSU511 int OUTPUT,
> @.outSU397 int OUTPUT,
> @.outSU960 int OUTPUT,
> @.outSU895 int OUTPUT,
> @.outSU935 int OUTPUT,
> @.out9SU809 int OUTPUT,
> @.out9SU885 int OUTPUT,
> @.outMN051 int OUTPUT,
> @.outSymbol varchar(20) OUTPUT,
> @.outForwardDate varchar(20) OUTPUT,
> @.outStrikePrice int OUTPUT,
> @.outTypeCode int OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> SELECT
> [1] AS 'SU185'
> ,[2] AS 'MN053'
> ,[3] AS 'SU598'
> ,[4] AS 'SU959'
> ,[5] AS 'SU897'
> ,[6] AS 'SU896'
> ,[7] AS 'SU797'
> ,[8] AS 'SU524'
> ,[9] AS 'SU511'
> ,[10] AS 'SU397'
> ,[11] AS 'SU960'
> ,[12] AS 'SU895'
> ,[13] AS 'SU935'
> ,[14] AS '9SU809'
> ,[15] AS '9SU885'
> ,[16] AS 'MN051'
> ,[17] AS 'BW685'
> From (
> SELECT TOP (100) PERCENT dbo.Trades.ForwardDate, dbo.Accounts.[Account
> Tag], dbo.Commodity.Symbol, dbo.Trades.StrikePrice, dbo.Trades.TypeCode,
> dbo.Trades.Quantity
> FROM dbo.Users INNER JOIN
> dbo.Commodity INNER JOIN dbo.Accounts INNER JOIN dbo.Trades ON
> dbo.Accounts.ID = dbo.Trades.AccountCode ON dbo.Commodity.ID =
> dbo.Trades.CommodityCode ON dbo.Users.ID = dbo.Trades.Trader
> WHERE (dbo.Commodity.Symbol LIKE N'CL%' OR
> dbo.Commodity.Symbol LIKE N'CL-OTC%') AND (dbo.Accounts.SheetAccount = 1)
> AND (dbo.Trades.ActiveFlag = 1) AND (dbo.Users.UserShortName = N'Carl')
> GROUP BY dbo.Trades.ForwardDate, dbo.Commodity.Symbol,
> dbo.Trades.StrikePrice, dbo.Trades.TypeCode, dbo.Accounts.[Account Tag],
> dbo.Trades.Quantity
> ORDER BY dbo.Trades.ForwardDate ) AS Data
> PIVOT(
> Sum(Data.Quantity)
> For Data.[Account Tag]
> IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[
10],[11],[12],[13],[14],[15],[16],
[17])
> ) AS PVT
> END
> GO
>
>
> --
> www.krushradio.com - Internet Radio for the rest of us

2005 Internet Replication

Is there any experience with SQL Server 2005 replication?
I want to do replication via Internet without implement VPN or physical
connection to SQL Server.
I think that this is imposible with SQL Server 2000 but watch a presentation
on TECHED which
says that this is possible with SQL Server 2005 ?
Grigoris,
I have set this up in a test environment and you're correct - using SQL
2005, it is now possible to use merge replication over HTTPS. Although you
need to be comfortable issuing certificates, maintaining virtual directories
etc, the guidelines in BOL are quite thorough and helpful. There is a
mistake in some documentation, and HTTP is not supported, so you have to
deal with certificates at some stage. Have a look at BOL for the full info,
and if you have any questions, probably the best place for the moment is the
beta newsgroups:
http://communities.microsoft.com/new...2005&slcid=us.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I see. Well they (Microsoft) keep to do things complicated.
I mean you need IIS, sertificates, http and ftp not supported and so on...
Well this is step forward but..
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eUAEsp0ZFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Grigoris,
> I have set this up in a test environment and you're correct - using SQL
> 2005, it is now possible to use merge replication over HTTPS. Although you
> need to be comfortable issuing certificates, maintaining virtual
> directories etc, the guidelines in BOL are quite thorough and helpful.
> There is a mistake in some documentation, and HTTP is not supported, so
> you have to deal with certificates at some stage. Have a look at BOL for
> the full info, and if you have any questions, probably the best place for
> the moment is the beta newsgroups:
> http://communities.microsoft.com/new...2005&slcid=us.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>