help with sql query

Dear all,

i have ' employee_Id ' column in table employeesIBM , and 'employee_Id' column in employeesSUN.

Now,

there are 4 records in employeesIBM.

and 10 records in employeesSUN.

*******What i want to achieve*****

i want to write a query which will display one column 'ALLemployees'

displaying 4 records of employeesIBM and 2 records in employeesSUN.

something like this >>>

employee_Id ******from employeesIBM table

1

2

3

4

employee_Id ******from employeesSUN table

10

11

want to write a query which will display something like this

ALLemployees

1

2

3

4

10

11

Please help me out with this.

Kris

Hi,

Try something like the following:

(
SELECT employee_id AS ALLemployees
FROM employeesIBM
)
UNION
(
SELECT employee_id
FROM employeesSUN
)

Not perfect, but it should get you the result set you're after. Note that if there are duplicate rows between tables and you don't want to filter out the duplicates then I believe you need to use UNION ALL instead of UNION

Hope that helps a bit, but sorry if it doesn't
|||

That does solves my prob.

Thanks,

Kris

sql

help with sql query

Lets assume i have ' employee_Id ' column in table employeesIBM , and 'employee_Id' column in employeesSUN.

Now,

Lets once again assume,that there are 4 records in employeesIBM.

and 10 records in employeesSUN.

*******What i want to achieve*****

i want to write a query which will display one column 'ALLemployees'

displaying 4 records of employeesIBM and 2 records in employeesSUN.

something like this >>>

employee_Id ******from employeesIBM table

1

2

3

4

employee_Id ******from employeesSUN table

10

11

want to write a query which will display something like this

ALLemployees

1

2

3

4

10

11

Please help me out with this.

Kris

Kris, you've specified returning only 2 of 10 rows from your employeeSUN table without specifying the defining criteria causing only those two rows to be returned. My initial thought is to define a UNION query to produce this result, but a little more information will be required.

select employee_id as ALLemployees from employeesIBM

union all

select employee_id as ALLemployees from employeesSUN

where <some condition exists>

|||

Hi Allen,

Consider that im not so thorough with sql , But sure your reply has helped me a lot.

Thanks

Kris

help with sql query

Lets assume i have ' employee_Id ' column in table employeesIBM , and 'employee_Id' column in employeesSUN.

Now,

Lets once again assume,that there are 4 records in employeesIBM.

and 10 records in employeesSUN.

*******What i want to achieve*****

i want to write a query which will display one column 'ALLemployees'

displaying 4 records of employeesIBM and 2 records in employeesSUN.

something like this >>>

employee_Id ******from employeesIBM table

1

2

3

4

employee_Id ******from employeesSUN table

10

11

want to write a query which will display something like this

ALLemployees

1

2

3

4

10

11

Please help me out with this.

Kris

Made another post about this at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=424936&SiteID=1

PS. Prolly better if you only post in one place. For questions of this sort I think the Transact-SQL forum would be better suited.

Ta.

Help with SQL and datagrids

Hello-

I have a fairly complicated question. Is it possible to have a datagrid where each column represents a different day of the week with different data. Meaning each column dependent on the day of the week would be produced using a formula.

My problem is I have a database with 100's of customers. And i need to display a datagrid with each column being a day of the month. The rows would be customers. So in each box would be that customers revenue for that specific day in the month. Can anyone give me any advice on how to do this?

ThanksSeems like this would be fairly straightforward, but we don't know the structure of your data. But why not do a select customer, (subquery for week) as week 1 for your query. Then when you bind to the datagrid, your columns would be fixed.

something along those lines. If you need more help, give us the tables and fields you are working with, and any queries you have attempted to write so far.|||Here is what is going on.

The SQL name is pb_report_shippers.

The columns that are most important to me are customer_id and total_ext_price and shipper_date_time.

So i need to be able to query the table and grab total_ext_price for a specific customer_id and add up all there total_ext_price and sort them into columns by date or (shipper_date_time)

Hope this clarifies. Any examples in vb would be great.

Thanks|||If you are not able to modify your query, then I would suggest this thread be continued on the datagrids forum, since that's more what you need. If you can modify your SP, or create a new one, I'm suggesting that you create columns in your SP for the weekly totals. To give a little more meat to the sample query I posted:

Create Procedure blah
as
select customer,
(select sum(total_ext_price)
from table t2
where shipper_date_time between cast('10/01/03' as datetime) and cast('10/01/03' as datetime)
AND t2.customerid = t1.customerid ) as week1
... continue on as needed

The code I've given you there is close to what you might need but you would have to put in the right dates etc. Once that SP is created, you merely call that. And you bind your datagrid to the columns using the week1 etc alias. I'm not sure if I need to go into more detail on this method, or if that conveys the idea to you. Hopefully that clarifies what I was suggesting.|||I am not using stored procedures for this, nor do I at this point. I get the idea kinda. I guess I need a little more specific example. Also I need to pull the entire month at one time.

For example I need to pull all September revenue for every customer in the database. I need to add up each customers revenue arrange them in a table column for each day.

How would I be able to figure out how many days are in a specific month and know how to query for that specific range of dates?

Thanks

help with sort

I have a column which has values as
1
2
2.1
3
4
2.1.1
2.1.1.1
2.1.2
2.1.3
2.2
2.1.4
2.3
2.99
2.99.1
2.1.5
2.99.1.1
2.1.6
2.100
2.100.1
2.101
2.102
2.1.7
and i need to get this sorted as
1
2
2.1
2.1.1
2.1.1.1
2.1.2
2.1.3
2.1.4
2.1.5
2.1.6
2.1.7
2.2
2.99
2.99.1
2.99.1.1
2.100
2.100.1
2.101
2.102
Is this comething doable thru sql. any help will be greatly appreciated.
Thanks
Message posted via http://www.webservertalk.com>> Is this comething doable thru sql.
Are these IP addresses or something? Is it always less than or equal to 4
digits in the string? If such sorting is a business requirement, you might
want to consider a better schema. You post somewhat exemplifies the
complexity behind queries involving tables with multiple values crammed in a
single column.
One quick & dirty way is to do:
SELECT col
FROM tbl
ORDER BY CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 4 ) AS INT ),
CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 3 ) AS INT ),
CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 2 ) AS INT ),
CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 1 ) AS INT );
You can find details about PARSENAME and REPLICATE functions in SQL Server
Books Online. The idea is a extract each component ( as identified by a
dot ) of the string and then sort them accordingly. The type conversion to
INT is to avoid character based sorting which is not what is specified in
your sample results.
Anith

Help with SELECT statement

Hi,
I have two tables with a UserID column and need to construct a query that
lists all UserIDs from Table A that is not present in Table B.
Any help with this select statement would be appreciated
NiclasSelect A.* from TableA as A where Not Exists (select * from TableB as B
where B.UserId = A.UserId)
You can also go with a Left Outer Join but it's a little more complicated to
understand:
Select A.* from TableA as A Left Outer Join TableB as B on A.UserId =
B.UserId
Where B.UserId is Null
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have two tables with a UserID column and need to construct a query that
> lists all UserIDs from Table A that is not present in Table B.
> Any help with this select statement would be appreciated
> Niclas
>|||A third possibility would be to use the IN clause:
Select A.* from TableA as A where A.UserId Not IN (select UserId from TableB
Where UserId is not Null)
The condition Where B.UserId is Not Null is a necessity if there is a
possibility that B.UserId can be Null; otherwise the result won't be good if
the IN clause encounter a Null value.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have two tables with a UserID column and need to construct a query that
> lists all UserIDs from Table A that is not present in Table B.
> Any help with this select statement would be appreciated
> Niclas
>|||Many thanks !
Niclas
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:emU3YG65FHA.2888@.tk2msftngp13.phx.gbl...
>A third possibility would be to use the IN clause:
> Select A.* from TableA as A where A.UserId Not IN (select UserId from
> TableB Where UserId is not Null)
> The condition Where B.UserId is Not Null is a necessity if there is a
> possibility that B.UserId can be Null; otherwise the result won't be good
> if the IN clause encounter a Null value.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "Niclas" <lindblom_niclas@.hotmail.com> wrote in message
> news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
>

Help with Select statement

I have 2 columns with data in different sequences in one table referencing a single column in different table.

I'm trying to learn SQL using SQLserver 2000.
I need some help Please!!

I'm having trouble creating a view that will give me the information that i need correctly.
I listed all the tables and the view that I tried but it's not working I dont think i have the view right.

Here is some info to help you understand what I'm trying to get:

Examples of the data that I'm having trouble with
only conscerns two of the tables

CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL, <---
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)
CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL, <Both ref above table
intAlternateDriverID INTEGER NOT NULL, <Both ref above table
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)

TDrivers Table has
intDriverID 1, 2, 3, 4, 5 and each id is associated with a name

1 = john
2 = mike
3 = sam
4 = jim
5 = tony

TScheduledRoutes Table
has column
intDriverID
and data is 1, 2, 3, 4, 5 that references TDrivers.intDriverID

and has column
intAlternateDriverID
and data is 5, 3, 1, 2, 4 that references TDrivers.intDriverID also

NOTICE the two have different sequence.

I need to get a select statement that would give me a list of
TScheduledRoutes.intDriverID full name
and their assciated alternate driver
TScheduledRoutes.intAlternateDriverID

output would give this as example

(intdriverID 1) john would have alt driverId 5 tony

I can't create a select statement that will give me both names at the same time.

Below is a list of the actual code and the view I cant get to do what I want it to and still keep the database in 3rd normal form.

Any suggestions would be greatly appreciated.

CREATE TABLE TRoutes
(
intRouteID INTEGER NOT NULL,
strRoute VARCHAR(30) NOT NULL,
strRouteDescription VARCHAR(50) NOT NULL,
CONSTRAINT TRoutes_PK PRIMARY KEY (intRouteID)
)

CREATE TABLE TBuses
(
intBusID INTEGER NOT NULL,
strBus VARCHAR(25) NOT NULL,
intCapacity INTEGER NOT NULL,
CONSTRAINT TBuses_PK PRIMARY KEY (intBusID)
)

CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL,
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)

CREATE TABLE TScheduleTimes
(
intScheduleTimeID INTEGER NOT NULL,
strScheduleTime DATETIME NOT NULL,
CONSTRAINT TScheduleTimes_PK PRIMARY KEY (intScheduleTimeID)
)

every column below is a foreign key to other tables

CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL,
intAlternateDriverID INTEGER NOT NULL,
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)

CREATE NONCLUSTERED INDEX TRoutes_NI ON TRoutes(strRoute)
CREATE NONCLUSTERED INDEX TBuses_NI ON TBuses(strBus)
CREATE NONCLUSTERED INDEX TDrivers_NI ON TDrivers (strLastName,strFirstName)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TBuses_TScheduledRoutes_FK
FOREIGN KEY (intBusID)REFERENCES TBuses(intBusID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TRoutes_TScheduledRoutes_FK
FOREIGN KEY (intRouteID)REFERENCES TRoutes(intRouteID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TDrivers_TScheduledRoutes_FK
FOREIGN KEY (intDriverID)REFERENCES TDrivers(intDriverID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TADrivers_TScheduledRoutes_FK
FOREIGN KEY (intAlternateDriverID)REFERENCES TAltDrivers(intAltDriverID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TScheduleTimes_TScheduledRoutes_FK
FOREIGN KEY (intScheduleTimeID)REFERENCES TScheduleTimes(intScheduleTimeID)

Here is what I tried but its not working. Is there better way to get the information I need and
keep the database in 3rd Normal form

CREATE VIEW V_SchedualedRoutes AS

SELECT TRoutes.strRoute,
TBuses.strBus,
(TDrivers.strLastName + ', '+ TDrivers.strFirstName)
AS strDriverFullName,
(SELECT TDrivers.strLastName + ', '
+ TDrivers.strFirstName)
FROM TDrivers
INNER JOIN TScheduledRoutes
ON TDrivers.intDriverID =
TScheduledRoutes.intDriverID
WHERE TScheduledRoutes.intAlternateDriverID=
TDrivers.intDriverI)
AS strAltDriFullName, TScheduleTimes.strScheduleTime
FROM TBuses
INNER JOIN TScheduledRoutes
ON TBuses.intBusID = TScheduledRoutes.intBusID
INNER JOIN TScheduleTimes
ON TScheduledRoutes.intScheduleTimeID =
TScheduleTimes.intScheduleTimeID
INNER JOIN TDrivers
ON TScheduledRoutes.intDriverID = TDrivers.intDriverID
AND TScheduledRoutes.intAlternateDriverID =
TDrivers.intDriverID
INNER JOIN TRoutes
ON TScheduledRoutes.intRouteID = TRoutes.intRouteIDI am having trouble following your query, but in essence it appears that the trouble you are having is knowing how to access the same table twice in a query - once for the main driver and once for the alternate driver. The solution is to use table aliases:

SELECT d1.strLastName MainDriver,
d1.strLastName AlternateDriver
FROM TScheduledRoutes sr
INNER JOIN TDrivers d1 ON sr.intDriverID = d1.intDriverID
INNER JOIN TDrivers d2 ON sr.intAlternateDriverID = d2.intDriverID

In your query, this bit looks to me like a syntax error:

(SELECT TDrivers.strLastName + ', ' + TDrivers.strFirstName) FROM TDrivers

... unless SQL Server has a very different SQL syntax that the one I know.|||Sorry about the confusion but thats it. Thank yousql

Help with SELECT query

My SELECT query returns a data set, one column of which contains a set
of values corresponding to the same date. I.e. for each date in column
"Date", I have a set of numbers in column "Numbers". However, I am
only interested in getting the largest value in column "Numbers"
corresponding to each value in column "Date". How do I do that?
Thanks,

Marcomdi00@.hotmail.com (Marco) wrote in news:e5f1d809.0411190312.c9f8b07
@.posting.google.com:

> My SELECT query returns a data set, one column of which contains a set
> of values corresponding to the same date. I.e. for each date in column
> "Date", I have a set of numbers in column "Numbers". However, I am
> only interested in getting the largest value in column "Numbers"
> corresponding to each value in column "Date". How do I do that?
> Thanks,
> Marco

SELECT "Date", MAX("Numbers")AS "Largest number"
FROM sometable
GROUP BY "Date"|||Marco wrote:

> My SELECT query returns a data set, one column of which contains a set
> of values corresponding to the same date. I.e. for each date in column
> "Date", I have a set of numbers in column "Numbers". However, I am
> only interested in getting the largest value in column "Numbers"
> corresponding to each value in column "Date". How do I do that?
> Thanks,
> Marco

Look up MAX and GROUP BY.

Help with sample code for ssis surrogate key transform

I am trying to write a ssis surrogate key data transform, my problem is I can't find an example how to add a column to the incoming columns and add some data to it. If anyone has a sample, can you please post it. I found a script option that works but I would like an actual transform.

Thanks

Basically - here is a surrogate key Transform Script - to generate image numbers for products
Input is ProdNum column - output ImgNo colum.
The idea is to get the result like this:
ProdNum ImgNo
1 1
1 2
2 1
3 1
3 2

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Dim imgno As Short, incr As Short, prevProdNum As String

Public Sub New()
imgno = 0
incr = 1
prevProdNum = ""
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.ProdNum <> prevProdNum Then
imgno = incr
Else
imgno += incr
End If
Row.ImgNo = imgno
prevProdNum = Row.ProdNum
End Sub
End Class|||Also - you can check out this article "SSIS Generating Surrogate Keys"

Help with reformatting xml

This is a simplistic example of what I’m trying to achieve;
I have some XML stored in a table (in a column of type xml):
<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>
And I want to generate new xml as follows:
<report>
<somevalue>13</somevalue>
</report>
Where 13 is the total value of all somevalue attributes.
I’ve been looking at the query() and nodes() methods and cannot quite do
what I want to do although I feel it might be possible. Currently I’m simply
selecting the xml and letting my client application do the formatting,
however I feel that this might cause some performance issues on large blobs
of xml data.
Is there a better way to do this?
Many thanks in advance for any help with this.
Julia Beresford.
Something like this?
DECLARE @.doc XML;
SELECT @.doc = N'<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>';
SELECT @.doc.query('<report>
<somevalue>
{ fn:sum(/root/node/@.somevalue) }
</somevalue>
</report>');
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:5A5E83A9-F098-454D-80FA-FA212A3B15E8@.microsoft.com...
> This is a simplistic example of what I'm trying to achieve;
> I have some XML stored in a table (in a column of type xml):
> <root>
> <node id="1" somevalue="0" />
> <node id="2" somevalue="1" />
> <node id="3" somevalue="5" />
> <node id="4" somevalue="7" />
> </root>
> And I want to generate new xml as follows:
> <report>
> <somevalue>13</somevalue>
> </report>
> Where 13 is the total value of all somevalue attributes.
> I've been looking at the query() and nodes() methods and cannot quite do
> what I want to do although I feel it might be possible. Currently I'm
> simply
> selecting the xml and letting my client application do the formatting,
> however I feel that this might cause some performance issues on large
> blobs
> of xml data.
> Is there a better way to do this?
> Many thanks in advance for any help with this.
> Julia Beresford.
>

Help with reformatting xml

This is a simplistic example of what I’m trying to achieve;
I have some XML stored in a table (in a column of type xml):
<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>
And I want to generate new xml as follows:
<report>
<somevalue>13</somevalue>
</report>
Where 13 is the total value of all somevalue attributes.
I’ve been looking at the query() and nodes() methods and cannot quite do
what I want to do although I feel it might be possible. Currently I’m sim
ply
selecting the xml and letting my client application do the formatting,
however I feel that this might cause some performance issues on large blobs
of xml data.
Is there a better way to do this?
Many thanks in advance for any help with this.
Julia Beresford.Something like this?
DECLARE @.doc XML;
SELECT @.doc = N'<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>';
SELECT @.doc.query('<report>
<somevalue>
{ fn:sum(/root/node/@.somevalue) }
</somevalue>
</report>');
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:5A5E83A9-F098-454D-80FA-FA212A3B15E8@.microsoft.com...
> This is a simplistic example of what I'm trying to achieve;
> I have some XML stored in a table (in a column of type xml):
> <root>
> <node id="1" somevalue="0" />
> <node id="2" somevalue="1" />
> <node id="3" somevalue="5" />
> <node id="4" somevalue="7" />
> </root>
> And I want to generate new xml as follows:
> <report>
> <somevalue>13</somevalue>
> </report>
> Where 13 is the total value of all somevalue attributes.
> I've been looking at the query() and nodes() methods and cannot quite do
> what I want to do although I feel it might be possible. Currently I'm
> simply
> selecting the xml and letting my client application do the formatting,
> however I feel that this might cause some performance issues on large
> blobs
> of xml data.
> Is there a better way to do this?
> Many thanks in advance for any help with this.
> Julia Beresford.
>

Help with Recursive Function

I am writing a function which I hope to use as a column value in a
select query. The function recursively walks a taxonomic heirarchy,
extracting the name for an organism at the taxonomic level requested
by the user. I'm having trouble figuring out the syntax to call the
function from itself (see **1), and the value returned.

When I test the funciton, it says 'commands completed successfully',
but nothing is returned. This is in SQL2000, runing on Windows2000.
The table the function acts on is:

CREATE TABLE [dbo].[tblbenthictaxa] (
[tsn] [int] IDENTITY (1, 1) NOT NULL ,
[rank_id] [int] NOT NULL ,
[dir_parent_tsn] [int] NULL ,
[req_parent_tsn] [int] NOT NULL ,
[taxa_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO

ReqParentTSN is the recursive link to rows in the table;
Level is the taxonomic level the user requested (an integer
representing Order, Family, Genus or Species).

CREATE FUNCTION dbo.CBN_RecursTaxa (
@.ReqParentTSN int,
@.Level int
)
RETURNS varchar(100) AS

BEGIN

Declare @.Rank int,
@.taxaname varchar(100)

SELECT @.ReqParentTSN = tblbenthictaxa.req_parent_tsn,
@.TaxaName = tblbenthictaxa.taxa_name,
@.Rank = tblbenthictaxa.rank_id
FROM tblbenthictaxa
WHERE tblbenthictaxa.TSN=@.ReqParentTSN

if @.Rank > @.Level
**1 --exec CBN_RecursTaxa @.ReqParentTSN, @.Level

RETURN @.TaxaName
END

Thanks in advance for any help,

TimTim Pascoe (tim.pascoe@.cciw.ca) writes:
> I am writing a function which I hope to use as a column value in a
> select query. The function recursively walks a taxonomic heirarchy,
> extracting the name for an organism at the taxonomic level requested
> by the user. I'm having trouble figuring out the syntax to call the
> function from itself (see **1), and the value returned.
> When I test the funciton, it says 'commands completed successfully',
> but nothing is returned. This is in SQL2000, runing on Windows2000.
> The table the function acts on is:

There are two ways to run a scalar UDF, and I don't know which you are
using. But I think this example, gives you the answer to both of your
questions:

CREATE FUNCTION nisse (@.i int) returns varchar(200) AS
BEGIN
DECLARE @.ret varchar(200),
@.tmp varchar(200)
SELECT @.i = @.i - 1, @.ret = 'nisse '
IF @.i > 0
BEGIN
EXEC @.tmp = dbo.nisse @.i
SELECT @.ret = @.tmp + @.ret
END
RETURN @.ret
END
go
SELECT dbo.nisse(4)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

The example was perfect. I knew it was something small, but the simple
things are sometimes the hardest to track down when you are learning.

The function works perfectly, and is much faster than the original ASP
script approach I had.

Thanks again,

Tim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Help with read from a text column

All, I have a table containing an ID and a text column. I need to dump the
content of the table into a text file using the following code:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRAN
set textsize 62713
DECLARE @.ptr varbinary(16)
SELECT @.ptr = textptr(note)
FROM notes
WHERE id = 307
READTEXT note @.ptr 0 62713
COMMIT TRAN
GO
However, I get only 8K out of the text column where I am suppose to get
62713. I ran OSQL to re-direct the output to a file.
thanks.
JohnHi
Query Analyser has a maximum column width or 8192 characters and you can not
increase the size beyone that in the options dialog. Although I have not
found anything to say that osql has a similar limit I can get 8342 character
s
out.
To write to a file correctly look at
http://support.microsoft.com/defaul...kb;en-us;317043
John
"John Smith" wrote:

> All, I have a table containing an ID and a text column. I need to dump the
> content of the table into a text file using the following code:
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> GO
> BEGIN TRAN
> set textsize 62713
> DECLARE @.ptr varbinary(16)
> SELECT @.ptr = textptr(note)
> FROM notes
> WHERE id = 307
> READTEXT note @.ptr 0 62713
> COMMIT TRAN
> GO
> However, I get only 8K out of the text column where I am suppose to get
> 62713. I ran OSQL to re-direct the output to a file.
> thanks.
> John

Help with Query.

Friends,
I have a table as shown below,
ABC DEF
23 2156
34 2156
41 2156
34 2157
38 2157
41 2157
I would like to return data for ABC column in a comma seperated format, I
would like to import the data into a new table.
23,34,41 2156
34,38,41 2157
How can i do that?
Any help is greatly appreciated.
TIA,
Santosh
Santhosh,
Here is an example. modify it to fit your schema.
CREATE TABLE Users(Uid int, Username VARCHAR(35))
CREATE TABLE Roles(Rid int, RoleName VARCHAR(35))
CREATE TABLE UserRoles(Uid int, Rid int)
Go
INSERT INTO Users VALUES(1, 'A')
INSERT INTO Users VALUES(2, 'B')
INSERT INTO Users VALUES(3, 'C')
INSERT INTO Users VALUES(4, 'D')
INSERT INTO Roles Values(1,'Admin')
INSERT INTO Roles Values(2,'Accounts')
INSERT INTO Roles Values(3,'Operations')
INSERT INTO Roles Values(4,'Marketing')
INSERT INTO UserRoles VALUES(1,1)
INSERT INTO UserRoles VALUES(1,4)
INSERT INTO UserRoles VALUES(2,3)
INSERT INTO UserRoles VALUES(2,4)
INSERT INTO UserRoles VALUES(3,1)
INSERT INTO UserRoles VALUES(3,2)
INSERT INTO UserRoles VALUES(3,3)
INSERT INTO UserRoles VALUES(4,2)
Go
CREATE Function dbo.GetRoles(@.Uid int)
RETURNS VARCHAR(400)
AS
BEGIN
DECLARE @.vchRoleList VARCHAR(400)
SET @.vchRoleList = ''
SELECT @.vchRoleList = @.vchRoleList +
CASE WHEN @.vchRoleList= '' THEN '' ELSE ', ' END + RoleName
FROM Roles R
INNER JOIN UserRoles UR
ON R.Rid = UR.Rid AND UR.UId = @.Uid
RETURN @.vchRoleList
END
GO
SELECT U.Uid, U.UserName, dbo.GetRoles(Uid)
FROM Users U
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
news:eIBVj844EHA.3368@.TK2MSFTNGP10.phx.gbl...
> Friends,
> I have a table as shown below,
> ABC DEF
> --
> 23 2156
> 34 2156
> 41 2156
> 34 2157
> 38 2157
> 41 2157
>
> I would like to return data for ABC column in a comma seperated format, I
> would like to import the data into a new table.
> 23,34,41 2156
> 34,38,41 2157
> How can i do that?
> Any help is greatly appreciated.
> TIA,
> --
> Santosh
>
>
|||It works.
Thanks,
Appreciate it.
Santosh
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:eD8U7354EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Santhosh,
> Here is an example. modify it to fit your schema.
>
> CREATE TABLE Users(Uid int, Username VARCHAR(35))
> CREATE TABLE Roles(Rid int, RoleName VARCHAR(35))
> CREATE TABLE UserRoles(Uid int, Rid int)
> Go
> INSERT INTO Users VALUES(1, 'A')
> INSERT INTO Users VALUES(2, 'B')
> INSERT INTO Users VALUES(3, 'C')
> INSERT INTO Users VALUES(4, 'D')
> INSERT INTO Roles Values(1,'Admin')
> INSERT INTO Roles Values(2,'Accounts')
> INSERT INTO Roles Values(3,'Operations')
> INSERT INTO Roles Values(4,'Marketing')
>
> INSERT INTO UserRoles VALUES(1,1)
> INSERT INTO UserRoles VALUES(1,4)
> INSERT INTO UserRoles VALUES(2,3)
> INSERT INTO UserRoles VALUES(2,4)
> INSERT INTO UserRoles VALUES(3,1)
> INSERT INTO UserRoles VALUES(3,2)
> INSERT INTO UserRoles VALUES(3,3)
> INSERT INTO UserRoles VALUES(4,2)
> Go
> CREATE Function dbo.GetRoles(@.Uid int)
> RETURNS VARCHAR(400)
> AS
> BEGIN
> DECLARE @.vchRoleList VARCHAR(400)
> SET @.vchRoleList = ''
> SELECT @.vchRoleList = @.vchRoleList +
> CASE WHEN @.vchRoleList= '' THEN '' ELSE ', ' END + RoleName
> FROM Roles R
> INNER JOIN UserRoles UR
> ON R.Rid = UR.Rid AND UR.UId = @.Uid
> RETURN @.vchRoleList
> END
> GO
> SELECT U.Uid, U.UserName, dbo.GetRoles(Uid)
> FROM Users U
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> news:eIBVj844EHA.3368@.TK2MSFTNGP10.phx.gbl...
>

Help with Query.

Friends,
I have a table as shown below,
ABC DEF
--
23 2156
34 2156
41 2156
34 2157
38 2157
41 2157
I would like to return data for ABC column in a comma seperated format, I
would like to import the data into a new table.
23,34,41 2156
34,38,41 2157
How can i do that?
Any help is greatly appreciated.
TIA,
--
SantoshSanthosh,
Here is an example. modify it to fit your schema.
CREATE TABLE Users(Uid int, Username VARCHAR(35))
CREATE TABLE Roles(Rid int, RoleName VARCHAR(35))
CREATE TABLE UserRoles(Uid int, Rid int)
Go
INSERT INTO Users VALUES(1, 'A')
INSERT INTO Users VALUES(2, 'B')
INSERT INTO Users VALUES(3, 'C')
INSERT INTO Users VALUES(4, 'D')
INSERT INTO Roles Values(1,'Admin')
INSERT INTO Roles Values(2,'Accounts')
INSERT INTO Roles Values(3,'Operations')
INSERT INTO Roles Values(4,'Marketing')
INSERT INTO UserRoles VALUES(1,1)
INSERT INTO UserRoles VALUES(1,4)
INSERT INTO UserRoles VALUES(2,3)
INSERT INTO UserRoles VALUES(2,4)
INSERT INTO UserRoles VALUES(3,1)
INSERT INTO UserRoles VALUES(3,2)
INSERT INTO UserRoles VALUES(3,3)
INSERT INTO UserRoles VALUES(4,2)
Go
CREATE Function dbo.GetRoles(@.Uid int)
RETURNS VARCHAR(400)
AS
BEGIN
DECLARE @.vchRoleList VARCHAR(400)
SET @.vchRoleList = ''
SELECT @.vchRoleList = @.vchRoleList +
CASE WHEN @.vchRoleList= '' THEN '' ELSE ', ' END + RoleName
FROM Roles R
INNER JOIN UserRoles UR
ON R.Rid = UR.Rid AND UR.UId = @.Uid
RETURN @.vchRoleList
END
GO
SELECT U.Uid, U.UserName, dbo.GetRoles(Uid)
FROM Users U
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
news:eIBVj844EHA.3368@.TK2MSFTNGP10.phx.gbl...
> Friends,
> I have a table as shown below,
> ABC DEF
> --
> 23 2156
> 34 2156
> 41 2156
> 34 2157
> 38 2157
> 41 2157
>
> I would like to return data for ABC column in a comma seperated format, I
> would like to import the data into a new table.
> 23,34,41 2156
> 34,38,41 2157
> How can i do that?
> Any help is greatly appreciated.
> TIA,
> --
> Santosh
>
>|||It works.
Thanks,
Appreciate it.
--
Santosh
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:eD8U7354EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Santhosh,
> Here is an example. modify it to fit your schema.
>
> CREATE TABLE Users(Uid int, Username VARCHAR(35))
> CREATE TABLE Roles(Rid int, RoleName VARCHAR(35))
> CREATE TABLE UserRoles(Uid int, Rid int)
> Go
> INSERT INTO Users VALUES(1, 'A')
> INSERT INTO Users VALUES(2, 'B')
> INSERT INTO Users VALUES(3, 'C')
> INSERT INTO Users VALUES(4, 'D')
> INSERT INTO Roles Values(1,'Admin')
> INSERT INTO Roles Values(2,'Accounts')
> INSERT INTO Roles Values(3,'Operations')
> INSERT INTO Roles Values(4,'Marketing')
>
> INSERT INTO UserRoles VALUES(1,1)
> INSERT INTO UserRoles VALUES(1,4)
> INSERT INTO UserRoles VALUES(2,3)
> INSERT INTO UserRoles VALUES(2,4)
> INSERT INTO UserRoles VALUES(3,1)
> INSERT INTO UserRoles VALUES(3,2)
> INSERT INTO UserRoles VALUES(3,3)
> INSERT INTO UserRoles VALUES(4,2)
> Go
> CREATE Function dbo.GetRoles(@.Uid int)
> RETURNS VARCHAR(400)
> AS
> BEGIN
> DECLARE @.vchRoleList VARCHAR(400)
> SET @.vchRoleList = ''
> SELECT @.vchRoleList = @.vchRoleList +
> CASE WHEN @.vchRoleList= '' THEN '' ELSE ', ' END + RoleName
> FROM Roles R
> INNER JOIN UserRoles UR
> ON R.Rid = UR.Rid AND UR.UId = @.Uid
> RETURN @.vchRoleList
> END
> GO
> SELECT U.Uid, U.UserName, dbo.GetRoles(Uid)
> FROM Users U
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> news:eIBVj844EHA.3368@.TK2MSFTNGP10.phx.gbl...
>> Friends,
>> I have a table as shown below,
>> ABC DEF
>> --
>> 23 2156
>> 34 2156
>> 41 2156
>> 34 2157
>> 38 2157
>> 41 2157
>>
>> I would like to return data for ABC column in a comma seperated format, I
>> would like to import the data into a new table.
>> 23,34,41 2156
>> 34,38,41 2157
>> How can i do that?
>> Any help is greatly appreciated.
>> TIA,
>> --
>> Santosh
>>
>

Help with query! Ranking of Sum() column

If anyone can help with this, I'd be most appreciative.
Basically, I'm trying to sum a column based on a unique ID and then
find out the RANK of that record in the table.
Here's the table:
Points
- PointID
- UserID
- Points
There can be multiple records with the same UserID.
Here's the query I'm using now:
SELECT Sum(Points), UserID FROM Points Group By UserID Order By
Sum(Points) desc
This basically returns ALL the records with the Points summed. I think
loop through in my code to find what row number a specific ID is. This
is NOT efficient and is slowing down my site considerably.
This is a query someone recommended I used:
SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
sum(points) FROM Points WHERE UserId = 65))
SQL Server doesn't like that query, though, because of the aggregate
function. I was searching for the rank of UserID 65.
If anyone could help me with this I'd appreciate it. I ONLY need the
rank of one record, so hoepfully I don't need to use a temp table for
this.
Thanks,
Andyhttp://www.aspfaq.com/show.asp?id=2427
<andymilk@.gmail.com> wrote in message
news:1146492469.413328.220320@.j33g2000cwa.googlegroups.com...
> If anyone can help with this, I'd be most appreciative.
> Basically, I'm trying to sum a column based on a unique ID and then
> find out the RANK of that record in the table.
> Here's the table:
> Points
> - PointID
> - UserID
> - Points
> There can be multiple records with the same UserID.
> Here's the query I'm using now:
> SELECT Sum(Points), UserID FROM Points Group By UserID Order By
> Sum(Points) desc
> This basically returns ALL the records with the Points summed. I think
> loop through in my code to find what row number a specific ID is. This
> is NOT efficient and is slowing down my site considerably.
> This is a query someone recommended I used:
> SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
> sum(points) FROM Points WHERE UserId = 65))
> SQL Server doesn't like that query, though, because of the aggregate
> function. I was searching for the rank of UserID 65.
> If anyone could help me with this I'd appreciate it. I ONLY need the
> rank of one record, so hoepfully I don't need to use a temp table for
> this.
> Thanks,
> Andy
>|||Try,
-- sql server 2000
create view v1
as
select UserID, sum(Points) as sum_Points
from t1
group by UserID
go
select * from v1
go
select
count(*) as rank,
a.UserID,
a.sum_Points
from
v1 as a inner join v1 as b
on (a.sum_Points < b.sum_Points)
or (a.sum_Points = b.sum_Points and a.UserID >= b.UserID)
group by
a.UserID,
a.sum_Points
order by
rank
go
AMB
"andymilk@.gmail.com" wrote:

> If anyone can help with this, I'd be most appreciative.
> Basically, I'm trying to sum a column based on a unique ID and then
> find out the RANK of that record in the table.
> Here's the table:
> Points
> - PointID
> - UserID
> - Points
> There can be multiple records with the same UserID.
> Here's the query I'm using now:
> SELECT Sum(Points), UserID FROM Points Group By UserID Order By
> Sum(Points) desc
> This basically returns ALL the records with the Points summed. I think
> loop through in my code to find what row number a specific ID is. This
> is NOT efficient and is slowing down my site considerably.
> This is a query someone recommended I used:
> SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
> sum(points) FROM Points WHERE UserId = 65))
> SQL Server doesn't like that query, though, because of the aggregate
> function. I was searching for the rank of UserID 65.
> If anyone could help me with this I'd appreciate it. I ONLY need the
> rank of one record, so hoepfully I don't need to use a temp table for
> this.
> Thanks,
> Andy
>

Help with query! Ranking of Sum() column

Are you sure this works with an aggregate function?
Also, can I return ONE row and get the correct rank?Can you provide some useful DDL, sample data, and desired output? (
http://www.aspfaq.com/5006 )
I'm having a hard time visualizing " I think loop through in my code to find
what row number a specific ID is."
A
<andymilk@.gmail.com> wrote in message
news:1146493870.042951.123210@.i39g2000cwa.googlegroups.com...
> Are you sure this works with an aggregate function?
> Also, can I return ONE row and get the correct rank?
>

Help with query rewrite

I have the following nonindexable query due to the "<>" operater. Column "id
"
in the following scenario is a clustered index.
DECLARE @.tid
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
Is there a way to rewrite such a search condition so as to make it an
indexexable search condition?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1How large is the table? How much of the table is equal to @.tid? Is
t.id unique?
If the test is going to eliminate only a small percentage of the table
then a table scan is the fastest way to get through it. In that case
the most you can hope for is that perhaps clustering on t.id will make
a small difference.
If the test will eliminate a major percentage of the table then a
clustering on t.id would probably help.
Roy Harvey
Beacon Falls, CT
On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:

>I have the following nonindexable query due to the "<>" operater. Column "i
d"
>in the following scenario is a clustered index.
>DECLARE @.tid
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>
>Is there a way to rewrite such a search condition so as to make it an
>indexexable search condition?|||If you have a clustered index on the "id" column, the index will get used in
the search, but it will be an index scan rather than a index seek(which will
be in case of =), for the simple reason that you are not trying to find one
value, you are trying to eleminate a value, so it has to compare it against
every value in the index. So I am assuming you are looking for an index see
k
rather than a scan,
You can try doing this
DECLARE @.tid int
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
I am not sure if you can make this assumption that "id" will always be > 0,
this actually runs an index seek on the table. The plan improves if you have
this inside a stored proc, as the query plan gets cached.
As Roy pointed out this can be a very expensive query without anything else
in your where clause depending on the size of the data. It almost took 31
seconds for me to run this on 11 Million records. If I was you I would look
at changing the query and including some more filtering in the where clause.
HTH
RA
"Roy Harvey" wrote:

> How large is the table? How much of the table is equal to @.tid? Is
> t.id unique?
> If the test is going to eliminate only a small percentage of the table
> then a table scan is the fastest way to get through it. In that case
> the most you can hope for is that perhaps clustering on t.id will make
> a small difference.
> If the test will eliminate a major percentage of the table then a
> clustering on t.id would probably help.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
>
>|||Thanks Rocky.
The "id" column is a nonclustered composite index with another column name
"col_k". The index was created with in this order (id, col_k).
Column "id" is an identity column and column "col_k" is not very unique.
There are approximately 15,000 records in the table.
Since Column "id" is first in the composite index, it seems like it could be
used in the revised query you wrote, but perhaps the optimizer believes a
scan is still faster than using the query (and yes, I have run sp_updatestat
s)
.
I further rewrote the query to give it an extra filter in the WHERE clause:
DECLARE @.tid int
DECLARE @.colk int
SET @.tid = 1000
SET @.colk = 5
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
and t.col_k = @.colk
However, my logical reads have not improved from the original and it is stil
l
performing a Clustered Index Scan (using the clustered index which is a
datetime field), which to me in this case, is in essence a table scan.
Any further ideas or suggestions to have it use the composite index would be
appreciated.
Rocky A wrote:[vbcol=seagreen]
>If you have a clustered index on the "id" column, the index will get used i
n
>the search, but it will be an index scan rather than a index seek(which wil
l
>be in case of =), for the simple reason that you are not trying to find one
>value, you are trying to eleminate a value, so it has to compare it against
>every value in the index. So I am assuming you are looking for an index se
ek
>rather than a scan,
>You can try doing this
>DECLARE @.tid int
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>and t.id>0
>I am not sure if you can make this assumption that "id" will always be > 0,
>this actually runs an index seek on the table. The plan improves if you hav
e
>this inside a stored proc, as the query plan gets cached.
>As Roy pointed out this can be a very expensive query without anything else
>in your where clause depending on the size of the data. It almost took 31
>seconds for me to run this on 11 Million records. If I was you I would look
>at changing the query and including some more filtering in the where clause
.
>HTH
>RA
>
>[quoted text clipped - 22 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||RA,
Is this your real query, or a simplified version?
If id is the only column you are selecting, then one would expect that
SQL-Server would scan the smallest index that contains the id column.
You did not post DDL, so we cannot check this.
If you are selecting other columns than the ones in the nonclustered
index and clustered index, then the nonclustered index will (most
likely) not be used, because that would require bookmark lookups for too
many rows. In that case, a clustered index scan would simply be faster.
Gert-Jan
"cbrichards via droptable.com" wrote:
> Thanks Rocky.
> The "id" column is a nonclustered composite index with another column name
> "col_k". The index was created with in this order (id, col_k).
> Column "id" is an identity column and column "col_k" is not very unique.
> There are approximately 15,000 records in the table.
> Since Column "id" is first in the composite index, it seems like it could
be
> used in the revised query you wrote, but perhaps the optimizer believes a
> scan is still faster than using the query (and yes, I have run sp_updatest
ats)
> .
> I further rewrote the query to give it an extra filter in the WHERE clause
:
> DECLARE @.tid int
> DECLARE @.colk int
> SET @.tid = 1000
> SET @.colk = 5
> SELECT t.id
> FROM table t
> WHERE t.id <> @.tid
> and t.id>0
> and t.col_k = @.colk
> However, my logical reads have not improved from the original and it is st
ill
> performing a Clustered Index Scan (using the clustered index which is a
> datetime field), which to me in this case, is in essence a table scan.
> Any further ideas or suggestions to have it use the composite index would
be
> appreciated.
> Rocky A wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||given the specific example, i would expect the engine to use any index
it can.
however, sending the data across the wire will be the bottleneck, so no
matter what it probably take as long as it takes to send the data.

Help with query rewrite

I have the following nonindexable query due to the "<>" operater. Column "id"
in the following scenario is a clustered index.
DECLARE @.tid
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
Is there a way to rewrite such a search condition so as to make it an
indexexable search condition?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1How large is the table? How much of the table is equal to @.tid? Is
t.id unique?
If the test is going to eliminate only a small percentage of the table
then a table scan is the fastest way to get through it. In that case
the most you can hope for is that perhaps clustering on t.id will make
a small difference.
If the test will eliminate a major percentage of the table then a
clustering on t.id would probably help.
Roy Harvey
Beacon Falls, CT
On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
>I have the following nonindexable query due to the "<>" operater. Column "id"
>in the following scenario is a clustered index.
>DECLARE @.tid
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>
>Is there a way to rewrite such a search condition so as to make it an
>indexexable search condition?|||If you have a clustered index on the "id" column, the index will get used in
the search, but it will be an index scan rather than a index seek(which will
be in case of =), for the simple reason that you are not trying to find one
value, you are trying to eleminate a value, so it has to compare it against
every value in the index. So I am assuming you are looking for an index seek
rather than a scan,
You can try doing this
DECLARE @.tid int
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
I am not sure if you can make this assumption that "id" will always be > 0,
this actually runs an index seek on the table. The plan improves if you have
this inside a stored proc, as the query plan gets cached.
As Roy pointed out this can be a very expensive query without anything else
in your where clause depending on the size of the data. It almost took 31
seconds for me to run this on 11 Million records. If I was you I would look
at changing the query and including some more filtering in the where clause.
HTH
RA
"Roy Harvey" wrote:
> How large is the table? How much of the table is equal to @.tid? Is
> t.id unique?
> If the test is going to eliminate only a small percentage of the table
> then a table scan is the fastest way to get through it. In that case
> the most you can hope for is that perhaps clustering on t.id will make
> a small difference.
> If the test will eliminate a major percentage of the table then a
> clustering on t.id would probably help.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
> >I have the following nonindexable query due to the "<>" operater. Column "id"
> >in the following scenario is a clustered index.
> >
> >DECLARE @.tid
> >SET @.tid = 1000
> >
> >SELECT t.id
> >FROM table t
> >WHERE t.id <> @.tid
> >
> >
> >Is there a way to rewrite such a search condition so as to make it an
> >indexexable search condition?
>|||Thanks Rocky.
The "id" column is a nonclustered composite index with another column name
"col_k". The index was created with in this order (id, col_k).
Column "id" is an identity column and column "col_k" is not very unique.
There are approximately 15,000 records in the table.
Since Column "id" is first in the composite index, it seems like it could be
used in the revised query you wrote, but perhaps the optimizer believes a
scan is still faster than using the query (and yes, I have run sp_updatestats)
.
I further rewrote the query to give it an extra filter in the WHERE clause:
DECLARE @.tid int
DECLARE @.colk int
SET @.tid = 1000
SET @.colk = 5
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
and t.col_k = @.colk
However, my logical reads have not improved from the original and it is still
performing a Clustered Index Scan (using the clustered index which is a
datetime field), which to me in this case, is in essence a table scan.
Any further ideas or suggestions to have it use the composite index would be
appreciated.
Rocky A wrote:
>If you have a clustered index on the "id" column, the index will get used in
>the search, but it will be an index scan rather than a index seek(which will
>be in case of =), for the simple reason that you are not trying to find one
>value, you are trying to eleminate a value, so it has to compare it against
>every value in the index. So I am assuming you are looking for an index seek
>rather than a scan,
>You can try doing this
>DECLARE @.tid int
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>and t.id>0
>I am not sure if you can make this assumption that "id" will always be > 0,
>this actually runs an index seek on the table. The plan improves if you have
>this inside a stored proc, as the query plan gets cached.
>As Roy pointed out this can be a very expensive query without anything else
>in your where clause depending on the size of the data. It almost took 31
>seconds for me to run this on 11 Million records. If I was you I would look
>at changing the query and including some more filtering in the where clause.
>HTH
>RA
>> How large is the table? How much of the table is equal to @.tid? Is
>> t.id unique?
>[quoted text clipped - 22 lines]
>> >Is there a way to rewrite such a search condition so as to make it an
>> >indexexable search condition?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||RA,
Is this your real query, or a simplified version?
If id is the only column you are selecting, then one would expect that
SQL-Server would scan the smallest index that contains the id column.
You did not post DDL, so we cannot check this.
If you are selecting other columns than the ones in the nonclustered
index and clustered index, then the nonclustered index will (most
likely) not be used, because that would require bookmark lookups for too
many rows. In that case, a clustered index scan would simply be faster.
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> Thanks Rocky.
> The "id" column is a nonclustered composite index with another column name
> "col_k". The index was created with in this order (id, col_k).
> Column "id" is an identity column and column "col_k" is not very unique.
> There are approximately 15,000 records in the table.
> Since Column "id" is first in the composite index, it seems like it could be
> used in the revised query you wrote, but perhaps the optimizer believes a
> scan is still faster than using the query (and yes, I have run sp_updatestats)
> .
> I further rewrote the query to give it an extra filter in the WHERE clause:
> DECLARE @.tid int
> DECLARE @.colk int
> SET @.tid = 1000
> SET @.colk = 5
> SELECT t.id
> FROM table t
> WHERE t.id <> @.tid
> and t.id>0
> and t.col_k = @.colk
> However, my logical reads have not improved from the original and it is still
> performing a Clustered Index Scan (using the clustered index which is a
> datetime field), which to me in this case, is in essence a table scan.
> Any further ideas or suggestions to have it use the composite index would be
> appreciated.
> Rocky A wrote:
> >If you have a clustered index on the "id" column, the index will get used in
> >the search, but it will be an index scan rather than a index seek(which will
> >be in case of =), for the simple reason that you are not trying to find one
> >value, you are trying to eleminate a value, so it has to compare it against
> >every value in the index. So I am assuming you are looking for an index seek
> >rather than a scan,
> >
> >You can try doing this
> >
> >DECLARE @.tid int
> >SET @.tid = 1000
> >
> >SELECT t.id
> >FROM table t
> >WHERE t.id <> @.tid
> >and t.id>0
> >
> >I am not sure if you can make this assumption that "id" will always be > 0,
> >this actually runs an index seek on the table. The plan improves if you have
> >this inside a stored proc, as the query plan gets cached.
> >
> >As Roy pointed out this can be a very expensive query without anything else
> >in your where clause depending on the size of the data. It almost took 31
> >seconds for me to run this on 11 Million records. If I was you I would look
> >at changing the query and including some more filtering in the where clause.
> >
> >HTH
> >RA
> >
> >> How large is the table? How much of the table is equal to @.tid? Is
> >> t.id unique?
> >[quoted text clipped - 22 lines]
> >> >Is there a way to rewrite such a search condition so as to make it an
> >> >indexexable search condition?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||given the specific example, i would expect the engine to use any index
it can.
however, sending the data across the wire will be the bottleneck, so no
matter what it probably take as long as it takes to send the data.

Help with query nulls and addition

Hi I have a query, what I would like to do is create a column that takes the results in two coulms and add them together:

Col A Col B Col C

Row1 1 1 2

Row2 2 3 5

Here is the query

declare

@.ttable( player_namevarchar(100), BuyInint, TopUpint, ReBuyint, Winningsint, Eventsint, Testint)

INSERT

INTO @.t(player_name, TopUp)SELECT Player_name,SUM([Top-ups])AS TOPUPS

FROM

(SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,

Events

.Top_up* Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 2))AS Topups

GROUP

BY player_name

INSERT

INTO @.t(player_name, ReBuy)

SELECT

Player_name,SUM([Re-buys])AS REBUYS

FROM

(SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value* Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 3))AS REBUYS

GROUP

BY Player_name

Insert

into @.t(player_name, BuyIn)

SELECT

dbo.Players.Player_name,SUM(dbo.Events.Buy_in)AS BuyIn

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

ORDER

BYSUM(dbo.Events.Buy_in)DESC

Insert

into @.t(player_name, Winnings)

SELECT

dbo.Players.Player_name,SUM(dbo.Event_data.Transaction_value)AS Winnings

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

insert

into @.t(player_name, Events)

SELECT

dbo.Players.Player_name,COUNT(dbo.Event_data.Place)AS Expr1

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name

HAVING

(NOT(COUNT(dbo.Event_data.Place)ISNULL))

insert

into @.t(player_name, test)

select

player_name,((TopUp)+(Rebuy))as Test

from

@.t

SELECT

player_name,min(BuyIn)as BuyIn,min(TopUp)as TopUps,min(ReBuy)as ReBuy,min(Winnings)as Winnings,min(Events)as Events,min(test)as test

FROM

@.t

GROUP

BY player_name

ORDER

BY BuyInDESC

--ORDER BY TOPUPS DESC

END

THis is where I attempt to add the coloms but I get a null result

insertinto @.t(player_name, test)

select

player_name,((TopUp)+(Rebuy))as Test

from

@.t

any help would be great.

You could us the ISNULL fucntion:

insertinto @.t(player_name, test)

select

player_name,(ISNULL(TopUp, 0)+ ISNULL(Rebuy, 0))as Test

from

@.t|||

Hi the is null removes the nulls but I am still unable to add the coloums together, instead of null I get 0 in the test col. I am able to add topup +topup or Buyin + Buyin and I get the result but when I try to add the different cols its null or 0 if I use your suggestion.

any idea?

|||How about using COALESCE instead of ISNULL?|||

Hi. I hav two ideas about that.

1. Declare the column 'test' as a calculated column:

declare @.table TABLE
(player_name varchar(100), BuyIn int, TopUp int, ReBuy int, Winnings int, Events int, Test AS (TopUp + ReBuy))

2. If you, for example SELECT the table for one player use this: "SELECT * FROM table WHERE player_name = 'player1'" and get somethiong like this:

player_name TopUp ReBuy

player1 5 NULL

player1 NULL 3

So, if you sum each row its equal to TopUp + NULL and ReBUy + NULL.

May be you need in the final select this:

SELECT (TopUP + ReBuy) FROM

(SELECT SUM(TopUP) as TopUp, SUM(ReBuy) as ReBuy FROM @.t) As t

or the other option could be to insert the first time, an after that update the rows for the player.

|||

This is most excellent, thanking you exactly what I was looking for: here is my working query with your suggestion. Thanks again a great help!!!

declare

@.ttable( player_namevarchar(100), BuyInint, TopUpint, ReBuyint, Winningsint, Eventsint)

INSERT

INTO @.t(player_name, TopUp)SELECT Player_name,SUM([Top-ups])AS TOPUPS

FROM

(SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,

Events

.Top_up* Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 2))AS Topups

GROUP

BY player_name

INSERT

INTO @.t(player_name, ReBuy)

SELECT

Player_name,SUM([Re-buys])AS REBUYS

FROM

(SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value* Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 3))AS REBUYS

GROUP

BY Player_name

Insert

into @.t(player_name, BuyIn)

SELECT

dbo.Players.Player_name,SUM(dbo.Events.Buy_in)AS BuyIn

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

ORDER

BYSUM(dbo.Events.Buy_in)DESC

Insert

into @.t(player_name, Winnings)

SELECT

dbo.Players.Player_name,SUM(dbo.Event_data.Transaction_value)AS Winnings

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

insert

into @.t(player_name, Events)

SELECT

dbo.Players.Player_name,COUNT(dbo.Event_data.Place)AS Expr1

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name

HAVING

(NOT(COUNT(dbo.Event_data.Place)ISNULL))

--insert into @.t (player_name, test)

--select

--player_name, (ISNULL(TopUp, 0) + ISNULL(Rebuy, 0)) as Test

--from @.t

Select

*,(TopUps+ ReBuy+ BuyIn)as Cost,(winnings-(TopUps+ ReBuy+ BuyIn))as Profit

FROM

(SELECT player_name,(ISNULL(min(BuyIn),0))as BuyIn,(ISNULL(min(TopUp),0))as TopUps,(ISNULL(min(ReBuy),0))as ReBuy,min(Winnings)as Winnings,min(Events)as Events

FROM

@.tGROUPBY player_name)as t

GROUP

BY player_name, buyin,topUps, Rebuy, winnings, events

Order

by Profitdesc