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 Complete Forum Thread with Replies
Related Forum Messages:
Problem With Proc And Multi Where Clause Statment.
I am trying to make a proc with this code: create proc AddImages ( @Error smallint output, @ImageName varchar(50), @Game varchar(25), @SubSet varchar(25), @FullSubSet varchar(75), @Width smallint, @Height smallint, @AltText varchar(50) ) as declare @AbbreviationExists varchar(25), @ImageExists varchar(25) set @AbbreviationExists = (select Short from Eaglef90.Abbreviations where Short = @SubSet) set @ImageExists = (select ImageName, Game, SubSet from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet) set @Error = 0 if @AbbreviationExists is null insert Eaglef90.Abbreviations (Short, Long) values (@SubSet, @FullSubSet) if @ImageExists is null insert Eaglef90.Images (ImageName, Game, SubSet, Width, Height, AltText) values (@ImageName, @Game, @SubSet, @Width, @Height, @AltText) else set @Error = 1 but when I hit execute in Query Analizer I recive this error: Msg 116, Level 16, State 1, Procedure AddImages, Line 7 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Line 7 has this code on it: set @ImageExists = (select ImageName, Game, SubSet from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet) Does anyone know what is wrong with that select statement? -- If I get used to envying others... Those things about my self I pride will slowly fade away. -Stellvia
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 !
Select Statement Using Multi-list Box Values For WHERE IN SQL Clause
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox: Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly. <asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName"> <SelectParameters> <asp:Parameter Name="ListSelection" DefaultValue="1"/> </SelectParameters> </asp:SqlDataSource> The selListID column is type integer in the database. I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result: Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim Item As ListItem For Each Item In ListBox1.Items If Item.Selected Then If Label1.Text <> "" Then Label1.Text = Label1.Text + Item.Value + "," Else Label1.Text = Item.Value + "," End If End If Next Label1.Text = Label1.Text.TrimEnd(",") SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID) End Sub What am I doing wrong here? Thanks!
View Replies !
Error While Using OUTPUT Clause - The Multi-part Identifier Could Not Be Bound
I was trying to copy child records of one parent record into another, and wanted to report back new child record id and corresponding child record id that was used to create it. I ran into run-time error with OUTPUT clause. Following is a script that will duplicate the situation I ran into: CREATE TABLE Parent( ParentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ParentName VARCHAR(50) NOT NULL) GO CREATE TABLE Child( ChildID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ParentID INT NOT NULL REFERENCES Parent(ParentID), ChildName VARCHAR(50) NOT NULL) GO INSERT INTO Parent(ParentName) VALUES('Parent 1') INSERT INTO Parent(ParentName) VALUES('Parent 2') GO INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 1') INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 2') GO At this stage, there Child table looks like: ChildID ParentID ChildName 1 1 Child 1 2 1 Child 2 What I want to do is copy Parent 1€™s children to Parent 2, and report back which source ChildID that was used to create the new child records. So I wrote the query: DECLARE @LinkTable TABLE (FromChildID INT, ToChildID INT) INSERT INTO Child(ParentID, ChildName) OUTPUT c.ChildID, inserted.ChildID INTO @LinkTable SELECT 2, c.ChildName FROM Child c WHERE c.ParentID = 1 SELECT * FROM @LinkTable In the end I was expecting Child table to look like: ChildID ParentID ChildName 1 1 Child 1 2 1 Child 2 3 2 Child 1 4 2 Child 2 and OUTPUT clause to return me: FromChildID ToChildID 1 3 Child record with ID 3 was created using ID of 1. 2 4 Child record with ID 4 was created using ID of 2. But infact I€™m getting following error: Msg 4104, Level 16, State 1, Line 9 The multi-part identifier "c.ChildID" could not be bound. Any ideas on how to fix the OUTPUT clause in the query to return me the expected output? Thanks Yogesh
View Replies !
Create Multi Column View From Single Column Data
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field. data_table product_code month value 350 1 10 350 2 20 350 3 30 product_table product_code profit_center 350 4520 result_view product_code profit_center mon1 mon2 mon3 350 4520 10 20 30 My current query gives the following result result_view product_code profit_center mon1 mon2 mon3 350 4520 10 0 0 350 4520 0 20 0 350 4520 0 0 30 Any direction toward a solution would be appreciated. Am using SS2005.
View Replies !
Multi-column Index Vs Single Column Indexes
Hi,Would like to know the performance differenece between Multi-columnIndex vs Single Column Indexes. Let's say I have a table with col1,col2, col3 along with a primary key column and non-indexed columns.In queries, I will use col1, col2, and col3 together and some timesjust one or two of these three columns. My questions is, should Icreate one index contains col1, col2, and col3, or create 3 seperatedcolumns. I.e. each column has its own index. Any performancedifference?Thanks a lot.
View Replies !
Multi Column Grouping Vs Page/Column Break
In my report i would have 2 groups. The first group should cause a real page break, the secound group should cause a column break. Any idea on how to realize this, i've been playing with quite some settings but ..... So, any help ...
View Replies !
Multi-Select String Parameter Values Are Converted To N'Value1', N'Value2' In Clause When Report Is Run
I understand that Multi-Select Parameters are converted behind the scenes to an In Clause when a report is executed. The problem that I have is that my multi-select string parameter is turned into an in claused filled with nvarchar/unicode expressions like: Where columnName in (N'Value1', N'Value2', N'Value3'...) This nvarchar / unicode expression takes what is already a fairly slow-performing construct and just drives it into the ground. When I capture my query with Profiler (so I can see the In Clause that is being built), I can run it in Management Studio and see the execution plan. Using N'Values' instead of just 'Value1', 'Value2','Value3' causes the query performance to drop from 40 seconds to two minutes and 40 seconds. It's horrible. How can I make it stop!!!? Is there any way to force the query-rewriting process in Reporting Services to just use plain-old, varchar text values instead of forcing each value in the list to be converted on the fly to an Nvarchar value like this? The column from which I am pulling values for the parameter and the column that I am filtering are both just plain varchar. Thanks, TC
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 !
Multi Column Case, Perhaps?
I have 6 columns: Column1a, Column1, Column2a, Column2, Column3a, Column3 I need a statement that will go through the whole table as follows: For each row, if column3 is not null then return column3 as columnB and column3a as ColumnA. If column3 is null then if column2 is not null then return column2 as columnB and column2a as columnA. If column3 & column2 is null then return column1 as columnB and column1a as columnA. Any ideas? Thanks!
View Replies !
Multi-Column Report
Using sqlserver 2000 report server, upto how many columns are supported by multi-column report.Is there any multi-row report as well ? I want to use upto 10 columns in multi-column report.Is there the specified support in 2000 report server or 2005 report server . Regards
View Replies !
Sum Up Multi Values Same Column
Hi All, I have the following Ex: Table A Col A <- Col that I need to check against Col B Col C <- Sum this col So if Col A has the following values: Col A Col C 2 10.00 4 15.00 2 25.00 4 15.00 3 10.00 3 5.00 7 4.00 9 20.00 I need to bring back the sums of 2, 4 and 3 in one resultset. How would I do this? Thanks, JJ
View Replies !
Multi Column Index
Hi there, My 'where' clause contains the following... startdate <= @Date AND enddate > @Date Should I put one index on 'startDate' and another on 'endDate' or should I have one index that covers both columns? I don't know what the difference would be. Cheers, XF.
View Replies !
Multi Aliases With The Same Column?
TABLE1 ====================== PriceList --------- 1 2 3 1 2 3 1 2 3 Price ----- 777 888 999 777 888 999 777 888 999 (pretend these columns are side by side) ====================== I need to make a query to: SELECT PRICE AS 'PRICE1' WHERE PRICELIST = 1 AND SELECT PRICE AS 'PRICE2' WHERE PRICELIST = 2 AND SELECT PRICE AS 'PRICE3' WHERE PRICELIST = 3 the output that i want is: PRICE1 ------ 777 777 777 PRICE2 ------ 888 888 888 PRICE3 ----- 999 999 999 (pretend these columns are also side by side)
View Replies !
Column Having Multi Languages Data
Hi, I have got a column which should multi languages data(Chinese,English etc). Source for this data is Excel. I have kept this column DataType as NVARCHAR but Chinese data is shown as 'Boxes'.But when i copy this and paste on Query pane i get proper data. Is the first thing doable? Hoe can i accomplish this? Thnks
View Replies !
Multi-column Report Problem
I am trying to get a simple list to wrap and print in multiple columns on my report. I have indicated 3 multi-columns in the report properties and see the multiple columns in the layout, but I must need to do something else because the report is still printing in one column to multiple pages. This should all fit on 1 page if the list will wrap or snake as it says it the directions for using multi-column reports. Any help would be appreciated. Thanks
View Replies !
Problem Rendering Multi Column
I've added a link to a sample on how my test-column report renders. http://www.leboeuf.be/MSDN/Dagelijkse%20Verkoop%20Merk.pdf Any idea what's happening, while normally previewing the report (column by page) this problem doesn't occur .... The rendering is the same to TIFF http://www.leboeuf.be/MSDN/Dagelijkse%20Verkoop%20Merk.tif Any suggestion on what is the problem and even better, if there is a solution.
View Replies !
Help!!!!--Problems About The Multi Column Report!
I have a 2 coulmns report ,one group ,when I print the report,it does not paging correctly.For example,suppose every column can display 20 rows, if every group has 20-40 rows ,the print is ok,it can paging correctly,but if one group has 10 rows or more than 40 and less than 60,the two groups will print on one paper,how can I print the every group on different paper? Any advice would be much appreciated. Thanks.
View Replies !
Multi Column Display In A Report??
I have a table with a data cell which is printing top-to-down 1 2 3 4 5 6 7 8 I want it to print like 1 2 3 4 5 6 7 How to do this?? The cell is in a group detials sections. and I am using Reporting Services 2000 with VS.NET 2003 Thanks in Advance
View Replies !
Multi Column Report With Graph
Hi, I have created a multi column report (2 columns), which is working fine. However, I need to have a chart on the same report, but it needs to be the width of the page, and not just the width of one column. If I could put the chart in the report footer / header that would be perfect, but obviously I can't! Has anyone else come across this, and any ideas / suggestions you have would be appreciated! Cheers Chris
View Replies !
Snaking (multi-column) Report
Hello, I am trying to build a Mailing Labels report which requires me to display data into 3 columns. I did some search and I found out that I need to go to the Layout tab under the 'Report Properties' menu option. I selected the columns to be 3 but when i preview the report I only see one single skinny column of data. What am I missing? I tried different sizes of the page and spaces/margins. thanks
View Replies !
Multi Column Subquery? W/ Alias
What I need to do is to create 3 columns with 3 different aliases from the same table that will return all the values during the following conditions: when pricelist = 1 when pricelist = 2 when pricelist = 3 pricelist -------- 1 2 3 Price -------- 912 -- (linked with 1) 234 -- (linked with 3) 56 -- (linked with 2) 3245 -- (linked with 3) 234 -- (linked with 1) 65 -- (linked with 2) these 2 columns are in the same table^^ so what i want my query to generate is: Price1 -------- 912 234 Price2 -------- 56 65 Price3 -------- 234 3245 Any help is apprecieated, thanks if the above does not make sense to you maybe this will: "can you make 3 aliases of the same column and only display the rows inside each column where pricelist = 1 for the 1st alias... where price = 2 for the 2nd alias...where pricelist = 3 for the 3rd alias"
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 !
Checking Column Name From Multi Database's Table
hi i have over 200 tables with all same column and data type locate all over the server(20)different database. so i have table call Tname to stored all the link like (databasename.dbo.tablename) so my job will call the Tname table to use cursor to insert records. but the problem is there's one or more table's column name K datatype has been modify by someone else. so is it cause the job fail. if there a way or SQL statment that i can use Table Tname to see which one is missing column name K? and is there a way i can print out all the datatype and len for all the table column k? please help thanks
View Replies !
Multi-column Report Page Header
I have a multi-column report with a page header that spans the width of the report. Whenever I close the report and reopen it, the header is changed to the width of the column. This isn't a real big deal because I deploy it with the header across the whole page; but once in a blue moon I accidentally hit the F5 key (which I am used to using for refresh in other environments I work in) and the report gets redeployed with the messed up header. Is there anyway to stop RS from automatically adjusting the page header? And is there anyway to disable the F5 key from deploying the reports. I do use it to build and run windows apps, and don't want to change that, but I don't want to deploy 80+ reports when I accidentially hit it. Thanks.
View Replies !
Multi-Column Export Rendering Issues
I created an SSRS Multi-Column Report that sets the Column-Spacing to 0 inches. However, when I export the report to PDF the columns are approxiametly .125 inches apart from one another. I need the columns to butt up against each other as I want to print the contents on a perforated label. Does anyone know why this happens or how to enforce the column spacing? Thanks!
View Replies !
Multi Column Primary Key's In Access Using ADOX [c#]
Hi all, I have been pulling my hair trying to figure out what the guys at microsoft were thinking when creating the ADOX library. I have an access table that is syncronized with a SQL server. The table has a primary key with two columns [User] and [Program]. The SQL Server has both columns in as the primary key columns and I have a syncronization mechanism that is responsible for several things, one of which is to recreate the Access data structure. All works well for all tables except this one. I have tried to create the multi-column key in several ways, none that worked. Let me show you what I am doing: CatalogClass catDCDLocal; Column c; catDCDLocal = new CatalogClass(); catDCDLocal.let_ActiveConnection(dbAccess.buildConnectionString(Settings.CattDCDLocalPath, Settings.SecurityDBPath, s.UserID, s.Password)); foreach (Table tbl in catDCDLocal.Tables) { if (tbl.Name == "Users") { /* This is retarded so need to clean up... Users table has a primary key consisting of 2 columns */ for (int i = tbl.Keys.Count - 1; i >= 0; i--) { //remove the keys tbl.Keys.Delete(i); } for (int i = tbl.Indexes.Count - 1; i >= 0; i--) { //remove the indexes tbl.Indexes.Delete(i); } tbl.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyUnique, "User", "", ""); tbl.Keys[0].Columns.Append("Program", DataTypeEnum.adWChar, 6); } } I have also tried: tbl.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyUnique, "User", "", ""); //tbl.Keys[0].Columns.Append("Program", DataTypeEnum.adWChar, 6); Key k = tbl.Keys[0]; Column col = tbl.Columns["Program"]; //col.ParentCatalog = catDCDLocal; k.Columns.Append(col, DataTypeEnum.adWChar, 6); Nothing works for me ;-(
View Replies !
Selecting The Same Column Twice In The Same Where Clause
Hi :From a crystal report i get a list of employee firstnames as a stringinto my store procedure. Why is it comming this way ? hmmmmmm it's aquestion for me too.ex: "e1,e2,e3"here are my tablestblProjectsProjectId123tblEmployeeemployeeId FirstName1 e12 e23 e3tblProjectsToEmployeeProjectId employeeId1 11 21 32 12 23 13 23 34 14 3i need to find out the project ids all 3 of these employees worked on.so the out put i need isprojectId13How can i get it ????????????now i can use replace command to format it to a OR clause or ANDclauseSET @string= 'employeeId =' + '''' + REPLACE('e1,e2,e3',',',''' ORemployeeId = ''') + ''''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 thesame SQL string and doing some thing likeWHERE (empTable1.Firstname ='e1' AND (empTable1.Firstnamein('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 verymessy way of doing it, because i get a comma seperated stringparameter i have to construct the sql string on the fly.any help or direction on this matter would greatly appreciated.thankseric
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 !
Ambiguous Column Names In Multi-Table Join
Hi all,A (possibly dumb) question, but I've had no luck finding a definitiveanswer to it. Suppose I have two tables, Employees and Employers, whichboth have a column named "Id":Employees-Id-FirstName-LastName-SSNetc.Employers-Id-Name-Addressetc.and now I perform the following join:SELECT Employees.*, Employers.*FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)The result-set will contain two "Id" columns, so SQL Server willdisambiguate them; one column will still be called "Id", while theother will be called "Id1." My question is, how are you supposed toknow which "Id" column belongs to which table? My intuition tells me,and limited testing seems to indicate, that it depends on the order inwhich the table names show up in the query, so that in the aboveexample, "Id" would refer to Employees.Id, while "Id1" would refer toEmployers.Id. Is this order guaranteed?Also, why does SQL Server use such a IMO brain-damaged technique tohandle column name conflicts? In MS Access, it's much morestraightforward; after executing the above query, you can use"Employees.Id" and "Employers.Id" (and more generally,"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"column you want, instead of "Id" and "Id1" -- the"just-tack-on-a-number" strategy is slightly annoying when dealing withcomplex queries.--Mike S
View Replies !
Multi Column Sub Select Equivalent For Sql Server 2005
Can anyone tell me how to do this in sql server?I am currently doing this query in oracle:select table1.col1,table1.col2,table2.col3,table4.col4where table1.col1 = table2.col3 andtable2.col3 = table4.col5 and(table1.col1,table1.col2) not inselect table2.col4,table2.col5 from table2it is the where two column values from any row are not found in anyrow in table2 part that I can't figure out.thanksJeff
View Replies !
How To Force A Page Break On A Multi-column Report ?
When I use the PageBreakAtEnd on the table or on a group in the table, all it does is create a new column of the column report. I'd want it to start a new page, how can I do this ? Should I work around this issue using code ? Background: What I need to achieve is a report with 2 columns where the list of products in category 1 are listed in the left column and then snake to the 2nd column on the same page, then to column 1 on page 2, column 2 on page 2, etc... When it comes to category 2, it should start a fresh new page regardless of whether the previous product was rendered in column 1 or column 2. I get the snaking to work using the "Columns" property of the report Body. However page breaks do not start a new page, they just start a new column.
View Replies !
Converting Multi-column Referential Constraint Into A Trigger
Hi there, I am looking for a way to define a trigger that is a replacement for a multi-column foreign key. I know how to a convert a single-column foreign key constraint into a trigger (i.e., to resolve diamond-structured references). CREATE TABLE parent_tab ( col_a INTEGER NOT NULL, CONSTRAINT pk PRIMARY KEY(col_a) ); CREATE TABLE child_tab ( col_x INTEGER NOT NULL, CONSTRAINT fk FOREIGN KEY (col_x) REFERENCES parent_tab(col_a) ON DELETE CASCADE ); The conversion would remove the foreign key definition and add this trigger: CREATE TRIGGER tr_single ON parent_tab INSTEAD OF DELETE AS BEGIN DELETE FROM child_tab WHERE (child_tab.col_x IN (SELECT col_a FROM deleted)) DELETE FROM parent_tab WHERE (parent_tab.col_a IN (SELECT col_a FROM deleted)) END; Unfortunately, now I need to resolve a situation where there is involved a multi-column foreign key. CREATE TABLE parent_tab ( col_a INTEGER NOT NULL, col_b INTEGER NOT NULL, CONSTRAINT pk PRIMARY KEY(col_a, col_b) ); CREATE TABLE child_tab ( col_x INTEGER NOT NULL, col_y INTEGER NOT NULL, CONSTRAINT fk FOREIGN KEY (col_x, col_y) REFERENCES parent_tab(col_a, col_b) ON DELETE CASCADE ); This does not work, because the temporary table "deleted" might contain more than one row. How do I make sure that the values belong to the same row? -- incorrect trigger, might delete too many rows CREATE TRIGGER tr_single ON parent_tab INSTEAD OF DELETE AS BEGIN DELETE FROM child_tab WHERE (child_tab.col_x IN (SELECT col_a FROM deleted) AND child_tab.col_y IN (SELECT col_b FROM deleted)) DELETE FROM parent_tab WHERE (parent_tab.col_a IN (SELECT col_a FROM deleted) AND parent_tab.col_b IN (SELECT col_b FROM deleted)) END; -- some magic needed :-) CREATE TRIGGER tr_single ON parent_tab INSTEAD OF DELETE AS BEGIN DELETE FROM child_tab WHERE (child_tab.col_x IN (SELECT col_a FROM deleted AS t1) AND child_tab.col_y IN (SELECT col_b FROM deleted AS t2) AND row_id(t1) = row_id(t2)) DELETE FROM parent_tab WHERE (parent_tab.col_a IN (SELECT col_a FROM deleted AS t1) AND parent_tab.col_b IN (SELECT col_b FROM deleted AS t2) AND row_id(t1) = row_id(t2)) END; I know the trigger definition above is ***... but I hope that it helps to make clear what I need. Btw., I use SQL Server 2005. Thanks in advance, slowjoe
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 !
Force A Page Break On A Multi-column Report, Still Unsolved
Hi out there It seams that I have run into a know and apparently unsolved problem. How do I force a page break, not column break, on a multi-column report? I have 4 columns, my data is grouped and is setup to page break after each group. But all I get is a column break, due to RS thinking of column's as pages. Several people have posted about the same problem, but I can't find a solution. See... http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=884945&SiteID=17 http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=226045&SiteID=17 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1654050&SiteID=1&pageid=0 http://www.themssforum.com/SVCS/Page-break-77428/ http://www.themssforum.com/SVCS/Createing-real/ http://www.bokebb.com/dev/english/2008/posts/2008116837.shtml Does anyone know how to solve this ???
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 !
The Multi Delete && Multi Update - Stored Procedure Not Work Ok
the stored procedure don't delete all the records need help Code Snippet DECLARE @empid varchar(500) set @empid ='55329429,58830803,309128726,55696314' DELETE FROM [Table_1] WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0 UPDATE [empList] SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0 WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0 UPDATE [empList] SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0 WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0 TNX
View Replies !
Help With Multi Join Or Multi Tier Select.
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
View Replies !
Multi Table / Multi Field Search
Our site, a non profit public utility one, needs help to develop a search function as follows. We have a main table containing the pages text within a text field. We have some other tables (6 in all) containing lists of data used to dinamically publish certain additional information within pages. We have already a fulltextsearch function working on the main table fields. It works very well and allows to scroll results in pages of 10 each. We nos need to develop a function able to make the same (including allowing a scroll), but to search at the same time withing some fields of all the tables containing information (lets say the mains one and the other 6 with data). We know how to make a fulltext search with different fields of one table, but nos how to do the same with different fields from different tables. Any idea about how to do this ??
View Replies !
Multi-database Multi-server
I am new to Reporting Services and hope that what I am looking to do is within capabilities :-) I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is: Dataserver A Database A1 Database A2 Database A3 Dataserver B Database B1 Database B2 Dataserver C Database C1 Database C2 Database C3 I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3 Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005. Is this something that Reporting Services is able to handle or do I need to look at some other solution? Thanks, Michael
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 !
|
TRACKER
