Problem With Where Clause When Multiple DESC Indexes

May 12, 2007

I have come across a problem executing a select with a multi-part where clause that only shows up if there are multiple indexes on the table. The situation using a simplified table is shown below

create table tblTest( utcTimestamp datetime NOT NULL, testType int NOT NULL)go

insert into tblTest (utcTimestamp, testType) VALUES('6/1/2003 0:0:0', 100)go

Now, without adding any indexes to the table, I can execute the following select and it works fine, returning the single row in 2003:

select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0' and testType = 100go

Furthermore, if I introduce a single descending index on just the utcTimestamp:

CREATE INDEX IX_tblTest_Timestamp ON tblTest (utcTimestamp DESC)go

the search still works.

HOWEVER, if I now introduce another index:

CREATE INDEX IX_tblTest_EntryType_Timestamp ON tblTest ( testType, utcTimestamp DESC)go

the search does **not** return the row.
However, if I change the where clause to remove the test of testType:

select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0'go

it works.

Also, strangely, if I populate the table with a number of records with different dates and execute the following search:

select * from tblTest where utcTimestamp > '1/1/2004 0:0:0' and testType = 100go

I get records from **earlier** than 1/1/2004 (i.e. like the sense of the compare is wrong)

Finally, as I was writing this report, I discovered that all of these problems go away if the DESC is removed from the indexes - so that's my workaround, but it still looks like a bug.

Thanks
Lionel

View 1 Replies


ADVERTISEMENT

Multiple Indexes

Apr 15, 2008

I have a stored procedure that we use to search our inventory.

The sproc is using dynamic sql and executing a parameterized.

It generates the sql string based on all the parameters passed in.

I'm trying to figure out how I should set up the indexes on the table(s) being queried.

There are 12 different fields possible in the where clause and 7 different order by's.

I heard that SQL 2005 might hash together multiple indexes so should I create an index for each of these fields?

View 7 Replies View Related

Indexes In Multiple Databases

Nov 1, 2000

Okay this is a test...actually I am still learning SQL and need some help. Does anyone have any information on being able to move indexes from one database to another. My scenario is I have 3 databases, Development, QA and Production. I want to move/copy indexes I created in Development to the QA database. I have many indexes so I do not want to have to recreated them if I can avoid it. Any suggestions?

Thanks!

View 1 Replies View Related

Multiple Indexes On The Same Column

Jan 21, 2008

Hi All

I have a table with indexes as follows









license_detail_prim
clustered, unique located on PRIMARY
component_id, license_id

LICENSE_DTL_IDX_LICENSE_ID
nonclustered located on PRIMARY
license_id

PK__license_dtl__6EB64F9B
nonclustered, unique, primary key located on PRIMARY
component_id, license_id

XIF39license_dtl
nonclustered located on PRIMARY
license_id


from the above, it is evident that multiple indexes have been created on the same columns

What is the side effects of havins such table design.

Thanks

View 5 Replies View Related

Multiple Where Clause?

Jun 11, 2007

where exigo_data_sync.orderdetail.itemcode in (B1001, B1001B, B1007, B1007B, B1008, B1008B, B1000, B1000B, B1006, B1006B, B1009, B1009B)

I keep getting a ADO error stating invalid column names...these are not column names they are the data that i want to use in the where clause. What am I doing wrong?

View 2 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 12, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):



1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.



Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???



2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???





View 1 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 16, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):



1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.



Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???



2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???


thanks,

Ran Kizi

View 3 Replies View Related

Integration Services :: SSIS Cache Transform Lookup With Multiple Indexes

Jan 16, 2013

I work in the healthcare area, and am handling the survey data ETL's.  There are around 8 different survey areas and based on information received from them for the visit they reference, I want to pull in more info from our invoicing database.  My idea is this:

1.)  Pull in the flat file to an ODBC staging table
2.)  Cache all invoice records that fall between the MIN(Date of Service) and MAX(Date of Service) from the staging table.
3.)  First lookup the information needed on patientID, providerID, date of service, and billing location.
4.)  For the surveys that didn't match on those 4 columns, try looking up based on patientID, date of service, and billing location (since I could be 99% sure this would still return the record I need).
5.)  For the remaining surveys, lookup based just on patientID and date of service.  These records will be flagged for manual review because clearly, if a patient has multiple appointments in the same day, this will be prone to error.

However, in trying to use only 3 of the columns in the lookup, I get the error saying basically that I need to utilize all 4. Is there a way around this, or is there an entirely different way I should be approaching this?  The reason I thought cache transform was the answer is because I will need to run a different package for each lookup, as the data and logic between each survey will vary, but the invoice data "pool" will stay the same regardless. 

View 5 Replies View Related

Sql WHERE Clause With Multiple Parameters

Jun 28, 2006

hello. I have a database that a client developed that I need to pull data from. it consists of articles that fall into a range of 3 main categories. each article will have up to 7 different subcategories they fall into. I need to be able to sort by main category as well as by subcategory. But when I create the SQL query it gets really messy. I tried using WHERE  Cat1= comm OR leg OR and so on, but there are seven categories so this gets very cumbersome and doesn't quite work. Is there a way to create an array or a subquery for this? I am a total newbie, so any help is much appreciated!

View 2 Replies View Related

Multiple Join Clause

Feb 12, 2004

I have a table "Users" like this:

GroupId
CompanyId
UserId

I need to query the users getting the company's and group's names, but I only know how to join one table. Example:

Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name
From Users JOIN Groups ON Users.GroupId = Groups.Id

Hon can I add the companies table in the Join ?

Thanks,
Moshe

View 2 Replies View Related

Multiple Criteria In WHERE Clause (was T-SQL)

Oct 5, 2005

How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.

So is there any other way to handle multiple criteria in T-SQL ?

View 12 Replies View Related

Where Clause With Multiple Cases

Nov 28, 2012

I have a table with a field that contains an integer which represents the state of a record. This field "intType" may contain values 0-4.

A parameter in my stored procedure "@intUserType" may contain values 0-3

If @intUserType = 0, I need to select the records where intType = 0 or 3 but if @intUserType = 3, I need to return all records where intType > 1, all other values of @intUserType should return no records

The query I am working with seems a bit forced and I feel like it could be simplified, but I can't seem to wrap my head around it.

This is what I am working with:

Code:
SELECT * FROM tblEmployees
WHERE (intType = (CASE WHEN @intUserType = 0 THEN 0 ELSE NULL END)
OR intType = (CASE WHEN @intUserType = 0 THEN 3 ELSE NULL END)
OR intType > (CASE WHEN @intUserType = 3 THEN 1 ELSE NULL END))

Maybe it is as good as it needs to be ... I don't know .. I've only been using SQL regulary for a couple of months and I have not had the time to really study it in depth.

View 4 Replies View Related

WHERE Clause With Multiple ANDs

Apr 2, 2015

I have a table (tblAttributes) that looks like this:

ID|ReferenceNumber|AttrID|AttrVal
1|1|90|7
1|1|91|8
1|1|92|9
2|1|90|10
2|1|91|11
2|1|92|12

I need to return all the distinct IDs where the combination of AttrID and AttrVal matches multiple criteria for that specific ID.

I have the following:

SELECT DISTINCT(ID)
FROM
((SELECT a.ID
FROM tblAttributes AS a
WHERE a.AttrID = 90 AND a.AttrVal = 7) AS x
INNER JOIN
(SELECT a.ID
FROM tblAttributes AS a
WHERE a.AttrID = 91 AND a.AttrVal = 8) AS y
ON x.ID = y.ID)

View 5 Replies View Related

Using Multiple Values In IN CLAUSE

Jul 26, 2006

how can i use multiple values in IN CLAUSE in a SQL query, that too when the number of values are changing at runtime.

complete SQL code required...............

I have following picture in mind but the values(in IN clause) are changing at Runtime

DECLARE @groups TABLE (group_id int)

SELECT * FROM abc WHERE abc_id IN (SELECT group_id FROM @groups)

View 4 Replies View Related

Multiple Where Clause On One Report

Aug 14, 2007



Hello People, Please help. I have a basic report with a parameter in the 'Where" clause called (@Stat) from the statement below:
" WHERE contractinfo.termdate >= GETDATE()
AND provider.status= 'Active' AND provider.credentialstatus = (@Stat)"

This variable has one of two values: 'A' or 'B' that the user selects, how do I set it up so that if user selects say 'A' then the Where clause would go to one set of constraints ie
"WHERE contract.description NOT LIKE 'NON%' "

But if the user selects 'B' then the Where clause would go to a different set of constraint ie
"WHERE contract.description LIKE 'NON%' "
Thanks


View 4 Replies View Related

ORDER BY Clause With Multiple Tables

Aug 25, 2005

Hi AllI am having a problem with an ORDER BY clause when selecting information from multiple tables. EgSELECT i.InvoiceId, pd.PayDescription, u.UserNameFROM   Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId                       LEFT OUTER JOIN tblUsers ON i.UserId = u.UserIdORDER BY pd.PayDescriptionthis is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?I am writing this for MSSQL Server 2000ThanksBraiden

View 6 Replies View Related

Like Clause - Preventing Multiple Cases

Sep 9, 2013

I have a like clause like this:

WHERE COLUMN LIKE CAT1%
or
COLUMN LIKE CAT2%
or
COLUMN LIKE CAT3%
ETC..

I want to know if it is possible just have one like clause from 1-9:

CAT1, CAT3, ...., CAT9

View 3 Replies View Related

Stuck With WHERE Clause With Multiple Elements

Jul 20, 2005

HiI'm a bit stuck with a SELECT query. This is a simplified version ofwhat I need. I've had a look in a few books and online but I'mdefinitely missing something. I'm trying to avoid looping and cursors.I'll be running this in a stored procedure on SQL 7.I have a separate query which returns a series of numbers, A, say 101103 107 109 113.I have a table (tableB) with a field myFieldB where I have anotherseries of numbers, B. I want return each row in tableB wherei - ALL values in A existii- ANY values in A existFor ii, I can use WHERE myFieldB IN AHow about for i?Is there a good guide on the web or a book on WHERE clauses and/ormore complex SQL?Thanks in advance!Sam

View 1 Replies View Related

To Use Multiple Value Paramater In The IN List Of A Where Clause

Jan 8, 2008

I have a parent and child package. i pass a parent package variable called @abc with a value of (1,2,3,4,5,6) to the child package. here in the oledb source i have a select statement like,
select *
from A
where id in (@abc)

It gives me an error. any way to make this work.

View 14 Replies View Related

Multiple Conditions In An UPDATE Clause

Dec 12, 2007

I have a table (GLTRANS) with thousands of lines.
1 column in the table (ACCNO) has 300 different values which all need to change to a new value.

ie. 11100 all change to 8100
11200 all change to 8200

I know how to do a simple UPDATE
UPDATE GLTRANS
SET ACCNO = '8100'
WHERE ACCNO = '11100'

But how can i combine into 1 script rather than having to continually change this script 300 times??


Thanks
Wilbur

View 6 Replies View Related

Multiple Values For Single Column In Where Clause

Jun 25, 2004

how does one specify multiple values for a single column in a where clause?

example:

SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')


if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:

View 2 Replies View Related

SQL Server 2012 :: Where Clause On Multiple Columns?

May 16, 2014

Right now I have to do something like this and it is time consuming every time I have to query a specific table...

SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here

Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).

View 4 Replies View Related

Sql Syntax For Multiple Likes Phrases In Where Clause

Nov 16, 2007



You know how you can go:



Code Block
where control_id in (11111,22222,33333,44444)




or




Code Block
where TextName in ('11111','22222','33333','44444')




and you can do this:




Code Block
where TextName like '11%' or TextName like '22%'





well how do you do this? Or can you... or can we right a function to do it... or are we just hosed writing like after like.

where TextName like in ('11%','22%',... and so on)? is anything like that possible?

or better yet
where TextName like in ( select substring(columnName, 1, 2) + '%' from whatever )

can you imagine the dynamics if that syntax actually worked?

Can a function be written to mimic this functionality? so I can do something like this:
where TextName = function('11%,22%,33%')

View 13 Replies View Related

Nothing Like 'DESC'

Jan 22, 2004

hi

Like Oracle SQL, does SQL server has a 'desc' command which describes the tables of a database?


It would be very helpful instead of using Enterprise Manager

View 3 Replies View Related

How To Pass Multiple Values To An IN Clause Through Stored Procedure

Jun 11, 2004

I created a stored procedure like the following in the hope that I can pass mulitple company_id to the select statement:


CREATE PROC sp_test @in_company_code nvarchar(1024)
AS

select company_code, name, description
from member_company
where company_code in (@in_company_code)


However, I tried the following :

exec sp_test 'abc', 'rrd', 'bbc'

Procedure or function sp_test has too many arguments specified.

and SQLServer doesn't like it.

Did I specify this stored procedure correct?
If so, how can I can pass multiple values to the stored procedure then to the sql statement?
If not, is it possible to specify a stored procedure like this?

Thanks!

View 2 Replies View Related

Update Statement - Correlation Name Is Specified Multiple Times In FROM Clause

Jun 11, 2014

I have this update statement that I need to have joined by MSA and spec.

I keep getting an error.
Msg 1011, Level 16, State 1, Line 3
The correlation name 't1' is specified multiple times in a FROM clause.

Here is my statement below. How can I change this?

UPDATE MSA
SET [Count on Billed Charges] = (Select Count(distinct[PCS Number])
From MonthEnds.dbo.vw_All_Products t1
Inner Join MonthEnds.dbo.vw_All_Products t1 on t1.[MSA Group] = t2.[MSA Group] and t1.[Spec 1] = t2.[Spec 1])

View 3 Replies View Related

Advanced Data Shaping - Multiple Relate Clause

Jan 18, 2006

Hello,I'm using a shape query, but instead of using a simple clause "RELATEfield1 to field2" (relates the parent to the child), i wan't to use 2relates. somthing like "RELATE field1 to field2 AND field3 to field4".I want to receive in the children RS only the records who apply bothconditions.How do i do that ?Thanks !

View 2 Replies View Related

Handling Multiple Columns Returned By A Subquery With An IN Clause

Mar 25, 2008

Hi,

I would like to know how i can handle multiple columns returned by a subquery via IN clause in case of sql server 2005. I can do that in oracle by using the following statement:

DELETE FROM TEST1
WHERE (ID, ID1) NOT IN (SELECT ID,ID1 FROM TEST2);

Thanks and Regards
Salil

View 9 Replies View Related

How To Get Column Desc..

Oct 15, 2001

How and where to get table and column descriptions of all system
table and system objects.....etc.,

View 3 Replies View Related

How Do I Get DESC Order?

Sep 21, 2007



Hey guys, I have a view with dates (TheDate) meant to be arranged in descending order. When I 'Execute SQL' while in the view, the DESC order works just fine and shows up with the latest date first going down. However, once I 'OPEN VIEW' the order keeps defaulting to ASCending order.

How do I keep it in DESC order for viewing? Here's the statement:

SELECT TOP (100) PERCENT TheDate
FROM dbo.MyDates
ORDER BY TheDate DESC

View 4 Replies View Related

How Do You Do ASC And DESC With Dynamic OrderBy

Mar 21, 2006

In the followinf portion of my SQL Statement, I cannot figure out how to specify ASC or DESC for the OrderBy... If I Put OrderDate ASC after the THEN, I get an error and if I put @SortDir after the END I get an error... Can Anyone help with this?
WHERE OrderID IN(SELECT OrderID FROM #rsltTable WHERE ID >= @l_FirstRecord AND ID <= @l_LastRecord)ORDER BYCASE @OrderByWHEN 'OrderDate ASC' THEN OrderDateWHEN 'OrderDate DESC' THEN OrderDateEND
Thank You,Jason

View 9 Replies View Related

Order By Asc Then Desc Links?

Aug 5, 2007

hi, i have headers at the top of my list and would like for people to click the pubs link and it orders the pubs alphabetically descending and when they click again to ascend also to do this with towns and addresses and postcodes?!

can someone please help?

J x

View 2 Replies View Related

Creating Index In DESC Order

Apr 15, 2003

Does anyone have a general rule or guide on when to use this SQL 2000 option when creating indexes? I was thinking generally on nonclustered indexes where the column would be unique and incremental and usually filtered on by range and often used in the order by clause. Such as columns of datetime or integers datatypes. Thanks.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved