Hi
I have a table with dates stored in a nvarchar(75), I would like to move
this over to datetime (for obvious reasons)
the problem is the field has some 'bad data'
how can i query the nvarchar field to return me anything that isnt a date?
so i can fix and convert
Thanks in advance
MikeMike
Take a look at ISDATE () function
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
> Hi
> I have a table with dates stored in a nvarchar(75), I would like to move
> this over to datetime (for obvious reasons)
> the problem is the field has some 'bad data'
> how can i query the nvarchar field to return me anything that isnt a date?
> so i can fix and convert
> Thanks in advance
> Mike
>|||Uri,
I have been looking at that all morning and trying to understand it fully.
using the following query
SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
this returns a 1 or 0
the problem with ISDATE() is that it seems to rely on an american date
format
so 13/02/2003 is invalid whereas 02/13/2003 is valid
being in the UK the function appears to be as much use as a chocolate fire
guard :)
Thanks
Mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
> Mike
> Take a look at ISDATE () function
>
> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>|||the UK problem can be fixed by using
SET DATEFORMAT dmy;
- got it working now
Thanks again
Mike
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:UpudnVw4esuFQG3anZ2dnUVZ8v6dnZ2d@.giganews.com...
> Uri,
> I have been looking at that all morning and trying to understand it fully.
> using the following query
> SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
> this returns a 1 or 0
> the problem with ISDATE() is that it seems to rely on an american date
> format
> so 13/02/2003 is invalid whereas 02/13/2003 is valid
> being in the UK the function appears to be as much use as a chocolate fire
> guard :)
> Thanks
> Mike
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Mike
>> Take a look at ISDATE () function
>>
>> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
>> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>>
>
Showing posts with label values. Show all posts
Showing posts with label values. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Query "hightest value" contents of each "folder"?
Hi,
Can anyone tell me how to select the "most recent" date values from a
grouped query? Consider the following:
CREATE TABLE [dbo].[TestQuery] ( [ID] [int] NOT NULL , [ID_Parent] [int] NOT
NULL , [Date] [datetime] NOT NULL ) ON [PRIMARY]
This is a simplified adjacency list. What I want to do is find the highest
valued item by date for each sub-tree. In other words, the single highest
date item when This.ID_Parent = That.ID_Parent. I think I first need to
group by ID_Parent, then select the TOP 1 from this query, but how to
aggregate them so I get the TOP 1 for each ID_Parent?
Thanks for any help you can give me,
RobinAnother way:
SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
WHERE T1.date =
(SELECT MAX(date)
FROM TestQuery AS T2
WHERE T1.id_parent = T2.id_parent)
GROUP BY T1.id_parent, T1.date
--
David Portas
SQL Server MVP
--
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Try this:
SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
LEFT JOIN TestQuery AS T2
ON T1.id_parent = T2.id_parent
AND T1.date < T2.date
WHERE T2.date IS NULL
GROUP BY T1.id_parent, T1.date
--
David Portas
SQL Server MVP
--
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||here is a related article:
subject: select first record from a group ordered by 3 columns
date: Nov 27 2002, 5:01 am
http://groups-beta.google.com/group...23b16be2b945c0f
David Portas wrote:
> Another way:
> SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
> FROM TestQuery AS T1
> WHERE T1.date =
> (SELECT MAX(date)
> FROM TestQuery AS T2
> WHERE T1.id_parent = T2.id_parent)
> GROUP BY T1.id_parent, T1.date
> --
> David Portas
> SQL Server MVP
> --
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
query
damn touchpads......
I have a query:
SELECT id, name, rank, serial_number from status
this will return many rows...if all values are null for a single row, i need
to be able to assign a unique "id" for that row (PK constraint on another
table) is there a way to do this so that for each row that has NULL values
across all columns. This way I can populate with an incrementing or otherwis
e
unique value (using d1, d2, d3... here)
id name rank serial_number
--
d1 null null null
d2 null null null
34r bill 0-6 4420
d3 null null null
d4 null null null
d5....
TIA!You can try using the following:
NOTE: This recreates the status table (truncate + re-populate)
-- BEGIN SCRIPT
create table status
(id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into status
values (null,null,null,null)
insert into status
values ('34r','bill','0-6','4420')
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
create table #status
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
create table #status2
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into #status
select * from status
insert into #status2
select * from status
update #status
set id = 'd'+convert(varchar(10), s.RowID)
from #status s, #status2 s2
where s.RowID = s2.RowID
and s.id is null
truncate table status
insert into status
SELECT id, name, rank, serial_number from #status
select * from status
drop table #status
drop table #status2
Hope it helps
"JMNUSS" wrote:
> damn touchpads......
> I have a query:
> SELECT id, name, rank, serial_number from status
> this will return many rows...if all values are null for a single row, i ne
ed
> to be able to assign a unique "id" for that row (PK constraint on another
> table) is there a way to do this so that for each row that has NULL values
> across all columns. This way I can populate with an incrementing or otherw
ise
> unique value (using d1, d2, d3... here)
> id name rank serial_number
> --
> d1 null null null
> d2 null null null
> 34r bill 0-6 4420
> d3 null null null
> d4 null null null
> d5....
> TIA!
>
I have a query:
SELECT id, name, rank, serial_number from status
this will return many rows...if all values are null for a single row, i need
to be able to assign a unique "id" for that row (PK constraint on another
table) is there a way to do this so that for each row that has NULL values
across all columns. This way I can populate with an incrementing or otherwis
e
unique value (using d1, d2, d3... here)
id name rank serial_number
--
d1 null null null
d2 null null null
34r bill 0-6 4420
d3 null null null
d4 null null null
d5....
TIA!You can try using the following:
NOTE: This recreates the status table (truncate + re-populate)
-- BEGIN SCRIPT
create table status
(id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into status
values (null,null,null,null)
insert into status
values ('34r','bill','0-6','4420')
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
create table #status
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
create table #status2
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into #status
select * from status
insert into #status2
select * from status
update #status
set id = 'd'+convert(varchar(10), s.RowID)
from #status s, #status2 s2
where s.RowID = s2.RowID
and s.id is null
truncate table status
insert into status
SELECT id, name, rank, serial_number from #status
select * from status
drop table #status
drop table #status2
Hope it helps
"JMNUSS" wrote:
> damn touchpads......
> I have a query:
> SELECT id, name, rank, serial_number from status
> this will return many rows...if all values are null for a single row, i ne
ed
> to be able to assign a unique "id" for that row (PK constraint on another
> table) is there a way to do this so that for each row that has NULL values
> across all columns. This way I can populate with an incrementing or otherw
ise
> unique value (using d1, d2, d3... here)
> id name rank serial_number
> --
> d1 null null null
> d2 null null null
> 34r bill 0-6 4420
> d3 null null null
> d4 null null null
> d5....
> TIA!
>
Monday, March 26, 2012
query
create table a
(
i varchar(10),
j varchar(11)
)
create table b
(
n varchar(10),
m varchar(11)
)
insert into a values(null, '1')
insert into a values(null, '2')
insert into b values(null, '3')
insert into b values(null, '4')
insert into a values('val1', '5')
insert into a values('val2', '6')
insert into b values('val3', '7')
insert into b values('val4', '8')
select * From a
inner join b on a.i = b.n
and a.i is null and b.n is null
hi i want to join table a and b and return the results
null, 1
null, 2
null, 3
null, 4
but i dont get this output. how do i do that?
thnx
ICHORThe query below should return the result you are after:
SELECT i, j
FROM a
WHERE i IS NULL
UNION
SELECT n, m
FROM b
WHERE n IS NULL
When you perform an INNER JOIN you only return the rows that satisfiy the
join of the first input with the second input. As a NULL is an unknown valu
e
NULL does not equal NULL hence the NULL records do not satisfy the join and
are not returned.
- Peter Ward
WARDY IT Solutions
"ichor" wrote:
> create table a
> (
> i varchar(10),
> j varchar(11)
> )
>
> create table b
> (
> n varchar(10),
> m varchar(11)
> )
> insert into a values(null, '1')
> insert into a values(null, '2')
> insert into b values(null, '3')
> insert into b values(null, '4')
>
> insert into a values('val1', '5')
> insert into a values('val2', '6')
> insert into b values('val3', '7')
> insert into b values('val4', '8')
>
> select * From a
> inner join b on a.i = b.n
> and a.i is null and b.n is null
>
> hi i want to join table a and b and return the results
>
> null, 1
> null, 2
> null, 3
> null, 4
> but i dont get this output. how do i do that?
> thnx
> ICHOR
>
>
(
i varchar(10),
j varchar(11)
)
create table b
(
n varchar(10),
m varchar(11)
)
insert into a values(null, '1')
insert into a values(null, '2')
insert into b values(null, '3')
insert into b values(null, '4')
insert into a values('val1', '5')
insert into a values('val2', '6')
insert into b values('val3', '7')
insert into b values('val4', '8')
select * From a
inner join b on a.i = b.n
and a.i is null and b.n is null
hi i want to join table a and b and return the results
null, 1
null, 2
null, 3
null, 4
but i dont get this output. how do i do that?
thnx
ICHORThe query below should return the result you are after:
SELECT i, j
FROM a
WHERE i IS NULL
UNION
SELECT n, m
FROM b
WHERE n IS NULL
When you perform an INNER JOIN you only return the rows that satisfiy the
join of the first input with the second input. As a NULL is an unknown valu
e
NULL does not equal NULL hence the NULL records do not satisfy the join and
are not returned.
- Peter Ward
WARDY IT Solutions
"ichor" wrote:
> create table a
> (
> i varchar(10),
> j varchar(11)
> )
>
> create table b
> (
> n varchar(10),
> m varchar(11)
> )
> insert into a values(null, '1')
> insert into a values(null, '2')
> insert into b values(null, '3')
> insert into b values(null, '4')
>
> insert into a values('val1', '5')
> insert into a values('val2', '6')
> insert into b values('val3', '7')
> insert into b values('val4', '8')
>
> select * From a
> inner join b on a.i = b.n
> and a.i is null and b.n is null
>
> hi i want to join table a and b and return the results
>
> null, 1
> null, 2
> null, 3
> null, 4
> but i dont get this output. how do i do that?
> thnx
> ICHOR
>
>
query
Hi
a) I Have table(tmarcs) with fields
id, marc_id,ind1, indeks, teks
Lets say i have values in this table
1,245,4,'The Sun', 'is shining'
1,245,0,'Sun',''
b) on ind1 field is length of article(prefix)
c) user wants to search books by title and enters only Sun
Query must return both titles, beacause The_ must be ignored on search
My query looks something like this
select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
'Sun'
but this query is veeeeery slow.(of course query is more complex but when to
remove this "replicate", query is very fast)
Any ideas how to build better query ;)?
Regards;
Mex
Hi
I'm not sure that you gain here performance try how does it work?
create table #t
(
c varchar(50)
)
insert into #t values ('The Sun')
insert into #t values ('The_ Sun')
insert into #t values ('The_Sun')
insert into #t values ('The_Sun_')
insert into #t values ('The Moon')
declare @.d varchar(50)
set @.d='Sun'
select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>
|||Meelis,
Your prefix is precooking how big a part of the title to strip off, but what
if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or 4 for
"Friend"?
For the data as you show it, you could use
... WHERE indeks IS LIKE '%Sun%'
However, just as your replicate code will not use an index on indeks, this
still will not use an index since the prefix is wild.
This need might be better met by fulltext indexing of the indeks column.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>
|||The ind1 is ment for aritcles in the bginning of title.
for english The, German Der,Das,Die aso.(length from 0 to 9)
The Sun (in english)
Die Sonne(in german)
in both cases ind1=4
if user enters for search Sonne, query must return also Die Sonne
because ind1=4 and first 4 chars are ignored
Mex
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
> Meelis,
> Your prefix is precooking how big a part of the title to strip off, but
> what if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or
> 4 for "Friend"?
> For the data as you show it, you could use
> ... WHERE indeks IS LIKE '%Sun%'
> However, just as your replicate code will not use an index on indeks, this
> still will not use an index since the prefix is wild.
> This need might be better met by fulltext indexing of the indeks column.
> RLF
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>
|||its much slower, because a '%'+@.d+'%'
Ill show you my original query
select top 500 t.id, 0, t.indeks, t.yhik_id, t.pealkiri, t.autor, t.aasta,
t.muud ,(select id from kohaviidad where teavik_id=t.id and kogu_id=37) ,
count(distinct e.id) from (TEAVIKUD t inner join eksemplarid e on
(e.teavik_id=t.id and e.asukoht_id not in(10,11) )) inner join (select
teavik_id from tmarcid where (marc_id in (210,222,240,242,245,246,740)) and
indeks like N'' + (select replicate('_',char(ind2))) +N'tuba number 10%')
tm1 on tm1.teavik_id=t.id where kustut=0 group by t.id, t.indeks,
t.yhik_id, t.pealkiri, t.autor, t.aasta, t.muud order by 5,6,7,8
Mex
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23W7U85WZHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Hi
> I'm not sure that you gain here performance try how does it work?
>
> create table #t
> (
> c varchar(50)
> )
> insert into #t values ('The Sun')
> insert into #t values ('The_ Sun')
> insert into #t values ('The_Sun')
> insert into #t values ('The_Sun_')
> insert into #t values ('The Moon')
>
> declare @.d varchar(50)
> set @.d='Sun'
> select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>
|||Meelis,
One common approach (in the US) for removing the article from the sort
criteria is to reform the title as:
1,245,4,'Sun, The', 'is shining'
1,245,0,'Sun',''
This means that the overhead is at update time when the code takes "The Sun"
and turns it into "Sun, The". Of course, if you want to display the title
as originally typed, the SELECT will need to turn it back around. In this
case, ind1 would be the length of the suffix.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:OCIcXEXZHHA.348@.TK2MSFTNGP02.phx.gbl...
> The ind1 is ment for aritcles in the bginning of title.
> for english The, German Der,Das,Die aso.(length from 0 to 9)
> The Sun (in english)
> Die Sonne(in german)
> in both cases ind1=4
> if user enters for search Sonne, query must return also Die Sonne
> because ind1=4 and first 4 chars are ignored
> Mex
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
>
a) I Have table(tmarcs) with fields
id, marc_id,ind1, indeks, teks
Lets say i have values in this table
1,245,4,'The Sun', 'is shining'
1,245,0,'Sun',''
b) on ind1 field is length of article(prefix)
c) user wants to search books by title and enters only Sun
Query must return both titles, beacause The_ must be ignored on search
My query looks something like this
select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
'Sun'
but this query is veeeeery slow.(of course query is more complex but when to
remove this "replicate", query is very fast)
Any ideas how to build better query ;)?
Regards;
Mex
Hi
I'm not sure that you gain here performance try how does it work?
create table #t
(
c varchar(50)
)
insert into #t values ('The Sun')
insert into #t values ('The_ Sun')
insert into #t values ('The_Sun')
insert into #t values ('The_Sun_')
insert into #t values ('The Moon')
declare @.d varchar(50)
set @.d='Sun'
select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>
|||Meelis,
Your prefix is precooking how big a part of the title to strip off, but what
if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or 4 for
"Friend"?
For the data as you show it, you could use
... WHERE indeks IS LIKE '%Sun%'
However, just as your replicate code will not use an index on indeks, this
still will not use an index since the prefix is wild.
This need might be better met by fulltext indexing of the indeks column.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>
|||The ind1 is ment for aritcles in the bginning of title.
for english The, German Der,Das,Die aso.(length from 0 to 9)
The Sun (in english)
Die Sonne(in german)
in both cases ind1=4
if user enters for search Sonne, query must return also Die Sonne
because ind1=4 and first 4 chars are ignored
Mex
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
> Meelis,
> Your prefix is precooking how big a part of the title to strip off, but
> what if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or
> 4 for "Friend"?
> For the data as you show it, you could use
> ... WHERE indeks IS LIKE '%Sun%'
> However, just as your replicate code will not use an index on indeks, this
> still will not use an index since the prefix is wild.
> This need might be better met by fulltext indexing of the indeks column.
> RLF
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>
|||its much slower, because a '%'+@.d+'%'
Ill show you my original query
select top 500 t.id, 0, t.indeks, t.yhik_id, t.pealkiri, t.autor, t.aasta,
t.muud ,(select id from kohaviidad where teavik_id=t.id and kogu_id=37) ,
count(distinct e.id) from (TEAVIKUD t inner join eksemplarid e on
(e.teavik_id=t.id and e.asukoht_id not in(10,11) )) inner join (select
teavik_id from tmarcid where (marc_id in (210,222,240,242,245,246,740)) and
indeks like N'' + (select replicate('_',char(ind2))) +N'tuba number 10%')
tm1 on tm1.teavik_id=t.id where kustut=0 group by t.id, t.indeks,
t.yhik_id, t.pealkiri, t.autor, t.aasta, t.muud order by 5,6,7,8
Mex
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23W7U85WZHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Hi
> I'm not sure that you gain here performance try how does it work?
>
> create table #t
> (
> c varchar(50)
> )
> insert into #t values ('The Sun')
> insert into #t values ('The_ Sun')
> insert into #t values ('The_Sun')
> insert into #t values ('The_Sun_')
> insert into #t values ('The Moon')
>
> declare @.d varchar(50)
> set @.d='Sun'
> select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>
|||Meelis,
One common approach (in the US) for removing the article from the sort
criteria is to reform the title as:
1,245,4,'Sun, The', 'is shining'
1,245,0,'Sun',''
This means that the overhead is at update time when the code takes "The Sun"
and turns it into "Sun, The". Of course, if you want to display the title
as originally typed, the SELECT will need to turn it back around. In this
case, ind1 would be the length of the suffix.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:OCIcXEXZHHA.348@.TK2MSFTNGP02.phx.gbl...
> The ind1 is ment for aritcles in the bginning of title.
> for english The, German Der,Das,Die aso.(length from 0 to 9)
> The Sun (in english)
> Die Sonne(in german)
> in both cases ind1=4
> if user enters for search Sonne, query must return also Die Sonne
> because ind1=4 and first 4 chars are ignored
> Mex
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
>
Query
ColumnX holds values between 1001 and 5999. How can i make a query to get th
at result set:
Group Quantity
1000s xxx
2000s xxx
3000s xxx
4000s xxx
5000s xxxSELECT ColumnX / 1000 AS grp, SUM(qty) AS totalqty
FROM T1
GROUP BY ColumnX / 1000
BG, SQL Server MVP
www.SolidQualityLearning.com
"LacOniC" <iletisim@.bigfoot.com> wrote in message
news:uHy2j$HNFHA.3076@.tk2msftngp13.phx.gbl...
> ColumnX holds values between 1001 and 5999. How can i make a query to get
that result set:
> Group Quantity
> 1000s xxx
> 2000s xxx
> 3000s xxx
> 4000s xxx
> 5000s xxx
>|||Try,
select min(colA), count(*)
from table1
group by colA / 1000
order by min(colA);
AMB
"LacOniC" wrote:
> ColumnX holds values between 1001 and 5999. How can i make a query to get
that result set:
> Group Quantity
> 1000s xxx
> 2000s xxx
> 3000s xxx
> 4000s xxx
> 5000s xxx
>
>|||Correction,
select min((colA / 1000) * 1000), count(*)
from table1
group by colA / 1000
order by min((colA / 1000) * 1000);
AMB
"Alejandro Mesa" wrote:
> Try,
> select min(colA), count(*)
> from table1
> group by colA / 1000
> order by min(colA);
>
> AMB
> "LacOniC" wrote:
>|||Try
Select Str((ColumnX / 1000) + 1, 2,0) + '000s' As Group,
Count(*)
From <TableName>
Group By Str((ColumnX / 1000) + 1, 2,0) + '000s'
"LacOniC" wrote:
> ColumnX holds values between 1001 and 5999. How can i make a query to get
that result set:
> Group Quantity
> 1000s xxx
> 2000s xxx
> 3000s xxx
> 4000s xxx
> 5000s xxx
>
>|||sorry, leae out the + 1
Select Str((ColumnX / 1000), 3,0) + '000s' As Group,
Count(*)
From <TableName>
Group By Str((ColumnX / 1000), 3,0) + '000s'
"LacOniC" wrote:
> ColumnX holds values between 1001 and 5999. How can i make a query to get
that result set:
> Group Quantity
> 1000s xxx
> 2000s xxx
> 3000s xxx
> 4000s xxx
> 5000s xxx
>
>|||This works very well. Thank you.|||Thak you. I got an error:
Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'Group'.
Select Str((M_AGE / 1000), 3,0) + '000s' As Group, Count(*)
From FORUM_MEMBERS
Group By Str((M_AGE / 1000), 3,0) + '000s'|||Thank you. I tried your query and:
SELECT M_AGE / 1000 AS grp, SUM(qty) AS totalqty
FROM FORUM_MEMBERS
GROUP BY M_AGE / 1000
Server: Msg 207, Level 16, State 3, Line 7
Invalid column name 'qty'.|||I forgot to say, data type is nvarchar, so can't use SUM already. Sorry.
at result set:
Group Quantity
1000s xxx
2000s xxx
3000s xxx
4000s xxx
5000s xxxSELECT ColumnX / 1000 AS grp, SUM(qty) AS totalqty
FROM T1
GROUP BY ColumnX / 1000
BG, SQL Server MVP
www.SolidQualityLearning.com
"LacOniC" <iletisim@.bigfoot.com> wrote in message
news:uHy2j$HNFHA.3076@.tk2msftngp13.phx.gbl...
> ColumnX holds values between 1001 and 5999. How can i make a query to get
that result set:
> Group Quantity
> 1000s xxx
> 2000s xxx
> 3000s xxx
> 4000s xxx
> 5000s xxx
>|||Try,
select min(colA), count(*)
from table1
group by colA / 1000
order by min(colA);
AMB
"LacOniC" wrote:
> ColumnX holds values between 1001 and 5999. How can i make a query to get
that result set:
> Group Quantity
> 1000s xxx
> 2000s xxx
> 3000s xxx
> 4000s xxx
> 5000s xxx
>
>|||Correction,
select min((colA / 1000) * 1000), count(*)
from table1
group by colA / 1000
order by min((colA / 1000) * 1000);
AMB
"Alejandro Mesa" wrote:
> Try,
> select min(colA), count(*)
> from table1
> group by colA / 1000
> order by min(colA);
>
> AMB
> "LacOniC" wrote:
>|||Try
Select Str((ColumnX / 1000) + 1, 2,0) + '000s' As Group,
Count(*)
From <TableName>
Group By Str((ColumnX / 1000) + 1, 2,0) + '000s'
"LacOniC" wrote:
> ColumnX holds values between 1001 and 5999. How can i make a query to get
that result set:
> Group Quantity
> 1000s xxx
> 2000s xxx
> 3000s xxx
> 4000s xxx
> 5000s xxx
>
>|||sorry, leae out the + 1
Select Str((ColumnX / 1000), 3,0) + '000s' As Group,
Count(*)
From <TableName>
Group By Str((ColumnX / 1000), 3,0) + '000s'
"LacOniC" wrote:
> ColumnX holds values between 1001 and 5999. How can i make a query to get
that result set:
> Group Quantity
> 1000s xxx
> 2000s xxx
> 3000s xxx
> 4000s xxx
> 5000s xxx
>
>|||This works very well. Thank you.|||Thak you. I got an error:
Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'Group'.
Select Str((M_AGE / 1000), 3,0) + '000s' As Group, Count(*)
From FORUM_MEMBERS
Group By Str((M_AGE / 1000), 3,0) + '000s'|||Thank you. I tried your query and:
SELECT M_AGE / 1000 AS grp, SUM(qty) AS totalqty
FROM FORUM_MEMBERS
GROUP BY M_AGE / 1000
Server: Msg 207, Level 16, State 3, Line 7
Invalid column name 'qty'.|||I forgot to say, data type is nvarchar, so can't use SUM already. Sorry.
query
drop table share
create table share
(
shid int identity ,
dt datetime,
price int
)
insert into share(dt,price) values('1-jan-2006',10)
insert into share(dt,price) values('2-jan-2006',11)
insert into share(dt,price) values('6-jan-2006',15)
insert into share(dt,price) values('4-jan-2006',9)
select * from share
output i want is
the difference in share prices from the previous day.
so
1 (11-10)
4 (15-11)
-6 (9-15)
select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
p2.price p2price
from
(select * from share
) as p2,
share p1
where p1.shid < p2.shid
order by p1.shid, p2.shid
thisis the closest i could come up with but it has duplicates.Try,
select b.price - a.price
from t1 as a inner join t1 as b
on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
[shid] then try:
select b.price - a.price
from t1 as a inner join t1 as b
on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
AMB
"ichor" wrote:
> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||u r right. sorry for the mistake.
the query works fine.
the first query is what i was after.
i guess my approach of writing the query was wrong
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
> Try,
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
> Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
> [shid] then try:
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
>
> AMB
> "ichor" wrote:
>|||I think this should work:
select shid, dt, price - isnull((Select price from share b where b.shid =
a.shid - 1), 0) from share a
Hope this help...
Ed
"ichor" wrote:
> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||can u explain why we need a b and c?
in the query?
"ichor" <ichor@.hotmail.com> wrote in message
news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>u r right. sorry for the mistake.
> the query works fine.
> the first query is what i was after.
> i guess my approach of writing the query was wrong
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
>|||We need to join a tabla to itself, so we need to alias the second reference
to it (b). The join condition is between a row in one side (b) and the a row
in the other side (a) where its [dt] value (in a) is equal to the previous of
current in b (the max([c.dt]) where [dt] < b.[dt]).
AMB
"ichor" wrote:
> can u explain why we need a b and c?
> in the query?
> "ichor" <ichor@.hotmail.com> wrote in message
> news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>
>|||could we have done this using only a and b and no c?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3AD01065-EBAA-4AAE-990C-E1F29FB8BD9B@.microsoft.com...
> We need to join a tabla to itself, so we need to alias the second
> reference
> to it (b). The join condition is between a row in one side (b) and the a
> row
> in the other side (a) where its [dt] value (in a) is equal to the previous
> of
> current in b (the max([c.dt]) where [dt] < b.[dt]).
>
> AMB
> "ichor" wrote:
>
create table share
(
shid int identity ,
dt datetime,
price int
)
insert into share(dt,price) values('1-jan-2006',10)
insert into share(dt,price) values('2-jan-2006',11)
insert into share(dt,price) values('6-jan-2006',15)
insert into share(dt,price) values('4-jan-2006',9)
select * from share
output i want is
the difference in share prices from the previous day.
so
1 (11-10)
4 (15-11)
-6 (9-15)
select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
p2.price p2price
from
(select * from share
) as p2,
share p1
where p1.shid < p2.shid
order by p1.shid, p2.shid
thisis the closest i could come up with but it has duplicates.Try,
select b.price - a.price
from t1 as a inner join t1 as b
on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
[shid] then try:
select b.price - a.price
from t1 as a inner join t1 as b
on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
AMB
"ichor" wrote:
> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||u r right. sorry for the mistake.
the query works fine.
the first query is what i was after.
i guess my approach of writing the query was wrong
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
> Try,
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
> Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
> [shid] then try:
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
>
> AMB
> "ichor" wrote:
>|||I think this should work:
select shid, dt, price - isnull((Select price from share b where b.shid =
a.shid - 1), 0) from share a
Hope this help...
Ed
"ichor" wrote:
> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||can u explain why we need a b and c?
in the query?
"ichor" <ichor@.hotmail.com> wrote in message
news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>u r right. sorry for the mistake.
> the query works fine.
> the first query is what i was after.
> i guess my approach of writing the query was wrong
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
>|||We need to join a tabla to itself, so we need to alias the second reference
to it (b). The join condition is between a row in one side (b) and the a row
in the other side (a) where its [dt] value (in a) is equal to the previous of
current in b (the max([c.dt]) where [dt] < b.[dt]).
AMB
"ichor" wrote:
> can u explain why we need a b and c?
> in the query?
> "ichor" <ichor@.hotmail.com> wrote in message
> news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>
>|||could we have done this using only a and b and no c?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3AD01065-EBAA-4AAE-990C-E1F29FB8BD9B@.microsoft.com...
> We need to join a tabla to itself, so we need to alias the second
> reference
> to it (b). The join condition is between a row in one side (b) and the a
> row
> in the other side (a) where its [dt] value (in a) is equal to the previous
> of
> current in b (the max([c.dt]) where [dt] < b.[dt]).
>
> AMB
> "ichor" wrote:
>
Friday, March 23, 2012
query
Hi
a) I Have table(tmarcs) with fields
id, marc_id,ind1, indeks, teks
Lets say i have values in this table
1,245,4,'The Sun', 'is shining'
1,245,0,'Sun',''
b) on ind1 field is length of article(prefix)
c) user wants to search books by title and enters only Sun
Query must return both titles, beacause The_ must be ignored on search
My query looks something like this
select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
'Sun'
but this query is veeeeery slow.(of course query is more complex but when to
remove this "replicate", query is very fast)
Any ideas how to build better query ;)?
Regards;
MexHi
I'm not sure that you gain here performance try how does it work?
create table #t
(
c varchar(50)
)
insert into #t values ('The Sun')
insert into #t values ('The_ Sun')
insert into #t values ('The_Sun')
insert into #t values ('The_Sun_')
insert into #t values ('The Moon')
declare @.d varchar(50)
set @.d='Sun'
select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>|||Meelis,
Your prefix is precooking how big a part of the title to strip off, but what
if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or 4 for
"Friend"?
For the data as you show it, you could use
... WHERE indeks IS LIKE '%Sun%'
However, just as your replicate code will not use an index on indeks, this
still will not use an index since the prefix is wild.
This need might be better met by fulltext indexing of the indeks column.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>|||The ind1 is ment for aritcles in the bginning of title.
for english The, German Der,Das,Die aso.(length from 0 to 9)
The Sun (in english)
Die Sonne(in german)
in both cases ind1=4
if user enters for search Sonne, query must return also Die Sonne
because ind1=4 and first 4 chars are ignored
Mex
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
> Meelis,
> Your prefix is precooking how big a part of the title to strip off, but
> what if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or
> 4 for "Friend"?
> For the data as you show it, you could use
> ... WHERE indeks IS LIKE '%Sun%'
> However, just as your replicate code will not use an index on indeks, this
> still will not use an index since the prefix is wild.
> This need might be better met by fulltext indexing of the indeks column.
> RLF
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> a) I Have table(tmarcs) with fields
>> id, marc_id,ind1, indeks, teks
>>
>> Lets say i have values in this table
>> 1,245,4,'The Sun', 'is shining'
>> 1,245,0,'Sun',''
>> b) on ind1 field is length of article(prefix)
>> c) user wants to search books by title and enters only Sun
>> Query must return both titles, beacause The_ must be ignored on search
>> My query looks something like this
>> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
>> 'Sun'
>> but this query is veeeeery slow.(of course query is more complex but when
>> to remove this "replicate", query is very fast)
>> Any ideas how to build better query ;)?
>>
>> Regards;
>> Mex
>>
>|||its much slower, because a '%'+@.d+'%'
Ill show you my original query :)
select top 500 t.id, 0, t.indeks, t.yhik_id, t.pealkiri, t.autor, t.aasta,
t.muud ,(select id from kohaviidad where teavik_id=t.id and kogu_id=37) ,
count(distinct e.id) from (TEAVIKUD t inner join eksemplarid e on
(e.teavik_id=t.id and e.asukoht_id not in(10,11) )) inner join (select
teavik_id from tmarcid where (marc_id in (210,222,240,242,245,246,740)) and
indeks like N'' + (select replicate('_',char(ind2))) +N'tuba number 10%')
tm1 on tm1.teavik_id=t.id where kustut=0 group by t.id, t.indeks,
t.yhik_id, t.pealkiri, t.autor, t.aasta, t.muud order by 5,6,7,8
Mex
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23W7U85WZHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Hi
> I'm not sure that you gain here performance try how does it work?
>
> create table #t
> (
> c varchar(50)
> )
> insert into #t values ('The Sun')
> insert into #t values ('The_ Sun')
> insert into #t values ('The_Sun')
> insert into #t values ('The_Sun_')
> insert into #t values ('The Moon')
>
> declare @.d varchar(50)
> set @.d='Sun'
> select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> a) I Have table(tmarcs) with fields
>> id, marc_id,ind1, indeks, teks
>>
>> Lets say i have values in this table
>> 1,245,4,'The Sun', 'is shining'
>> 1,245,0,'Sun',''
>> b) on ind1 field is length of article(prefix)
>> c) user wants to search books by title and enters only Sun
>> Query must return both titles, beacause The_ must be ignored on search
>> My query looks something like this
>> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
>> 'Sun'
>> but this query is veeeeery slow.(of course query is more complex but when
>> to remove this "replicate", query is very fast)
>> Any ideas how to build better query ;)?
>>
>> Regards;
>> Mex
>>
>|||Meelis,
One common approach (in the US) for removing the article from the sort
criteria is to reform the title as:
1,245,4,'Sun, The', 'is shining'
1,245,0,'Sun',''
This means that the overhead is at update time when the code takes "The Sun"
and turns it into "Sun, The". Of course, if you want to display the title
as originally typed, the SELECT will need to turn it back around. In this
case, ind1 would be the length of the suffix.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:OCIcXEXZHHA.348@.TK2MSFTNGP02.phx.gbl...
> The ind1 is ment for aritcles in the bginning of title.
> for english The, German Der,Das,Die aso.(length from 0 to 9)
> The Sun (in english)
> Die Sonne(in german)
> in both cases ind1=4
> if user enters for search Sonne, query must return also Die Sonne
> because ind1=4 and first 4 chars are ignored
> Mex
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
>> Meelis,
>> Your prefix is precooking how big a part of the title to strip off, but
>> what if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or
>> 4 for "Friend"?
>> For the data as you show it, you could use
>> ... WHERE indeks IS LIKE '%Sun%'
>> However, just as your replicate code will not use an index on indeks,
>> this still will not use an index since the prefix is wild.
>> This need might be better met by fulltext indexing of the indeks column.
>> RLF
>> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
>> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> a) I Have table(tmarcs) with fields
>> id, marc_id,ind1, indeks, teks
>>
>> Lets say i have values in this table
>> 1,245,4,'The Sun', 'is shining'
>> 1,245,0,'Sun',''
>> b) on ind1 field is length of article(prefix)
>> c) user wants to search books by title and enters only Sun
>> Query must return both titles, beacause The_ must be ignored on search
>> My query looks something like this
>> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
>> 'Sun'
>> but this query is veeeeery slow.(of course query is more complex but
>> when to remove this "replicate", query is very fast)
>> Any ideas how to build better query ;)?
>>
>> Regards;
>> Mex
>>
>>
>
a) I Have table(tmarcs) with fields
id, marc_id,ind1, indeks, teks
Lets say i have values in this table
1,245,4,'The Sun', 'is shining'
1,245,0,'Sun',''
b) on ind1 field is length of article(prefix)
c) user wants to search books by title and enters only Sun
Query must return both titles, beacause The_ must be ignored on search
My query looks something like this
select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
'Sun'
but this query is veeeeery slow.(of course query is more complex but when to
remove this "replicate", query is very fast)
Any ideas how to build better query ;)?
Regards;
MexHi
I'm not sure that you gain here performance try how does it work?
create table #t
(
c varchar(50)
)
insert into #t values ('The Sun')
insert into #t values ('The_ Sun')
insert into #t values ('The_Sun')
insert into #t values ('The_Sun_')
insert into #t values ('The Moon')
declare @.d varchar(50)
set @.d='Sun'
select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>|||Meelis,
Your prefix is precooking how big a part of the title to strip off, but what
if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or 4 for
"Friend"?
For the data as you show it, you could use
... WHERE indeks IS LIKE '%Sun%'
However, just as your replicate code will not use an index on indeks, this
still will not use an index since the prefix is wild.
This need might be better met by fulltext indexing of the indeks column.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>|||The ind1 is ment for aritcles in the bginning of title.
for english The, German Der,Das,Die aso.(length from 0 to 9)
The Sun (in english)
Die Sonne(in german)
in both cases ind1=4
if user enters for search Sonne, query must return also Die Sonne
because ind1=4 and first 4 chars are ignored
Mex
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
> Meelis,
> Your prefix is precooking how big a part of the title to strip off, but
> what if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or
> 4 for "Friend"?
> For the data as you show it, you could use
> ... WHERE indeks IS LIKE '%Sun%'
> However, just as your replicate code will not use an index on indeks, this
> still will not use an index since the prefix is wild.
> This need might be better met by fulltext indexing of the indeks column.
> RLF
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> a) I Have table(tmarcs) with fields
>> id, marc_id,ind1, indeks, teks
>>
>> Lets say i have values in this table
>> 1,245,4,'The Sun', 'is shining'
>> 1,245,0,'Sun',''
>> b) on ind1 field is length of article(prefix)
>> c) user wants to search books by title and enters only Sun
>> Query must return both titles, beacause The_ must be ignored on search
>> My query looks something like this
>> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
>> 'Sun'
>> but this query is veeeeery slow.(of course query is more complex but when
>> to remove this "replicate", query is very fast)
>> Any ideas how to build better query ;)?
>>
>> Regards;
>> Mex
>>
>|||its much slower, because a '%'+@.d+'%'
Ill show you my original query :)
select top 500 t.id, 0, t.indeks, t.yhik_id, t.pealkiri, t.autor, t.aasta,
t.muud ,(select id from kohaviidad where teavik_id=t.id and kogu_id=37) ,
count(distinct e.id) from (TEAVIKUD t inner join eksemplarid e on
(e.teavik_id=t.id and e.asukoht_id not in(10,11) )) inner join (select
teavik_id from tmarcid where (marc_id in (210,222,240,242,245,246,740)) and
indeks like N'' + (select replicate('_',char(ind2))) +N'tuba number 10%')
tm1 on tm1.teavik_id=t.id where kustut=0 group by t.id, t.indeks,
t.yhik_id, t.pealkiri, t.autor, t.aasta, t.muud order by 5,6,7,8
Mex
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23W7U85WZHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Hi
> I'm not sure that you gain here performance try how does it work?
>
> create table #t
> (
> c varchar(50)
> )
> insert into #t values ('The Sun')
> insert into #t values ('The_ Sun')
> insert into #t values ('The_Sun')
> insert into #t values ('The_Sun_')
> insert into #t values ('The Moon')
>
> declare @.d varchar(50)
> set @.d='Sun'
> select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> a) I Have table(tmarcs) with fields
>> id, marc_id,ind1, indeks, teks
>>
>> Lets say i have values in this table
>> 1,245,4,'The Sun', 'is shining'
>> 1,245,0,'Sun',''
>> b) on ind1 field is length of article(prefix)
>> c) user wants to search books by title and enters only Sun
>> Query must return both titles, beacause The_ must be ignored on search
>> My query looks something like this
>> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
>> 'Sun'
>> but this query is veeeeery slow.(of course query is more complex but when
>> to remove this "replicate", query is very fast)
>> Any ideas how to build better query ;)?
>>
>> Regards;
>> Mex
>>
>|||Meelis,
One common approach (in the US) for removing the article from the sort
criteria is to reform the title as:
1,245,4,'Sun, The', 'is shining'
1,245,0,'Sun',''
This means that the overhead is at update time when the code takes "The Sun"
and turns it into "Sun, The". Of course, if you want to display the title
as originally typed, the SELECT will need to turn it back around. In this
case, ind1 would be the length of the suffix.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:OCIcXEXZHHA.348@.TK2MSFTNGP02.phx.gbl...
> The ind1 is ment for aritcles in the bginning of title.
> for english The, German Der,Das,Die aso.(length from 0 to 9)
> The Sun (in english)
> Die Sonne(in german)
> in both cases ind1=4
> if user enters for search Sonne, query must return also Die Sonne
> because ind1=4 and first 4 chars are ignored
> Mex
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
>> Meelis,
>> Your prefix is precooking how big a part of the title to strip off, but
>> what if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or
>> 4 for "Friend"?
>> For the data as you show it, you could use
>> ... WHERE indeks IS LIKE '%Sun%'
>> However, just as your replicate code will not use an index on indeks,
>> this still will not use an index since the prefix is wild.
>> This need might be better met by fulltext indexing of the indeks column.
>> RLF
>> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
>> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> a) I Have table(tmarcs) with fields
>> id, marc_id,ind1, indeks, teks
>>
>> Lets say i have values in this table
>> 1,245,4,'The Sun', 'is shining'
>> 1,245,0,'Sun',''
>> b) on ind1 field is length of article(prefix)
>> c) user wants to search books by title and enters only Sun
>> Query must return both titles, beacause The_ must be ignored on search
>> My query looks something like this
>> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
>> 'Sun'
>> but this query is veeeeery slow.(of course query is more complex but
>> when to remove this "replicate", query is very fast)
>> Any ideas how to build better query ;)?
>>
>> Regards;
>> Mex
>>
>>
>
query
Hi
a) I Have table(tmarcs) with fields
id, marc_id,ind1, indeks, teks
Lets say i have values in this table
1,245,4,'The Sun', 'is shining'
1,245,0,'Sun',''
b) on ind1 field is length of article(prefix)
c) user wants to search books by title and enters only Sun
Query must return both titles, beacause The_ must be ignored on search
My query looks something like this
select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
'Sun'
but this query is veeeeery slow.(of course query is more complex but when to
remove this "replicate", query is very fast)
Any ideas how to build better query ;)?
Regards;
MexHi
I'm not sure that you gain here performance try how does it work?
create table #t
(
c varchar(50)
)
insert into #t values ('The Sun')
insert into #t values ('The_ Sun')
insert into #t values ('The_Sun')
insert into #t values ('The_Sun_')
insert into #t values ('The Moon')
declare @.d varchar(50)
set @.d='Sun'
select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>|||Meelis,
Your prefix is precooking how big a part of the title to strip off, but what
if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or 4 for
"Friend"?
For the data as you show it, you could use
... WHERE indeks IS LIKE '%Sun%'
However, just as your replicate code will not use an index on indeks, this
still will not use an index since the prefix is wild.
This need might be better met by fulltext indexing of the indeks column.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>|||The ind1 is ment for aritcles in the bginning of title.
for english The, German Der,Das,Die aso.(length from 0 to 9)
The Sun (in english)
Die Sonne(in german)
in both cases ind1=4
if user enters for search Sonne, query must return also Die Sonne
because ind1=4 and first 4 chars are ignored
Mex
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
> Meelis,
> Your prefix is precooking how big a part of the title to strip off, but
> what if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or
> 4 for "Friend"?
> For the data as you show it, you could use
> ... WHERE indeks IS LIKE '%Sun%'
> However, just as your replicate code will not use an index on indeks, this
> still will not use an index since the prefix is wild.
> This need might be better met by fulltext indexing of the indeks column.
> RLF
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>|||its much slower, because a '%'+@.d+'%'
Ill show you my original query
select top 500 t.id, 0, t.indeks, t.yhik_id, t.pealkiri, t.autor, t.aasta,
t.muud ,(select id from kohaviidad where teavik_id=t.id and kogu_id=37) ,
count(distinct e.id) from (TEAVIKUD t inner join eksemplarid e on
(e.teavik_id=t.id and e.asukoht_id not in(10,11) )) inner join (select
teavik_id from tmarcid where (marc_id in (210,222,240,242,245,246,740)) and
indeks like N'' + (select replicate('_',char(ind2))) +N'tuba number 10%')
tm1 on tm1.teavik_id=t.id where kustut=0 group by t.id, t.indeks,
t.yhik_id, t.pealkiri, t.autor, t.aasta, t.muud order by 5,6,7,8
Mex
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23W7U85WZHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Hi
> I'm not sure that you gain here performance try how does it work?
>
> create table #t
> (
> c varchar(50)
> )
> insert into #t values ('The Sun')
> insert into #t values ('The_ Sun')
> insert into #t values ('The_Sun')
> insert into #t values ('The_Sun_')
> insert into #t values ('The Moon')
>
> declare @.d varchar(50)
> set @.d='Sun'
> select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>|||Meelis,
One common approach (in the US) for removing the article from the sort
criteria is to reform the title as:
1,245,4,'Sun, The', 'is shining'
1,245,0,'Sun',''
This means that the overhead is at update time when the code takes "The Sun"
and turns it into "Sun, The". Of course, if you want to display the title
as originally typed, the SELECT will need to turn it back around. In this
case, ind1 would be the length of the suffix.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:OCIcXEXZHHA.348@.TK2MSFTNGP02.phx.gbl...
> The ind1 is ment for aritcles in the bginning of title.
> for english The, German Der,Das,Die aso.(length from 0 to 9)
> The Sun (in english)
> Die Sonne(in german)
> in both cases ind1=4
> if user enters for search Sonne, query must return also Die Sonne
> because ind1=4 and first 4 chars are ignored
> Mex
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
>
a) I Have table(tmarcs) with fields
id, marc_id,ind1, indeks, teks
Lets say i have values in this table
1,245,4,'The Sun', 'is shining'
1,245,0,'Sun',''
b) on ind1 field is length of article(prefix)
c) user wants to search books by title and enters only Sun
Query must return both titles, beacause The_ must be ignored on search
My query looks something like this
select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
'Sun'
but this query is veeeeery slow.(of course query is more complex but when to
remove this "replicate", query is very fast)
Any ideas how to build better query ;)?
Regards;
MexHi
I'm not sure that you gain here performance try how does it work?
create table #t
(
c varchar(50)
)
insert into #t values ('The Sun')
insert into #t values ('The_ Sun')
insert into #t values ('The_Sun')
insert into #t values ('The_Sun_')
insert into #t values ('The Moon')
declare @.d varchar(50)
set @.d='Sun'
select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>|||Meelis,
Your prefix is precooking how big a part of the title to strip off, but what
if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or 4 for
"Friend"?
For the data as you show it, you could use
... WHERE indeks IS LIKE '%Sun%'
However, just as your replicate code will not use an index on indeks, this
still will not use an index since the prefix is wild.
This need might be better met by fulltext indexing of the indeks column.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
> Hi
> a) I Have table(tmarcs) with fields
> id, marc_id,ind1, indeks, teks
>
> Lets say i have values in this table
> 1,245,4,'The Sun', 'is shining'
> 1,245,0,'Sun',''
> b) on ind1 field is length of article(prefix)
> c) user wants to search books by title and enters only Sun
> Query must return both titles, beacause The_ must be ignored on search
> My query looks something like this
> select indeks, tekst from tmarcs where indeks like replicate('_',ind1) +
> 'Sun'
> but this query is veeeeery slow.(of course query is more complex but when
> to remove this "replicate", query is very fast)
> Any ideas how to build better query ;)?
>
> Regards;
> Mex
>|||The ind1 is ment for aritcles in the bginning of title.
for english The, German Der,Das,Die aso.(length from 0 to 9)
The Sun (in english)
Die Sonne(in german)
in both cases ind1=4
if user enters for search Sonne, query must return also Die Sonne
because ind1=4 and first 4 chars are ignored
Mex
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
> Meelis,
> Your prefix is precooking how big a part of the title to strip off, but
> what if the book is "Our Friend, the Sun"? Would ind1 be 16 for "Sun" or
> 4 for "Friend"?
> For the data as you show it, you could use
> ... WHERE indeks IS LIKE '%Sun%'
> However, just as your replicate code will not use an index on indeks, this
> still will not use an index since the prefix is wild.
> This need might be better met by fulltext indexing of the indeks column.
> RLF
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>|||its much slower, because a '%'+@.d+'%'
Ill show you my original query
select top 500 t.id, 0, t.indeks, t.yhik_id, t.pealkiri, t.autor, t.aasta,
t.muud ,(select id from kohaviidad where teavik_id=t.id and kogu_id=37) ,
count(distinct e.id) from (TEAVIKUD t inner join eksemplarid e on
(e.teavik_id=t.id and e.asukoht_id not in(10,11) )) inner join (select
teavik_id from tmarcid where (marc_id in (210,222,240,242,245,246,740)) and
indeks like N'' + (select replicate('_',char(ind2))) +N'tuba number 10%')
tm1 on tm1.teavik_id=t.id where kustut=0 group by t.id, t.indeks,
t.yhik_id, t.pealkiri, t.autor, t.aasta, t.muud order by 5,6,7,8
Mex
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23W7U85WZHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Hi
> I'm not sure that you gain here performance try how does it work?
>
> create table #t
> (
> c varchar(50)
> )
> insert into #t values ('The Sun')
> insert into #t values ('The_ Sun')
> insert into #t values ('The_Sun')
> insert into #t values ('The_Sun_')
> insert into #t values ('The Moon')
>
> declare @.d varchar(50)
> set @.d='Sun'
> select * from #t where c not LIKE '%[_]%' and c like '%'+@.d+'%'
>
>
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uDKpxsWZHHA.4220@.TK2MSFTNGP03.phx.gbl...
>|||Meelis,
One common approach (in the US) for removing the article from the sort
criteria is to reform the title as:
1,245,4,'Sun, The', 'is shining'
1,245,0,'Sun',''
This means that the overhead is at update time when the code takes "The Sun"
and turns it into "Sun, The". Of course, if you want to display the title
as originally typed, the SELECT will need to turn it back around. In this
case, ind1 would be the length of the suffix.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:OCIcXEXZHHA.348@.TK2MSFTNGP02.phx.gbl...
> The ind1 is ment for aritcles in the bginning of title.
> for english The, German Der,Das,Die aso.(length from 0 to 9)
> The Sun (in english)
> Die Sonne(in german)
> in both cases ind1=4
> if user enters for search Sonne, query must return also Die Sonne
> because ind1=4 and first 4 chars are ignored
> Mex
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23uJFd%23WZHHA.4368@.TK2MSFTNGP06.phx.gbl...
>
Tuesday, March 20, 2012
Queries dependent on results from other datasets.
Hi,
I need data from two datasources. From the first datasource a get only one row. Then I need to use one of the values in this row to filter the next query. I couldn't make it to work. Is it possible? Suggestions on how to do this?
Tomsi
One possible solution is to create internal parameter that will take default value from 1st data source.
When you can use this parameter in the second data set.
Queries are not seeing NULL Values in my DB
For some reason my Stored Procs are not recognizing NULL values within my database:
For example:
Select *
From Results
Where
(
home_Phone IS NULL
)
All the home_Phone vaules that are NULL are not being picked up by the query.
Any ideas are appriciated.
Thanks in advance everyone.
RB
Are you sure the values are NULL and not a blank?
Try where home_phone = '' to be sure.
Nick
|||All SQL Server Aggregate Functions ignore NULL except COUNT(*). Hope this helps.|||Caddre wrote:
All SQL Server Aggregate Functions ignore NULL except COUNT(*).
I am confused. How is that query using an aggregate function?
|||I am not saying the query is using aggregate function I was just telling the user about NULL value behavior in SQL server. I should have explained in detail and did not intend to confuse the user.|||
Thanks everyone for your responses its working very well.
Regards
RB
Queries against Analysis Server executed several times in SSRS
Using Reporting Services 2005 with SSAS 2005 I have a report working with
some parameters. In queries for the parameters some default values were
calculated. E.g. the current date. Then these values were used as input to
create mdx statements. Parameters!kdjfaj.value
When watching what is happening on the SSAS Server with SQL Profiler I
watched the queries executing SEVERAL times ("query begin" as event class).
Is this a normal behaviour of Reporting Services?
Or is it just a gag of the Profiler Tool to repeat the queries in time?
Or is this depending on the order of the datasets for the report? If this
would be the case - how could I change the order of execution of the dataset
queries?
Best regards,
StefoonI have a similar problem. I have a report created in Visual Studio
based on a querry that retrieves data.
Today however we found a need to count how many times rhis report was
printed. Piece of cake I thought just put an UPDATE query at the botom
of the querry that retrieves data.
tested my simple UPDATE Invoice.ReminderNo = Invoice.ReminderNo + 1 in
managment studio and it worked as i shoud.
But when i tried running the report it suddenly added 10. so I put in
another UPDATE that added one letter/execution to a text field. sure
anough 10 pretty A:s.
The query contains 5 separate datasets and is run 10 times when i
render my report. I find that to striking to be a coincidence. I
believe that the querry is run twice/dataset.
How many datasets do you have, how many times is the querry run?
On 1 Mar, 14:25, Stefoon23 <Stefoo...@.discussions.microsoft.com>
wrote:
> Using Reporting Services 2005 with SSAS 2005 I have a report working with
> some parameters. In queries for the parameters some default values were
> calculated. E.g. the current date. Then these values were used as input to
> create mdx statements. Parameters!kdjfaj.value
> When watching what is happening on the SSAS Server with SQL Profiler I
> watched the queries executingSEVERALtimes("querybegin" as event class).
> Is this a normal behaviour of Reporting Services?
> Or is it just a gag of the Profiler Tool to repeat the queries in time?
> Or is this depending on the order of the datasets for the report? If this
> would be the case - how could I change the order of execution of the dataset
> queries?
> Best regards,
> Stefoon|||Hi Markus,
the proportion is about the same. It seems that any query is executed 2
times.
I understand, that the parameter queries could run several times if you have
cascading picklists or something, but the query for the report's data?
Very mysterious.
Stefoon
"markus.rohsto@.gmail.com" wrote:
> I have a similar problem. I have a report created in Visual Studio
> based on a querry that retrieves data.
> Today however we found a need to count how many times rhis report was
> printed. Piece of cake I thought just put an UPDATE query at the botom
> of the querry that retrieves data.
> tested my simple UPDATE Invoice.ReminderNo = Invoice.ReminderNo + 1 in
> managment studio and it worked as i shoud.
> But when i tried running the report it suddenly added 10. so I put in
> another UPDATE that added one letter/execution to a text field. sure
> anough 10 pretty A:s.
> The query contains 5 separate datasets and is run 10 times when i
> render my report. I find that to striking to be a coincidence. I
> believe that the querry is run twice/dataset.
> How many datasets do you have, how many times is the querry run?
> On 1 Mar, 14:25, Stefoon23 <Stefoo...@.discussions.microsoft.com>
> wrote:
> > Using Reporting Services 2005 with SSAS 2005 I have a report working with
> > some parameters. In queries for the parameters some default values were
> > calculated. E.g. the current date. Then these values were used as input to
> > create mdx statements. Parameters!kdjfaj.value
> > When watching what is happening on the SSAS Server with SQL Profiler I
> > watched the queries executingSEVERALtimes("querybegin" as event class).
> > Is this a normal behaviour of Reporting Services?
> > Or is it just a gag of the Profiler Tool to repeat the queries in time?
> >
> > Or is this depending on the order of the datasets for the report? If this
> > would be the case - how could I change the order of execution of the dataset
> > queries?
> >
> > Best regards,
> > Stefoon
>
>
some parameters. In queries for the parameters some default values were
calculated. E.g. the current date. Then these values were used as input to
create mdx statements. Parameters!kdjfaj.value
When watching what is happening on the SSAS Server with SQL Profiler I
watched the queries executing SEVERAL times ("query begin" as event class).
Is this a normal behaviour of Reporting Services?
Or is it just a gag of the Profiler Tool to repeat the queries in time?
Or is this depending on the order of the datasets for the report? If this
would be the case - how could I change the order of execution of the dataset
queries?
Best regards,
StefoonI have a similar problem. I have a report created in Visual Studio
based on a querry that retrieves data.
Today however we found a need to count how many times rhis report was
printed. Piece of cake I thought just put an UPDATE query at the botom
of the querry that retrieves data.
tested my simple UPDATE Invoice.ReminderNo = Invoice.ReminderNo + 1 in
managment studio and it worked as i shoud.
But when i tried running the report it suddenly added 10. so I put in
another UPDATE that added one letter/execution to a text field. sure
anough 10 pretty A:s.
The query contains 5 separate datasets and is run 10 times when i
render my report. I find that to striking to be a coincidence. I
believe that the querry is run twice/dataset.
How many datasets do you have, how many times is the querry run?
On 1 Mar, 14:25, Stefoon23 <Stefoo...@.discussions.microsoft.com>
wrote:
> Using Reporting Services 2005 with SSAS 2005 I have a report working with
> some parameters. In queries for the parameters some default values were
> calculated. E.g. the current date. Then these values were used as input to
> create mdx statements. Parameters!kdjfaj.value
> When watching what is happening on the SSAS Server with SQL Profiler I
> watched the queries executingSEVERALtimes("querybegin" as event class).
> Is this a normal behaviour of Reporting Services?
> Or is it just a gag of the Profiler Tool to repeat the queries in time?
> Or is this depending on the order of the datasets for the report? If this
> would be the case - how could I change the order of execution of the dataset
> queries?
> Best regards,
> Stefoon|||Hi Markus,
the proportion is about the same. It seems that any query is executed 2
times.
I understand, that the parameter queries could run several times if you have
cascading picklists or something, but the query for the report's data?
Very mysterious.
Stefoon
"markus.rohsto@.gmail.com" wrote:
> I have a similar problem. I have a report created in Visual Studio
> based on a querry that retrieves data.
> Today however we found a need to count how many times rhis report was
> printed. Piece of cake I thought just put an UPDATE query at the botom
> of the querry that retrieves data.
> tested my simple UPDATE Invoice.ReminderNo = Invoice.ReminderNo + 1 in
> managment studio and it worked as i shoud.
> But when i tried running the report it suddenly added 10. so I put in
> another UPDATE that added one letter/execution to a text field. sure
> anough 10 pretty A:s.
> The query contains 5 separate datasets and is run 10 times when i
> render my report. I find that to striking to be a coincidence. I
> believe that the querry is run twice/dataset.
> How many datasets do you have, how many times is the querry run?
> On 1 Mar, 14:25, Stefoon23 <Stefoo...@.discussions.microsoft.com>
> wrote:
> > Using Reporting Services 2005 with SSAS 2005 I have a report working with
> > some parameters. In queries for the parameters some default values were
> > calculated. E.g. the current date. Then these values were used as input to
> > create mdx statements. Parameters!kdjfaj.value
> > When watching what is happening on the SSAS Server with SQL Profiler I
> > watched the queries executingSEVERALtimes("querybegin" as event class).
> > Is this a normal behaviour of Reporting Services?
> > Or is it just a gag of the Profiler Tool to repeat the queries in time?
> >
> > Or is this depending on the order of the datasets for the report? If this
> > would be the case - how could I change the order of execution of the dataset
> > queries?
> >
> > Best regards,
> > Stefoon
>
>
Queried Parameter Null Values
Hello,
For queried parameters, I need to have the option of using a null value as well. Is there a way to include a null value, or do I have to include it in the query that is returned?
Thanks.
Use DBNull.Value in .NET as the parameter value.
If this does not help you, please clarify your question.
|||I have a parameter that pulls back data to it. But, this field should be optional, so the user should also be able to select null as another value. How do I allow the user to select null? I can't figure this out... By the way, I tried setting the default value to System.DBNull.Value, and that didn't work... nothing returns, but I did try the query and the query did work. For some reason, this is with RS...
|||What are you using to send the command to the database? An ObjectDataSource, custom ADO.NET, etc. If you can tell us what you're using, how the users are currently selecting values, etc, it would help.
|||I'm using a Reporting Services DataSet, standard report... The query works fine, and it has been tested. I can't figure out how to send a null value to the stored procedure parameter. That is what the problem is.
|||Hello,
Here is the answer that I've found: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1789337&SiteID=1
Queried default values
Hello!
Iâ'm using reporting services in a web application. I have made my own user
interface (asp.net / c#) it works rather well except handling queried default
values.
Iâ'm using Stored Procedures to get the default values (non-queried default
values are no problem). The available values are selected the same way
without problems. The SP to get the default and valid values are using one of
the report parameters as a parameter and it works fine using the report
manager.
ReportParams = rs.GetReportParameters(ReportPath,null,true,ParamValues,null);
Above returns NULL or empty default values.
Can anyone help me how to handle queried default values?
If anyone is familiar with my problem, please reply with a solution.
Best Regards,
daniel_bI've done this in vb.net, and it works without problems. Are you sure
you are using the web service correctly?
I set the ParamValues parameter to be Nothing (null) if I have no known
parameter values yet, or to be an array of ParameterValues (with
prompt, name and value set) if I already know the value of some of the
parameters. I sometimes know the value of some parameters when I am
dealing with dependant parameters where the list of values for one
parameter is not known until another parameter has its value set.
ParameterList = service.GetReportParameters(ReportName, Nothing,
True, values, Nothing)
Does this help?
Kulgan.
Iâ'm using reporting services in a web application. I have made my own user
interface (asp.net / c#) it works rather well except handling queried default
values.
Iâ'm using Stored Procedures to get the default values (non-queried default
values are no problem). The available values are selected the same way
without problems. The SP to get the default and valid values are using one of
the report parameters as a parameter and it works fine using the report
manager.
ReportParams = rs.GetReportParameters(ReportPath,null,true,ParamValues,null);
Above returns NULL or empty default values.
Can anyone help me how to handle queried default values?
If anyone is familiar with my problem, please reply with a solution.
Best Regards,
daniel_bI've done this in vb.net, and it works without problems. Are you sure
you are using the web service correctly?
I set the ParamValues parameter to be Nothing (null) if I have no known
parameter values yet, or to be an array of ParameterValues (with
prompt, name and value set) if I already know the value of some of the
parameters. I sometimes know the value of some parameters when I am
dealing with dependant parameters where the list of values for one
parameter is not known until another parameter has its value set.
ParameterList = service.GetReportParameters(ReportName, Nothing,
True, values, Nothing)
Does this help?
Kulgan.
Quarterly Values
i have a date heirarchy
dim date
Year
Quarter
Month
and measures SalesVolume
how can i create salesvolume calculated measure for prev quarter, prev 3 quarters and so on.Dear Chilukurisri,
try to use the same logic of the other post and try to use the function QTD too.
Helped?
Regards!
|||thanx pedroCGD, but QTD will give Quarters to date value so if we select the current member then it will give the sum of quarters for all the values starting beginning of the year but what i am looking is for a formula to calculate last 3 quarters irrespective of wheather i select what member in the current year i.e end of the quarter or middle of the quarter. How can i achieve that?
Subscribe to:
Posts (Atom)