Database

Re: Combining Like and In

15-Sep-08 1:21

ADO.NET

Bob
Ashfield Consultants Ltd

Combining Like and In

Brady Kelly15-Sep-08 0:49

Brady Kelly

15-Sep-08 0:49

I have a requirement to allow a user to search using Staff Number is like, i.e. starts with or ends with, as well as Staff Number is in, i.e. a list of Staff Numbers. Is there some way I could handle these two filter criterion together, or should I simply create an IN query when I detect a list of numbers, and a like query otherwise?

Blue_Boy15-Sep-08 0:54

Blue_Boy

15-Sep-08 0:54

I think query should be something like this:

<br />
select * from tablename <br />
where ([Staff Number] like '%value%')<br />
or ([Staff Number] in ('value'))

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

max date without repeat in query

mahmoudinirat15-Sep-08 0:26

mahmoudinirat

15-Sep-08 0:26

I have a table and this table like the following
id_______ name_________ date
1________mm___________28/3/1999
2________mm___________21/3/1999
3________mm___________25/4/1999
4________nn____________10/8/1999
5________nn____________15/9/1999
6________nn____________2/10/1999

i need the following result

id_______ name_________ date
1________mm___________25/4/1999
2________nn____________2/10/1999

thank you

Re: max date without repeat in query

Blue_Boy15-Sep-08 0:41

Blue_Boy

15-Sep-08 0:41

select [name],<br />
(select top 1 t1.[date] from MyTable as t1 where t1.[name] = MyTable.[name] order by t1.[id] desc)<br />
 from MyTable <br />
group by [name]

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: max date without repeat in query

Ashfield15-Sep-08 1:15

Re: inline table function

15-Sep-08 1:15

try this

select name, max(date)
from table
group by name

Bob
Ashfield Consultants Ltd

inline table function

bdl15-Sep-08 0:22

bdl

15-Sep-08 0:22

I have an inline table function and it takes tow parameters of type VARCHAR.
I functions when I hard code to string within of call like this:
SELECT * FROM dbo.TestTable('a', 'b')

Here is definition of inline table function:

FUNCTION GetIPTimeRangeTable (@StartDate VARCHAR(10) = '', @EndDate VARCHAR(10) = '')
RETURNS TABLE
AS
RETURN
(
SELECT
* FROM UserDataSetsAccess
WHERE 1 = 1
AND CreatedAT between @StartDate AND @EndDate
)

If call function like this:
SELECT * FROM GetIPTimeRangeViewTable('2008-7-1', '2008-7-11')
Every ting is OK but if I call function like this
SELECT * FROM GetIPTimeRangeViewTable('2008-7-1', CONVERT(CHAR(10), GETDATE(),121))
I get error:
Incorrect syntax near the keyword 'CONVERT'.
Do you have any explanation for that!
Tanks

nelsonpaixao21-Sep-08 15:21

nelsonpaixao

21-Sep-08 15:21

try using varchar(10), you there have 8 or 9 char not 10!!!
you can get 8 (1-1-2004), 9 (10-9-2005) or 10 (10-10-2004) chars.

you used getdate(), if you ran it october 10th it will work Laugh | :laugh:

nelsonpaixao@yahoo.com.br

trying to help & get help

copy data from three different tables to one table

justintimberlake15-Sep-08 0:14

Re: copy data from three different tables to one table

15-Sep-08 0:14

Hello all, i have a three tables user,aspnet_user,and aspnetmembership table and i made my table name myapplicationuser which contins colum similer to that of the three tables. Now from Store procedure i want to check those data in tables user,aspnet_user,and aspnetmembership and not in myapplicationuser and insert into myapplicationuser , i.e check if exist if not insert it (from stored procedure)

Ashfield15-Sep-08 1:24

Re: copy data from three different tables to one table

15-Sep-08 1:24

Try using IF EXISTS statement.

IF Not exists (select 1 from tablex where columny = 'z')
begin
  insert ....
end
else
begin
   update ...
end

Bob
Ashfield Consultants Ltd

justintimberlake15-Sep-08 1:58

Re: copy data from three different tables to one table

15-Sep-08 1:58

whats wrong with this one why is it opening a new sqlqury ide

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[copyuser]
AS
SET NOCOUNT ON;
DECLARE @iRowId int, @userName NVARCHAR(100)

DECLARE usercursor1 CURSOR FOR
SELECT UserID,Username FROM users
print 'reached here'

OPEN usercursor1
FETCH usercursor1 into @iRowId,@userName
WHILE @@Fetch_Status = 0
BEGIN --loop started
PRINT @userName
IF not EXISTS (SELECT 1 FROM Esso_UserInfo WHERE UserName=@userName)
BEGIN --if started
PRINT 'not exist'
END --if closed
ELSE
BEGIN --else started
PRINT 'exist'
END --else closed
FETCH usercursor1 into @iRowId,@userName
End --loop closed
CLOSE usercursor1
DEALLOCATE usercursor1
RETURN

Ashfield15-Sep-08 2:02

Re: copy data from three different tables to one table

15-Sep-08 2:02

justintimberlake wrote:
whats wrong with this one

No idea. Where does it go wrong?

justintimberlake wrote:
why is it opening a new sqlqury ide

Again, no idea.

A tip, don't use cursors they slow processing down enormously. Try running updates first and then inserting where not exists.

Bob
Ashfield Consultants Ltd

justintimberlake15-Sep-08 2:10

Re: copy data from three different tables to one table

15-Sep-08 2:10

it just opnes a new sqlquery editor and shows return value 0

justintimberlake15-Sep-08 2:26

15-Sep-08 2:26

hey its ok the stored procedure works

Sql Update Trigger

Jesu Prabhu.J14-Sep-08 21:18

14-Sep-08 21:18

Hello everyone,

I need ur help to write a trigger. I have 3 tables (Ex: Employee master,Employee salary details and Employee in_out details),if i update any value in the Employee master table, the value must be automatically updated in other related tables.

Plzzzzzzzzzz help urgent!!!!!!

Thanks.

Re: Sql Update Trigger

Ashfield14-Sep-08 22:19

14-Sep-08 22:19

Jesu Prabhu.J wrote:
Plzzzzzzzzzz help urgent!!!!!!

Not, its not urgent.

Jesu Prabhu.J wrote:
I need ur help to write a trigger.

So write your version and if it doesn't work post it and someone will help. A clue, you need a trigger on Employee for insert and update which will select the values from the inserted table and update the other two tables.

There you go, now google triggers.

Bob
Ashfield Consultants Ltd

Re: Sql Update Trigger

Jesu Prabhu.J14-Sep-08 22:34

14-Sep-08 22:34

I am using SQL Server 2000, If i change any value Ex: Employee Name in the (Employee Master) the value Employee Name must get updated in the other 2 tables..

Thanks for the Reply..

Re: Sql Update Trigger

Ashfield14-Sep-08 23:10

How to store symbols like sigma, integral etc in sql server 2000

14-Sep-08 23:10

Jesu Prabhu.J wrote:
I am using SQL Server 2000, If i change any value Ex: Employee Name in the (Employee Master) the value Employee Name must get updated in the other 2 tables..

Yes, I assumed you were using SQL Server. You still need a trigger on Employee Master. There are hundreds of examples on Google.

Bob
Ashfield Consultants Ltd

Jesu Prabhu.J14-Sep-08 20:49

Re: How to store symbols like sigma, integral etc in sql server 2000

14-Sep-08 20:49

Plzzzzzz help me out with this problem, i have to store special symbols like integral, sigma, pi etc in tha database. I'm using the nvarchar datatype, but getting unexpected results..

Thanks in advance...........

razov15-Sep-08 0:32

razov

15-Sep-08 0:32

I think using nvarchar should be sufficient.

Although you'll not be able to see these characters in 'Query Analyzer' or 'Enterprise Manager' (you will see boxes instead) but when this data is called by client application e.g. C# you can see correct depiction of the character.

Razov

Re: How to store symbols like sigma, integral etc in sql server 2000

Jesu Prabhu.J15-Sep-08 0:38