DECLARE @MONTHNO char
DECLARE @DB CHAR (8)
DECLARE @CR CHAR (8)
DECLARE @batchno int
DECLARE @ACTIVEYEAR int
set @batchno = 5
set @ACTIVEYEAR = 2007
SET @MONTHNO = 2
SET @DB = 'DB_'+CONVERT(NCHAR(2),@MONTHNO)
SET @CR = 'CR_'+CONVERT(NCHAR(2),@MONTHNO)
exec('update bmaster set '
+@DB+'='+@DB+' mysursor.damt,'
+@CR+'='+@CR+' mysursor.camt from bmaster inner join (select transact.year,transact.acctno,'
+' sum(transact.camt) as camt,'
+' sum(transact.damt) as damt,'
+' sum(transact.ccamt) as ccamt,'
+' sum(transact.cdamt) as cdamt'
+' from transact where transact.batchno='+@batchno+ ' and transact.year='+@ACTIVEYEAR
+' group by transact.year,transact.acctno) as mysursor on bmaster.year=mysursor.year and bmaster.acctno=mysursor.acctno')
the error as follows:-
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'mysursor'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
I cannot figure out how to update ufd column from this statment. I can select the columns I want to update but how can I change it to update them from the select?
I have an update statment in my SSIS that use to take 10 minutes in SQL 2000 dts and now its take 1 hour 15 minutes in SQL 2005.
this is my sql update statment - Update WeeklySalesHistory set weekendingdate = (SELECT LastTransDateTime from ReplicationControl where TableName = 'WEEKHST') where weekendingdate is null
It is using ole db connection. About 36,000 records that it is updating.
I have read ole db can be slow and to use staging table. Does that mean on all updates like this I have to use a staging table and then insert. I didn't use to have to do this in SQL 2000. Has it changed. Are there any other options?
UPDATE GOLDIE SET GOLDIE_ID = (SELECT *, SUBSTRING(GOLDIE_ID,1, CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0 THEN 0 ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1 end) STRIPPED_COL FROM GOLDIE_ID)
Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.
I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior. For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this? Thank you
where I can look for the user,who execute this statment?
It was 6.02.2007 or 7.02.2007. All users connect using SQL Serwer Authentication. It is test database and from 1 to 3 users connect to it, and submit not more then 10 SQl statmant per day.
I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?
I cant find the error in this sql statment. What I want it to do is return everything from book, locations.locationname, and author.fullname. I want to to only return the first 10 rows. This will be used in paging, so, eventually it will be first 10, than 11-20, etc. Heres what I have, without the row limitSELECT RowNum, book.*, locations.LocationName, author.fullname FROM (SELECT book.*, locations.LocationName, author.fullname ROW_NUMBER() OVER(ORDER BY book.id) as RowNum FROM book INNER JOIN Author ON book.AuthorID = Author.ID OR book.AuthorID2 = Author.ID OR book.AuthorID3 = Author.ID OR book.AuthorID4 = Author.ID OR book.AuthorID5 = Author.ID INNER JOIN Locations ON book.LocationID = Locations.ID WHERE (Author.FullName LIKE '%' + @Search + '%') OR (Author.FirstName LIKE '%' + @Search + '%') OR (Author.LastName LIKE '%' + @Search + '%')) as BookInfo
I have two tables. One for videos and one for a "block list"Videos : VideoID UserID VideoURL VideoTitle etc. VideoBlockList :VideoID UserIDI have a datalist to show the videos but i want for the datalist to miss out any videos that a user can not see.So if the block list has "VideoID" = 2 and UserID = 1 if user 1 does a search then the search will skip out the video 2.how is this done? i tryed to do it using a specific / custom SQL statment but it errored cos the NOT value conflicted with the search... any ideas? thanks in advance si!
Select @ID = top 1 ID From Contents Where Contents.UserID=@UserID And Contents.Status=4 AND Contents.InEdit=1 why it dosn't get back the ID vlaue but when i remove the top1 one its work well why
Hi I have two tablesTABLE 1 named problemas with the field N_problem (numeric)Tabe 2 named resolvidos with the field Resol (numeric) How can i select all the records from table 1 who are not in Tabe2 ? Thank you mario
Does anyone know how to write a statement in SQL Server that is similiar to Microsoft Access's IIF function. Im not quite sure how the syntax works in a SQL Server IF statement. Thanks!
if (@cknum is not null) and (@company is null) select * from PMTK_tbl where Company = @company
else if (@compnay is not null) and (cknum is null) select * from PMTK_tbl where check_Num = @cknum else select * from PMTK_tbl where Check_Num = @cknum and Company = @company end if
I have a database used for a point of sale system it has a main table with the total amount of a check - the tip the tip is save in a different table that holds payments the issue is i need to make a statement that finds checks based of the total with the tips added. the table that holds tips can have more then one tip because you can have multiple payments on a check.
The statement i have now is like this
select jc.total + sum(jp.tip) as total from jc innerjoin jp on key where total <= @max and total >= @min group by jc.total
but this statement uses total before the tip is added. I am not sure how else to do this any help would be great
I imported a excel file from SSIS and created a table called Lockbox. To avoid the user from having to change the excel file -it is being imported as is. I only need 4 fields: [Contract ID] , [Check Number], [Owner ID], [Site ID]
The table I need to import to Transaction has Diffrent Column Names -ex-CustomerID, ResortID. The columns are in diffrent order. And I need to add more information into them like UserID = 'Hwells', Trantype = 'MF' and convert to a diffrent data type [Site ID] to text.
Hello, Is there an SELECT statement to just return the last 100 row in my tables? I have about 500 rows in my tables and I only need the info on the last 100 rows. Thanks Steve
hello, i have a page "Picture.aspx?PictureID=4" i have a FormView witch shows details about that picture and uses a stored procedure with input parameter the "@PictureID" token from query string the Pictures table has among other rows "PictureID", "UserID" - uniqueidentifier - from witch user the picture belongs to i have a second FormView on the same page, witch should show "other pictures from the same user" and uses a Stored Procedure how should i write that stored procedure...frist to take the UserID from the picture with PictureID=4, then to pass it as input parameter and select the pictures witch has as owner the user with that UserID, and if can be done, to avoid showing the PictureID=4 again a solution should be to add at querry the UserID too, but i want to avoid that any sugestion is welcomed, please help me THANKS
Hello, I have three sql select statments I would like to combine into one. I have created a statment that works but I am not sure if it is a good solution performance wise. Is there a better way to run this query? Thanks Very Much!! if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate) Begin Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate End else if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight From SiteIndex Where nextupdate < GetDate() Order By importance desc) begin Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight From SiteIndex Where nextupdate < GetDate() Order By importance desc end else Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight From SiteIndex Order By nextupdate
i have 3 tables in sql the relation between them is one to one person ==> employee ==> sales_department_stuff& another relation ( one to one )between person(the same as above ) ==> customer i want to put person id in employee not repeated in customer becase all (employee and customer is a person ) sooooooo i put this sql statment to try to insert person id in employee then in sales_department_stuff table to complet his ordersinsert into Person (Person_Name_Ar,Person_Name_En) values ('aaaaa',' ssssssss')select @@identity from Personinsert into Employee values (@@identity,'1') select @@identity from Personinsert into sales_department_Staff values (@@identity,'1')select @@identity from Personinsert into Driver values (@@identity,'2','2222','1/1/2005','5') /* SET NOCOUNT ON */ RETURN
the first 3 statment is run and success but underline stetment return error that he can insert null value in id field ,,,,,,,,,,,,,,,, he can compile @@identity in these statment ?????????????? plz help me
Hello All; I am using an sql statement to pass only the year using DatePart function, but i receive the following error.
curYear = DatePart(DateInterval.Year, Now) Dim Date1 = "'" & curYear & "/01/01'" Dim Date2 = "'" & curYear & "/31/12'" Dim strAll As String = "SELECT * FROM Boxes WHERE EntryDate BETWEEN Convert(DateTime, " & Date1 & ") And CONVERT(DATETIME," & Date2 & ")" The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. What is the cause, please?
Okay here is the deal. I need to take all data from tbl 1 and match it to data in 3 other tbls. I need to have it return everything back to me even if it is null.... IE tbl 1 I match the invoice_num to tbl2 site_id and then tbl2 marekt to tbl3 market. however even if tbl1 invoice_num dose not match tb2 site_id I still need to have it retun to a null value to the site_id. Here is what I have so far. This will return everything where the invoice_num and site_id match.
Code:
Select distinct t1.ID,t1.Deposit_date,t1.Ref_Num,t1.Company,t1.check_num,t1.Check_Date,t1.Check_Date,t1.Check_Total, t1.Individual_PMT,t1.Invoice_Num,t1.Invoice_Desc, t2.site_id,t3.CompanyCode,t3.CostCenter From( PMTK_tbl as t1 Left Join Leaseinfo as t2 on t2.site_id = t1.Invoice_Num) inner Join CostCenters AS t3 on t2.market = t3.market and t2.market_region = t3.RegionCode
I have a table "Person" that has two columns "FirstName" and "LastName". How can I insert multiple rows into this table using the INSERT stsmt. I want to use just one insert statement.
Hi all, i'm new to SQL but i've been asked to write an SQL statement to select the latest numeric version value(in this case version 3) from this table, any help?
