Please Help ... Can't Figure This One Out

Greetings,
I need to write a SQL stored procedure to accomplish the following and I'm
not having any luck. So that I don't confuse anyone too much, I'll leave out
the code I'm trying and just explain what I'm trying to do.
Start with
Table: sequenceOrder
orderNumber totalUnits
101 26
102 11
103 9
104 8
105 16
I have a VB.NET program that will be passing in two parameters, one is
workDays (number of work days during a given w) and the other is
dailyUnits (maximum number units to be made during each wday).
I need a running total, by record line, for the totalUnits. If the running
total is less than dailyUnits, then I need to add the current value for that
work day. If the running total is greater than dailyUnits, then I need to
add 1 to the current value for that work day. Also, I need to number the
orders within that workDay.
Using the sample table above and having workDays = 3 and dailyUnits = 30 the
output table would like this:
Finish with
Table: sequenceOrder
orderNumber totalUnits workDay sequenceOrder
101 26 1 1
102 11 2 1
103 9 2 2
104 8 2 3
105 16 3 1
106 12 3 2
Is this possible, if so how do I get this done?
Thanks for all the help,
James Walker, Jr."James Walker" <walker@.modernfold.com> wrote in message
news:%232sOjRBgGHA.2188@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> I need to write a SQL stored procedure to accomplish the following and I'm
> not having any luck. So that I don't confuse anyone too much, I'll leave
> out the code I'm trying and just explain what I'm trying to do.
> Start with
> Table: sequenceOrder
> orderNumber totalUnits
> 101 26
> 102 11
> 103 9
> 104 8
> 105 16
> I have a VB.NET program that will be passing in two parameters, one is
> workDays (number of work days during a given w) and the other is
> dailyUnits (maximum number units to be made during each wday).
> I need a running total, by record line, for the totalUnits. If the running
> total is less than dailyUnits, then I need to add the current value for
> that work day. If the running total is greater than dailyUnits, then I
> need to add 1 to the current value for that work day. Also, I need to
> number the orders within that workDay.
> Using the sample table above and having workDays = 3 and dailyUnits = 30
> the output table would like this:
> Finish with
> Table: sequenceOrder
> orderNumber totalUnits workDay sequenceOrder
> 101 26 1 1
> 102 11 2 1
> 103 9 2 2
> 104 8 2 3
> 105 16 3 1
> 106 12 3 2
> Is this possible, if so how do I get this done?
>
Ok, this is a fun one. Here's a simple solution, using intiger division of
the running total by the number of hours per day. But I worry that the
assignment of orders to days is too "dumb". I have a suspicion that there's
a better solution somewhere that would sequence the work to fill up the days
and minimize idle time.
create table Orders
(
OrderNumber int primary key,
TotalUnits int not null
)
insert into Orders(orderNumber,TotalUnits)
select 101,26
union all select 102,11
union all select 103,9
union all select 104,8
union all select 105,16
go
declare @.days int,
@.dailyUnits int
set @.days = 3
set @.DailyUnits = 30;
with UpcomingOrders(OrderNumber,TotalUnits,Ru
nningTotal) as
(
select OrderNumber, TotalUnits,
(select sum(TotalUnits) from Orders where OrderNumber <= o.OrderNumber)
RunningTotal
from Orders o
)
select
Ordernumber,
TotalUnits,
1+RunningTotal/@.DailyUnits WorkDay,
row_number() over (partition by RunningTotal/@.DailyUnits order by
OrderNumber) SequenceOrder
from UpcomingOrders
where 1+RunningTotal/@.DailyUnits <= @.days
David|||David,
Thanks for the reply. It works great. Also, "filling up idle time" is in the
next phase. As in my example, the first day has 4 more units it could make,
so I will need to search down the records where the totalUnits is <= 4 and
then move that order up for the w.
James Walker, Jr.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23JMsfHCgGHA.452@.TK2MSFTNGP02.phx.gbl...
> "James Walker" <walker@.modernfold.com> wrote in message
> news:%232sOjRBgGHA.2188@.TK2MSFTNGP04.phx.gbl...
> Ok, this is a fun one. Here's a simple solution, using intiger division
> of the running total by the number of hours per day. But I worry that the
> assignment of orders to days is too "dumb". I have a suspicion that
> there's a better solution somewhere that would sequence the work to fill
> up the days and minimize idle time.
>
> create table Orders
> (
> OrderNumber int primary key,
> TotalUnits int not null
> )
> insert into Orders(orderNumber,TotalUnits)
> select 101,26
> union all select 102,11
> union all select 103,9
> union all select 104,8
> union all select 105,16
> go
> declare @.days int,
> @.dailyUnits int
> set @.days = 3
> set @.DailyUnits = 30;
>
> with UpcomingOrders(OrderNumber,TotalUnits,Ru
nningTotal) as
> (
> select OrderNumber, TotalUnits,
> (select sum(TotalUnits) from Orders where OrderNumber <= o.OrderNumber)
> RunningTotal
> from Orders o
> )
> select
> Ordernumber,
> TotalUnits,
> 1+RunningTotal/@.DailyUnits WorkDay,
> row_number() over (partition by RunningTotal/@.DailyUnits order by
> OrderNumber) SequenceOrder
> from UpcomingOrders
> where 1+RunningTotal/@.DailyUnits <= @.days
>
> David
>

Please help

I have a certain problem that I'm going to try and demonstrate using the northwind
database as an example.

I'm trying to write a stored procedure in SQL Server 2000, I am using the products table and
the order details table.

The first procedure I wrote uses a left outer join to show all the products and if the product
doesn't show up in the order details table it will show up with a zero no problem the stored procedure
is as follows and works great.

ALTER PROCEDURE dbo.StoredProcedure1
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]

FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID

GROUP BY dbo.Products.ProductID, dbo.Products.ProductName

Now I change the stored procedure to filter the second table by an orderid and now the only products
that show up are the products that exist in the second table.

My question is how do I show all the products like in the first stored procedure
and filter the stored procedures second table and still show all the products
including the ones that don't show up.

I wrote the second stored procedure and like I said it only shows the products that show up in the second table.

ALTER PROCEDURE dbo.StoredProcedure2
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]

FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID

GROUP BY dbo.Products.ProductID, dbo.Products.ProductName, dbo.[Order Details].OrderID

HAVING (dbo.[Order Details].OrderID = 10248)

I would really appreciate if somebody codes demonstrate to me how to do this
thanks in advance.This is how it hsould be:

SELECT dbo.Products.ProductID, dbo.Products.ProductName,COUNT(dbo.[Order Details].ProductID)AS[in orders]
FROM dbo.ProductsLEFTOUTERJOIN
dbo.[Order Details]ON dbo.Products.ProductID= dbo.[Order Details].ProductID
WHERE dbo.[Order Details].OrderID= 10248
GROUPBY dbo.Products.ProductID, dbo.Products.ProductName, dbo.[Order Details].OrderID

|||Thank you very much for your quick response ndinakar,
but the procedure you wrote gives the same result as the second procedure
I wrote.

What I wanted to do was show all the products that exist in the products table
like in the first procedure which shows all the products and products that don't show up
in the second table show up with a zero I would like to do the same thing but when I filter
the second table by the orderId the only result I get is the products that show up in the second table not all the products.

How can I show all the products from the products table
and still filter the productID in the second table|||Of course it only shows products from the second table. Your WHERE clause tells it to only show specific products from the second table.

Your question makes little sense...|||I showed have explained what I was trying to do in the first place,

I've written a program in ASP.NEV VB I have a drop-down list in a data grid
the drop-down list contains instructors names and the value contains their ID

The drop-down list data source is the instructors table each time and instructor makes a dive
it goes into the orders table by the instructors ID.

What I was trying to do is get a list of all the instructors and their ID and see how many times they show up
in the orders table for certain dates and arrange the drop-down list according to the amount of dive's
the instructor made during that date.

----------Instructors table----------

Inst_Idint40
Inst_Full_Namenvarchar500
Activebit10

-------Orders Table --------------

Order_Idint40
Activity_Idint40
Inst_Idint41
Client_Namenvarchar500
Telnvarchar301
Paymentint40
Voucher_Nonvarchar501
Agent_Idint41
Reservation_Noint41
Worker_Idint41
Order_Date_Timedatetime80
Equipmentnvarchar2|||Try something like:

SELECT dbo.Products.ProductID, dbo.Products.ProductName, ISNULL(COUNT(dbo.[Order Details].ProductID),0 AS [in orders]
FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID
GROUP BY dbo.Products.ProductID, dbo.Products.ProductName
ORDER BY (3)

This will give you a 0 value for products with no corresponding orders.

Does this help, or am I still misunderstanding the requirements?|||OK, I see what I was missing. That OrderDate requirement.

No problem. Just do your OUTER JOIN to an inline table (or subquery).

LEFT OUTER JOIN
 (SELECT ProductID, DetailID
 FROM [Order Details]
 WHERE OrderDate = @.OrderDate) AS OrderDetails

Now just treat your virtual OrderDetails table the same as you would your real OrderDetails table. The rest of the query should be the same.|||I didn't exactly understand how to do it I am quite new to SQL Server
I tried copying your script like so

CREATE PROCEDURE dbo.StoredProcedure1
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]
FROM dbo.Products LEFT OUTER JOIN
(SELECT ProductID, DetailID
FROM [Order Details]
WHERE OrderDate = @.OrderDate) AS OrderDetails

GO

But it did not work i probably don't understand how to make an inline table
if you could just give me a little more help thanks a lot|||SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(OrderDetails.DetailID) AS InOrders
FROM dbo.Products LEFT OUTER JOIN
(SELECT ProductID, DetailID
FROM [Order Details]
WHERE OrderDate = @.OrderDate) AS OrderDetails
GROUP BY dbo.Products.ProductID, dbo.Products.ProductName
ORDER BY (3)|||I tried using the procedure you wrote and then tried it with my actual table
and each time I get the same error incorrect Syntex neither the keywords 'group'
do you have any suggestions but I've also attached my actual procedure.

---------------------------

ALTER PROCEDURE dbo.GetInstructors
(@.Id int)
AS

SELECT dbo.Instructors.Inst_Id, dbo.Instructors.Inst_Full_Name, COUNT(dbo.Orders_Sub.Inst_Id) AS Expr1
FROM dbo.Instructors LEFT OUTER JOIN
(SELECT dbo.Orders_Sub.Inst_Id
FROM dbo.Orders_Sub
WHERE dbo.Orders_Sub.Order_Id = @.Id) AS Orders_Sub
GROUP BY dbo.Instructors.Inst_Id, dbo.Instructors.Inst_Full_Name
ORDER BY (3)

---------------------------

I really appreciate the help you giving me thank you in advance again.|||Sorry. I left out the join condition:

FROM dbo.Instructors LEFT OUTER JOIN
(SELECT dbo.Orders_Sub.Inst_Id
FROM dbo.Orders_Sub
WHERE dbo.Orders_Sub.Order_Id = @.Id) AS Orders_Sub
ON dbo.Instructors.Inst_Id = Orders_Sub.Inst_Id

|||Thank you very much for your help it works like a dream.

Please Help

I am trying to pull the last 30 records in a table. I'm trying to write a
dynamic stored procedure in order to do this. I am joining a couple tables
and the table i need to get the 30 records out of is the second table. I
can't seem to get it to work with my code. Below is the code that I am using
to try to do this, I have declared the variables earlier in this procedure:
(Select Distinct LotID, @.LowNum = max(LotID)-30, @.HighNum = max(LotID)
From dbo.schedulegovcom
Where LotID<=@.HighNum
and >=@.LowNum
Group by LotID) pTry:
SELECT TOP 30
[field list]
FROM
dbo.schedulegovcom
ORDER BY
LotID DESC
Mike
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:F449DAD0-7B92-4E1A-9676-1604C2D79D3A@.microsoft.com...
>I am trying to pull the last 30 records in a table. I'm trying to write a
> dynamic stored procedure in order to do this. I am joining a couple tables
> and the table i need to get the 30 records out of is the second table. I
> can't seem to get it to work with my code. Below is the code that I am
> using
> to try to do this, I have declared the variables earlier in this
> procedure:
> (Select Distinct LotID, @.LowNum = max(LotID)-30, @.HighNum = max(LotID)
> From dbo.schedulegovcom
> Where LotID<=@.HighNum
> and >=@.LowNum
> Group by LotID) p|||You can not mix resultset with assigning value to a variable in the same
select statement.
-- wrong
select @.i = orderid, customerid from dbo.orders
AMB
"A.B." wrote:

> I am trying to pull the last 30 records in a table. I'm trying to write a
> dynamic stored procedure in order to do this. I am joining a couple tables
> and the table i need to get the 30 records out of is the second table. I
> can't seem to get it to work with my code. Below is the code that I am usi
ng
> to try to do this, I have declared the variables earlier in this procedure
:
> (Select Distinct LotID, @.LowNum = max(LotID)-30, @.HighNum = max(LotID)
> From dbo.schedulegovcom
> Where LotID<=@.HighNum
> and >=@.LowNum
> Group by LotID) p|||Thanks that worked
"Mike Jansen" wrote:

> Try:
> SELECT TOP 30
> [field list]
> FROM
> dbo.schedulegovcom
> ORDER BY
> LotID DESC
> Mike
> "A.B." <AB@.discussions.microsoft.com> wrote in message
> news:F449DAD0-7B92-4E1A-9676-1604C2D79D3A@.microsoft.com...
>
>

Please All Help me ( challenge )

I'm in need to write stored procedures which insert id for employee in a table in a database but i want to make stored procedures to prevent the user from entering id with value 0 i want to catch this from the stored procedures as kindly message and send this message to windows application

Use Raiserror with severy >=16:

create procedure InsertEmployee

@.id int

as

if @.id = 0

begin

RAISERROR ('Employee ID can''t be 0',16,1);

end

else

begin

--INSERT here

print 'ID > 0 '

end

|||But the important section is how to catch this error message from windows application and show it when the user try to enter value for id employee less than 1|||

That depends on the technology that you are using to connect to the database server and also the programming language that you have used to develop the client application. You're unlikely to get the answer to your question in a T-SQL forum.

See if you can find a more relevant forum in this list:

http://forums.microsoft.com/MSDN/default.aspx?siteid=1

Just to steer you in the right direction, and now that you've been advised on how to raise errors in your stored procedures, then this forum might be the next port of call:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1

Chris

|||If you want to use SqlCommand explicitly:

SqlCommand cmd = new SqlCommand("<name of your SP>",conn);
cmd.Parameters.Add("@.ID",SqlDBType.Int);
cmd.Parameters["@.ID"].Value = <value from user input>;
try
{
cmd.ExecuteNoQuery();
}
catch(SqlException e)
{
//Catch error here
//For example
MessageBox.Show(e.Errors[0].Message);
}

If you want to use SqlDataAdapter/TableAdapter

SqlDataAdapter da = <some code to obtain>;
try
{
da.Update(ds);//ds - you dataset or datatable

}
catch(SqlException e)

{

//Catch error here

//For example

MessageBox.Show(e.Errors[0].Message);

}|||The whole point is that if your are giving id as parameter why not check the value inside your application before sending it to the SP.
Now other option is to write a trigger inside which u rollback ur transaction if id is 0. This will prevent 0's not only from appl but also from backend

Place or book to test writing T-SQL queries?

Hi,
I am looking for a place where I get to write sql queries and test my sql knowledge.If any one can suggest me site or book where I can test my sql knowledge. I have good exposure of SQL syntax and want to test how I implement it. Also, If Forum moderator can suggest me any book on this to improve myself in T-SQL.

Regards

Download the FREE copy of SQL Server 2005 Express, and use it to test your code on your local computer. Get your copy here.

For books, look at BOTH volumes of Itzak Ben-Gan's Microsoft Press titles.

|||Jump start to test your SQL skills...
http://www.sql-ex.ru/

|||Is there any book that provides sample scenarios for which one can write queries , like questions handbook? I do not have access to web all time, so , was looking for some offline solution. My requirement is getting some real life scenario query making.|||

SQL Server Express is installed locally on your computer, so it can be used whether or not you're connected to the web.

That will give you the environment in which to practice.

As to what to practice, try one of the Celko "puzzle" books or the SQL Server 2005: Applied techniques Step By Step book.

A search on Amazon.com for beginning SQL Server will also give you some possibilities.

Additionally, come to this forum, or any other sql forum where people post questions looking for help, and work on solving their issue(s).

(Even if someone else has provided a solution, disregard that solution while you work on coming up with your own and then check to see what others came up with.)

pl/sql wrappers newbie guide?

Does anyone know of a good web reasource for writing wrappers for a low level c program?
I've been given the task to write a simple wrapper by tomorrow so I don't have time to get to the book store for an O'Reilly book.
Many thanks!
CraigTry: asktom.oracle.com

Search for "external procedure" "language c"|||Thanks Tony!

pl/sql job package

hi
does anyone know about job package. I want to know how can i use it to do following:
I want to write a procedure in oracle which will monitor a table which
contains a date field (containing a date>= system date). When the date
specified in the date field matches with the system date the row
containing the date should be deleted from the table.
As you must have noticed that the procedure should run 24x7 and
continuously monitor the table.
How to do that?
Please helpuse the DBMS_JOB package, which is documented here:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_job.htm#999107|||i am new to using these packages.
Could you please tell me how to use the DBMS_JOB package to do the aforementioned task.
thanks
Originally posted by andrewst
use the DBMS_JOB package, which is documented here:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_job.htm#999107|||This will set up a job to run the stored procedure called myproc immediately, and then every minute thereafter (1 minute = 1/1440 days). Change the frequency to whatever value you require.

Put whatever you need to do in the stored procedure called myproc (well, probably called something more sensible than that).

declare
job integer;
begin
dbms_job.submit( job, 'myproc;', sysdate, 'sysdate+1/1440' );
commit;
end;
/

You need to check that your database is configured to run job queues:

select name, value from v$parameter
where name like 'job_queue%';

You should see a value for job_queue_processes > 0 otherwise no jobs will run. job_queue_interval is the number of seconds Oracle waits between running jobs. If you schedule your job to run more frequently than that, it won't.|||it did not work. i did the following to check the package
SQL> create table temp
2 (
3 current_date date
4 );

Table created.

SQL> create or replace procedure temp_precedure
2 is
3 begin
4 INSERT INTO TEMP VALUES(SYSDATE);
5 END;
6 /

Procedure created.
SQL> declare
2 job integer;
3 begin
4 dbms_job.submit(job,'temp_precedure;',sysdate,'sys date+1/1440');
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

I monitored the table for 5 min and no rows where added to the table.
When i manually executed the procedure it successfully added new row to the table.
also i got following error
SQL> select name, value from v$parameter where name like 'job_queue%';
select name, value from v$parameter where name like 'job_queue%'
*
ERROR at line 1:
ORA-00942: table or view does not exist

I was working on a remote database on the intranet. I have a student login ( I am a student). I am using Oracle 8i Enterprise Edition installed on Sun Solaris. I access it on win2kpro using oracle 8i enterprise ed. client(sqlplusw).

please help
thanks
Originally posted by andrewst
This will set up a job to run the stored procedure called myproc immediately, and then every minute thereafter (1 minute = 1/1440 days). Change the frequency to whatever value you require.

Put whatever you need to do in the stored procedure called myproc (well, probably called something more sensible than that).

declare
job integer;
begin
dbms_job.submit( job, 'myproc;', sysdate, 'sysdate+1/1440' );
commit;
end;
/

You need to check that your database is configured to run job queues:

select name, value from v$parameter
where name like 'job_queue%';

You should see a value for job_queue_processes > 0 otherwise no jobs will run. job_queue_interval is the number of seconds Oracle waits between running jobs. If you schedule your job to run more frequently than that, it won't.|||You'll need to contact your DBA about this.|||Thanks it worked. i did not have the priveledge of issuing a job.
But what about the following:

SQL> select name, value from v$parameter where name like 'job_queue%';
select name, value from v$parameter where name like 'job_queue%'
*
ERROR at line 1:
ORA-00942: table or view does not exist

I searched oracle 8i complete reference and could not find the aforementioned table.
Thanks
Originally posted by andrewst
You'll need to contact your DBA about this.|||Hi andrewst,

Is there any processing time taken by oracle which increases the interval by a second?

I kept the interval as sysdate+1/1440

And the time stamp of the records inserted by procedure in job queue is

TO_CHAR(START_DATE,'
-------
15-dec-2003 19:15:56
15-dec-2003 19:16:57
15-dec-2003 19:17:59
15-dec-2003 19:19:00
15-dec-2003 19:20:02
15-dec-2003 19:21:03
15-dec-2003 19:22:05

As we can see at every step, its either 61 seconds of 62 seconds. What could be the reason?

Thanks,

Originally posted by andrewst
This will set up a job to run the stored procedure called myproc immediately, and then every minute thereafter (1 minute = 1/1440 days). Change the frequency to whatever value you require.

Put whatever you need to do in the stored procedure called myproc (well, probably called something more sensible than that).

declare
job integer;
begin
dbms_job.submit( job, 'myproc;', sysdate, 'sysdate+1/1440' );
commit;
end;
/

You need to check that your database is configured to run job queues:

select name, value from v$parameter
where name like 'job_queue%';

You should see a value for job_queue_processes > 0 otherwise no jobs will run. job_queue_interval is the number of seconds Oracle waits between running jobs. If you schedule your job to run more frequently than that, it won't.|||The reason would be that your job takes 1-2 seconds to run, so that by the time it evaluates sysdate+1/1440, sysdate is 1-2 seconds later than the start of this run.

You can compensate for that like this:

trunc(sysdate,'mi')+1/1440

Now it will always run exactly on the minute. Of course, if the job ever takes more than 1 minute to run then it will skip a run.

PL/SQL cursors and index tables

I'm trying to write a script that stores values in an index table using a cursor. My syntax looks write but i'm getting the following message:

Please enter the number of rows to be selected: 5
last_name_tbl last_name_tbl_type;
*
ERROR at line 9:
ORA-06550: line 43, column 27:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 43, column 4:
PL/SQL: SQL Statement ignoredOriginally posted by bbk
I'm trying to write a script that stores values in an index table using a cursor. My syntax looks write but i'm getting the following message:

Please enter the number of rows to be selected: 5
last_name_tbl last_name_tbl_type;
*
ERROR at line 9:
ORA-06550: line 43, column 27:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 43, column 4:
PL/SQL: SQL Statement ignored
Show your code

Pivot Tables and MDX

Hi!
I want to write some MDX queries that pull the same data I'm retriving into
an excel pivot table that is hitting my cube. Up front, I'm pretty sure thi
s is not doable, but need to ask anyway. I want some way of hooking into th
e MSOLAP provider to see th
e MDX that's generated and sent to analysis services to retrieve the data in
to the pivot table. This would be an excellent shortcut to getting my MDX q
uery results to match what I'm getting in the pivot table.
Any anyone know of a way to see the MDX that's being generated from the pivo
t table?
-thanksInclude property Log File in your connection string.
Log File="C:\work\Sales_stock\mdx\excel_log\log.txt"
in log.txt you will find mdx queries.
Ramunas Balukonis
"brian p" <anonymous@.discussions.microsoft.com> wrote in message
news:399BECD7-270E-4539-BAC0-C628EB09D2B8@.microsoft.com...
> Hi!
> I want to write some MDX queries that pull the same data I'm retriving
into an excel pivot table that is hitting my cube. Up front, I'm pretty
sure this is not doable, but need to ask anyway. I want some way of hooking
into the MSOLAP provider to see the MDX that's generated and sent to
analysis services to retrieve the data into the pivot table. This would be
an excellent shortcut to getting my MDX query results to match what I'm
getting in the pivot table.
> Any anyone know of a way to see the MDX that's being generated from the
pivot table?
> -thanks

Pivot Query or Cross Tab queries

I can't seem to figure out how to create a cross tab or pivot query on data.
If I was writing the SQL in Access I would write it like
TRANSFORM SUM(DTL.Amount) AS Amount
SELECT
STC.ClientID
, STC.Client
, STC.SS_ID
, STC.Region
, STC.[Sales Director]
, STC.[Product Consultant]
, STC.CCC
FROM (SELECT
STC.DW_SELLER_ID AS ClientID
, STC.SELLER_NAME AS Client
, STC.SS_ID
, STC.TEAM_ACQ_NAME AS Region
, STC.ACCOUNT_MANAGER_NAME AS [Sales Director]
, STC.[Product Consultant]
, CTC.CLIENT_CENTRIC_CATEGORY_DSCR AS CCC
FROM dbo.tSeller_Team_Current AS STC
LEFT JOIN OPENQUERY(SSOT_DWDB,
'SELECT DW_CLIENT_ID, CLIENT_CENTRIC_CATEGORY_DSCR
FROM DW.DW_SALES_CLIENT_TEAM_CURR') AS CTC
ON STC.DW_SELLER_ID = CTC.DW_CLIENT_ID
WHERE STC.Channel='RF'
AND STC.SELLER_ACTIVATION_DATE Is Not Null
AND SELLER_CLOSED_DATE Is Null) AS STC
LEFT JOIN dbo.tCRC_Details AS DTL
ON STC.ClientID=DTL.ClientID
GROUP BY
STC.ClientID
, STC.Client
, STC.SS_ID
, STC.Region
, STC.[Sales Director]
, STC.[Product Consultant]
, STC.CCC
PIVOT DTL.[Description]
How could I write the query so it works in a View?
Thanks in advance.
Mark
I don't believe you can do crosstab queries within SQL Server like you can
in Access
"MChrist" <MChrist@.discussions.microsoft.com> wrote in message
news:C5FE6FA4-8AB9-49DE-BA7F-1BD0F226B01A@.microsoft.com...
>I can't seem to figure out how to create a cross tab or pivot query on
>data.
> If I was writing the SQL in Access I would write it like
> TRANSFORM SUM(DTL.Amount) AS Amount
> SELECT
> STC.ClientID
> , STC.Client
> , STC.SS_ID
> , STC.Region
> , STC.[Sales Director]
> , STC.[Product Consultant]
> , STC.CCC
> FROM (SELECT
> STC.DW_SELLER_ID AS ClientID
> , STC.SELLER_NAME AS Client
> , STC.SS_ID
> , STC.TEAM_ACQ_NAME AS Region
> , STC.ACCOUNT_MANAGER_NAME AS [Sales Director]
> , STC.[Product Consultant]
> , CTC.CLIENT_CENTRIC_CATEGORY_DSCR AS CCC
> FROM dbo.tSeller_Team_Current AS STC
> LEFT JOIN OPENQUERY(SSOT_DWDB,
> 'SELECT DW_CLIENT_ID, CLIENT_CENTRIC_CATEGORY_DSCR
> FROM DW.DW_SALES_CLIENT_TEAM_CURR') AS CTC
> ON STC.DW_SELLER_ID = CTC.DW_CLIENT_ID
> WHERE STC.Channel='RF'
> AND STC.SELLER_ACTIVATION_DATE Is Not Null
> AND SELLER_CLOSED_DATE Is Null) AS STC
> LEFT JOIN dbo.tCRC_Details AS DTL
> ON STC.ClientID=DTL.ClientID
> GROUP BY
> STC.ClientID
> , STC.Client
> , STC.SS_ID
> , STC.Region
> , STC.[Sales Director]
> , STC.[Product Consultant]
> , STC.CCC
> PIVOT DTL.[Description]
> How could I write the query so it works in a View?
> Thanks in advance.
> Mark
>
|||Thanks for your response Al. After scoping books and the help for several
hours that's the conclusion I came to also. Strange how the little baby
brother can do something that big brother can't. But that's as consistent as
the use of functions and naming conventions across MS software.
Have a great weekend.
Mark
"Al Newbie" wrote:

> I don't believe you can do crosstab queries within SQL Server like you can
> in Access
> "MChrist" <MChrist@.discussions.microsoft.com> wrote in message
> news:C5FE6FA4-8AB9-49DE-BA7F-1BD0F226B01A@.microsoft.com...
>
>