how to control the number of row?

i want to contorl the output number of row with a certain number when select data from table that not exceed the number with my setting.

For example,if the result is exceed the 500 rows, then i just require 200 rows, that what is the command of this function?

Thanks for any help...

You can try something like:

SELECT top 500
...
from TableName

another more archaic form is to execute

SET ROWCOUNT 500

However, this will also limit the number of records update or inserted, deleted etc. Turn off the limit by:

SET ROWCOUNT 0

The TOP option is in general the better option. Look the two up in books online.

sql

How to construct SQL SELECT statement

I want to search an entire table for a particular keyword but i'm not sure how, if the keyword was TEST then I want to return rows where any of the fields contain TEST, THIS IS A TEST, PLEASE TEST THIS etc etc i.e. the keyword can be anywhere in the fields value

I believe I need to use the LIKE clause but i'm not sure how.

Thanks

BenSelect * From Table Where Field Like '%' + sSearch.Replace("'","''") + '%'|||Hi Ben,

You're right, you can use the LIKE clause,
E.g.
SELECT * FROM Students WHERE StudentName LIKE '%NEW%'

Then the matched records include: HAPPY NEW YEAR and ASP.NEW :)

Regards,|||Hi Colt, thanks for that

If I wanted to apply the search to more than one fields could I use

SELECT * From Students WHERE StudentName, Field2, Field3, Field4 LIKE '%NEW%'

Is that the correct syntax?

Ben|||Hi,

You may try:

SELECT *
From Students
WHERE StudentName LIKE '%NEW%' OR
 Field2 LIKE '%NEW%' OR
 Field3 LIKE '%NEW%' OR
 Field4 LIKE '%NEW%'

Regards,

How to construct a like compariso when there are special chara

select *
from
(
select 'abc[Y]def' as c1
union all
select 'abcdef' as c1
) as t1
where c1 like '%\[Y\]%' escape ''
go
AMB
"Alejandro Mesa" wrote:
> bic,
> use a scape character.
> Example:
> select *
> from
> (
> select 'abc[Y]def' as c1
> union all
> select 'abc[Y]def' as c1
> ) as t1
> where c1 like '%\[Y\]%' escape ''
> go
>
> AMB
> "bic" wrote:
>"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149792727.734355.86450@.u72g2000cwu.googlegroups.com...
> Another way is to double the brackets
> select *
> from
> (
> select 'abc[Y]def' as c1
> union all
> select 'abcYdef' as c1
> ) as t1
> where c1 like '%[[Y]]%'
> go
>
Be careful with the bracket doubling thing - it can get confusing on complex
comparisons with lots of [ ]'s in them.

How to connect to a SQL view NOT table

Normally i can use Web Matrix to connect to a SQL table. e.g.
Dim queryString As String = "SELECT [MyTable].* FROM [MyTable]"
Is is possible to connect to a SQL View. The reason is because i have generated a view using UNIONS and JOINS and some columns are generated by concatenating columns together (ie.. has alias columns).
I also need to use VB.NET to refer to these alias columns.

hy..
I suppose it's the same as that in a table.
Dim queryString As String = "SELECT * from [MyView]"
...|||

Hey,

I think you can just use 'SELECT [MyView].* FROM [MyView]' as a query.
We use views here, we query them like they were tables, so ...
If you still have problems with it, let me know and i'll look into it a little deeper

how to concatenate the xml returned in different rows, and different select statements

hi
How would I write my query to concatenate the xml segment returned from
different rows, and from different select statements
Look at FOR XML...
E.g.,
declare @.x1 xml, @.x2 xml;
set @.x1 = '<a/>';
set @.x2 = '<b/>';
select @.x1, @.x2 for xml path(''), type
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169742062.183408.76500@.j27g2000cwj.googlegro ups.com...
> hi
> How would I write my query to concatenate the xml segment returned from
> different rows, and from different select statements
>
|||I would recommend you go with FOR XML, as Michael Rys suggested. However, if for
some reason you're unable or not willing to use that method, you can always
convert your instances to a string type, concatenate them, and convert back to
XML
Denis Ruckebusch
http://blogs.msdn.com/denisruc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169742062.183408.76500@.j27g2000cwj.googlegro ups.com...
> hi
> How would I write my query to concatenate the xml segment returned from
> different rows, and from different select statements
>

how to concatenate recordsets

is it possible to concatenate 2 recordsets ?

i have 2 select, and i need then to be displayed one after other.

using union all the results are gathered, ordered like only one select.

thanks for all

You can do this in your SQL query using a union, provided the schemas are identical.

In code, if you have two seoearet recordsets and want to join then, programatically create a datatable and add both these result sets to the new datatable by iterating over them.

Hope this helps
Chris

|||

UNION ALL is the what you need. Here is an introduction for UNION and UNION ALL:http://www.w3schools.com/sql/sql_union.asp

|||

ixxx:

is it possible to concatenate 2 recordsets ?

i have 2 select, and i need then to be displayed one after other.

using union all the results are gathered, ordered like only one select.

thanks for all

What I understod from post is, you asked a question and provided its answer at the same time!

Here is the questoin (you wrote):

ixxx:

is it possible to concatenate 2 recordsets ?

i have 2 select, and i need then to be displayed one after other.

and here is the answer you wrote as well (which is the correct answer :) ):

ixxx:

using union all the results are gathered, ordered like only one select.

I guess there is something missing that you need us to help you with (other than just the UNION).[just a guess becuase you asked the question and answered it in the same post!].

Anyway,,, we are here to help you.

Good luck.

I

|||

"using union all the results are gathered, ordered like only one select."

My guess what the OP means:

using union, all the results are gathered, ordered like only one select.

Not

using union all, the results are gathered, ordered like only one select.

|||

yes that is it

using union, all the results are gathered.

using union all, i get duplicated rows.

thanks for all

How to concate 2 ore more text fields into one field?

Hello,

I hope someone has already done this, but I have a table with a text column- example ColA, now i want to run a query to select the ColA in this table and combine the results of ColA into a ColB in another table.

Something like - Note: the codes below doesn't work!!

DECLARE @.ResultID as int
DECLARE @.AccID int
DECLARE _rows CURSOR
FOR SELECT AccID FROM tableA

FETCH NEXT FROM _rows INTO @.AccID

WHILE (@.@.fetch_status <> -1)
BEGIN

UPDATE TableB SET Report = Report + (SELECT txtField FROM tableA WHERE AccID = @.AccID)

WHERE AccID = @.AccID

FETCH NEXT FROM _rows INTO @.AccID

END

Thanks in advance

I tested something like this:(Assume you are using SQL Server 2000 text fields)

You can try it out in your code.

DECLARE @.ptr varbinary(50),@.ptr2 varbinary(50)

DECLARE @.len int, @.len2 int

update Table_B SET colB=(SELECT colA FROM Table_A where Accid=1) WHERE Accid=1

select @.ptr = TEXTPTR(colB), @.len=datalength(colB), @.ptr2 = TEXTPTR(report), @.len2=datalength(report) from Table_B WHERE Accid=1

UPDATETEXT Table_B.report @.ptr2 @.len2 0 Table_B.colB @.ptr

In SQL Server 2005, you can define Varchar(MAX) field, it will be a lot more easy to manipulate by using .WRITE function.

|||

Thanks Limno,

Sorry I could reply this sooner. This is one slick trick - appreciated.

However, I couldn't get this working as the way you have. Not sure what I am missing.

I kept having the error

Server: Msg 7116, Level 16, State 4, Line 13
Offset 17 is not in the range of available text, ntext, or image data.
The statement has been terminated.

DECLARE @.ResultID as int
DECLARE @.AccID int
DECLARE @.Heading varchar(255)
DECLARE @.ptrReport varbinary(16)
DECLARE @.ptrTmpField varbinary(16)
DECLARE @.Len1 int, @.Len2 int

SELECT @.ptrTmpField = TEXTPTR(tmpField), @.Len1 = Datalength(tmpField),
@.ptrReport = TEXTPTR(Report), @.len2 = Datalength(report)
FROM PHILTBL2 WHERE AccID = 1624728

UPDATETEXT PHILTBL2.Report @.ptrReport @.len2 0 tmpField @.ptrTmpField

Thanks

|||

If I changed the updatetext statement as

UPDATETEXT PHILTBL2.Report @.ptrReport @.len2 0 @.ptrTmpField

The error went away, but I get gebrish text in my report field.

Look like the textptr doesn't work

|||

Check this link that Jared Ko provided earlier today:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

|||

Thanks CetinBasoz, but the the problem I am dealing with is the text field and it's a different animal than varchar field. :-(

Check here for more info

http://msdn2.microsoft.com/en-us/library/ms189466.aspx

|||

Please see below scripts for some examples on how to use UPDATETEXT with TEXTPTR.

-- Script #1

/* To concatenate several binary values into one image field: */
create table #Bin ( SegmentID int IDENTITY ( 1 , 1 ) , Segment varbinary(2) )
insert #Bin values (0x11)
insert #Bin values (0x22)
select * from #Bin
go
declare @.imageptr varbinary(16), @.segmentid int, @.segment varbinary(16)
create table #AllBin ( Segments image null )
-- Get valid pointer first
insert #AllBin values ( 0x0 )
select @.imageptr = TEXTPTR( Segments ) from #AllBin
-- Set data to null
update #AllBin set Segments = null

select @.segmentid = -1
while(1=1)
begin
select @.segmentid = min(segmentid)
from #Bin
where segmentid > @.segmentid
if @.segmentid is null break

select @.segment = segment from #Bin where segmentid = @.segmentid

updatetext #AllBin.Segments @.imageptr null 0 @.segment
end
select * from #AllBin
go
drop table #Bin
drop table #AllBin
go

-- Script #2

create procedure #t (
@.t1 text , @.i1 image, @.t2 text, @.i2 image, @.t3 varchar(30), @.i3 varbinary(2)
)
as
declare @.tptr varbinary(16), @.iptr varbinary(16), @.tpos int, @.ipos int
create table #blob(id int identity, t text, i image)

insert #blob values(@.t1, @.i1)
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob

update #blob set t = @.t2, i = @.i2 where id = @.@.identity
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob

select @.tptr = TEXTPTR(t), @.tpos = PATINDEX('%TEXT...%', t) - 1,
@.iptr = TEXTPTR(i), @.ipos = 2
from #blob

updatetext #blob.t @.tptr @.tpos 0 @.t3
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob

updatetext #blob.i @.iptr @.ipos 1 @.i3
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob
go

exec #t 'SOME TEXT HERE...', 0x02498765bcde3,
'MODIFIED TEXT...', 0xab86ec64,
'(INSERT BEFORE TEXT) ', 0xcd -- replace 3rd byte

-- Inserted text & image value
/* id text_val image_val
-- -- --
1 SOME TEXT HERE... 0x002498765BCDE3
*/

-- Updated text & image value. This one replaces the existing values
/*
id text_val image_val
-- -- --
1 MODIFIED TEXT... 0xAB86EC64
*/

-- Modified text value only. This one inserts some text into the existing value
/*
id text_val image_val
-- -- --
1 MODIFIED (INSERT BEFORE TEXT) TEXT... 0xAB86EC64
*/

-- Modified image value only. This one changes a byte in the existing value
/*
id text_val image_val
-- -- --
1 MODIFIED (INSERT BEFORE TEXT) TEXT... 0xAB86CD64

*/
go
drop proc #t
go

Also, here is a link to a SP that show how to generate text value from multiple strings.

|||Sorry my bad. When I wrote it was around 3 AM here.

how to concat/pivot rows to column?

hello,

I'm wondering how it's possible to have a select statement resultant rows concatenated into one row and column.
For example:
select letter from alphabet_table
a
b
c
d
e
...
26 rows returned.

Other than a cursor, how would I write a query to return the following:
row1: abcdefghijkl...

thanks in advance!There are a number of ways, none of which is truly generic (ie there isn't a "one size fits all" choice). Without understanding both what lead you to want to concatenate these values (and what rules you use to concatenate them), and what you will do with the concatenated result, I can't give you much useful advice.

-PatP|||Originally posted by Pat Phelan
There are a number of ways, none of which is truly generic (ie there isn't a "one size fits all" choice). Without understanding both what lead you to want to concatenate these values (and what rules you use to concatenate them), and what you will do with the concatenated result, I can't give you much useful advice.

-PatP

PatP, thanks for your reply. After posting I realize I should have included more information.
Here's more specifics:
CREATE TABLE [elements] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) NOT NULL ,
[description] [varchar] (50) NULL ,
[code] [varchar] (5000) NOT NULL ,
[ord] [int] NOT NULL
) ON [PRIMARY]
GO

elements.code contains html tags, such as table, tr, td. I am using a stored procedure to build html code based on an input parameter. The parameter matches the 'name' column.
so to build a table, i would select the code and order by the ord column. the result is similar to the following:
<table width="100%" border="0">
<tr>
<td>
</td>
<td>
</td>
<td>
</td>
</tr>
</table>
(10 rows).
I would like to query the table based on the parameter passed to return the same results, except in one record:
<table width="100%" border="0"><tr><td></td><td></td><td></td></tr></table>
(1 row).

hope this helps clear it up|||That helps a bunch. The biggest problem that I see is that you can't allow your html table definition to exceed 4000 characters if you use 16 bit characters (aka UTF-8), or 8000 characters if you use 8 bit (OEM) characters. This could be a real problem for complex pages.

With that said, I'd start with:CREATE FUNCTION dbo.tableDef(@.name AS VARCHAR(50) RETURNS VARCHAR(8000) AS BEGIN
DECLARE
@.c VARCHAR(5000)
, @.r VARCHAR(8000)

SELECT @.r = ''
DECLARE z1 CURSOR FOR SELECT [code]
FROM [elements]
WHERE name = @.name
ORDER BY ord

OPEN z1
FETCH z1 INTO @.c

WHILE 0 = @.@.fetch_status
BEGIN
SET @.r = @.r + @.c
FETCH z1 INTO @.c
END

CLOSE z1
DEALLOCATE z1

RETURN @.r
END-PatP|||Oh yeah, usage would help, wouldn't it ? Sorry!SELECT [name], dbo.tableDef([name])
FROM [elements]
GROUP BY [name]-PatP|||Originally posted by Pat Phelan
Oh yeah, usage would help, wouldn't it ? Sorry!SELECT [name], dbo.tableDef([name])
FROM [elements]
GROUP BY [name]-PatP

many thanks, Pat. i was hoping there was a 'simpler' method of reaching this goal. sometimes i wish i could rewrite ms's implementation of the ansi select to include special tricks.
like: select + * from blah would concat results. ;)

i'll let you know how it works, i'm not too worried about the 4/8k character limit, i can always have a couple of columns.

thanks again.

how to compare Time......... using DateTime field

hi guyz i want to compare time from DateTime field i.e. i want to identify if the time is from 1pm to 2pm the do this else do.....

select DATEPART(hour, loginTime) .....returns me the hour i can get the Hour part of the time but the prblem is how to identify it

whether it is less than 2:00:00 pm and greater than 1:00:00 pm i can do this task using at application level but i want this to b done at query level

any ideas????

i used following query..

my table is like Finddate

id bdate

1 2007-01-01 13:30:00.000
2 2007-01-01 14:30:00.000
3 2007-01-01 14:20:00.000
4 2007-01-01 23:30:00.000
5 2007-01-01 22:30:00.000

and i am selecting the records between 1 to 2 pm.

1 pm means 13 and 2 pm means 14, so i written query

select*from finddatewheredatepart(hour,bdate)>=13anddatepart(hour,bdate)<=14

this will gives me result as

id bdate

1 2007-01-01 13:30:00.000
2 2007-01-01 14:30:00.000
3 2007-01-01 14:20:00.000

hope this will help u..

|||

hi Mahadeomatre thnx for ur help i got the solution,ur query should b like this.

select*from finddatewheredatepart(hour,bdate) >=13anddatepart(hour,bdate)<14

then according to ur table it wil return only 1st row

1 2007-01-01 13:30:00.000

and "datepart(hour,bdate)<14" will omit the other two rows as per requirment.

2 2007-01-01 14:30:00.000
3 2007-01-01 14:20:00.000

How to compare chinese words/signs in a sql-statement?

I have the following line of (delphi)code:

SQL.Add('SELECT Language_ID FROM Languages WHERE Language_Name = :language');
ADOQuery2.Parameters.ParamByName('language').Value := TntComboBox1.Text;

TntComboBox1.Text is an widestring with chinese marks, which it got from the same database.

After more then an hour, i used the query analyzer and i saw, MS SQL cant compare chinese signs. (its not delphi's fault, i tested)

Must i change some settings? And where?
I can insert the chinese language and put in a table for example, without a problem...

thnx,
ErikAre your field types set to unicode NCHAR and NVARCHAR?

blindman|||Yes, i have, otherwise i can't insert and select it.
I can't do:
SELECT something FROM table WHERE column_with_chinese_signs = 'chinese_Sign R'
R

only with:
SELECT something FROM table WHERE column_with_chinese_signs = N'chinese_Sign R'

see the N!

but now i can't implement it in Delphi... SQL.ADD('... = N x')
x is a variable (e.g. a sign but can also be a word)

I hpe i was clear

How to Compare 2007-9-11 and Month(GetDate()) ?

I am going to compare thie value 2007-9-11 (this value was retrived from the column(TxnDate) in my DataBase, type is DateTime)

I write code

select * from ZT_ModifyLog where Year(TXnDate) = Year(GetDate()) AND ( ( Month(TXnDate) < Month(GetDate()) ) and Month(TXnDate) >= Month(GetDate())-1)

it will like

select * from ZT_ModifyLog where Year(2007-9-11 ) = Year(GetDate()) AND ( ( Month(2007-9-11 ) < Month(GetDate()) ) and Month(2007-9-11 ) >= Month(GetDate())-1)

→ select * from ZT_ModifyLog where TxnDate(2007) = 2007 AND 10 < GetDate(10) and 9 >= 9 so return TXnDate between 2007/9/1~ 2007/9/30

but what if Month(GetDate())-13)?? when the -1 biger than 12... I guess the code will cause error ... but can't think out how to avoid and change my code

pleae help... thank you very much

Please do not open multiple threads for same question. Refer:http://forums.asp.net/t/1176161.aspx.

How to compare 2 tables ot different DBs in Oracle style

In Oracle this is done this way :
SQL>
1 select ID, NAME from TABLENAME
2* minus select ID,NAME from TABLENAME@.DATABASE2
SQL>

Any ideas ?Yeah...minus is a way to do a non existance checj=k...or a left outer join where the right table key is null...

I don't know what your talking about with @.DATABASE2|||Originally posted by Brett Kaiser
I don't know what your talking about with @.DATABASE2 That's Oracle speak for DATABASE2..TABLENAME in SQL-92.

-PatP|||See...my Oracle naivite' is showing...

Never dealt with more than 1 db in Oracle...|||select ID, NAME from TABLENAME
minus select ID,NAME from TABLENAME@.DATABASE2

This statement compares table TABLENAME in current database and DATABASE2 and shows the lines that are missing in the current database.

If it returns

1 | Test1
2 | Test2

this meanse that these 2 lines exists in TABLENAME in current DB, and doest not exists in TABLENAME in DATABASE2.

In SQL Server it should be something like this :

SELECT ID, NAME FROM TABLENAME
MINUS
SELECT ID,NAME FROM DATABASE2..TABLENAME

but we don't have MINUS in T-SQL|||Might I suggest using a FULL OUTER JOIN to see what was added or deleted, then a compare of the column values to see what keys have different values associated with them?

-PatP|||FYI, http://www.sql-server-performance.com/vg_database_comparison_sp.asp

Originally posted by The-Saint
In Oracle this is done this way :

SQL>
1 select ID, NAME from TABLENAME
2* minus select ID,NAME from TABLENAME@.DATABASE2
SQL>

Any ideas ?

how to compare 2 date by "month"?

I'm doing DTS, Here is one of my Sql Query in DTS

Select * From ZT_DailyRpt_Detail
Where
isNumeric(TenDayDate) = 1 And TenDayDate <
Convert(varchar(10),DateAdd(Month,-CAST
((SELECT keepmonth
FROM zt_databackup a, zt_biller b
WHERE a.companycode = b.companycode AND
zt_DailyRpt_Detail.biller_code = b.billercode) AS int),GetDate()),112)

the sub query 【SELECT keepmonth
FROM zt_databackup a, zt_biller b
WHERE a.companycode = b.companycode AND
zt_DailyRpt_Detail.biller_code = b.billercode) AS int】will return a value 6 or 12

for the reason, every month have differnet days, ( some have 31 days , some are 30 days ) I don't want to check the day of date , only to compare month

if sub query return "6" and I do DTS on 2007/07/29 , will select date whichTenDayDate< 2007/01 not TenDayDate<2007/01/29 ( don't want to check the day of date)

does my query correct? if not can you correct it for me? thank you very much

Not sure I understood your problem, but maybe what you're looking for isDATEDIFFsql function?

|||

sorry for not describe my question clearly

my problem is

if I want to compare 2 date for example one is 2007/01/22 another is 2007/07/30

2007/07/30 - 2007/01/22 = 2007/06/08 right? that is not what I need

I need the result is 2007/06

I want to do DTS

if user run DTS in 2007/07/28 , want to select the data < 2007/07/28- 6 months , so it would be where data < 2007/01/28

I am not select Data < 2007/01/28 , I want to select Data < 2007/01 ( need not to compare the Day )

thank you

How to combine to boolean collumns ?

Hi, my name is Wilhelm and I'm new to this forum.

I having problems with a SQL SELECT statement. I'm trying to combine two boolean columns in ms sql server 2000.

I tried this:

SELECT (Bol1 OR Bol2) as Bol

Anyone have any suggestion why this don't work ?
I think i have done something similar in Orcale ones.SELECT (Bol1 | Bol2) as Bol|||I just found out. Thanks anyway :)

how to code sql comments within sql statement

I want to write a query like this:

select ' select count(*) '+ '/*' + table_name + '*/' + char(10) + ' FROM '+table_name...

The result should looks like this:

--
select count(*) /* - emp_name */
FROM emp_name

query result:

COUNT(*)-EMP_NAME
-
3

-
But, now I can only got

COUNT(*)
-
3

What's wrong with my original code?Actually, I am getting

- as the result, I think I just need to find out how to turn headings on.
3

Thanks|||

Well... you are comment out whatever is written. Comment out means that it will be ignored, it will not show up in the results, etc.

It looks like you want to create column headers for aggregrated columns. Fortunately, you can use an ALIAS for that task.

For example:

Code Snippet

SELECT
count(*) AS 'count(*) --Emp_Name'
FROM MyTable

Of course, whatever is typed as the ALIAS has to be know in advance, it will not dynamically put in the column or table name.

|||
Thanks, Arnie:

I got it now.

How to choose the default hierarchy for a dimension?

I created a server-time dimension and generated several hierarchies within the same dimension, does anyone know how to select a particular hierarchy as the default one?

I didn't see it on the properties for the dimension or hierarchy.What is default hierarchy semantics that you are looking for ? I.e. what will it be used for ? What special treatment will it receive ?|||It's a time dimension and there are 3 hierarchies defined:
Reporting Year -> Reporting Quarter
Year - Quarter - Month - Date
Year - Week - Date

It seems as though the Reporting Year -> Reporting Quarter hierarchy is used by default, how would I change the configuration to use a different hierarchy by default?|||

Can you please explain what do you mean by

"It seems as though the Reporting Year -> Reporting Quarter hierarchy is used by default"

|||The 'first' heirarchy is the default hierarchy. IE: The left most heirarchy in your model.

C|||Chris - can you please explain what does "default hierarchy" means in AS ? Thanks !|||From your browsing client, If you grab a Dimension containing multiple hierarchies, and drag it over (by whatever means you're using) to your Rows, or Columns, or Filter etc... it will show your default hierarchy.|||Ah, that explains it. Sorry for my confusing terminology. Thanks, Chris + Mosha.