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