Help with custom total in matrix (hopefully easy adjustment)

Hey guys. I have a matrix that is working the way I want it as far as
groupings and data. I am doing a custom total that works for the first row
group with the following code:
Private currentTotal As Double = 0
Function SetCurrentValue(ByVal currentValue As Double) As Double
currentTotal += currentValue
return currentTotal
End Function
Function GetCurrentValue() As Double
return currentTotal
End Function
Function ResetTotal() As Boolean
currentTotal=0
return True
End Function
In my detail I have:
=IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
If I use only the first IIF statement I get the correct values 19, 38 , 57,
76, 95, 114, 133, 152, 171, 190, 209, and 228.
If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
361, 399, 437 in the first row and in the second row I get:
494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
I need to run the reset method in between row groupings to get it to go back
to 19 for the second row (it will be a different starting value eventually,
but for now I need it to reset).
I tried putting the code as a second row grouping and the column subtotal,
but it won't reset in the proper spot. Let me know!
Thanks!,
BJYou can use rownumber with scope/group so that it resets each and everytime
group changes. Provided your 19,38,57 etc.. are fixed values...
Amarnath
"bjkaledas" wrote:
> Hey guys. I have a matrix that is working the way I want it as far as
> groupings and data. I am doing a custom total that works for the first row
> group with the following code:
> Private currentTotal As Double = 0
> Function SetCurrentValue(ByVal currentValue As Double) As Double
> currentTotal += currentValue
> return currentTotal
> End Function
> Function GetCurrentValue() As Double
> return currentTotal
> End Function
> Function ResetTotal() As Boolean
> currentTotal=0
> return True
> End Function
> In my detail I have:
> =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> If I use only the first IIF statement I get the correct values 19, 38 , 57,
> 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> 361, 399, 437 in the first row and in the second row I get:
> 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> I need to run the reset method in between row groupings to get it to go back
> to 19 for the second row (it will be a different starting value eventually,
> but for now I need it to reset).
> I tried putting the code as a second row grouping and the column subtotal,
> but it won't reset in the proper spot. Let me know!
> Thanks!,
> BJ|||Where would I use the rownumber function? Would I keep track of this in my
custom code or when I call the function in my detail textbox?
"Amarnath" wrote:
> You can use rownumber with scope/group so that it resets each and everytime
> group changes. Provided your 19,38,57 etc.. are fixed values...
>
> Amarnath
> "bjkaledas" wrote:
> > Hey guys. I have a matrix that is working the way I want it as far as
> > groupings and data. I am doing a custom total that works for the first row
> > group with the following code:
> >
> > Private currentTotal As Double = 0
> > Function SetCurrentValue(ByVal currentValue As Double) As Double
> > currentTotal += currentValue
> > return currentTotal
> > End Function
> >
> > Function GetCurrentValue() As Double
> > return currentTotal
> > End Function
> >
> > Function ResetTotal() As Boolean
> > currentTotal=0
> > return True
> > End Function
> >
> > In my detail I have:
> >
> > =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> > IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> >
> > If I use only the first IIF statement I get the correct values 19, 38 , 57,
> > 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> >
> > If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> > 361, 399, 437 in the first row and in the second row I get:
> >
> > 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> >
> > I need to run the reset method in between row groupings to get it to go back
> > to 19 for the second row (it will be a different starting value eventually,
> > but for now I need it to reset).
> >
> > I tried putting the code as a second row grouping and the column subtotal,
> > but it won't reset in the proper spot. Let me know!
> >
> > Thanks!,
> >
> > BJ|||You can use in the textbox so that when it displays it resets exactly.
Amarnath
"bjkaledas" wrote:
> Where would I use the rownumber function? Would I keep track of this in my
> custom code or when I call the function in my detail textbox?
> "Amarnath" wrote:
> > You can use rownumber with scope/group so that it resets each and everytime
> > group changes. Provided your 19,38,57 etc.. are fixed values...
> >
> >
> > Amarnath
> >
> > "bjkaledas" wrote:
> >
> > > Hey guys. I have a matrix that is working the way I want it as far as
> > > groupings and data. I am doing a custom total that works for the first row
> > > group with the following code:
> > >
> > > Private currentTotal As Double = 0
> > > Function SetCurrentValue(ByVal currentValue As Double) As Double
> > > currentTotal += currentValue
> > > return currentTotal
> > > End Function
> > >
> > > Function GetCurrentValue() As Double
> > > return currentTotal
> > > End Function
> > >
> > > Function ResetTotal() As Boolean
> > > currentTotal=0
> > > return True
> > > End Function
> > >
> > > In my detail I have:
> > >
> > > =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> > > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> > > IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> > > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> > >
> > > If I use only the first IIF statement I get the correct values 19, 38 , 57,
> > > 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> > >
> > > If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> > > 361, 399, 437 in the first row and in the second row I get:
> > >
> > > 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> > >
> > > I need to run the reset method in between row groupings to get it to go back
> > > to 19 for the second row (it will be a different starting value eventually,
> > > but for now I need it to reset).
> > >
> > > I tried putting the code as a second row grouping and the column subtotal,
> > > but it won't reset in the proper spot. Let me know!
> > >
> > > Thanks!,
> > >
> > > BJ

Help with CASE in Stored Procedure

hi guys! Help please..I'm new to sotred procedure. Currently, i have a stored procedure (see Below) but i can't figure out on how to solve the errors(see below). What im trying to do with my stored procedure is to take two(2) parameters, the string to be search(@.Search_Arg) and the search by(@.Search_by) Either by Login name or by firstname. Under my CASE statement, When the @.Search_by is equal to Username i want to select the user with User_login equals to @.Search_Arg and when the @.Search_by is equal to Firstname it will select all user with User_FirstName equals to @.Search_Arg...Any help please..Thanks in advance!

CREATE PROCEDURE sp_User_Search
@.Search_Arg varchar(50),
@.Search_By varchar(20)
AS
BEGIN

CASE @.Search_By
WHEN 'Username' THEN SELECT * FROM RCPS_UserAccount WHERE User_login = @.Search_Arg
WHEN 'Firstname' THEN SELECT * FROM RCPS_UserAccount WHERE User_FirstName = @.Search_Arg
END
END

Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 7
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 9
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 10
Incorrect syntax near the keyword 'END'.what you have there is a dynamic search condition. read this for various techniques:

http://www.sommarskog.se/dyn-search.html

in particular, this might suit you:

select col1, col2, col3 from RCPS_UserAccount
where
(User_login = @.user_login or @.user_login is null) and
(User_FirstName = @.user_firstname or @.user_firstname is null)|||Case can return only one value at a time
use of 'Select *' is not allowed in case statement. What you can do is :

CREATE PROCEDURE sp_User_Search
@.Search_Arg varchar(50),
@.Search_By varchar(20)
AS
BEGIN
SELECT * FROM RCPS_UserAccount
WHERE
CASE
When @.Search_By = 'Username' then
User_login
When @.Search_By = 'Firstname' then
User_FirstName
End
= @.Search_Arg
END
END

...but I think way suggested by jezemine is better than this one.sql

Help with CASE and LIKE

Hello guys! i'm having problem with my stored procedure..can anybody please help me.
I have a stored procedure below that is successfully executed/saved/"Compiled"(whatever you called it) but when I try to use it by supplying value to its paramaters it throws an error (Please see the error message below). I suspected that the error occurs from line with the Bold Letters becuase "@.SeacrhArg" variable is of type varchar while columns "Transac.Item_ID" and "Transac.Item_TransTicketNo" is of type Int. What you think guys?

ERROR:

Msg 245, Level 16, State 1, Procedure sp_Transaction_Search, Line 9
Syntax error converting the varchar value 'Manlagnit' to a column of data type int.



STORED PROCEDURE:

USE [RuslinCellPawnShoppeDB]
GO
/****** Object: StoredProcedure [dbo].[sp_Transaction_Search] Script Date: 09/04/2007 08:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Transaction_Search]
@.SeacrhArg varchar(20),
@.SearchBy varchar(20),
@.TransType varchar(20),
@.FromDate datetime,
@.Todate datetime
AS
BEGIN
SELECT Customer.Customer_LastName,Customer.Customer_Middl eInitial, Customer.Customer_FirstName, Customer.Customer_Address,
Items.Item_Description,Items.Item_Principal, Transac.ItemTrans_Date_Granted, Transac.ItemTrans_DateCreated,
Transac.ItemTrans_Status, Transac.Item_ID,Transac.Item_TransID,Transac.Item_ TransTicketNo


FROM RCPS_TF_ItemTransaction Transac
INNER JOIN RCPS_Customer Customer
ON Transac.CustomerID = Customer.CustomerID
INNER JOIN RCPS_Items Items
ON Items.ItemID = Transac.Item_ID

WHERE
CASE
WHEN @.SearchBy = 'FirstName' THEN Customer.Customer_FirstName
WHEN @.SearchBy = 'LastName' THEN Customer.Customer_LastName
WHEN @.SearchBy = 'Item ID' THEN Transac.Item_ID
WHEN @.SearchBy = 'Ticket No' THEN Transac.Item_TransTicketNo
END
LIKE @.SeacrhArg AND

Transac.ItemTrans_DateCreated BETWEEN @.FromDate AND dateadd(day,1,@.Todate) AND
(
(@.TransType = 'Pawned' AND Transac.ItemTrans_Status = 1) OR
(@.TransType = 'Renewed' AND Transac.ItemTrans_Status = 2) OR
(@.TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3) OR
(@.TransType = 'Sold' AND Transac.ItemTrans_Status = 5)
)
END

CALL STORED PROCEDURE

USE [RuslinCellPawnShoppeDB]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[sp_Transaction_Search]
@.SeacrhArg = '%man%',
@.SearchBy = 'LastName',
@.TransType = 'Pawned',
@.FromDate = N'9/01/2007 12:00:00 AM',
@.Todate = N'9/6/2007 12:00:00 AM'

SELECT 'Return Value' = @.return_value

GOI suspect Transac.ItemTrans_Status is a varchar and that is where the problem is|||WHERE CASE
WHEN @.SearchBy = 'FirstName' AND Customer.Customer_FirstName LIKE @.SeacrhArg THEN 1
WHEN @.SearchBy = 'LastName' THEN Customer.Customer_LastName LIKE @.SeacrhArg THEN 1
WHEN @.SearchBy = 'Item ID' THEN CONVERT(VARCHAR, Transac.Item_ID) LIKE @.SeacrhArg THEN 1
WHEN @.SearchBy = 'Ticket No' THEN CONVERT(VARCHAR, Transac.Item_TransTicketNo) LIKE @.SeacrhArg THEN 1
ELSE 0
END = 1
AND Transac.ItemTrans_DateCreated >= @.FromDate
AND Transac.ItemTrans_DateCreated < DATEADD(DAY, 1, @.Todate)
AND 1 = CASE
WHEN @.TransType = 'Pawned' AND Transac.ItemTrans_Status = 1 THEN 1
WHEN @.TransType = 'Renewed' AND Transac.ItemTrans_Status = 2 THEN 1
WHEN @.TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3 THEN 1
WHEN @.TransType = 'Sold' AND Transac.ItemTrans_Status = 5 THEN 1
ELSE 0
END|||Thanks a loT Peso..But I preferred to use BETWEEN..AND.. for date unless you have there agood reason to use the <>= sign..But anyway thanks alot!|||if you use BETWEEN, you inadvertently get too may records.

BETWEEN '20070101' AND '20071231' will NOT get the records dated "20071231 10:14:23"

BETWEEN '20070101' AND '20080101' will get the records dated "20071231 10:14:23" but ALSO the records dated "20080101 00:00:00".

>= '20070101' <'20080101' will get the records dated "20071231 10:14:23" but NOT the records dated "20080101 00:00:00".

help with binding

hi guys! help please..how will bind my texbox (TxtLastName) to my dataset (dsLastName) column (User_LastName). Thanks in avdance!well first of all i guess this is the wrong section you have posted in....

secondly if you want to add the value of a specific row in a dataset to a textbox you can use something like

TxtLastName.Text = dsLastName.Tables[index].Rows[index].Columns["User_LastName"]

if however you want to bind it you might as well look over here and get some clue
http://www.velocityreviews.com/forums/t95227-bind-textbox-to-dataset.html

help with Benchmark

Hi guys!! does any one know a page at microsoft.com or another site that has
the time sql 2005 takes to insert a row ?
i saw at microsfot.com some benchmarks but based on transactions and
comparing Sql with Oracle and IBM, that's not what i'm looking for, i'm
looking for times in mili seconds or micro seconds Sql takes to do it
Thanks!!
No, that information isn't available from Microsoft (and it would be a
license violation for anyone else to publish it without Microsoft's
permission). One major reason is that you have to say under what
conditions. Is it in a user-defined transaction or not? How many indexes
of what type? Do you need to split a b-tree node? Are there concurrent
users? Are there any conflicts? blah blah blah. In other words, it's not
a useful number in this day and age. Now if there were a standardized
benchmark that gave this information it might be interesting, in the context
of that benchmark. Old benchmarks like Wisconsin used to do some of this
(though it was more query than insert/update focused). But most such
benchmarks are no longer published.
You might find some data in comparisons the MySQL folks publish as they (a)
try to make themselves look good by focusing on single-statement comparisons
rather than complex application comparisons and (b) they don't seem to mind
violating Microsoft (and others') license agreements. At least that was the
situation when I looked a couple of years ago.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Rick" <elmargaro@.hotmail.com> wrote in message
news:OMlF5gCVGHA.5808@.TK2MSFTNGP12.phx.gbl...
> Hi guys!! does any one know a page at microsoft.com or another site that
> has the time sql 2005 takes to insert a row ?
> i saw at microsfot.com some benchmarks but based on transactions and
> comparing Sql with Oracle and IBM, that's not what i'm looking for, i'm
> looking for times in mili seconds or micro seconds Sql takes to do it
> Thanks!!
>
>
|||oki doki, thanks Hal, i'll look for some comparisons.
Regards.
"Hal Berenson" <hberenson@.scalabilityexperts.com> escribi en el mensaje
news:eSysFQJVGHA.4864@.TK2MSFTNGP12.phx.gbl...
> No, that information isn't available from Microsoft (and it would be a
> license violation for anyone else to publish it without Microsoft's
> permission). One major reason is that you have to say under what
> conditions. Is it in a user-defined transaction or not? How many indexes
> of what type? Do you need to split a b-tree node? Are there concurrent
> users? Are there any conflicts? blah blah blah. In other words, it's
> not a useful number in this day and age. Now if there were a standardized
> benchmark that gave this information it might be interesting, in the
> context of that benchmark. Old benchmarks like Wisconsin used to do some
> of this (though it was more query than insert/update focused). But most
> such benchmarks are no longer published.
> You might find some data in comparisons the MySQL folks publish as they
> (a) try to make themselves look good by focusing on single-statement
> comparisons rather than complex application comparisons and (b) they don't
> seem to mind violating Microsoft (and others') license agreements. At
> least that was the situation when I looked a couple of years ago.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Rick" <elmargaro@.hotmail.com> wrote in message
> news:OMlF5gCVGHA.5808@.TK2MSFTNGP12.phx.gbl...
>

help with Benchmark

Hi guys!! does any one know a page at microsoft.com or another site that has
the time sql 2005 takes to insert a row '
i saw at microsfot.com some benchmarks but based on transactions and
comparing Sql with Oracle and IBM, that's not what i'm looking for, i'm
looking for times in mili seconds or micro seconds Sql takes to do it
Thanks!!No, that information isn't available from Microsoft (and it would be a
license violation for anyone else to publish it without Microsoft's
permission). One major reason is that you have to say under what
conditions. Is it in a user-defined transaction or not? How many indexes
of what type? Do you need to split a b-tree node? Are there concurrent
users? Are there any conflicts? blah blah blah. In other words, it's not
a useful number in this day and age. Now if there were a standardized
benchmark that gave this information it might be interesting, in the context
of that benchmark. Old benchmarks like Wisconsin used to do some of this
(though it was more query than insert/update focused). But most such
benchmarks are no longer published.
You might find some data in comparisons the mysql folks publish as they (a)
try to make themselves look good by focusing on single-statement comparisons
rather than complex application comparisons and (b) they don't seem to mind
violating Microsoft (and others') license agreements. At least that was the
situation when I looked a couple of years ago.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Rick" <elmargaro@.hotmail.com> wrote in message
news:OMlF5gCVGHA.5808@.TK2MSFTNGP12.phx.gbl...
> Hi guys!! does any one know a page at microsoft.com or another site that
> has the time sql 2005 takes to insert a row '
> i saw at microsfot.com some benchmarks but based on transactions and
> comparing Sql with Oracle and IBM, that's not what i'm looking for, i'm
> looking for times in mili seconds or micro seconds Sql takes to do it
> Thanks!!
>
>|||oki doki, thanks Hal, i'll look for some comparisons.
Regards.
"Hal Berenson" <hberenson@.scalabilityexperts.com> escribi en el mensaje
news:eSysFQJVGHA.4864@.TK2MSFTNGP12.phx.gbl...
> No, that information isn't available from Microsoft (and it would be a
> license violation for anyone else to publish it without Microsoft's
> permission). One major reason is that you have to say under what
> conditions. Is it in a user-defined transaction or not? How many indexes
> of what type? Do you need to split a b-tree node? Are there concurrent
> users? Are there any conflicts? blah blah blah. In other words, it's
> not a useful number in this day and age. Now if there were a standardized
> benchmark that gave this information it might be interesting, in the
> context of that benchmark. Old benchmarks like Wisconsin used to do some
> of this (though it was more query than insert/update focused). But most
> such benchmarks are no longer published.
> You might find some data in comparisons the mysql folks publish as they
> (a) try to make themselves look good by focusing on single-statement
> comparisons rather than complex application comparisons and (b) they don't
> seem to mind violating Microsoft (and others') license agreements. At
> least that was the situation when I looked a couple of years ago.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Rick" <elmargaro@.hotmail.com> wrote in message
> news:OMlF5gCVGHA.5808@.TK2MSFTNGP12.phx.gbl...
>

help with Benchmark

Hi guys!! does any one know a page at microsoft.com or another site that has
the time sql 2005 takes to insert a row '
i saw at microsfot.com some benchmarks but based on transactions and
comparing Sql with Oracle and IBM, that's not what i'm looking for, i'm
looking for times in mili seconds or micro seconds Sql takes to do it
Thanks!!No, that information isn't available from Microsoft (and it would be a
license violation for anyone else to publish it without Microsoft's
permission). One major reason is that you have to say under what
conditions. Is it in a user-defined transaction or not? How many indexes
of what type? Do you need to split a b-tree node? Are there concurrent
users? Are there any conflicts? blah blah blah. In other words, it's not
a useful number in this day and age. Now if there were a standardized
benchmark that gave this information it might be interesting, in the context
of that benchmark. Old benchmarks like Wisconsin used to do some of this
(though it was more query than insert/update focused). But most such
benchmarks are no longer published.
You might find some data in comparisons the MySQL folks publish as they (a)
try to make themselves look good by focusing on single-statement comparisons
rather than complex application comparisons and (b) they don't seem to mind
violating Microsoft (and others') license agreements. At least that was the
situation when I looked a couple of years ago.
--
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Rick" <elmargaro@.hotmail.com> wrote in message
news:OMlF5gCVGHA.5808@.TK2MSFTNGP12.phx.gbl...
> Hi guys!! does any one know a page at microsoft.com or another site that
> has the time sql 2005 takes to insert a row '
> i saw at microsfot.com some benchmarks but based on transactions and
> comparing Sql with Oracle and IBM, that's not what i'm looking for, i'm
> looking for times in mili seconds or micro seconds Sql takes to do it
> Thanks!!
>
>|||oki doki, thanks Hal, i'll look for some comparisons.
Regards.
"Hal Berenson" <hberenson@.scalabilityexperts.com> escribió en el mensaje
news:eSysFQJVGHA.4864@.TK2MSFTNGP12.phx.gbl...
> No, that information isn't available from Microsoft (and it would be a
> license violation for anyone else to publish it without Microsoft's
> permission). One major reason is that you have to say under what
> conditions. Is it in a user-defined transaction or not? How many indexes
> of what type? Do you need to split a b-tree node? Are there concurrent
> users? Are there any conflicts? blah blah blah. In other words, it's
> not a useful number in this day and age. Now if there were a standardized
> benchmark that gave this information it might be interesting, in the
> context of that benchmark. Old benchmarks like Wisconsin used to do some
> of this (though it was more query than insert/update focused). But most
> such benchmarks are no longer published.
> You might find some data in comparisons the MySQL folks publish as they
> (a) try to make themselves look good by focusing on single-statement
> comparisons rather than complex application comparisons and (b) they don't
> seem to mind violating Microsoft (and others') license agreements. At
> least that was the situation when I looked a couple of years ago.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Rick" <elmargaro@.hotmail.com> wrote in message
> news:OMlF5gCVGHA.5808@.TK2MSFTNGP12.phx.gbl...
>> Hi guys!! does any one know a page at microsoft.com or another site that
>> has the time sql 2005 takes to insert a row '
>> i saw at microsfot.com some benchmarks but based on transactions and
>> comparing Sql with Oracle and IBM, that's not what i'm looking for, i'm
>> looking for times in mili seconds or micro seconds Sql takes to do it
>> Thanks!!
>>
>sql

Help with adding column

hi guys! I have a table with 3 columns but i realized that i need to add 1 column between column 2 and 3. Can anybody please help me on how to do that? Thanks in advance!just create a new table and populate it like this:

insert into NewTable
select col1, col2, 'some value', col3 from OldTable

then you can drop your old table. be sure to create indexes, fks, etc on the new table as appropriate once it's populated.|||i need to add 1 column between column 2 and 3.

The physical order of data in a database has no meaning.

Just use alter table and add the field to the end.
You can always specify the display in your select statements.

help with a query

hi guys. I am trying to write a query but dont know how to.

TABLES: Category (id,name ) ; SubCategory (id,Category_ID) , Article (id,SubCategory_ID,title)

I would like to display TOP 5 articles for each category.

GROUP BY is probably the answer...but I just don't know how to that...I hope you know. thanks.

If you are using SQL Server 2005:

Here is one solution:

SELECT t.Category_ID,t.Name, t.title, t.TOP5 FROM (SELECT Category$.Category_ID,Category$.Name, articles$.title, ROW_NUMBER() OVER (PARTITION BY Category$.Category_ID
ORDER BY Category$.Category_ID) AS TOP5
FROM SubCategory$ INNER JOIN
articles$ ON SubCategory$.SubCategory_ID = articles$.SubCategory_ID INNER JOIN
Category$ ON SubCategory$.Category_ID = Category$.Category_ID) t
WHERE t.TOP5<=5

|||

I tried this but i work on sqlserver express...any other option?

|||It should work on your SQL Server 2005 express database.|||

error I get is:The OVER SQL construct or statement is not supported.

maybe we can use a GROUP by that will make it something that the sqlsserver will understand?

|||

Here is another one:

SELECT

t3.Category_ID, t3.SubCategory_ID, t3.ID, t3.Name, t3.titleFROM(SELECT t1.Category_ID, t1.SubCategory_ID, t1.ID, t1.Name, t1.title,(SELECTCOUNT(*)FROM(SELECT Category$.Category_ID, SubCategory$.SubCategory_ID, articles$.ID, Category$.Name, articles$.titleFROM SubCategory$INNERJOIN articles$ON SubCategory$.SubCategory_ID= articles$.SubCategory_IDINNERJOIN Category$ON SubCategory$.Category_ID= Category$.Category_ID) t2WHERE t1.Category_ID=t2.Category_IDAND t2.ID<=t1.ID)as rankNum

FROM

(SELECT Category$.Category_ID, SubCategory$.SubCategory_ID, articles$.ID, Category$.Name, articles$.title

FROM

SubCategory$INNERJOIN

articles$

ON SubCategory$.SubCategory_ID= articles$.SubCategory_IDINNERJOIN

Category$

ON SubCategory$.Category_ID= Category$.Category_ID) t1) t3

WHERE

t3.rankNum<6|||

it's working :)

thank you guru :)

after this was done, I need to display it on ASP.NET html table.

each 5 records will create 1 table with 5 rows. I know there is a gridview but it takes care for only 1 record each time.

thanks alot for the query i would never come to this

Help understanding Stored proc

hi guys! Can anybody please explain what does lines 4,6,8,13 and 26 does? Thanks in advance!

1 USE [PawnShoppeDB]
2 GO
3 /****** Object: StoredProcedure [dbo].[sp_Customer_AddCustomer] Script Date: 06/14/2007 16:50:07 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 ALTER Procedure [dbo].[sp_Customer_AddCustomer]
9 @.Customer_FirstName varchar(50),
10 @.Customer_LastName varchar(50),
11 @.Customer_MiddleInitial varchar(5),
12 @.Customer_Address varchar(100),
13 @.Identity int output
14 AS
15 Begin
16 Declare @.DateCreated DateTime
17 Set @.DateCreated = getDate()
18
19 Insert into RCPS_Customer
20 Values (@.Customer_FirstName,
21 @.Customer_MiddleInitial,
22 @.Customer_LastName,
23 @.Customer_Address,
24 '0',@.DateCreated)
25
26 Set @.identity = Scope_identity()
27 EndLol.

Check books online (SQL Server help) for:
ANSI_NULLS
QUOTED_IDENTIFIER
Scope_identity()
output parameters

Alter proc replaces an existing proc with the same name with the code you posted. That'll be in the help files too.

Help sorting a Recursive Query

Guys, I need help sorting a recursive query. This is my table

CTLG Table

txtID(PK)
txtParentID(FK)
numSortID
txtKeywords
txtTitle
memContent

I'm using txtParentID as the FK, which matches with txtID, to create the recursion.

This is my current Query

This is my current Query:

Code Snippet

WITH tree (data, id, level, pathstr, numSortID, memContent)

AS (SELECT txtTitle, txtid, 0,

CAST('' AS NVARCHAR(MAX)), numSortID, memContent

FROM CTLG

WHERE txtParentID IS NULL

UNION ALL

SELECT txtTitle, V.txtid, t.level + 1, t.pathstr + '>' + V.txtTitle, V.numSortID, v.memContent

FROM CTLG V

INNER JOIN tree t

ON t.id = V.txtParentID)

SELECT SPACE(level) + data as data, id, level, pathstr, numSortID, memContent

FROM tree

The output is this:

Data ID Level pathstr NumSortID Undergraduate Catalog 1 0 1 History 12 1 >History 7 Academic Calendar 14 1 >Academic Calendar 8 Preface 2 1 >Preface 2 NonDiscrimination Statement 3 1 >NonDiscrimination Statement 3 Accreditation 4 1 >Acreditation 4 Memberships 5 1 >Memberships 5 Mission Statement 8 1 >Mission Statement 6 Fall Calendar 20 2 >Academic Calendar>Fall Calendar 1 Winter Calendar 21 2 >Academic Calendar>Winter Calendar 2 Summer Calendar 22 2 >Academic Calendar>Summer Calendar

3

I need my out put to look like the following:

Undergraduate Catalog Level (0) NumSortID (1)

Preface (1) (2)
NonDiscrimination Statement (1) (3)
Accreditation (1) (4)
memberships (1) (5)
Mission Statement (1) (6)
History (1) (7)
Academic Calendar (1) (8)
Fall Calendar (2) (1)
Summer Calendar(2) (2)
Winter Calendar (2) (3)

The Order that I would like to have is based on NumSortID and by Levels. Like the above example.

you forget the order by clause,

Code Snippet

WITH tree (data, id, level, pathstr, numSortID, memContent)

AS (SELECT txtTitle, txtid, 0,

CAST('' AS NVARCHAR(MAX)), numSortID, memContent

FROMCTLG

WHEREtxtParentID IS NULL

UNION ALL

SELECT txtTitle, V.txtid, t.level + 1, t.pathstr + '>' + V.txtTitle, V.numSortID, v.memContent

FROMCTLG V

INNER JOIN tree t

ON t.id = V.txtParentID)

SELECT SPACE(level) + data as data, id, level, pathstr, numSortID, memContent

FROMtree

Order By numSortID

|||

Well no actually I didn't, except that it does sort them out by numSortID, except that it doesn't take into account the levels. Some items that are in level 2 also have a numSortID of 1, 2, and 3. So the item that has level 2 with a numSortID of 1 should not be at the top. it should actually be at the bottom since it's level 2.

|||

Here it is,

Create Table #ctlg (

[Data] Varchar(100) ,

[ID] int ,

[Parent] int

);

Insert Into #ctlg Values('UndergraduateCatalog','1','0');

Insert Into #ctlg Values('History','12','1');

Insert Into #ctlg Values('AcademicCalendar','14','1');

Insert Into #ctlg Values('FallCalendar','20','14');

Insert Into #ctlg Values('WinterCalendar','21','14');

Insert Into #ctlg Values('SummerCalendar','22','14');

Insert Into #ctlg Values('Preface','2','1');

Insert Into #ctlg Values('NonDiscriminationStatement','3','1');

Insert Into #ctlg Values('Accreditation','4','1');

Insert Into #ctlg Values('Memberships','5','1');

Insert Into #ctlg Values('MissionStatement','8','1');

WITH tree (data, id, level, pathstr, sortkey)

AS (

SELECT [Data], [ID], 0, CAST([Data] AS NVARCHAR(MAX)), CAST(10000000 + [ID] AS NVARCHAR(MAX))

FROM #CTLG

WHERE [Parent]=0

UNION ALL

SELECT V.[Data], V.[ID], t.level + 1, t.pathstr +'>'+ V.[Data], CAST(t.sortkey + CAST(10000000 + V.[ID] AS NVARCHAR(MAX)) AS NVARCHAR(MAX))

FROM #CTLG V

INNER JOIN tree t

ON t.id = V.[Parent])

SELECT

data, id, level, pathstr

FROM

tree

order By sortkey

|||

It works! But can you explain to me how you came up with this. I would like to understand it as well.

|||

Simple, as per your requirement you have to sort with Path. Instead of the Name you want to use the Id of the each node. You already have the ‘named’ path. But that won’t suite for your requirement. So I created on more path with ID. Since Id is a integer I made the numbers as same length (adding 100000). So when you create a path with id & use it for the sorting your desired result came.

|||

Very, very Cool!

Help selecting an alias

Hello guys, I need help in something as I don't know if it is possible
what I want.
I have a select like this...

SELECT Cod1 as SQL, Cod2 as Oracle FROM table

and I need to sort by alias SQL or Oracle as the select is composed
dinamically so it could be either Cod1 as SQL or Cod2 as SQL and the
user needs to filter the data using SQL or ORACLE.

I need something like this:

SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' AND
Oracle = 'two'

Any ideas?

Thank youHi

I don't understand what you mean. Is the column SQL, SQL Server Code and the
column Oracle, Oracle Code? If so why would the alias SQL be 'one' and the
alias Oracle be 'two'

> SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' AND
> Oracle = 'two'
As written I assume you mean something like

Select SQL, Oracle from
(SELECT Cod1 as SQL, Cod2 as Oracle FROM table) ss
WHERE SQL = 'one' AND Oracle = 'two'

Which is the same as

SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE Cod1 = 'one' AND Cod2 =
'two'

neither of which appear to be too useful.

Please post some DDL and expected output.
--
-Dick Christoph
"Pumkin" <PopClaudia@.gmail.com> wrote in message
news:1143202849.620253.156550@.z34g2000cwc.googlegr oups.com...
> Hello guys, I need help in something as I don't know if it is possible
> what I want.
> I have a select like this...
> SELECT Cod1 as SQL, Cod2 as Oracle FROM table
> and I need to sort by alias SQL or Oracle as the select is composed
> dinamically so it could be either Cod1 as SQL or Cod2 as SQL and the
> user needs to filter the data using SQL or ORACLE.
> I need something like this:
> SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' AND
> Oracle = 'two'
> Any ideas?
> Thank you|||On 24 Mar 2006 04:20:49 -0800, Pumkin wrote:

>Hello guys, I need help in something as I don't know if it is possible
>what I want.
>I have a select like this...
>SELECT Cod1 as SQL, Cod2 as Oracle FROM table
>and I need to sort by alias SQL or Oracle as the select is composed
>dinamically so it could be either Cod1 as SQL or Cod2 as SQL and the
>user needs to filter the data using SQL or ORACLE.
>I need something like this:
>SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' AND
>Oracle = 'two'
>Any ideas?
>Thank you

Hi Pumkin,

You can't reference an alias directly (except in the ORDER BY clause),
but you can do it indirectly if you use a derived table:

SELECT SQL, Oracle
FROM (SELECT Cod1 AS SQL, Cod2 AS Oracle
FROM YourTable) AS Der
WHERE SQL = 'one'
AND Oracle = 'two'
ORDER BY SQL ASC, Oracle DESC

> the select is composed
>dinamically

Please read the following article very carefully:

http://www.sommarskog.se/dynamic_sql.html

--
Hugo Kornelis, SQL Server MVP|||Pumkin (PopClaudia@.gmail.com) writes:
> Hello guys, I need help in something as I don't know if it is possible
> what I want.
> I have a select like this...
> SELECT Cod1 as SQL, Cod2 as Oracle FROM table
> and I need to sort by alias SQL or Oracle as the select is composed
> dinamically so it could be either Cod1 as SQL or Cod2 as SQL and the
> user needs to filter the data using SQL or ORACLE.
> I need something like this:
> SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' AND
> Oracle = 'two'

I think I would have needed to have more information about where
this query appears.

What you can do is:

SELECT SQL, Oracle
FROM (SELECT Cod1 AS SQL, Cod2 AS Oracle FROM tbl) AS d
WHERE SQL = 'one' AND Oracle = 'two'

The thiing in a parentheses in a derived table. You can use a derived
table for several purposes. Here the purpose is to define queries that
are defined in the rest in the query.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank You all... Your idea with the table within the table really
worked.

SELECT SQL, Oracle
FROM (SELECT Cod1 AS SQL, Cod2 AS Oracle FROM tbl) AS d
WHERE SQL = 'one' AND Oracle = 'two'
That is the final select that worked for me.