Sql Statment

Nov 13, 2007

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

 

View 5 Replies


ADVERTISEMENT

Insert And Delete Statment In One Statment

Jan 18, 2008



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

View 15 Replies View Related

Help With Sql Statment?

Jul 11, 2006

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 Related

SQL Not Statment Help

Jul 11, 2007

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! 

View 19 Replies View Related

Need Help With Sql Statment

Apr 14, 2008

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

View 2 Replies View Related

IF Statment

May 16, 2002

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 Related

If Statment

Apr 2, 2007

need 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

View 2 Replies View Related

Need Help With An Sql Statment

May 1, 2008

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

View 4 Replies View Related

SQL Statment

Jun 1, 2006

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)

View 4 Replies View Related

Use Statment

Jun 9, 2008

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??

View 1 Replies View Related

SQL Statment

Feb 18, 2007

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

View 2 Replies View Related

Sql Statment

Aug 17, 2007

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'

View 2 Replies View Related

SQL Statment By Asp.net

Oct 8, 2006

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 ...."





View 1 Replies View Related

SELECT Statment

Mar 20, 2007

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

View 4 Replies View Related

A SELECT Statment, Please Help Me

Oct 2, 2007

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

View 5 Replies View Related

Optimize SQL Statment

Mar 9, 2008

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 

View 1 Replies View Related

Sql Insert Statment

Mar 28, 2008

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
 
  
 

View 7 Replies View Related

Using Date In SQL Statment

Jun 3, 2006

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?

View 2 Replies View Related

Help On Sql Select Statment

Apr 20, 2007

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

View 4 Replies View Related

Using Case Statment

Apr 7, 2008

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

View 3 Replies View Related

Help With Select Statment

May 23, 2008

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?

View 3 Replies View Related

Sql Statment History

Mar 1, 2006

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.

View 2 Replies View Related

Help Explaing An Sql Statment?

May 7, 2006

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.

View 3 Replies View Related

INSERT STATMENT

May 1, 2007

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

View 4 Replies View Related

Help With A SELECT Statment

Jul 17, 2007

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

View 7 Replies View Related

Update Statment

Aug 25, 2007

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.

View 2 Replies View Related

Case Statment - Not Sure If I Should Use It Or Not

Oct 30, 2007

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

View 5 Replies View Related

Help With Select Statment

Mar 8, 2008

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

View 19 Replies View Related

Single Statment

Mar 13, 2008

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

View 1 Replies View Related

Difficult SQL Statment

Jan 25, 2006

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 Related

SQL Statment Error... Why??

Mar 12, 2008

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 ]"

View 12 Replies View Related

CASE Statment

Oct 18, 2007

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 ','.









View 3 Replies View Related

Where Do I Put My SQL Update Statment

Sep 26, 2006

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)

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved