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 limit
SELECT 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 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 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?
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 am trying to have all these calulcation int one column under AccessFeeFinal, but it is not working. It was working when I just had the one case statement, but we needed to add another one in because we were getting some blank info in the column. Below are the two statement then I added a third on to show you how I think it should be, but it is not working for me. Please help me out if you can. I was thinking maybe I needed to use an IF Statement.
CASE clm_att1 WHEN 'NG' THEN (clm_H30)*(clio_fee04/100) WHEN 'NA' THEN '0.00' WHEN 'AF' THEN (clm_H30)*(clio_fee04/100)*.65 ELSE Null END as AccessFeeFinal,
CASE clm_att1 WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100) WHEN 'NA' THEN '0.00' WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65 ELSE Null END as AccessFeeFinal,
CASE clm_att1 WHEN 'NG' THEN (clm_H30)*(clio_fee04/100) WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100) WHEN 'NA' THEN '0.00' WHEN 'AF' THEN (clm_H30)*(clio_fee04/100)*.65 WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65 ELSE Null END as AccessFeeFinal,
iam having two statement for updating one table. just i need to club into one statment.can any one tell how this can be achieved.
first statement: =================== --Updating Customer code update stg set stg.customer_code=dtl.cgd_customer_code from Subsidiary_Ageing_tb stg(nolock) inner join fin_ods..cust_group_dtl dtl(nolock) on dtl.cgd_cust_group_code=stg.customer_grp
second statment: =================== --updating customer name update stg set stg.customer_name=dtl.clo_cust_name from Subsidiary_Ageing_tb stg(nolock) inner join fin_ods..cust_lo_info dtl(nolock) on dtl.clo_cust_code=stg.customer_code
Hello !I habe 2 TablesTable1: OrdersFields: Ordernr, OpiecesTable2: CalloffsOrdernr, CpiecesIn Table1 ordernr is primary key.In Table2 the same ordernr can exist oftenMy problemIf the sum(Cpieces) < Opieces:I have to create a new virtual calloffwith Cpieces = opieces - sum(cpieces)Its too high for me.Please helpBest regardsaaapaul
I'm trying to run the following query and getting an error as a follows, can someone help me fix this...?
Code Snippet
Public Sub PopulateListBox() Try Dim cmd As SqlCeCommand = conn.CreateCommand() cmd.CommandText = "SELECT DISTINCT names FROM addresses"
Dim resultSet As SqlCeResultSet resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable Or ResultSetOptions.Updatable) DataGrid1.DataSource = resultSet Me.ListBox1.DataSource = resultSet Catch ex As Exception MsgBox("Error getting names.") End Try End Sub
?err.Description "Cannot generate a keyset cursor for the query because there is a DISTINCT operator. [ Cursor Option = 3 ]"
