I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
I get the error below when trying to modify a table or colum with MSSMS. I can expad out the tree and look at the columns, but I can't change them, view the data in them, or create new ones. __________________________________________________________________________________________________________________________________ TITLE: Microsoft SQL Server Management Studio ------------------------------ Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration) I resintallelled .NET Framework, recommended from another post. I am using .NET 3.5 BETA Frame work, ans SQL Server 2005 V 9.00.139.00
I have a product table that has a productID column and a productName column. Then I have a productCategory table that associates productIDs with categoryIDs. And lastly I have a category table containing a categoryID and categoryName. I want to write a query that will return a table with three columns...productID, productName, and and calculated column containing the categories the product belongs to separated by a comma. A sample output would be...
If any of this isn't clear please feel free to write with questions. So far I have this query - SELECT Products.ProductID, ProductName, CategoryName FROM Products, ProductCategory, Categories WHERE Product.ProductID = ProductCategory.ProductID AND Categories.CategoryID = ProductCategory.CategoryID
I want to select columns from different tables into a single table...Not sure if a temp table is suited for this and if so how I should implement it (NOTE: this query will be executed many times by different users at the same time, so I'd rather avoid temp tables!)I have:TABLE1idfirstnamedescriptioncreatedateTABLE2idcarnamespecificationsimportdateNow, I want a resultset that has the columns (columns from other tables from which the values should be retreived are behind the desired columns):id (TABLE1.id, TABLE2.id)title (TABLE1.firstname , TABLE2.carname)description (TABLE1.description , TABLE2.sepcifications)date (TABLE1.createdate , TABLE2.importdate)Thanks!
I'm trying to select from multiple table in one select statement but i'm having problems. Here is the code i'm trying:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoRead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[snow_ors_additionalInfoRead] GO
CREATE PROCEDURE dbo.snow_ors_additionalInfoRead @Reference int AS SELECT Account.CanTravel, Account.SEEmployee, Account.WorkHours, Account.DrivingLicence, Account.CriminalConvictions, Account.CriminalConvictionsDetails1, Account.CriminalConvictionsDate1, Account.CriminalConvictionsDetails2, Account.CriminalConvictionsDate2, Account.CriminalConvictionsDetails3, Account.CriminalConvictionsDate3, Application.VacancyMonitoring, Application.VacancyMonitoringDetails FROM Account, Application WHERE Account.Reference = @Reference AND Application.reference = @Reference
Hi there,I'm new to sql and thus I'm having problems with a specific query which Ihope you guys can help me with.Basicly I have a few tables which I'm trying to do a query on:Table groups contains information about specific groups e.g. "Windows" or"Unix".Table users contains information about specific users e.g. "a", "b" or "c".Table users_groups contain information about group relationship (a user canbe in multiple groups) e.g. (a, Windows), (b, Unix), (a, Unix).In this case user c is ungrouped.Now I'd like to find the users which does belong to group Windows and thosewho do not:select distinct username from users_groups where groupname = "Windows" orderby username asc;This works pretty well for finding users in the specific group. In this casethe result is a.However I'd like to get the opposite result (b and c) but I'm stuck.The problem is that I'd like a list of all users excluding those which arein "Windows"Here is a partial query:select distinct users.username from users left join users_groups onusers.username = users_groups.username where users_groups.username is nullorder by users.username asc;This only gives me those users who are not grouped at all. This mean thatuser b is not in those results.Please advise.Thanks in advance.-- Henrik
I'm trying to determine the oldest timestamp that meets given criteria which includes a specific action_type code. That code can legitimately be performed more than once on an item (all actions are recorded in an actions table, so the full history is available).
I'm having trouble getting the most recent timestamp for the given action_type I'm looking for, when that action_type has been performed more than once.
My intent with the query below is to get the most recent action_time and it's request_id that meets the criteria
SELECT TOP 1 a.action_time, r.request_id FROM requests r JOIN incident i ON r.request_id = i.request_id LEFT JOIN actions a ON r.request_id = a.request_id WHERE i.refund_type = 1 AND (r.status_code = 3 OR r.status_code = 14) AND a.action_type = 3 ORDER BY a.action_time
So for example, if the item with request_id 1334 has had action_type 3 performed on it twice, once at 2007-10-15 13:30:00 and then again at 2007-10-17 14:40:00, it's picking up the former, when I want the later. That it had action_type 3 performed on it twice is legitimate. (larger context an item was approved by a manager, then rejected by a processor, and then approved by a manager again after resubmission)
Hopefully this has made sense so far.
So, how do I make sure I'm getting the most recent action time, when I don't know if the item in particular will have more than one of this action type performed on it?
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
I currently have two tables called Book and JournalPaper, both of which have a column called Publisher. Currently the data in the Publisher column is the Publisher name that is entered straight into either table and has been duplicated in many cases. To tidy this up I have created a new table called Publisher where each entry will have a unique ID.
I now want to remove the Publisher columns from Book and JournalPaper, replace it with an ID foreign key column and move the Publisher name data into the Publisher table. Is there a way I can do this without duplicating the data as some publishers appear several times on both tables?
Any help with this will be greatly appreciated as my limited SQL is not up to this particular challenge!!! Thanks!
I want to have a linking table say for example we call this a claim. Based on the claim number you need to relate to one of say 6 different types of claims. The types of claims related to their own individual parent table. (individual because each type of claim tracks completely different information) does anyone have an idea on how to set this up?
Sample Structure
table = Claim Field 1 = ClaimTypeA_ID Field 2 = ClaimTypeB_ID Field 3 = ClaimTypeC_ID Field 4 = ClaimTypeD_ID Field 5 = ClaimTypeE_ID Field 6 = ClaimTypeF_ID
The six field relate to the 6 different tables ID.
If I do this how do I store the data? put 0's in each of the claim types that are not used???
I am trying to figure out how to use the select maximum command in SQL Server 2005. I have already created a database and I have it populate it with multiple fields and multiple records. I Would like to create a new column or field which contains the maximum value from four of the fields. I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column.
Any help you can provide will be greatly appreciated!
An IBM Global Services consultant is telling my client that in order to have SAP BI read any data from any other application supported by SQL Server 2005, that all tables and fields MUST be in UPPER CASE. This would mean that SAP BI could not read ANY data from AdventureWorks (which everyone needs ) but more importantly from 95% of applications written and stored on SQL Server. I find this to be ludicrious, frankly, but don't know how to find out if it is true. Anyone?
I have several tables that I need to sum up a colomn and display the results. I have been struggling with this for some time. Here are my Tables and their columns:
Ingredients_tbl IngredientID Description
Inventory_tbl IngredientID AmountRemaining Renconciled Active
Order_Details_tbl IngredientID OrderAmount Status
Order_Details_Details_tbl IngredientID OrderAmount Status
I want to display all the ingredients from the ingredients table, sum the amountremaining of the items that are reconciled = 'false' from the inventory_tbl, sum the orderamount from both the order_details_tbl and Order_details_details_tbl for each ingredient based on status. Here is some sample data and results I am looking for:
IngredientID Description Inventory = Sum of AmountRemaining of the records in Inventory_tbl where Renconciled = 'false' Production1 = sum of OrderAmount of the records in the Order_Details_tbl where Status = 'ORDERED' Or Status = 'MIXED' Production2 = sum of OrderAmount of the records in the Order_Details_Details_tbl where Status = 'ORDERED' Or Status = 'MIXED'
I thought this was going to be as easy as a few simple joins and aggregate sum on the colomns like this:
SELECT Ingredients_tbl.IngredientID, Ingredients_tbl.Description, Ingredients_tbl.LowInventory, SUM(Inventory_tbl.AmountRemaining) AS Inventory, SUM(Order_Details_tbl.OrderAmount) AS Production1, SUM(Order_Details_Details_tbl.OrderAmount) AS Production2 FROM Ingredients_tbl LEFT OUTER JOIN Inventory_tbl ON Inventory_tbl.IngredientID = Ingredients_tbl.IngredientID AND Inventory_tbl.Reconciled = 'False'
[Code] .....
But this obviously has its issues with incorrect returned sum values.
Microsoft.AnalysisServices.Viewers.DLL dll in VS 2005 but sometimes it gives an error.
"Code generatio for property 'ConnecitonManager'" failed. Error was:'Property accesor 'ConnectionManager' on object 'AssosiactionViewer1' threw the following exception:'Object referance not set to instance of an object"
Is there anyone here who use "Microsoft SQL Server 2005 Datamining Viewer Controls" in SQLServer2005 FeaturePack ? http://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en
i am using VS2005 Version 8.0.50727.762 (SP.050727-7600) and SQL Server 2005 SP2
For a uncomplicated example, our database has 10 tables. Each table contains the column COMPANY. I need to change company from 1 to 4, and I don't want to have to update each table individually (because the number of tables is actually closer to 800).
COMPANY being a primary key isn't an issue.
Is there a painless way to update the COMPANY field in all user defined tables?
Hoping someone here can help. Perhaps I'm missing something obvious, but I'm surprised not to see a data flow task in SSIS for splitting *columns* to different destinations. I see the Conditional Split task can be used to route a *row* one way or another, but what about columns of a single row?
As a simple and somewhat contrived example, let's say I have a row with twelve fields and I'm importing the row into a normalized data structure. There are three target tables with a 1-to-1 relationship (that is, logically they are one table, but physically they are three tables, with one of them considered the "primary" table), and the twelve input fields can be mapped to four columns in each of the three tables.
How do I "split" the columns? The best way I can see is to Multicast the row to three different OLE-DB Destinations, each of which inserts to one of the three target tables, only grabbing the four fields needed from the input row.
Or should I feed the row through three successive OLE-DB Command tasks, each one inserting into the appropriate table? This would offer the advantage, theoretically, of allowing me to grab the identity-based surrogate primary key from the first of the three inserts in order to enable the two subsequent inserts.
I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.
I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)
Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.
Basically the wizard creates the following Control Flow tab entries (in processing sequence order):
an Execute SQL Task: NonTransactableSql an Execute SQL Task: START TRANSACTION a Sequence Container: Transaction Scoping Sequence, which contains an Execute SQL Task: AllowedToFailPrologueSql an Execute SQL Task: PrologueSql a Foreach Loop Container, which contains a Transfer Task with an icon I did not notice in the Toolbox an Execute Package Task: Execute Inner Package an Execute SQL Task: EpilogueSql an "on success" arrow to an Execute SQL Task: COMMIT TRANSACTION an Execute SQL Task: PostTransaction Sql an "on failure" arrow to an Execute SQL Task: ROLLBACK TRANSACTION an Execute SQL Task: CompensatingSql
Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?
This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.
So now I'm trying to understand why the Wizard created this more-complex package.
Any help will be appreciated, including references to non-Microsoft books/websites/etc.
Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005. I have four tables, an Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date) Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets) Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time) and a linking table for all of them.... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)
My problem is that I can insert data to each specific table by itself using seperate insert statements.....eg....
//insert an attendance record to the attendance table string userID;
userID = Session["User"].ToString();
SqlDataSource pgpDataSource = new SqlDataSource(); pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
finally { pgpDataSource = null; } //same code as above for the resistance table
However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables. I have read several forums where they recommend using something called @@IDENTITY but i have no idea how or where to use this in order to fill my exercise table... Any help would be so much appreciated.... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C# Cheers Scotty
We just upgraded from SQL Server 2000 to 2005. In the past, when I ran the import/export wizard to copy multiple tables from one database to another with SQL Server 2000, I had no problem. Now when I used the import/export wizard to copy multiple tables with SQL Server 2005, I kept getting an error. For example, when copied three tables, the first table might be copied fine and I got an error with the second table and the whole thing stop. Sometimes I could copy two tables. However, when I ran the import/export wizard to copy each table one at a time, it worked.
The error that I got was "Cannot insert duplicate key in object..." I selected the options to "Delete rows in existing destination tables", and "Enable identity insert". What am I doing wrong?
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
Hi, I think you can do this in oracle but I'm trying to figure a way to do it in ms sql.. I need to select a distinct combination of columns, like so...
select distinct(ItemName,ItemData,FID) from tblSIFups
Does anyone know how to achieve that? I have multiple data where I shouldn't and I don't have any control over the application so I need to clean it this way.
Hi, I have tried this code from http://jtkane.spaces.live.com/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry for full-text search on multiple tables & columns. Here's my code: SELECT * from [tStaffDir] AS e, [tStaffDir_PrevEmp] t,CONTAINSTABLE([tStaffDir], *, @Name) as AwhereA.[KEY] = e.[ID] andt.[ID] = e.[ID] I have FT the both the tables above and I am able to get results from the [tStaffDir] table but not the [tStaffDir_PrevEmp] table.The [tStaffDir_PrevEmp] table does have a column (which is [ID]) that is indexed, unique and non-Nullable.Please advise what I should do and look out for. Many Thanks.
1 2015 ba1 137 HL EL Eco 2 2015 ba1 138 EL SL HS 3 2015 ba1 139 SL EL His
From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.
Here is the TABLE 2 Structure and sample data:
table 2 (NAME - tblstudetail)
id studentID session course sub1 sub2 sub3
1 15120001 2015 ba1 EL SL HS 2 15120002 2015 ba1 HL EL Eco 3 15120003 2015 ba1 SL EL His 4 15120004 2015 ba1 HL EL Eco
AND so no..........................
Now i just want to COUNT the Number of Groups Filled in tblStudateil.
I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL Drop table Bezeichnungen; GO create table Bezeichnungen ( Artikelnummer nvarchar(18), Artikelbezeichnung nvarchar(80), Artikelgruppe nvarchar(13),
Hi all, I am writing a portion of an app that is of intensely high online eCommerce usage. I have a question about identity columns and locking or not. What I am doing is, I have two tables (normalized), one is OrderDemographics(firstname,lastname,ccum,etc) the other is OrderItems. I have the primary key of OrderDemographics as a column called 'ID' (an Identity Integer that is incrementing). In the OrderItems table, the 'OrderID' column is a foreign key to the OrderDemographics Primary Key column 'ID'. What I have previously done is to insert the demographics into OrderDemographics, then do a 'select top 1 ID from OrderDemographics order by ID DESC' to get that last ID, since you can't tell what it is until you add another row.... The problem is, there's up to 20,000 users/sessions at once and there is a possiblity that in the fraction of a second it takes to select back that ID integer and use it for the initial OrderItems row, some other user might have clicked 'order' a fraction of a second after the first user and created another row in OrderDemographics, thus incrementing the ID column and throwing all the items that Customer #1 orders into Customer #2's order.... How do I lock a SQL table or lock the Application in .NET to handle this problem and keep it from occurring? Thanks, appreciate it.
Table A has columns CompressedProduct, Tool, Operation
Table B in a differnt database has columns ID, Product, Tool Operation
I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.
But I want to be able to select any records that are in table A but not in Table B.
ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.
This code return all the records from A. I need to filter out the records found in Table B.
SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link, 'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts JOIN tblProduct p ON ts.ProductID = p.ProductID JOIN tblTool t ON ts.ToolID = t.ToolID JOIN tblOperation o ON ts.OperationID = o.OperationID WHERE ts.ToolID=66 ') oq LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
