I have a temp table with a column of all the same number. When I run my select statement I need it to bring back all the other data and just the single instance of the column with all the same numbers. How would I do this in my select statement?
For example, here is my table:
Here1One
There1Two
Up1Three
Down1Four
Back1Five
I need to bring back everything from column 1 and 3, but just the single instance of 1 from column 2 since it contains all the same number. All the data is collected in a single select statement.
I have a table that is returning rows from a table query. It seems I have done it before but I cannot seem to get the right procedure to obtain the values. I will paste in the code below in which you will see my bad attempts at accomplishing what I need.
Dim uid As String Dim pw As String Dim em As String, fn, ln, mi As String Dim par As String Dim Field, n, j As Integer Dim JJ As Integer
Just had a quick question for you . I have these 3 tables that are related in some way - below are the structures. I know the structures are correct and they work fine. However, I am using Visio Enterprise Architect to design these tables and when I try to generate the code, I get an error saying that it does not like the table with 2 fields as the primary key. And I just wanted to get feedback to find out whether I am correct or Visio is.
Table: Orders Columns: OrderID int PK Name varchar(100) Phone varchar(20)
Table: Products Columns: ProductID int PK Name varchar(100)
Table: OrderLineItems Columns: OrderID int PK ProductID int PK Price money
I need to create these tables..But i dont know how to create in correct way The question is in quote ...Please help me ..
quote: Blue library has required an application to manage their works. This library organizes books with categories and sub categories. So each book belongs to a category. Books are stored with their title, author(s), edition, publication date, ISBN, and Publisher Company. Publisher Information must be stored in the separate table for data integrity. Additionally each book must have information about if it is international edition or not. Books must be searchable by their title, author and their categories. Also, student information with their department information must be stored. Each student must have std id, name, surname, dept_id, enterance_date. And department table should have dept_id, dept_name columns.
When a student comes to library to reserve a book, the librarian should be able to reserve the requested books to the student. Each student may take at most 3 books. And each book should be returned in a month. The library manager should be able to take the list of books that are not returned on time. Also, library manager should be able to add/delete/update categories. However, other librarians should not be able to perform those tasks. Librarians should add/delete/update the books and reservations. For those functionalities, you should have two user categories that are Library Manager and library operators. Your database should support multiple operators. Unauthorized access to database should be prevented with user passwords. Each operator and Library Manager should have their own passwords. Library manager should have all privileges on library operator accounts. (i.e: retrieving a forgotten password, removing an account, adding a new operator account, etc.) User management process should be done by library manager account.
I'm a newbie so I'll explain what I'm trying to achieve the best I can ...
I'd like to essentially loop through a SQL table to display the correct results. The workflow is the user query's the database and returns records (by property ID). In the return there are duplicate records being returned - in this case, two property owners returned with the same property ID.
How would I loop through the SQL statement in the application (code) to identify when the property id's are the same and display only one owner for that property?
Code Block SELECT tce.TimeCardID, tce.TimeCardExpenseID, tc.DateCreated, e.LoginID, e.FirstName + ' ' + e.LastName AS FullName, tce.ExpenseAmount, tce.ExpenseDescription, op.ProjectName, op.ProjectDescription, ec.ExpenseCode FROM OPS_TimeCards tc JOIN OPS_Employees e ON e.EmployeeID = tc.EmployeeID JOIN OPS_TimeCardExpenses tce ON tc.TimeCardID = tce.TimeCardID Join OPS_Projects op ON op.ProjectID = tce.ProjectID Join OPS_ExpenseCodes ec ON ec.ExpenseCodeID = tce.ExpenseCodeID WHERE e.LoginID = 'jross' ORDER BY tc.DateCreated DESC
this query returns me the correct data....but i need to tweak the query so it does not duplicate rows....My tce.TimeCardID is a PK in its table and so is TimeCardExpenseID...but the problem is U can have many TimeCardExpenseID's for one timecard so my results look like
TimeCardID TimeCardExpenseID 1 2 1 3 1 4
I want my query to return the "TimeCardID" but i just want that one ID to represent all the TimeCardExpenseID's...but i can not get it to work and have no clue....
My question is how can I query tblTargetRatio table to return correct record for the following cases:
1 EmployeeID = 1 and Date = 03/12/2012 (Expecting Ratio = 8) 2 EmployeeID = 1 and Date = 10/10/2012 (Expecting Ratio = 7) 3 EmployeeID = 1 and Date = 08/12/2012 (Expecting Ratio = 5)
Hello,I have 3 optional text boxes. I don't know if the best way to set upthe table would be a field for each box, since this would leave gaps inthe table if the user only filled in one box. Is there a good methodto use?? This is kind of like storing check box values, in that therecould be multiple answers.
Hi, I have one column of data which is 15.678 but in the excel, i format it to 15.68 ( two decimal place, so in excel i should see 15.68), when i am trying to import the data from excel to sql server by using odbc connection, it still getting 15.678, how can i get the data from 15.678 to 15.68 ( what i see is wat i get). Thanks for help.
I have a wrong €œdbo.Samples€? table: SampleID SampleName Matrix SampleType ChemGroup ProjectID 1 Blueriver01 Soil QA VOCs 1 7 Greentree01 Water Primary VOCs 1 8 Greentree02 Water Duplicate VOCs 1 9 Greentree03 Water QA VOCs 2 10 Greentree11 Soil Primary VOCs 1 11 Greentree11 Soil Duplicate VOCs 1 12 Greentree11 Soil QA VOCs 3 13 Redrock01 Water Primary VOCs 1 14 Redrock02 Water Duplicate VOCs 1 15 Redrock03 Water QA VOCs 2 16 Redrock11 Soil Primary VOCs 1 17 Redrock12 Soil Duplicate VOCs 1 18 Redrock13 Soil QA VOCs 3
I used the following sql code to correct the wrong ProjectIds:
USE ChemDatabase GO ALTER TABLE Samples SET ProjectID = 4 WHERE SampleID = 7 SET ProjectID = 4 WHERE SampleID = 8 SET ProjectID = 5 WHERE SampleID = 9 SET ProjectID = 4 WHERE SampleID = 10 SET ProjectID = 4 WHERE SampleID = 11 SET ProjectID = 6 WHERE SampleID = 12 SET ProjectID = 7 WHERE SampleID = 13 SET ProjectID = 7 WHERE SampleID = 14 SET ProjectID = 8 WHERE SampleID = 15 SET ProjectID = 7 WHERE SampleID = 16 SET ProjectID = 7 WHERE SampleID = 17 SET ProjectID = 9 WHERE SampleID = 18 GO
I got the following error message: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'SET'.
Please help and tell me what it is the right syntax for my €˜SET€™ used in this sql code. I think there are more mistakes in this set of sql code. Please enlighten me and advise me how to make this set of code right.
INSERT INTO #TEMP VALUES(1,'James',NULL) INSERT INTO #TEMP VALUES(1,'James','George') INSERT INTO #TEMP VALUES(1,'James','Vikas')
INSERT INTO #TEMP2(ID) VALUES(1) INSERT INTO #TEMP2(ID) VALUES(1) INSERT INTO #TEMP2(ID) VALUES(1)
Now i want to get the result as
;WITH CTE AS( SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,* FROM #TEMP ) SELECT CASE WHEN RowNum=1 THEN CREATEDBY WHEN RowNum > 1 THEN ModfiedBy END FROM CTE
But when i convert this select to update, i am missing something...
My update is
;WITH CTE AS( SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,* FROM #TEMP ) UPDATE #TEMP2 SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY WHEN RowNum > 1 THEN ModfiedBy END FROM CTE WHERE #TEMP2.ID=CTE.ID
After running geography::Point(Latitude, Longitude , 4326) on the latitude and longitude provided for each location, my Geography column for each row is populated with the following:
Hi, I am working on a .dll which need to access .sdf ( sql server mobile db). In my project, I added a reference "System.Data.SqlServerCe.dll". The dll is located in C:Program FilesMicrosoft Visual Studio 8Common7IDE. I am able to compile the project. Then I created a Unit project for this dll file. In Unit project, I added the same reference. Now the magic things happen. The compiling was failed. I got "Error 1 The type 'System.Data.Common.DbConnection' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. C:VS2005MobileDBUnitMobileDBProjectUnitMobileDB.cs 50 13 UnitMobileDBProject".
I don't understand how come the same .dll could pass in a dll project but failed in unit test project. And I never saw the System.Data.SqlServerCe display in ".Net" Section of Add reference. But in my office, I installed same version of VS2005 for software tester. I could see the System.Data.SqlServerCe display in the ".Net" section of Add Reference.
I am currently developing my first database driven application and I have stumbled over some quite simple issue. I'll describe my database design first: I have one table named images(id (identity), name, description) and one table named albums (id, name, description). Since I'd like to establish a n:n connection between these, I defined an additional table ImageInAlbum (idImage, idAlbum). The relation between these tables works as expected (primary keys, foreign keys appear to be ok).
Now I'd like to insert data via a stored procedure in sql server 2005 and I'm not sure how this procedure will look like. To add a simple image to a given album, I am trying to do the following: * Retrieve name, description from the UI * Insert a new row into images with this data * Get the ID from the newly created row * Insert a new row into "ImageInAlbum" with the ID just retrieved and a fixed Id from the current album.
I know how I would do the first two things, but I am not used to Stored Procedures syntax yet to know how to do the other things.
Any help is appreciated ... even if it means telling me that I am doing something terribly wrong
I am using a BCP format file to import a CSV file. The file looks like the following:
"01","02"
The format file looks like the following:
6.0Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â SQLCHARÂ Â Â Â 0Â Â Â Â Â 0Â Â Â Â Â Â """Â Â Â Â Â Â Â Â 0Â Â Â "" 2Â Â Â Â Â SQLINTÂ Â Â Â Â Â 0Â Â Â Â Â 0Â Â Â Â Â Â "",""Â Â Â Â Â 1Â Â MROS 3Â Â Â Â Â SQLINTÂ Â Â Â Â Â 0Â Â Â Â Â 0Â Â Â Â Â Â "" "Â Â Â 2Â Â MROF
When both the two fields are set to SQLCHAR data types the data imports successfully without the quotes as 01 and 02. These fields will always be numbers and I want them as integers so I set the data type to int in the database and SQLINT in the format file. The results was that the 01 became 12592 and the 02 became 12848. where these numbers are coming from?
I am staring to lose my mind on all this "work-around" BS in trying to create a dynamic header.
I have a report with contains a table, whose first two lines are a header line in which I want to repeat on each page. Then I have about 20 detail rows of information related to the header. The 21st detail row is a merged cell that contains a subreport relating to the header item.
When printing to PDF, the detail rows consume about half the page and the subreport is anywere from half a page to several pages long. When the subreport is more than half a page, it skips the end of the first page and starts on the top of the next page, leaving the bottom half of the first page blank. No matter what I set the page breaking to, it continues this behavior. In addition, when the subreport data spans more than one page, my parent table's header row will not print to the next page even it the RepeatOnNewPage is set to true.
I am coding this way because I have found no good way of creating a dynamic header that will correctly and consistantly display data that has detail that spans more than one page.
persn_key  persn_id  address   is_active  updated_date 1         10       NYC     0       2015-11-04 14:19:54.817 2         10      Chicago    1      null
and Fact table like
fact_key  persn_key units_purchased 1         1       10
persn_key is the surrogate key between tables.
My question here is as the dimension has SCD type 2 on it and every time when there is a change the persn_key gets a new key value but the fact table still points to oldest key.how to update the surrogate key on fact table to the current key value? As per the requirement fact surrogate key must be pointing to current active record on the dimension.
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
I'm not sure if this could be done, but if anyone has any insight on how to do this please let me know...
Currently, I have a table that has a field of Categories. I recently created a Category table in which each category has it's own ID. I need to replace the Data that was in my original table with the new ID's based on the actual category name... Is there any way of doing this without manaul data entry?
I'm not sure if this could be done, but if anyone has any insight on how to do this please let me know...
Currently, I have a table that has a field of Categories. I recently created a Category table in which each category has it's own ID. I need to replace the Data that was in my original table with the new ID's based on the actual category name... Is there any way of doing this without manaul data entry?
Hi AllWonder if you could help, I have a bog standard table called STOCKPRICESthat has served me well for a while, but now I need to change the structureof it and because a number of users have used it in it's present form I needto so the following in SQL script:a) Grab a snapshot of the current SQL data.b) Re-structure the STOCKPRICES table.c) Post this grabbed data back, but in the new format.My script plan was to firstly to rename the current STOCKPRICES table toSTOCKPRICESOLD (you can do this can't you), create a new STOCKPRICES tablein the new format and then somehow extract the data from STOCKPRICESOLD andsquirt it into STOCKPRICES.The current schema for STOCKPRICES is as follows:# --------------------------------------------------# Table structure for table 'STOCKPRICES'# --------------------------------------------------DROP TABLE IF EXISTS `STOCKPRICES`;CREATE TABLE `STOCKPRICES` (`STOCKID` VARCHAR(30),`CURRENCYID` VARCHAR(30),`HDNETAMOUNT` DECIMAL(10,3) DEFAULT 0,`HDTAXAMOUNT` DECIMAL(10,3) DEFAULT 0,`RRPNETAMOUNT` DECIMAL(10,3) DEFAULT 0,`RRPTAXAMOUNT` DECIMAL(10,3) DEFAULT 0,`NETAMOUNT` DECIMAL(10,3) DEFAULT 0,`TAXAMOUNT` DECIMAL(10,3) DEFAULT 0,INDEX `indxCUURENCYID` (`CURRENCYID`),INDEX `indxSTOCKID` (`STOCKID`));Like I said it's very basic.My new table wants to be like the following:# --------------------------------------------------# Table structure for NEW table 'STOCKPRICES'# --------------------------------------------------DROP TABLE IF EXISTS `STOCKPRICES`;CREATE TABLE `STOCKPRICES` (`STOCKID` VARCHAR(30),`CURRENCYID` VARCHAR(30),`PRICELEVELID` VARCHAR(30),`NETAMOUNT` DECIMAL(10,3) DEFAULT 0,`TAXAMOUNT` DECIMAL(10,3) DEFAULT 0,INDEX `indxPRICELEVELID` (`PRICELEVELID`),INDEX `indxCUURENCYID` (`CURRENCYID`),INDEX `indxSTOCKID` (`STOCKID`));The new re-structure means that PRICELEVELID will include a unique referenceto the HD, RRP, standard prices (plus 3 others that I'm going to create).I know this probably very simple data architecture to you guys, but I'm sureyou can appreciate why I need to change the structure to this method so thatI'm not creating redundant data fields if the user only enters a standardprice I won't be storing nothing for the 2 x HD and 2 x RRP price fields.I don't think I've got a problem renaming the old one and re-creating thenew one, but how do I get the data from one to another?My problem is that I have:code, currency, hdnet, hdtax, rrpnet, rrptax, net, taxIVP GBP 2.00 0.35 200.00 35.00 100.00 17.50etc...and I need to get it into the format:code, currency, pricelevelid, net, taxIVP GBP hd 2.00 0.35IVP GBP rrp 200.00 35.00IVP GBP standard 100.00 17.50etc...Any ideas?RgdsLaphan
I've created C#.net program (behind code style). when I run it in Internet explorer, the following error occurs in IE window. pls instruct me how to handle and correct this error. And how to initialize the connectionstring... Great thank!
Server Error in '/' Application. -------------------------------------------------------------------------------- The ConnectionString property has not been initialized. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [InvalidOperationException: The ConnectionString property has not been initialized.] System.Data.SqlClient.SqlConnection.Open() +809 CodeBox.BehindCode.getSubject() +80 CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Page.ProcessRequestMain() +724 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0
I have a simple while process to use with a trigger to insert values into another table. IN VB this was simple but the while in TSQL seems a little different. If anyone can point out my flaw greatly appreciated.
while @cnter < @nodays --insert values insert into table values (value1, value2) --then increment counters and repeat set @sdate = @sdate + 1 Set @cnter = @cnter + 1 How or what is the best way to loop back?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped How do you think..and why ?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped How do you think..and why ?
Im new to SQL so please bear with me & help me as to why Im not getting the desired results.
I want to find the difference between two sets of tables that reside in different databases but contain the same data. I ONLY WANT a. records that are only in A but not in B b. records that are only in B but not in A ______________________________________________________________________
Here is what I wrote using something that I found in this forum -
CREATE PROCEDURE RPT_DETAILS AS BEGIN DECLARE @Rowcount AS INT DECLARE @First_Name AS VARCHAR(50) DECLARE @Last_Name AS VARCHAR(50) DECLARE @Id AS INT
CREATE TABLE #Prowess(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50)) CREATE TABLE #SDK(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))
INSERT INTO #Prowess SELECT bb.beenumber, be.FirstName, be.LastName FROM beebusiness bb join beeentity be on bb.beebusinessguid = bb.beebusinessguid
INSERT INTO #SDK SELECT cast(sa_ss as INT), first_name, last_name from ml
SELECT @ROWCOUNT = MAX(ID) FROM #SDK PRINT '------------------------------------------------------------------------------------------' PRINT '------------------------COMPARISION REPORT Between Prowess & SDK--------------------------' PRINT '------------------------------------------------------------------------------------------'
PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0 BEGIN SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID FROM #Prowess WHERE ID = @ROWCOUNT PRINT ' * '+@First_Name+@Last_Name SET @ROWCOUNT = @ROWCOUNT - 1 END
SELECT @ROWCOUNT = MAX(ID) FROM #Sdk
PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0 BEGIN SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID FROM #Sdk WHERE ID = @ROWCOUNT PRINT ' * '+@First_Name+@Last_Name SET @ROWCOUNT = @ROWCOUNT - 1 END
declare @var varchar(50) set @var= 'COLUMNNAME' select ID, a.@var , b.@var from rooper a join jim_rooper b on b.id = a.id join b_rooper bb on bb.id = a.id where a.@var != b.@var
All that Im trying to do here is instead of using a columnname, Im trying to substitute it with a variable so that it can be referenced at multiple places...
