Selecting The Same Column Twice In The Same Where Clause
Hi :
From a crystal report i get a list of employee firstnames as a string
into my store procedure. Why is it comming this way ? hmmmmmm it's a
question for me too.
ex: "e1,e2,e3"
here are my tables
tblProjects
ProjectId
1
2
3
tblEmployee
employeeId FirstName
1 e1
2 e2
3 e3
tblProjectsToEmployee
ProjectId employeeId
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
4 1
4 3
i need to find out the project ids all 3 of these employees worked on.
so the out put i need is
projectId
1
3
How can i get it ????????????
now i can use replace command to format it to a OR clause or AND
clause
SET @string= 'employeeId =' + '''' + REPLACE('e1,e2,e3',',',''' OR
employeeId = ''') + ''''
some thing like this.
OR clause will give me all 4 projects.
in('e1','e2','e3') will give me all 4 projects.
of cause AND command will not give me any.
other method i tried was adding the employee table 3 times into the
same SQL string and doing some thing like
WHERE (empTable1.Firstname ='e1' AND (empTable1.Firstname
in('e2','e3'))
AND (empTable2.Firstname ='e2' AND (empTable1.Firstname in('e1','e3'))
AND ...
and goes alone. this gives me some what i needed. but it's a very
messy way of doing it, because i get a comma seperated string
parameter i have to construct the sql string on the fly.
any help or direction on this matter would greatly appreciated.
thanks
eric
View Complete Forum Thread with Replies
Related Forum Messages:
Need Help Selecting Data With Date -based Where Clause
OK. I have this query, works on another box fine. SELECT * FROM bookkeep RIGHT OUTER JOIN acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S') on my sql box, if i run it, i get no data. i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works OR if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works. my problem is the basic query is hard coded and i really can't change it. is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?
View Replies !
Selecting Rows From SQL Server Dbs With A Where Clause Using The System Date.
Hello i currently have a website that has an SQL server 2005 dbs that stores appointments. I would like to do a select statement in my sqldatasource that selects all the records that have an 'appointmentDate' more than 2 weeks after the current date (ie the system date). I am stuck on the SQL statement i need to produce to achieve this. I was thinking along the lines of SELECT * FROM appointments WHERE appointmentDate > System.Date + 14; However this is clearly not the right SQL statement. Any help would be appreciated. Many thanks, James.
View Replies !
Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View Replies !
Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples: 1) Rows ordered using textual id rather than numeric id Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id Result set is ordered as: 1, 11, 2 I expect: 1,2,11 if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. 2) SQL server reject query below with next message Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique. Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. It reproducible on Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) and Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) In both cases database collation is SQL_Latin1_General_CP1251_CS_AS If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again. Could someone clarify - is it bug or expected behaviour?
View Replies !
Selecting Where Cloumn = Column OR Column = Value
Hi I have a gridview with a sql data source and a few drop down lists where i choose values to sorth what i would like to retrive from the table. The problem im facing is that when i pass values to the database, i can sort out and retrive my items when both my listboxes have selected values. But when i want to select everything from the table where the column = clumn or cloumn = value i dont get anything back. I have 2 dropdown lists. One for category and one for location. These are populated drom the database by selecting the column and retriving destinct values. I have a function that fires when one of the dropdown lists are changed witch changes the sql datasource select value so it retrives items sorted by the selected categories and locations. Here is the function: protected void DropDownList_Change(object sender, EventArgs e) { string Category = DropDownListKategori.SelectedValue.ToString(); string Location = DropDownListEtabl.SelectedValue.ToString(); SqlDataSource1.SelectCommand = "SELECT ARTNR, ARTTYP, AKTIVITET, DATUM, KUND, PLATS, KOMMENTAR FROM ARTIKEL WHERE ARTTYP = @ARTTYP AND PLATS = @PLATS"; SqlDataSource1.SelectParameters.Clear(); SqlDataSource1.SelectParameters.Add(@"ARTTYP", Category); SqlDataSource1.SelectParameters.Add(@"PLATS", Location); SqlDataSource1.DataBind(); } The dropdown lists have an option to not retrive values by category or location. I have set that value to PLATS and ARTTYP thinking the query would would retrive everything if these were selected SELECT ARTNR, ARTTYP, AKTIVITET, DATUM, KUND, PLATS, KOMMENTAR FROM ARTIKEL WHERE ARTTYP = ARTTYP AND PLATS = PLATS however if both these drop down lists are set to not filter, my query gets nothing in return. And if one of the lists has this selected and ther other one has a value, say a location or something nothing is retrived either. However if both columns have something selected i do get values returned. My speculation is that the query beeing run when this happens is that it is trying to find columns having the value ARTTYP or PLATS (category or location), as a string and actually seeing it as Im trying to retrive values where column = column. Any suggestions on how i can make my query run as intended or is there another problem Im not seeing.
View Replies !
Selecting Only Rows That Same The Value In One Column
Mike writes "Hi, I am a beginner with TSQL and I hope this is not a silly question :-) Lets say I have a table with 2 columns, 1 a primary key identity field with increment 1 and the other a char. EG: ID ANIMAL --------- 1 CAT 2 DOG 3 PIG 4 RAT 5 PIG 6 DOG 7 DOG . . And so on with many entries How do I return a selection of rows that have the contents of the ANIMAL field matching 1 or more times EG: From above table I want to return rows 2,6 & 7 and 3 & 5 ONLY and not 1(CAT) and 4(RAT) because they only occur once. In my real life situation I have unknown numeric data in field 2 but the principal is the same. How do I do this? Thanks in Advance Mike"
View Replies !
Selecting Single Sorted Row From Many Column
HiBeen at this for 2 days now.Each business has several packages which they can sort usingsort_order.I'm trying to get one package for each business(that I can do), howeverI want it to be the one with the lowest sort_order valueAs you can see below the first record has sort_order=5 when it shouldbe 1.Most of the sort_order columns will be zero by defaultAny help so i can get on with my life!CheersGary------------Current select-------------------SELECT *FROM dbo.testAccommodation_Packages T1WHERE (NOT EXISTS(SELECT *FROM testAccommodation_PackagesWHERE business_id = T1.business_id AND Package_ID < T1.Package_ID))--------------results:-----------------------Package_IDbusiness_iditem_namesort_order123rd Night FREE ...5113Donegal Town ... 0204Executive ...0--------------To recreate----------------------CREATE TABLE [testAccommodation_Packages] ([Package_ID] [int] IDENTITY (1, 1) NOT NULL ,[business_id] [int] NULL ,[Item_Name] [nvarchar] (300) NOT NULL ,[sort_order] [int] NULL CONSTRAINT[DF_Accommodation_Packages_sort_order] DEFAULT (0),)-------------------------------------------------INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('1','2','3rd Night FREE when you stay 2 nights MIDWEEK (129 EuroPPS)','5')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('2','2','Selected Donegal Town Hotel Weekend Sale - 2 B&B and 1Dinner Only € 129 PPS','4')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('3','2','2 Night Specials -Jan, Feb & Mar 2 B&B and 1 Dinner 149Euro PPS','3')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('4','2','Easter Hotel Breaks in Donegal Town - 2 B&B + 1 D€169pps','2')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('5','2','2005 Bluestack Hillwalking, 2 nights B&B, 1 Dinner, 5course Lunch 159 Euros PPS (~109 Stg)','1')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('6','2','April Pamper Package - 2 Night Special ONLY€195pps','10')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('7','2','Discount Hotel Prices for 8th & 9th April Only € 119PPS','7')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('8','2','Golden Year Breaks in Donegal - 4B&B + 2 Dinner€229pps','8')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('9','2','Hotel Summer Breaks Sale in Donegal - 2B&B + 1 Dinner€169pps','9')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('10','2','STAY SUNDAY NIGHTS FOR €25PPS','6')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('11','3','Donegal Town Midweek Special 99 Euro PPS 3 Nights B&B','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('12','3','Bridge Weekend 2 nights B&B 79 Euro PPS (approx 55Stg) Double Room','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('13','3','Donegal Spring Weekend Specials 2 B&B 1 Dinner109.00euros pps','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('14','3','Valentines Weekend 2 nights B&B and 1 four coursegourmet dinner 99Euro PPS','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('19','3','Golden Years Break.40% OFF 4 nights B&B€129.00p.p.s.','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('20','4','Executive Celebration Offer 1 night B&B + Dinner €139 PPS','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('21','4','Watercolour Painting Break 3 B&B Full Board andTuition € 335 PPS','0')
View Replies !
Selecting A Column From Multiple Tables
Hello, Is it possible to get a list of rows from Multiple tables which have the same Column Name. I have 3 tables which comtain similar info and I want to get a list of Names the structure is ID;Name;Address;Phone No. I was thinking something along the lines of SELECT Name FROM TABLE1,TABLE2, TABLE3 But this does not work. Is there a nice way of doing this with SQL or should I do code outside the SQL DB
View Replies !
Selecting Multiple Column From Sub Query.
Hi, I am having two tables Products and Transaction In products I have ProductID and Description. (10 Records) In Transaction I have ProductID, Lot, Quantity and ListID . ( 4 Million Records) When I use the inner join between these tables as below query its taking lot of time to give output. select ProductID, Desc, Lot, qty from Products inner join Transactions on Products.ProductID = Transactions.ProductID where ListID = '9090909' otherwise, if I use the below query its takes very few milli seconds to give output but I am not able to get the description from the product table. select * from Transactions TR where TR.ListID= '9090909' and TR.ProductID in (select NDC from Products where Products.ProductID = TR.ProductID) Any from can help me to get the description too at very few times. Regards,
View Replies !
Selecting The Column Description In 2005
I am migrating between SQL Server 2000 and SQL Server 2005 but hit a snag when attempting to write a query to display the column's description. I used this code with SQL Server 2000 to get the "Description" data. select o.name 'table' , c.name 'column' , p.value as 'description' , t.name 'datatype' , c.isnullable 'nullable?' , c.length, m.text 'default_text' from sysobjects o join syscolumns c on o.id = c.id join sysproperties p on o.id = p.id and p.smallid = c.colid join systypes t on c.xtype = t.xtype left outer join syscomments m on m.id = c.cdefault order by 'table' , 'column' How can I reproduce this with SQL Server 2005? I tried using the following which gives me a lot of the same data but not "Description": SELECT * FROM INFORMATION_SCHEMA.COLUMNS Any help here would be greatly appreciated.
View Replies !
Inserting Values Into A Column By Selecting Value From Different Table
Hi, I have a question regarding how to insert one column values into a table by selecting from different table..Here is the syntax.. ------------ insert into propertytable values (select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE', 13926, 0, 4, 1, 451, 1, 8, 1) the first column in the propertytable will be... select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE' How do I do that..Help PLZ..
View Replies !
SQL Question About Selecting Values Dependent On Another Column
I have a table with one row for each test a user has taken, with columns for userid, score, and test date. I have a query that gets the highest score and the date of the latest test for each user. Easy. But how can I also get the score achieved on the latest test for each agent? Thanks, John (By the way, I've been looking for a good SQL mailing list to ask this question and have been unsuccessful. If there's a better forum than this for this type of question, please let me know).
View Replies !
Selecting Relative Record Number As A Column Value
Hello I am in the process of learning Visual Basic, unfortunately I am learning by the seat of my pants on a very hot project with no prior training. I have an SQL that I need to reproduce in Visual Basic and I am having an issue. The script seems to work well as long as I do not select the Relative Recod Number of a file as a column value. The problem is however I need that value for a match to another file. Records in File A have a match in file B but the next record down is the record I need. So what I am doing is matching File A to File B on a common field which returns the Relative Record Number of the record in File B. I am then doing a sub selection on File B selecting Relative Record Number as a column value. I match the Relative Record Number + 1 from File B to the Relative Record Number of the SubQuery to retrieve my desired value. Can anyone tell me what I need to use in place of RRN on the SubQuery so the selection will work. I have included a copy of the SQL . "SELECT FILE1.DOCUMENT_ID, FILE1.KEY1_DATA, FILE1.KEY2_DATA, FILE1.KEY3_DATA, FILE1.DATE, FILE2.OPTVOL From S12B4441.DVDTA.FILE1 FILE1 inner join S12B4441.DVDTA.FILE2 FILE2 on FILE1.DOCUMENT_ID = FILE2.DOCUMENT_ID inner join (SELECT RRN(FILE2) AS RECNUM, A.DOCUMENT_ID, A.OPTVOL FROM S12B4441.DVDTA.FILE2) SUB on RRN(EKD0310) +1 = SUB.RECNUM Where FILE1.OPTVOL <> ' ' AND FILE1.ITEM = 'D' AND FILE1.CABINET in('INSDOC', 'CHGDOC') ORDER BY FILE1OPTVOL DESC"
View Replies !
Selecting Records From One Column In A Table And Inserting..
Godwin writes "Hello, Heres my question.. I have 2 tables.2 paticular columns exist in both the tables. I want to be able to select those 2 columns on the 1st table and insert them on to the same 2 columns on the 2nd table. Now,this 2nd table has another 3 columns that exist in another table.I would like to take those 3 column values from that 3rd table and insert it into the 2nd table by modifying those existing records in the 2nd table.In the 3rd table,there will be around 5 records...I want to copy the existing records 5 times in the 2nd table and insert the 3rd tables rows inside the 2nd table in that respective column for 5 rows. I hope you understand what I mean...Im sorry for really confusing.. Please help me Thanks Godwin"
View Replies !
Selecting Column Criteria Based On Report Parameter
I have a report with a date type parameter. Depending on the value return by this date type parameter the dataset will return either the credit, deposit or process date. How do I go about coding it so that it will dynamically select the right column in my query for my dataset? Sincerely appreciate all the help I can get. Thanks in advance.
View Replies !
Using Column Name In 'LIKE' Clause
Hi i want to join two tables basing on like condition of the column values of two tables. My query is like this: select pc_assign_worklist.pxRefObjectInsName AS "pxRefObjectInsName", pc_assign_worklist.pxUrgencyAssign AS "pxUrgencyAssign", pc_assign_worklist.pyLabel AS "pyLabel", pc_assign_worklist.pyAssignmentStatus AS "pyAssignmentStatus", pc_assign_worklist.pxAssignedOperatorID AS "pxAssignedOperatorID" , pc_assign_worklist.pxCreateDateTime AS "pxCreateDateTime" , pc_assign_worklist.pxCreateOpName AS "pxCreateOpName", pc_index_workparty.MemberIdentifier AS "MemberIdentifier", pc_index_workparty.LastName AS "Last Name", pc_index_workparty.FirstName AS "First Name", pc_index_workparty.pxInsName AS "pxInsName" from dbo.pc_assign_worklist, dbo.pc_index_workparty where pxAssignedOperatorID ='dasxkx1' AND pc_index_workparty.pzInsKey Like '%'+pc_assign_worklist.pxRefObjectInsName+'%' ORDER BY pxUrgencyAssign DESC ----------- i want to compare the two columns of two tables using like or contains clause as column1 in table a has value like "hi i am" where as column2 in table2 has value "hi". I need help on how to accomplish this.
View Replies !
Cannot Check Column In Where Clause
In a stored procedure I have I have dates in the format YYYYMMDD with symbols representing the first 3 digits e.g. °30903 =20030903, and I have to convert them to proper dates, and then eliminate all old data, so I replace symbols and then convert to int SELECT af.AccomType, af.AccomRef, af.AccomName,af.address1, af.address2, bf1.RoomCode, Convert(Int,Replace(Replace(Replace(Replace(REPLAC E(Replace(MAX(bf1.EndBook),'°','200'),'´','204'),' 99','1999'),'97','1997'),'47',1947),'98','1998')) AS max_date, ............... WHERE af.Resort=@strResort AND (af.AccomType = 'H' OR af.AccomType = 'O') AND max_date>20040721 order by max_date. Problem is I get an error saying invalid column max_date. It works in the order by clause when I get rid of the 'max_date>20040721 '. Thanks
View Replies !
Using @column For Where Clause In Select
Hi, I want to do something like "select range from mytable where @ColumnToUse = 1" then "select range from mytable where @ColumnToUse = 2" etc. and @columnToUse lists a different column heading each time. I know I can put this into an "exec ('select range...')" type statement, but it's really helpful if I don't evaluate all my variables at this stage in my scripts. Can anyone help? THanks Dan
View Replies !
Multi-column IN Clause
Hi, I have a [TestTable] table with three rows. The pair of columns [Test1] and [Test2] are id, the [Test3] is a data column. First, I get a table variable with list of id pairs. Next, I would like to update the rows of that ids. However, I have not found the elegant way how to do it. For one column it is simple, just IN clause, which does not work (or I could not find how) for multi-columns. Does someone have a hint? Thanks, Martin Note: The example bellow is dummy; on the other hand, I hope it shows the important points. Please, do not beat me on syntax errors. Code Block DECLARE @MyTableVar table( Test1 int NOT NULL, Test2 int NOT NULL ); SELECT [Test1],[Test2] INTO @MyTableVar FROM [TestTable] WHERE [Test3] = '%dd%'; UPDATE [TestTable] SET [Test3] = [Test3] + 'ds' WHERE ([Test1], [Test2]) IN (SELECT [Test1], [Test2] FROM @MyTableVar);
View Replies !
Problem With WHERE Clause When Column Length &> 255
Hello, I am using the JDCB-ODBC driver for my app, but one of the columns in my WHERE clause is 255 characters long. Because of this no rows are returned even though the statement should return 1 or 2 rows. I've tried other JDBC-ODBC drivers too and they have the same problem. Additionally, I've tried using RTrim(), Substring(), etc and it still will not work....any ideas? Is it a driver bug? Anyone know of a driver that will work? Sample code.... ResultSet rs = dbRetrieve.getStatement().executeQuery( sql ) ; if (rs.next()) { // Never gets here } Thanx!
View Replies !
Computed Column In Where Clause - QUERY Help
Hi I need suggestion for a query. Consider following 2 tables. Table-1 "T1" ----------- |ID|Name |1 |abc |2 |def |3 |erw |4 |rwg |5 |her Table-2 "T2" ---------- |ID|Qty |1 |12 |1 |2 |2 |22 |3 |10 |2 |14 I want a query which displays ID, Name and MAX(Qty) for each item where Max(Qty)>=10 i.e. result should be Result ---------- |ID|Name|Qty |1 |abc |12 |2 |def |22 |3 |erw |10 I tried: Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty FROM T1 t1 WHERE MaxQty>=10 But it fails as computed or inline query columns can not be added in where clause. However following works: Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty FROM T1 t1 WHERE (Select Max(Qty) From T2 where ID=t1.ID) >=10 BUT IS IT OPTIMIZED? Please suggest an optimized way to handle such scenarios.
View Replies !
Column Name Conflicts With T-SQL Clause Keyword
I am busy extending a VB6 app to talk to SQL Server Express 2005 and have come across a naming conflict. Some of the columns in the application's Access 97 tables is "index" which obviously exists as a T-SQL keyword and therefore any queries I perform including this column throws a syntax error. I tried prefixing the columns with their table names as in TableName.Index, but this still throws up the syntax error which I thought was a bit odd. I preferably want a fix that will be Access as well as T-SQL compliant but if that's not possible I will just write a string converter that does the job based on anyone's suggestions. TIA
View Replies !
WHERE Clause Using Different Column Passed By Parameter
Hi all, I'd think this is possible but I'm having trouble getting data returned from the query. Fields PART_NUMBER and INTERNAL_SKU exist in the SKU table. This will be inside a SP. If user passes 'PN' as first parameter then I'd need to have the WHERE clause by PART_NUMBER, if he passes 'SK' (or anything else for now) then the WHERE clause shold be by SKU. Can't I just build the WHERE by replacing @SearchField with its value ? I've looked up the CASE statement but I don't think it does what I need. DECLARE @strSearchType varchar(2) DECLARE @strSearchValue varchar(15) DECLARE @SearchField varchar(15) set @strSearchType = 'PN' set @strSearchValue = '1234567' IF @strSearchType = 'PN' begin set @SearchField = 'PART_NUMBER' end ELSE begin set @SearchField = 'INTERNAL_SKU' end SELECT SKU as 'SKU', PART_NUMBER as 'PartNumber', DESCRIPTION as 'Description' FROM SKUs WHERE @SearchField = @strSearchValue FOR XML PATH('SKU'), ROOT('Response') Thanks, ST
View Replies !
Multiple Values For Single Column In Where Clause
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 Replies !
Where Clause Changing Based On NULL Column(s)
Hello DBAs: I am having difficulty constructing a where clause. I have 4 columns in a table. Col1 is never NULL. Col2,3,4 may or may not contain NULL values. Here is the situation. 1. Col2,3,4 are NULL WHERE Col1 = condition 2. Col2 is NULL, 3 may or may not be NULL. When Col3 is NOT NULL, Col4 may or may not be NULL where col1 and (col3(Not Null values) OR Col4(Not Null values)) = condition. How do I construct this logic. Please help
View Replies !
Using The AS Clause In A SQL Stored Proc -- Problem Using Datetime Column
I'm trying to concatenate fields in SQL stored proc for use in textfield in asp.net dropdownlist. I'm running into a problem when I tryto use a DateTime field, but can't find the answer (so far) on theInternet. Was hoping someone here would know?My sql stored proc:SELECT AnomalyID, DateEntered + ', ' + Station + ', ' + Problem As'SelectInfo'FROM tblAnomalyWHERE WorkOrder=@varWO AND Signoff=NullORDER BY DateEnteredbut I get the error:The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I'm not the brightest bulb on the block, so any clues greatlyappreciated!Thanks, Kathy
View Replies !
Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement
Greetings, I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered. How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column. ALTER PROCEDURE [dbo].[MyProcedure] AS BEGIN SELECT DISTINCT A.Name, A.index FROM ... ... ORDER BY A.[Index], A.Name ASC END ALTER PROCEDURE [dbo].[MyProcedure] AS BEGIN SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index] END Thanks
View Replies !
Selecting The Most Recently Edited Item AND Selecting A Certain Type If Another Doesn't Exist
I've got a big problem that I'm trying to figure out: I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those. I don't need this for an individual ID select, I need it applied to every record from the table. My address table has some columns that look like: [AddressID] [int] [LocationID] [int] [Type] [nvarchar](10) [Address] [varchar](50) [City] [varchar](50) [State] [char](2) [Zip] [varchar](5) [AddDate] [datetime] [EditDate] [datetime] AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table. So there will be multiple addresses to one LocationID, but each address will have it's own AddressID. How can I do this efficiently with perfomance in mind??? Thank you in advance for any and all replies...
View Replies !
GROUP By Clause Or DISTINCT Clause
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
View Replies !
Filtering Results In The Where Clause Vs A Having Clause
I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following. When on SQL Server 2000 the following statement ran without issue: UPDATE dbo.Track_ID SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed WHERE Processed = 0 AND LegNum = 1 AND TrackID IN ( SELECT TrackID FROM dbo.Track_ID GROUP BY TrackID HAVING MAX(LegNum) = 1 AND TrackID + 'x1' IN ( SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID)) FROM dbo.Track_ID INNER JOIN dbo.transactions ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id GROUP BY dbo.Track_ID.TrackID ) ) Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time. I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same? UPDATE dbo.Track_ID SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed WHERE Processed = 0 AND LegNum = 1 AND TrackID IN ( SELECT TrackID FROM dbo.Track_ID WHERE TrackID + 'x1' IN ( SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID)) FROM dbo.Track_ID INNER JOIN dbo.transactions ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id GROUP BY dbo.Track_ID.TrackID ) GROUP BY TrackID HAVING MAX(LegNum) = 1 )
View Replies !
Having Clause Without GROUP BY Clause?
Hi, What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ? eg : SELECT SUM(col1) from test HAVING col2 < 5 SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5 I want the equivalent query in MSSQLServer for the above Oracle query. Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?. Thanks, Gopi.
View Replies !
SQL Inner Join Clause And The Where Clause
Hi everyone, I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ? Thanks
View Replies !
Diff In On Clause And Where Clause?????
hi.. i have basic question like what is differance between conditions put in ON clause and in WHERE clause in JOINS???? see conditions that shown in brown color select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) ) select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) ) another thing... if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove d1.ImplicitID >= d2.ImplicitID from WHERE clause????
View Replies !
Top Clause With GROUP BY Clause
How Can I use Top Clause with GROUP BY clause? Here is my simple problem. I have two tables Categories Products I want to know Top 5 Products in CategoryID 1,2,3,4,5 Resultset should contain 25 Rows ( 5 top products from each category ) I hope someone will help me soon. Its urngent thanks in advance regards Waqas
View Replies !
Selecting A View And Selecting FROM A View Is Wildly Different
A colleague of mine has a view that returns approx 100000 rows in about 60 seconds. He wants to use the data returned from that view in an OLE DB Source component. When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins). He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname> In this instance all the data was returned in approx 60 seconds (as expected). This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case. Can anyone explain why? Thanks -Jamie
View Replies !
How To Refer A Column When The Referencing Column Is An Identity Column
Hi all, The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'.. i cannot implement this constraint, it throws the error when i execute the below Alter query, ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE CASCADE the error thrown is : Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be created where the referencing column 'child_table.child_id' is an identity column. any workarounds for this ?
View Replies !
TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column
Hi guys, If I have a temporary table called #CTE With the columns [Account] [Name] [RowID Table Level] [RowID Data Level] and I need to change the column type for the columns: [RowID Table Level] [RowID Data Level] to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time. What will be the right syntax using SQL SERVER 2000? I am trying to solve the question in the link below: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1 Thanks in advance, Aldo. I have tried the code below, but getting syntax error... ALTER TABLE #CTE ALTER COLUMN [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT; I have also tried: ALTER TABLE #CTE MODIFY [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
View Replies !
Selecting NOT TOP 1
Hi, I am trying to select all entries from a database apart from the top/latest entry, via a stored procedure. Below is the code that i have but i am doing it wrong somehow. Any ideas? Thanks CREATE PROCEDURE spNEWSARCHIVE AS SELECT * FROM tblNEWS WHERE NewsID NOT TOP 1 ORDER BY NewsID DESC GO
View Replies !
Selecting A Set For Min Value
Hi,I have the following set and would like to select all rows that havemin value of column 4 for a given column 1 and 2 group, irrespective ofcolumn 3, as below:C1 C2 C3 C4---------------------A B x 5A B y 10A BB XX 55A BB YY 11AA CC z 1AA CC zz 11Need---A B x 5A BB YY 11AA CC z 1using sql server 2000 (which does not provide rank or partition by)Thanks in advance,Tamas
View Replies !
Selecting MIN( ) : Please Help
Hi, in MS SQL 2000 or 2005: I have a table with URL(text field) and COUNTER(int). I want to select the URL from the row that has the lowest COUNTER. (There can be multiple records with an identical counter, but i just need any one of them, doesnt matter which.) something like: select URL from theTable where MIN(COUNTER) <--doesnt work. any suggestions about how i can do this in just 1 sql statement or stored procedure? Thanks in advance!
View Replies !
Sql Ranged Selecting
How can I select data between Mth row and Nth row from a specific search condition? for example , I want to get the 5th to 10th employee who is born in March and ordered by their name.
View Replies !
Need Some Help On SELECTING Event
hello all, i have a nested gridview. how do i add a SELECTING event for this datasource for my inner gridview? because i need to increase the commandtimeout. thnx. private SqlDataSource ChildDataSource(string strCustno) { string strQRY = "Select ..."; SqlDataSource dsTemp = new SqlDataSource(); dsTemp.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString ; dsTemp.SelectCommand = strQRY; return dsTemp; }
View Replies !
Selecting Record
Hi i was wanting to know how to select a record in a gridview. I have a gridview with firsname and lastname. I currently have select command on it but don't want it. I want to be able to select first name or last name and have it take me to that record on the database.
View Replies !
Selecting Distinct
Hi, This is quite an obvious problem, but for some reason I can't think through the solution. I have two columns, a datetime (datecreated) and an id (FK) othertableid (id)what I would like to be able to do is select a list of id and datecreated, but only chose the latest row for each id.So I guess it's a bit like a distinct on the id column, but ensuring the date returned is the top date. Please help
View Replies !
|
TRACKER
