Get a List of Output Columns on Script Transformation

I am using a script component to transform data. In the script component I created a bunch of fields for the output. Is there any way to loop through that list of columns? Is there code I can use in the script component to access the names, data types, data etc?

I saw a lot of informaiton on the OutputColumnCollection as part of some IDTSOuput90 thing (greek to me). As best I can guess this is for creating your own new columns, but can I see what columns are already defined via the script interface?

I was able to get a specific column by doing this...

Dim o As IDTSOutput90

o = ComponentMetaData.OutputCollection.GetObjectByID(88)

MsgBox("Column Count: " & o.OutputColumnCollection.Count)

MsgBox("Colum: " & o.OutputColumnCollection.FindObjectByID(139).Name)

But Is there a way to just loop through all the ids? I have to know the id to stick in findobjectbyid(x). Can i do a loop and have x = all the ids? Additionally the 88 is hard code value... is there a way to get the current or specific output set?

The ultimate goal is to loop through the column collection and set a column = value. Basically I have an array of values and want to loop through the columns and set it to the array postion.

|||

Ok... sorry about the multiple posts here... but I got some more logic to work...

I was able to get able to get columns by doing this...

Dim o As IDTSOutput90

o = ComponentMetaData.OutputCollection.GetObjectByID(88)

For Each col As IDTSOutputColumn90 In o.OutputColumnCollection

MsgBox("Name: " & col.Name)

Next

So the updated question is this...

The ultimate goal is to loop through the column collection and set a column = value. Basically I have an array of values and want to loop through the columns and set it to the array postions value. I can't find how to set the column value equal to something while i loop through it. Additionally... is there a reference or good way to get the data type? When i use the col.datatype is returns a number vs description. I want to have logic when it set the value to do coverts based on the data type.

Generating Multi Level nodes in Stored Procedures

Hi all,

What I am trying to do is generate a stored procedure that is desired
to output XML in this type of format

<Parent Device>
<Device>
<Device ID>1</DeviceID>
<ChildRegister>
<ChildRegisterID>22</ChildRegisterID>
</ChildRegister>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegister>
<ChildRegisterID>23</ChildRegisterID>
</ChildRegister>
</Device>
</Parent Device
The area of concern is the child register, the XML being generated
disregards the Device the ChildRegister belongs to and always places it
as elements of the last device.

<Parent Device>
<Device>
<Device ID>1</DeviceID>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegister>
<ChildRegisterID>23</ChildRegisterID>
</ChildRegister>
<ChildRegister>
<ChildRegisterID>22</ChildRegisterID>
</ChildRegister>
</Device>
</Parent Device
I am trying to produce XML like the first one I described and have yet
to discover a way of associating the ChildRegister with the parent
Device in XML. I am not sure if it is a limitation of SQL Server, or if
my implementation is incorrect. If anyone could post hints or
solutions, I would greatly appreciate it.
A shortened version of the stored procedure is below

Cheers :)
Alvin

SELECT
1AS TAG
,NULL AS PARENT
,NULL AS [Device!2!DeviceID!element]
,NULL AS [ChildRegister!3!RegisterID!element]

FROM udetails INNER JOIN
Detail ON udetails.ID = Detail.ID
WHERE (uDetails.JobID = @.ID)

UNION ALL

SELECT
2 AS TAG
,1 AS PARENT
,TempTable.DeviceIDAS [Device!2!DeviceID!element]
,NULL AS [ChildRegister!3!RegisterID!element]

>From #Temp as TempTable INNER JOIN
device ON TempTable.DeviceID = device.DeviceID

UNION ALL

SELECT
3 AS TAG
,2 AS PARENT
,NULL AS [Device!2!DeviceID!element]
,RegisterID AS [ChildRegister!3!RegisterID!element]

FROM #Temp t INNER JOIN
register ON t.DeviceID =
register.DeviceID

FOR XML EXPLICIT(teohster@.gmail.com) writes:
> I am trying to produce XML like the first one I described and have yet
> to discover a way of associating the ChildRegister with the parent
> Device in XML. I am not sure if it is a limitation of SQL Server, or if
> my implementation is incorrect. If anyone could post hints or
> solutions, I would greatly appreciate it.
> A shortened version of the stored procedure is below

For all problems like this, it is a good idea to post:

o CREATE TABLE statements of the tables inolved.
o INSERT statements with sample data.
o The desired output given the sample data.

You posted the last, but not the first two.

This permits people to post a tested solution to your query. In this
case, an aggrevating factor is that I am not extremely versed in XML,
so I would have to play around with the query.

It may be more effective to ask the real pros in
microsoft.public.sqlserver.xml though.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Generating and directing new rows in PostExecute.

I want to construct a dataset based on all rows passed in from a source and, upon running through all of them, push each dataset record to an output.

Unfortunately, upon calling the AddRow method, I get an "object reference not set to an instance of an object" error. Am I not allowed to create new rows in PostExecute?

Bill,

Can you post your code, indicate which line the error occurs on, and tell us how you have configured the outputs of the component.

-Jamie

|||It errors on reference to a row buffer.

With dsMaterialBuffer
.AddRow()
'Code for row values
End With

I've added the following to check if it's actually defined within PostExecute;
If dsMaterialBuffer Is Nothing Then MsgBox("dsMaterialBuffer is nothing.") 'My row buffer

dsMaterialBuffer, which is the row buffer I'm using, evaluates to "Nothing" and that is where my object reference errors are coming from.

Is it possible that the row buffers are being thrown out once PostExecute hits?

The outputs, by the way, are non-synchronous with standard columns.|||

What is the name of the output as defined in the inputs and outputs tab of the script component editor?

-Jamie

|||dsMaterial|||

Hmm, strange. if you want, drop me an email via here: http://blogs.conchango.com/jamiethomson/contact.aspx and I'll reply so you can send me the package. I can take a look and see if anything jumps out.

If you can build the package so that I am able to run it as well (i.e. not reliant on external data sources, just use a script source component instead) then that'd help.

-Jamie

Generating and directing new rows in PostExecute.

I want to construct a dataset based on all rows passed in from a source and, upon running through all of them, push each dataset record to an output.

Unfortunately, upon calling the AddRow method, I get an "object reference not set to an instance of an object" error. Am I not allowed to create new rows in PostExecute?

Bill,

Can you post your code, indicate which line the error occurs on, and tell us how you have configured the outputs of the component.

-Jamie

|||It errors on reference to a row buffer.

With dsMaterialBuffer
.AddRow()
'Code for row values
End With

I've added the following to check if it's actually defined within PostExecute;
If dsMaterialBuffer Is Nothing Then MsgBox("dsMaterialBuffer is nothing.") 'My row buffer

dsMaterialBuffer, which is the row buffer I'm using, evaluates to "Nothing" and that is where my object reference errors are coming from.

Is it possible that the row buffers are being thrown out once PostExecute hits?

The outputs, by the way, are non-synchronous with standard columns.|||

What is the name of the output as defined in the inputs and outputs tab of the script component editor?

-Jamie

|||dsMaterial|||

Hmm, strange. if you want, drop me an email via here: http://blogs.conchango.com/jamiethomson/contact.aspx and I'll reply so you can send me the package. I can take a look and see if anything jumps out.

If you can build the package so that I am able to run it as well (i.e. not reliant on external data sources, just use a script source component instead) then that'd help.

-Jamie

Generating a flat file output from a select

I want to create a script file to executed from the command line. The script will contain a simple select, which depending on which database it is run against will produce a flat file with the output results.
e.g. a script file with a select such as 'SELECT name FROM sysusers'. when the script is executed from the command line it will produce a flat file with a list of the users on the database.
http://www.aspfaq.com/2482
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.
|||Hi,
You can use BCP OUT with QUERYOUT option. Inside
Query out you can give your TSQL to extract the data out.
Sample:-
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.

Generating a flat file output from a select

I want to create a script file to executed from the command line. The scrip
t will contain a simple select, which depending on which database it is run
against will produce a flat file with the output results.
e.g. a script file with a select such as 'SELECT name FROM sysusers'. when
the script is executed from the command line it will produce a flat file wit
h a list of the users on the database.http://www.aspfaq.com/2482
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.|||Hi,
You can use BCP OUT with QUERYOUT option. Inside
Query out you can give your TSQL to extract the data out.
Sample:-
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.

Generating a correct xpath_namespaces

I want to open a XML document with a xmlns declaration, i open the xml with:
-- Open the XML doc
EXEC sp_xml_preparedocument @.itemHandle OUTPUT, @.xml, '<PriceList xmlns:maxbo="http://maxbo.no/schema/PriceList.xsd" />'

Then i try to select somthing (this returns nothing)
select * from
OPENXML(@.itemHandle, N'/PriceList')

Here are the first few rows.
<?xml version="1.0" encoding="iso-8859-1"?>
<PriceList xmlns="http://maxbo.no/schema/PriceList.xsd">.....

The following code works fine, so how do i generate the xmlns for the above scenario? (it seems obvious to me that the xpath_namespace declartation is somhow faulty)

I want to open a XML document with a xmlns declaration, i open the xml with:
-- Open the XML doc
EXEC sp_xml_preparedocument @.itemHandle OUTPUT, @.xml
Then i try to select somthing (this returns nothing)
select * from
OPENXML(@.itemHandle, N'/PriceList')
Here are the first few rows.
<?xml version="1.0" encoding="iso-8859-1"?>
<PriceList>....

You need to provide the namespace/prefix binding to the third argument as you do above, but you need to use a prefix to bind the namespace URI to AND use it in the query. Default namespaces are unfortunately not picked up.

Try:

EXEC sp_xml_preparedocument @.itemHandle OUTPUT, @.xml, '<PriceList xmlns:maxbo="http://maxbo.no/schema/PriceList.xsd" />'
select * from
OPENXML(@.itemHandle, N'/maxbo:PriceList')

Best regards

Michael

|||

That works, although I have to prefix every item in the list like this:

updateDate varchar(50) '/maxbo:PriceList/maxbo:Update/text()',

But at least it works. If you have an easier way of accessing subitems, I would be gratefull, since some paths can become quite long.

Generate SQL Query for Data?

We're using Sql Server 2000. Is there some way to generate SQL query output of my table data. So an output that looks something like:

INSERT INTO mytable (id, col1, col2) VALUES (1,'a','b');
INSERT INTO mytable (id, col1, col2) VALUES (2,'c','d');

Code Snippet

select 'Insert into MyTable(Id,Col1,Col2) values (' + convert(varchar,ID) + ',''' + col1 + ''',''' + col2 + ''');'

from <MyTable>

|||If you are looking for a way to script INSERT statements for your data, look at this:

http://vyaskn.tripod.com/code/generate_inserts.txt

Steve Kass
Drew University
http://www.stevekass.com

Generate Flat File via Stored Procedure

I have a need to do the following:

Generate a Stored Procedure and have the output written in a csv format.

I have everything I need to capture the data via stored procedure, but I am lost on a way to 'INSERT' the data values into a csv file.

This stored procedure will be triggered by another application.

Could someone please help.

thanks

there are many ways to do that

1. in sql server 200 you can use a dts to out put the result

2. in sql server 2005 you can use SSIS

3. you can use the openrowset function

4. you cann configure a linked server and output the result.

|||

This might help

create procedure usp_runbcppkg

as

declare @.as_bcp varchar(255)

declare @.as_query nvarchar(255)

declare @.as_select varchar(255)

declare @.as_appcode varchar(20)

set @.as_select = 'bcp "select appcode, servername, region, usage_ctr, st_date, ed_date from pubs..temp"'

set @.as_query = 'master.dbo.xp_cmdshell ' + @.as_bcp

begin

--cursor to get the list of state

declare cur_appcode cursor for

select distinct state from authors

order by 1

open cur_appcode

fetch next from cur_appcode into @.as_appcode

while @.@.fetch_status = 0

begin

Truncate & Insert the date for the state in Temp Table

truncate table temp

insert into temp (au_id, au_lname, phone, address, city, state, zip)

select au_id, au_lname, phone, address, city, state, zip

from pubs..authors where state = @.as_appcode

--<Servername> to be entered

--<Password> to be entered

set @.as_bcp = @.as_select + ' queryout c:\temp\' + @.as_appcode + '.xls -S<Servername> -Usa -P<Password> -w'

exec master..xp_cmdshell @.as_bcp

fetch next from cur_appcode into @.as_appcode

end

close cur_appcode

deallocate cur_appcode

end|||

An OPENROWSET example which emails the results.

@.reportxls must exist on the server (an empty xls flle with column headers only).

Openrowset will append results to the file. If this is what you want then remove the copy, rename and delete parts of the code.

CREATE PROCEDURE sp_Test
(@.selectcmd as varchar(400), @.email_list as varchar(300), @.reportxls as varchar(40))

as

declare @.result as int, @.attach as varchar(140), @.renamecmd as varchar(200), @.deletecmd as varchar(200), @.copycmd as varchar(200), @.insertcmd as varchar(200), @.sql as varchar(600)
set @.attach = 'd:\reports\' + @.reportxls
set @.copycmd = 'copy d:\reports\' + @.reportxls + ' d:\reports\' + @.reportxls + '#'
set @.renamecmd = 'ren d:\reports\' + @.reportxls + '# ' + @.reportxls
set @.deletecmd = 'del d:\reports\' + @.reportxls
set @.insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:\reports\' + @.reportxls + ';'', ''SELECT * FROM [Sheet1$]'')'
set @.sql = @.insertcmd + @.selectcmd

EXEC @.result = master.dbo.xp_cmdshell @.copycmd
IF (@.result = 0)
BEGIN
EXEC (@.sql)
EXEC master.dbo.xp_sendmail @.recipients = @.email_list,
@.message = 'Test Body Message',
@.subject = 'Report Request',
@.attachments = @.attach
EXEC master.dbo.xp_cmdshell @.deletecmd
EXEC master.dbo.xp_cmdshell @.renamecmd
END

GO

Generate Data Model

Hi,
I need to verify the relationship between tables. Is there
any possible to generate the output of the existing Data
Model?
Thanks,
RegardsHi
Look at sp_foreignkeys system stored procedure
Build a diagram to show relationship between tables.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:bea401c47a0c$d91206d0$a301280a@.phx.gbl...
> Hi,
> I need to verify the relationship between tables. Is there
> any possible to generate the output of the existing Data
> Model?
> Thanks,
> Regards|||I would build a diagram now also... Or you might use a 3rd part modelling
tool like Erwin..
Don't get too attached to the SQL DB Diagram... I don't think it will be in
the next version of SQL.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:bea401c47a0c$d91206d0$a301280a@.phx.gbl...
> Hi,
> I need to verify the relationship between tables. Is there
> any possible to generate the output of the existing Data
> Model?
> Thanks,
> Regards

Generate Data Model

Hi,
I need to verify the relationship between tables. Is there
any possible to generate the output of the existing Data
Model?
Thanks,
RegardsHi
Look at sp_foreignkeys system stored procedure
Build a diagram to show relationship between tables.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:bea401c47a0c$d91206d0$a301280a@.phx.gbl...
> Hi,
> I need to verify the relationship between tables. Is there
> any possible to generate the output of the existing Data
> Model?
> Thanks,
> Regards|||I would build a diagram now also... Or you might use a 3rd part modelling
tool like Erwin..
Don't get too attached to the SQL DB Diagram... I don't think it will be in
the next version of SQL.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:bea401c47a0c$d91206d0$a301280a@.phx.gbl...
> Hi,
> I need to verify the relationship between tables. Is there
> any possible to generate the output of the existing Data
> Model?
> Thanks,
> Regards

Generate Data Model

Hi,
I need to verify the relationship between tables. Is there
any possible to generate the output of the existing Data
Model?
Thanks,
Regards
Hi
Look at sp_foreignkeys system stored procedure
Build a diagram to show relationship between tables.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:bea401c47a0c$d91206d0$a301280a@.phx.gbl...
> Hi,
> I need to verify the relationship between tables. Is there
> any possible to generate the output of the existing Data
> Model?
> Thanks,
> Regards
|||I would build a diagram now also... Or you might use a 3rd part modelling
tool like Erwin..
Don't get too attached to the SQL DB Diagram... I don't think it will be in
the next version of SQL.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:bea401c47a0c$d91206d0$a301280a@.phx.gbl...
> Hi,
> I need to verify the relationship between tables. Is there
> any possible to generate the output of the existing Data
> Model?
> Thanks,
> Regards

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury822.34602709630
Standard433.34602709630
Custom397.34602709630
Modified285.34602709630
Other222.34602709630
More...676.34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury822.10032954961
Standard433.05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.com
Hi,
The Query can be re-written as:
=============================
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via droptable.com" wrote:

> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury822.34602709630
> Standard433.34602709630
> Custom397.34602709630
> Modified285.34602709630
> Other222.34602709630
> More...676.34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for each
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury822.10032954961
> Standard433.05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunction
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com
>
|||Hi Chandra,
Thanks for the assist - that took care of it.
Message posted via http://www.droptable.com
|||Good to know that the solution addressed your needs. Really appreciate if you
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via droptable.com" wrote:

> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.droptable.com
>

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury 822 .34602709630
Standard 433 .34602709630
Custom 397 .34602709630
Modified 285 .34602709630
Other 222 .34602709630
More... 676 .34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury 822 .10032954961
Standard 433 .05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
--
Message posted via http://www.sqlmonster.comHi,
The Query can be re-written as:
=============================ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via SQLMonster.com" wrote:
> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury 822 .34602709630
> Standard 433 .34602709630
> Custom 397 .34602709630
> Modified 285 .34602709630
> Other 222 .34602709630
> More... 676 .34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for each
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury 822 .10032954961
> Standard 433 .05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunction
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Chandra,
Thanks for the assist - that took care of it.
--
Message posted via http://www.sqlmonster.com|||Good to know that the solution addressed your needs. Really appreciate if you
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via SQLMonster.com" wrote:
> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.sqlmonster.com
>

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury 822 .34602709630
Standard 433 .34602709630
Custom 397 .34602709630
Modified 285 .34602709630
Other 222 .34602709630
More... 676 .34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury 822 .10032954961
Standard 433 .05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunct
ion
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.comHi,
The Query can be re-written as:
=============================
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via droptable.com" wrote:

> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury 822 .34602709630
> Standard 433 .34602709630
> Custom 397 .34602709630
> Modified 285 .34602709630
> Other 222 .34602709630
> More... 676 .34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for eac
h
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury 822 .10032954961
> Standard 433 .05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFun
ction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass
,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunctio
n
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com
>|||Hi Chandra,
Thanks for the assist - that took care of it.
Message posted via http://www.droptable.com|||Good to know that the solution addressed your needs. Really appreciate if yo
u
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via droptable.com" wrote:

> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.droptable.com
>