hi,
I Have a following SP running on sql 2005:-
DECLARE @MONTHNO int
DECLARE @DB CHAR (8)
DECLARE @CR CHAR (8)
SET @MONTHNO = 2
SET @DB = 'DB_'+CONVERT(NCHAR(2),@MONTHNO)
SET @CR = 'CR_'+CONVERT(NCHAR(2),@MONTHNO)
SELECT (acctno),@CR from bmaster
. The table has a 12 field with cr_1 ... to 12
. the sql can’t understand the @cr the column papers "with No column name'
Hi all
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?
Thank you in advance .....
Regards,
sms
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
View 6 Replies View RelatedI 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!
View 19 Replies View RelatedSelect @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!
View 1 Replies View Relatedneed help with if statment in a stored procedure
here is what i have and it does not work
Code:
CREATE PROCEDURE Get_ckcompany
@cknum int,
@company varchar
AS
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
GO
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
select zsong.song, zsong.trk
from zsong, zfmt
where zfmt.upc='13117' AND zfmt.muzenbr=zsong.muzenbr
That staement works, but if the UPC code is repeated twice it returns the results twice (i want to only read it once)...
how do i make it only return the first result or the last one or whatever (since they all reference the same thing)
Posted - 06/09/2008 : 11:10:47
--------------------------------------------------------------------------------
trying to run the following script.
use 001
select *
from imitmidx_sql
Get incorrect syntax near 001
If I change the use 001 to Data_58 it works fine. Do I need special syntax when the database name is 001??
Hello All,
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.
Is their a sql statment that can do this?
SQL2005
Thanks for your time
is there any chance to use the follwoing script to add new record to the database
"
rs.addnew
rs.("name")="Scot"
rs.update
"
insted of the old fashion " insert into ...."
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
Hi There i have a query that uses conditional Select statment my query is:
Select A,B,C case
when '1' then 'one'
when '2' then 'two'
end
from table
what if i want to include addional condition in the case condition like C case
when '1' and A is null then 'C is one and A is null'
Thank you :D
whats wrong with this statment?
declare @alpha as nvarchar(50)
set @alpha = select top 1 monthyear from dbo.Performance_Indicators_Rolling order by recordkey
I get an error
Incorrect syntax near the keyword 'select'.
Can anyone please help?
I wanted to knw how to get the last 10 SQL Statments issued by the
users client to the SQL Server.
dbcc inputbuffer (spid) gives us the last statement how to get a
history of statments by a SPID.
Hi everyone. I'm very new to SQL but I've been asked to provide a explanation for an SQL statement as part of a presentation I have to do for work.
Yup, how unfair :)
Can anyone help me?
The statement is as follows:
UPDATE company.incidents set Priority = 1 where resltime = 0
I just need to understand the basic functionality of the above. I've tried books and I can't work it out. Any help will be soooo appreciated.
Thanks in advance.
Hi All,
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.
Thanks in advance,
Vishal S
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?
Name Version Episode
John 4c 60
John 4b 50
John 4a 40
John 3 30
John 2 20
John 1 10
Regards
Hi,
I have an error for the following statement:-
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'.
thanks for your help.
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,
Thanks
Wendy
Im trying to get a count of all user logins to display in a report - I have a column, count, which has a value of 1 for each record entered.
Select firstname, lastname, count(count) as TotalLogins Order by TotalLogins. But the count is always appearing as 1.
Report should look like:
John Smith 132
Jane Doe 101
Doris Day 99
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
View 8 Replies View RelatedI'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 ]"
Any ideas why i am getting an error
set dateformat YMD
set datefirst 7
CREATE TABLE #ChildSessions (
siteid integer null
,childid integer null
,sessionid integer null
,sun integer default 0
,mon integer default 0
,tue integer default 0
,wed integer default 0
,thr integer default 0
,fri integer default 0
,sat integer default 0)
declare @firstofweek as datetime
declare @lastofweek as datetime
--select datepart(dw,getdate())
select @firstofweek=cast(floor(cast(dateadd(day,(-1*datepart(dw,getdate())+1),getdate()) as float)) as datetime)
select @lastofweek=dateadd(minute,-1,dateadd(day,7,@firstofweek))
declare @myday integer
set @myday=0
while @myday<7
BEGIN
INSERT INTO #childSessions
SELECT
c.siteid
,c.childid
,sg.sessionid
,case @myday WHEN 1 THEN 1 ELSE 0 end
,case @myday WHEN 2 THEN 1 ELSE 0 end
,case @myday WHEN 3 THEN 1 ELSE 0 end
,case @myday WHEN 4 THEN 1 ELSE 0 end
,case @myday WHEN 5 THEN 1 ELSE 0 end
,case @myday WHEN 6 THEN 1 ELSE 0 end
,case @myday WHEN 7 THEN 1 ELSE 0 end
FROM
child c
,sessionAttendance sa
,session s
,sessiongroup sg
WHERE
c.childID = sa.childid
AND c.siteid = sa.siteid
AND c.active = 1
AND c.potential = 0
AND s.identityid = sa.identityid
AND s.siteid = sa.siteid
AND sg.sessionid = s.sessionID
AND sg.siteid = s.siteid
AND s.dayofweek = @myday
AND @firstofweek <= sa.dateTo
AND @lastofweek >= sa.dateFrom
SET @myday=@myday+1
END
SELECT
c.forename,
c.surname,
sg.sessionname,
CASE (sum(sun)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(mon)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(tue)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(wed)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(thr)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(fri)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(sat)) WHEN 0 THEN ' ' ELSE 'X'
FROM child c,sessiongroup sg,#childsessions cs
WHERE c.childid=cs.childid
AND c.siteid=cs.siteid
AND sg.sessionid=cs.sessionid
AND sg.siteid=cs.siteid
GROUP BY c.forename,c.surname,sg.sessionname
ORDER BY sg.sessionname,c.forename,c.surname
DROP TABLE #childsessions
Msg 102, Level 15, State 1, Line 66
Incorrect syntax near ','.
OK I have my SSIS Dataflow behaving the way it should but how does one map to a table that needs an updatde to a table.
In this case it is an Oracle Table so the SQl would liik like this...
UPDATE PPS_PRINCIPALSSET NAME = @Variable2WHERE (PRINCIPAL_ID = @Variable1)