I'm a bit stuck with a SELECT query. This is a simplified version of
what I need. I've had a look in a few books and online but I'm
definitely 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 101
103 107 109 113.
I have a table (tableB) with a field myFieldB where I have another
series of numbers, B. I want return each row in tableB where
i - ALL values in A exist
ii- ANY values in A exist
For ii, I can use WHERE myFieldB IN A
How about for i?
Is there a good guide on the web or a book on WHERE clauses and/or
more complex SQL?
We are using a table that may give 1 to and unknown number of data elements (ie. years) . How can we break this to show only three years in each row. Since we don't know the number years we really won't know the number of rows needed. Years are stored in their own table by line.
car make year1 year2 year3 A volare 1995 1996 1997 a volare 1997 1998 1999 b toyat 1965 1966 1968
We can pivot out the first X# but we don't know how many lines so we don't know how many rows we will be creating.
I am trying to query multiple tables that have the same column. They are a supposed to be a unique ID to the product, but not to the database. I have a primary key already set to each othe tables. The tables do not share anything between them, but i would like to search for these IDs in other tables before my insert command is executed. I am writing my code in VB. example of what i am trying to accomplish. Table1 = TVs ID/SKU_ID/Manufacturer 1 87654 Samsung
I want to search for SKU_ID in multiple tables( I think a total of 9 tables). I want to search between tables to catch for the same SKU_IDs. and if it finds it, raise error. I will be able to figure the vb.net portion. I just dont know how to go about the sql query portion. Thanks in advance and appreciate any help. Thanks!
I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?> <ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
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?
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!
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 ?
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.
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)
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
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
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)
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).
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.
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?
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 !
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);
I am using SSRS 2005 SP2 and I need to allow my users to select the products they want in the report. I created a data set to get the list of the products and then used it in a multivalue parameter to generate a drop down list. My problem is that the list is too long (more than 2000 elements) and thus, it takes 10 sec to collapse the drop down list after tickinging the products.
Does anyone have an miraculous solution? Thanks in advance for your answers.
This one I hope is easy, but I've been working late and it seems to be aluding me. I have a field called time_in which is a char(6) field type. This field holds time elements such at 10:57
My query below is substracting one hour from Time_In and returning it as Hold_Time. I only want to return the time element without the date portion or the milliseconds, etc.
Code Block select substring(cu.time_in, 1,5) as time_in, DATEADD(hour, -1, substring(cu.time_in, 1,5)) as hold_time from reports.careunits_hold_hours cu
I am working on a project developing a fairly large number of reports with a team of developers. Many of these reports have common elements and code, such as common headers with user-selectable colors. Additionally, many of the common parts of the reports are at mockup stage currently, and many features will have to be added to the reports as time goes on.
We're attempting to create a generalized framework that will minimize the duplication of effort as we develop these reports, and as we go back and modify or fix them later.
What is the best way to approach this?
My first attempt was to create a report template and base all the reports off of the same template. That was fine for the first pass, but as we need to make changes later, they will not be propagated to the already existing reports.
My second attempt was to have each componant of that template reference a subreport, so that changes to the actual report template will be minimized as we go forward. This works great for minimizeing work, but it appears that you lose many features with the use of subreports, and there seems to be a pretty serious performance impact as well. I have posted about one such issue here: Pagination
If anyone has pointers about how to go about this, and where I should start, they would be greatly apreciated!
Hi, I use the FOR XML AUTO to retrive native XML from a database with: SELECT [xml] FROM myxml WHERE id = 81 FOR XML AUTO, elements, root('ROOT')" However it returns the database name and table name as parent elements. How can I return just my raw XML data without additional elements: XML is Stored: <ROOT> <CHAPTER> <TITLE>This is a test</TITLE> </CHAPTER> </ROOT> Returns: <databasename> <tablename> <ROOT> <CHAPTER> <TITLE>This is a test</TITLE> </CHAPTER> </ROOT> </tablename> </databasename>