Problems in Search opertion using Like

Hi,
In our application we have a search page where people can search based on
different fields some of our fields support all chars like %, _ etc.,
We have to perform a like search on this field.
If the users typed % or _ in the text field then as usual its
considering it as wild card charecters.
Is there any way solve the problems like this.
Thanks & Regards,
Prasad DannaniTrap for searches on % and _. Replace these characters with a token like
PERCENT and UNDERSCORE. Modify your content so all instances of % and _ are
also replaced by PERCENT and UNDERSCORE. Then in your application have it
render PERCENT as % and UNDERSCORE as _.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based
on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>|||Use the ESCAPE clause for LIKE. I.e.escape the special craracter with a spec
ial character (specified
in the ESCAPE clause). Find all rows with text containing string "30% cheap"
:
SELECT *
FROM
(
SELECT 'It is 30% cheaper' AS a
UNION
SELECT 'it costs 30 bucke'
UNION
SELECT 'it costs 50 bucke'
) AS i
WHERE a LIKE '%30!% cheap%' ESCAPE '!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based o
n
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>|||Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:

> Trap for searches on % and _. Replace these characters with a token like
> PERCENT and UNDERSCORE. Modify your content so all instances of % and _
> are also replaced by PERCENT and UNDERSCORE. Then in your application have
> it render PERCENT as % and UNDERSCORE as _.
How about using the ESCAPE keyword or bracketing? eg.
SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
Those will look for the string 45% rather than all strings starting with 45.
That way you don't have to mess around with token replacement.
Dan
[vbcol=seagreen]
> "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> on|||Hi Dan,
Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
worked but it clearly says sql server like operator is always having
problems with one or the other charecter.
So, i was decided to make use of left function in the where clause
on for using Like which is not good performance wise.
We tries your first Query but its not worked for us.
In c or other languages we have proper escape charecters. I don't
why is it not there for sql server.
Once Again Thanks for Helping Us,
Prasad Dannani.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23%23umhYxoFHA.2580@.TK2MSFTNGP09.phx.gbl...
> Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
>
have[vbcol=seagreen]
> How about using the ESCAPE keyword or bracketing? eg.
> SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
> SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
> Those will look for the string 45% rather than all strings starting with
45.
> That way you don't have to mess around with token replacement.
> Dan
>
based[vbcol=seagreen]
>|||Did you read my post? Read it and you see how you escape a LIKE clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:ejirWb8oFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi Dan,
> Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
> worked but it clearly says sql server like operator is always having
> problems with one or the other charecter.
> So, i was decided to make use of left function in the where clause
> on for using Like which is not good performance wise.
> We tries your first Query but its not worked for us.
> In c or other languages we have proper escape charecters. I don't
> why is it not there for sql server.
> Once Again Thanks for Helping Us,
> Prasad Dannani.
>sql

Problems in Search opertion using Like

Hi,
In our application we have a search page where people can search based on
different fields some of our fields support all chars like %, _ etc.,
We have to perform a like search on this field.
If the users typed % or _ in the text field then as usual its
considering it as wild card charecters.
Is there any way solve the problems like this.
Thanks & Regards,
Prasad DannaniTrap for searches on % and _. Replace these characters with a token like
PERCENT and UNDERSCORE. Modify your content so all instances of % and _ are
also replaced by PERCENT and UNDERSCORE. Then in your application have it
render PERCENT as % and UNDERSCORE as _.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based
on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>|||Use the ESCAPE clause for LIKE. I.e.escape the special craracter with a special character (specified
in the ESCAPE clause). Find all rows with text containing string "30% cheap":
SELECT *
FROM
(
SELECT 'It is 30% cheaper' AS a
UNION
SELECT 'it costs 30 bucke'
UNION
SELECT 'it costs 50 bucke'
) AS i
WHERE a LIKE '%30!% cheap%' ESCAPE '!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>|||Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
> Trap for searches on % and _. Replace these characters with a token like
> PERCENT and UNDERSCORE. Modify your content so all instances of % and _
> are also replaced by PERCENT and UNDERSCORE. Then in your application have
> it render PERCENT as % and UNDERSCORE as _.
How about using the ESCAPE keyword or bracketing? eg.
SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
Those will look for the string 45% rather than all strings starting with 45.
That way you don't have to mess around with token replacement.
Dan
> "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> In our application we have a search page where people can search based
> on
>> different fields some of our fields support all chars like %, _ etc.,
>> We have to perform a like search on this field.
>> If the users typed % or _ in the text field then as usual its
>> considering it as wild card charecters.
>> Is there any way solve the problems like this.
>> Thanks & Regards,
>> Prasad Dannani|||Hi Dan,
Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
worked but it clearly says sql server like operator is always having
problems with one or the other charecter.
So, i was decided to make use of left function in the where clause
on for using Like which is not good performance wise.
We tries your first Query but its not worked for us.
In c or other languages we have proper escape charecters. I don't
why is it not there for sql server.
Once Again Thanks for Helping Us,
Prasad Dannani.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23%23umhYxoFHA.2580@.TK2MSFTNGP09.phx.gbl...
> Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
> > Trap for searches on % and _. Replace these characters with a token like
> > PERCENT and UNDERSCORE. Modify your content so all instances of % and _
> > are also replaced by PERCENT and UNDERSCORE. Then in your application
have
> > it render PERCENT as % and UNDERSCORE as _.
> How about using the ESCAPE keyword or bracketing? eg.
> SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
> SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
> Those will look for the string 45% rather than all strings starting with
45.
> That way you don't have to mess around with token replacement.
> Dan
> > "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> > news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> In our application we have a search page where people can search
based
> > on
> >> different fields some of our fields support all chars like %, _ etc.,
> >>
> >> We have to perform a like search on this field.
> >>
> >> If the users typed % or _ in the text field then as usual its
> >> considering it as wild card charecters.
> >>
> >> Is there any way solve the problems like this.
> >>
> >> Thanks & Regards,
> >>
> >> Prasad Dannani
> >>
>|||Did you read my post? Read it and you see how you escape a LIKE clause.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:ejirWb8oFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi Dan,
> Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
> worked but it clearly says sql server like operator is always having
> problems with one or the other charecter.
> So, i was decided to make use of left function in the where clause
> on for using Like which is not good performance wise.
> We tries your first Query but its not worked for us.
> In c or other languages we have proper escape charecters. I don't
> why is it not there for sql server.
> Once Again Thanks for Helping Us,
> Prasad Dannani.
>

Problems in Search opertion using Like

Hi,
In our application we have a search page where people can search based on
different fields some of our fields support all chars like %, _ etc.,
We have to perform a like search on this field.
If the users typed % or _ in the text field then as usual its
considering it as wild card charecters.
Is there any way solve the problems like this.
Thanks & Regards,
Prasad Dannani
Trap for searches on % and _. Replace these characters with a token like
PERCENT and UNDERSCORE. Modify your content so all instances of % and _ are
also replaced by PERCENT and UNDERSCORE. Then in your application have it
render PERCENT as % and UNDERSCORE as _.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based
on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>
|||Use the ESCAPE clause for LIKE. I.e.escape the special craracter with a special character (specified
in the ESCAPE clause). Find all rows with text containing string "30% cheap":
SELECT *
FROM
(
SELECT 'It is 30% cheaper' AS a
UNION
SELECT 'it costs 30 bucke'
UNION
SELECT 'it costs 50 bucke'
) AS i
WHERE a LIKE '%30!% cheap%' ESCAPE '!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>
|||Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:

> Trap for searches on % and _. Replace these characters with a token like
> PERCENT and UNDERSCORE. Modify your content so all instances of % and _
> are also replaced by PERCENT and UNDERSCORE. Then in your application have
> it render PERCENT as % and UNDERSCORE as _.
How about using the ESCAPE keyword or bracketing? eg.
SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
Those will look for the string 45% rather than all strings starting with 45.
That way you don't have to mess around with token replacement.
Dan
[vbcol=seagreen]
> "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> on
|||Hi Dan,
Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
worked but it clearly says sql server like operator is always having
problems with one or the other charecter.
So, i was decided to make use of left function in the where clause
on for using Like which is not good performance wise.
We tries your first Query but its not worked for us.
In c or other languages we have proper escape charecters. I don't
why is it not there for sql server.
Once Again Thanks for Helping Us,
Prasad Dannani.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23%23umhYxoFHA.2580@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
have
> How about using the ESCAPE keyword or bracketing? eg.
> SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
> SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
> Those will look for the string 45% rather than all strings starting with
45.[vbcol=seagreen]
> That way you don't have to mess around with token replacement.
> Dan
based
>
|||Did you read my post? Read it and you see how you escape a LIKE clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:ejirWb8oFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi Dan,
> Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
> worked but it clearly says sql server like operator is always having
> problems with one or the other charecter.
> So, i was decided to make use of left function in the where clause
> on for using Like which is not good performance wise.
> We tries your first Query but its not worked for us.
> In c or other languages we have proper escape charecters. I don't
> why is it not there for sql server.
> Once Again Thanks for Helping Us,
> Prasad Dannani.
>

Problems Configuring HTTP Access to SQL Server 2005 Analysis Serveices on Windows XP

I tried to setup HTTP access to SQL Server 2005 Analysis Services on Windows XP, based on the technet article of the same name.

On setting up the virtual directory properties step, I could not add the msmdpump.dll in the Add/Edit Application Extension Mapping as the OK button is greyed out. What needs to be done to enable adding msmdpump.dll?

Any help would be appreciated!

Make sure that ".dll" is in the extension box (don't forget the period). And if the path to msmdpump.dll has an ellipse in the middle of it (...) after you browse to it and select it, click in the path text. The ellipse will go away and the ok button will become active. I had the same problem...|||

Ahha. The dot in front the dll did the trick.

Thank you very much!

problem: select Max(date) which is less than actual Max(date)

based on the data at the beginning of this thread I use this query and get
this result
SELECT recid, Max(curDate) curDate, MAX(expDate) expDate
FROM #temp
GROUP BY recid
HAVING MAX(expDate)='1/25/06'
--gives the correct result for this query but......
recID curDate expDate
1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
I am selecting only rows where the Max(expDate) is '1/25/06'. That would
include rows with recID of 1, 4, and 5 since recID's 2, 3, and 6 have a
Max(expDate) of '2/1/06' which is greater than '1/25/06'.
Well, I need to write another query that selects not only the rows with a
Max(expDate) of '1/25/06', but will also select rows that contain an expDate
of '1/25/06' -- IF -- the corresponding curDate for that row and recID i
s
the Max(curDate) for that recID for the corresponding expDate of '1/25/06'.
recID 6 contains a row with expDate of '1/25/06' which is not its
Max(expDate). However, the corresponding curDate for that row is '1/5/06'
OK. I see your problem. I screwed up. The curDate was supposed to be
'1/5/06' which would make it the Max(curDate) for recID 6 for the
corresponding expdate of '1/25/06'. So I need to pick up that row in
addition to the rows that have a Max(expDate) of '1/25/06', even though for
recID 6 expDate of 1/25/06 is not the Max(expDate), but curDate is
Max(curDate) for recID 6 expDate 1/25/06.
Sorry for the goof up. Any suggestions how I could write such a query?
"Roy Harvey" wrote:

> Rich,
> This is just a wee bit hard for me to sort out. One part in
> particular has me especially .
> The total set of data for recID = 6 is:
>
> Your desired result includes:
> I simply do not see where a curDate in 2006 came from that input data.
> Your specs seem to talk about using a different value for expDate
> under specific circumstances, but I was unable to see anything about
> using any different calculation for curDate than a value of curDate.
> Roy Harvey
> Beacon Falls, CT
>Here is what I came up with, including the last couple of lines of
data load with the correction. The basic idea is to write it as two
queries with a UNION. The first part is what you already have
working. The second part tries to get the row that meets the
secondary criteria. Note that the first query is pretty much
duplicated in the second for the NOT IN test.
While it appears to return the result you wanted from the data
provided, I do not guarantee it will do as well when you have the real
data.
select 6, '1/5/06', '1/25/06' union -- corrected curDate
select 6, '11/27/05', '2/1/06'
SELECT recid, Max(curDate) curDate, MAX(expDate) expDate
FROM #temp
GROUP BY recid
HAVING MAX(expDate)='1/25/06'
UNION ALL
SELECT *
FROM #temp as X
WHERE recid NOT IN --the list of recid from beginning
(SELECT recid
FROM #temp
GROUP BY recid
HAVING MAX(expDate)='1/25/06')
AND expDate = '1/25/06'
AND curDate >=
(select curDate from #temp as Y
where X.recid = Y.recid
and Y.expDate =
(select max(expDate) from #temp as Z
where X.recid = Z.recid))
Roy Harvey
Beacon Falls, CT|||Thanks very much. The Union query was the route I was going to go, but I
thought that maybe there a more sophisticated way to go. I guess I need to
have a little more confidence in myself.
Actually, the way you did it is fairly sophisticated. My idea of using a
Union query was way more verbose, and thus, I was trying to avoid it. But
you nailed it
thanks very much for your help.
Rich
"Roy Harvey" wrote:

> Here is what I came up with, including the last couple of lines of
> data load with the correction. The basic idea is to write it as two
> queries with a UNION. The first part is what you already have
> working. The second part tries to get the row that meets the
> secondary criteria. Note that the first query is pretty much
> duplicated in the second for the NOT IN test.
> While it appears to return the result you wanted from the data
> provided, I do not guarantee it will do as well when you have the real
> data.
> select 6, '1/5/06', '1/25/06' union -- corrected curDate
> select 6, '11/27/05', '2/1/06'
>
> SELECT recid, Max(curDate) curDate, MAX(expDate) expDate
> FROM #temp
> GROUP BY recid
> HAVING MAX(expDate)='1/25/06'
> UNION ALL
> SELECT *
> FROM #temp as X
> WHERE recid NOT IN --the list of recid from beginning
> (SELECT recid
> FROM #temp
> GROUP BY recid
> HAVING MAX(expDate)='1/25/06')
> AND expDate = '1/25/06'
> AND curDate >=
> (select curDate from #temp as Y
> where X.recid = Y.recid
> and Y.expDate =
> (select max(expDate) from #temp as Z
> where X.recid = Z.recid))
> Roy Harvey
> Beacon Falls, CT
>|||If I may, I added one more twist to this query. I added one more row to
#temp where recID 2 now also has an expDate of 1/25/06 which is less than it
s
max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that correspond
s
to the expDate of 1/25/06 - same situation at recID 6 where the curDate that
corresponds to recID 2 expDate of 2/1/06 is less than the recID 2
Max(curDate) of 1/7/06. The 2nd part of the current Union query only picks
up recID 6.
So what I need to do is modify your query to see if I can pick up recID 2 in
addition to recID 6 along with the original recID's of 1, 4, 5.
This is the part where I was afraid of getting verbose. I will experiment
The real scenario has hundreds of thousands of records and there are rows
that match my sample. The pain.
"Rich" wrote:
> Thanks very much. The Union query was the route I was going to go, but I
> thought that maybe there a more sophisticated way to go. I guess I need t
o
> have a little more confidence in myself.
> Actually, the way you did it is fairly sophisticated. My idea of using a
> Union query was way more verbose, and thus, I was trying to avoid it. But
> you nailed it
> thanks very much for your help.
> Rich
> "Roy Harvey" wrote:
>|||I must be trying too hard. Your query works fine with the additional new ro
w
for recID 2. Sorry bout that.
"Rich" wrote:
> If I may, I added one more twist to this query. I added one more row to
> #temp where recID 2 now also has an expDate of 1/25/06 which is less than
its
> max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that correspo
nds
> to the expDate of 1/25/06 - same situation at recID 6 where the curDate th
at
> corresponds to recID 2 expDate of 2/1/06 is less than the recID 2
> Max(curDate) of 1/7/06. The 2nd part of the current Union query only pic
ks
> up recID 6.
> So what I need to do is modify your query to see if I can pick up recID 2
in
> addition to recID 6 along with the original recID's of 1, 4, 5.
> This is the part where I was afraid of getting verbose. I will experiment
> The real scenario has hundreds of thousands of records and there are rows
> that match my sample. The pain.
>
> "Rich" wrote:
>|||so, taking this to the nth level (for posterity incase I need to refer to
this again), I added yet one more row to recID 2. This additional row is a
2nd expDate of 1/25/06. So now recID 2 has 2 rows with expdate of 1/25/06.
One of these rows contains a Max(curDate) for recID 2.
create table #temp(
recID int, curDate datetime, expDate datetime)
insert into #temp
select 1, '11/1/05', '1/5/06' union
select 1, '11/5/05', '1/13/06' union
select 1, '11/12/05', '1/25/06' union
select 1, '11/28/05', '1/25/06' union
select 2, '11/7/05', '1/7/06' union
select 2, '11/13/05', '1/12/06' union
select 2, '11/27/05', '1/15/06' union
select 2, '1/7/06', '1/25/06' union
select 2, '1/8/06', '1/25/06' union
select 2, '12/1/05', '2/1/06' union
select 3, '11/3/05', '1/7/06' union
select 3, '11/8/05', '1/12/06' union
select 3, '11/17/05', '1/23/06' union
select 3, '12/1/05', '2/1/06' union
select 4, '11/5/05', '1/3/06' union
select 4, '11/9/05', '1/7/06' union
select 4, '11/19/05', '1/14/06' union
select 4, '11/23/05', '1/25/06' union
select 5, '11/5/05', '1/3/06' union
select 5, '11/9/05', '1/7/06' union
select 5, '11/19/05', '1/25/06' union
select 5, '11/27/05', '1/25/06' union
select 6, '11/5/05', '1/3/06' union
select 6, '11/9/05', '1/7/06' union
select 6, '11/19/05', '1/25/06' union
select 6, '1/5/06', '1/25/06' union
select 6, '11/27/05', '2/1/06'
The goal is to retrieve only the rows where Max(expDate) is 1/25/06, and to
also retrieve rows that contain an expdate of 1/25/06 that is not the
Max(expDate) but these additional rows contain a curdate in the same row as
the expDate of 1/25/06 which is the Max(curDate) for that recID. In this
sample, recID 2 contains 2 rows with expDate of 1/25/06 of which one of thos
e
2 rows contains the Max(curDate) for recID 2, and dittor for recID 6. The
goal is to retrieve rows for recID 1, 4, 5 which contain Max(expDate) of
1/25/06 and also rows for recID 2 and recID6 which contain a Max(curDate) fo
r
the rows that contain an expDate of 1/25/06.
What I did was to modify Roy's query as follows:
SELECT recid, Max(curDate) curDate, MAX(expDate) expDate
FROM #temp
GROUP BY recid
HAVING MAX(expDate)='1/25/06'
UNION ALL
select recID, Max(curDate) m, expdate from --I added this line
(SELECT * FROM #temp as X
WHERE
recid NOT IN --the list of recid from beginning
(SELECT recid FROM #temp GROUP BY recid
HAVING MAX(expDate)='1/25/06')
AND
expDate = '1/25/06'
AND curDate >
(select curDate from #temp as Y
where X.recid = Y.recid
and Y.expDate =
(select max(expDate) from #temp as Z
where X.recid = Z.recid))) t1
Group By recid, expdate
--this gives
recID curDate expDate
1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
2 2006-01-08 00:00:00.000 2006-01-25 00:00:00.000
6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000
"Rich" wrote:
> I must be trying too hard. Your query works fine with the additional new
row
> for recID 2. Sorry bout that.
> "Rich" wrote:
>|||This gives the same result:
select recid,max(curDate) curDate, expDate
from #temp
where expDate='1/25/06'
group by recid,expDate
-oj
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:7634C35E-CD6B-4E14-8D6E-7DBAE3861BAC@.microsoft.com...
> so, taking this to the nth level (for posterity incase I need to refer to
> this again), I added yet one more row to recID 2. This additional row is
> a
> 2nd expDate of 1/25/06. So now recID 2 has 2 rows with expdate of
> 1/25/06.
> One of these rows contains a Max(curDate) for recID 2.
>
> create table #temp(
> recID int, curDate datetime, expDate datetime)
> insert into #temp
> select 1, '11/1/05', '1/5/06' union
> select 1, '11/5/05', '1/13/06' union
> select 1, '11/12/05', '1/25/06' union
> select 1, '11/28/05', '1/25/06' union
> select 2, '11/7/05', '1/7/06' union
> select 2, '11/13/05', '1/12/06' union
> select 2, '11/27/05', '1/15/06' union
> select 2, '1/7/06', '1/25/06' union
> select 2, '1/8/06', '1/25/06' union
> select 2, '12/1/05', '2/1/06' union
> select 3, '11/3/05', '1/7/06' union
> select 3, '11/8/05', '1/12/06' union
> select 3, '11/17/05', '1/23/06' union
> select 3, '12/1/05', '2/1/06' union
> select 4, '11/5/05', '1/3/06' union
> select 4, '11/9/05', '1/7/06' union
> select 4, '11/19/05', '1/14/06' union
> select 4, '11/23/05', '1/25/06' union
> select 5, '11/5/05', '1/3/06' union
> select 5, '11/9/05', '1/7/06' union
> select 5, '11/19/05', '1/25/06' union
> select 5, '11/27/05', '1/25/06' union
> select 6, '11/5/05', '1/3/06' union
> select 6, '11/9/05', '1/7/06' union
> select 6, '11/19/05', '1/25/06' union
> select 6, '1/5/06', '1/25/06' union
> select 6, '11/27/05', '2/1/06'
>
> The goal is to retrieve only the rows where Max(expDate) is 1/25/06, and
> to
> also retrieve rows that contain an expdate of 1/25/06 that is not the
> Max(expDate) but these additional rows contain a curdate in the same row
> as
> the expDate of 1/25/06 which is the Max(curDate) for that recID. In this
> sample, recID 2 contains 2 rows with expDate of 1/25/06 of which one of
> those
> 2 rows contains the Max(curDate) for recID 2, and dittor for recID 6. The
> goal is to retrieve rows for recID 1, 4, 5 which contain Max(expDate) of
> 1/25/06 and also rows for recID 2 and recID6 which contain a Max(curDate)
> for
> the rows that contain an expDate of 1/25/06.
> What I did was to modify Roy's query as follows:
> SELECT recid, Max(curDate) curDate, MAX(expDate) expDate
> FROM #temp
> GROUP BY recid
> HAVING MAX(expDate)='1/25/06'
> UNION ALL
> select recID, Max(curDate) m, expdate from --I added this line
> (SELECT * FROM #temp as X
> WHERE
> recid NOT IN --the list of recid from beginning
> (SELECT recid FROM #temp GROUP BY recid
> HAVING MAX(expDate)='1/25/06')
> AND
> expDate = '1/25/06'
> AND curDate >
> (select curDate from #temp as Y
> where X.recid = Y.recid
> and Y.expDate =
> (select max(expDate) from #temp as Z
> where X.recid = Z.recid))) t1
> Group By recid, expdate
> --this gives
> recID curDate expDate
> 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
> 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> 2 2006-01-08 00:00:00.000 2006-01-25 00:00:00.000
> 6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000
>
> "Rich" wrote:
>|||On Thu, 2 Mar 2006 22:56:26 -0800, Rich
<Rich@.discussions.microsoft.com> wrote:

>so, taking this to the nth level (for posterity incase I need to refer to
>this again)....
Sounds like you have things well in hand. (I really don't have time
to get into the logic any further.) My only thought, in light of oj's
reply, is to add more variety to the test data. Make it a LOT more
complicated. I'm sure you know about the problem of programmers only
testing part of their logic because they use the application the say
they think it should be used. Well, us DB folks can suffer from the
same problem, testing with test cases that are only a subset of what
the real world might hit us with.
Good luck!
Roy Harvey
Beacon Falls, CT|||Thanks all for your replies. OJ's solution is less verbose than the one I
was working with, but at least we all got the idea what I was trying to do -
at least I am starting to get the idea. And Roy is correct on making the
data more complex to cover more twists. Believe me, the actual data is
quite a bit more complex, but at least now I am chiseling down the queries
that I need. Start out with verbose till I understand what I am looking for
and then drill down to the less verbose.
"Rich" wrote:
> so, taking this to the nth level (for posterity incase I need to refer to
> this again), I added yet one more row to recID 2. This additional row is
a
> 2nd expDate of 1/25/06. So now recID 2 has 2 rows with expdate of 1/25/06
.
> One of these rows contains a Max(curDate) for recID 2.
>
> create table #temp(
> recID int, curDate datetime, expDate datetime)
> insert into #temp
> select 1, '11/1/05', '1/5/06' union
> select 1, '11/5/05', '1/13/06' union
> select 1, '11/12/05', '1/25/06' union
> select 1, '11/28/05', '1/25/06' union
> select 2, '11/7/05', '1/7/06' union
> select 2, '11/13/05', '1/12/06' union
> select 2, '11/27/05', '1/15/06' union
> select 2, '1/7/06', '1/25/06' union
> select 2, '1/8/06', '1/25/06' union
> select 2, '12/1/05', '2/1/06' union
> select 3, '11/3/05', '1/7/06' union
> select 3, '11/8/05', '1/12/06' union
> select 3, '11/17/05', '1/23/06' union
> select 3, '12/1/05', '2/1/06' union
> select 4, '11/5/05', '1/3/06' union
> select 4, '11/9/05', '1/7/06' union
> select 4, '11/19/05', '1/14/06' union
> select 4, '11/23/05', '1/25/06' union
> select 5, '11/5/05', '1/3/06' union
> select 5, '11/9/05', '1/7/06' union
> select 5, '11/19/05', '1/25/06' union
> select 5, '11/27/05', '1/25/06' union
> select 6, '11/5/05', '1/3/06' union
> select 6, '11/9/05', '1/7/06' union
> select 6, '11/19/05', '1/25/06' union
> select 6, '1/5/06', '1/25/06' union
> select 6, '11/27/05', '2/1/06'
>
> The goal is to retrieve only the rows where Max(expDate) is 1/25/06, and t
o
> also retrieve rows that contain an expdate of 1/25/06 that is not the
> Max(expDate) but these additional rows contain a curdate in the same row a
s
> the expDate of 1/25/06 which is the Max(curDate) for that recID. In this
> sample, recID 2 contains 2 rows with expDate of 1/25/06 of which one of th
ose
> 2 rows contains the Max(curDate) for recID 2, and dittor for recID 6. The
> goal is to retrieve rows for recID 1, 4, 5 which contain Max(expDate) of
> 1/25/06 and also rows for recID 2 and recID6 which contain a Max(curDate)
for
> the rows that contain an expDate of 1/25/06.
> What I did was to modify Roy's query as follows:
> SELECT recid, Max(curDate) curDate, MAX(expDate) expDate
> FROM #temp
> GROUP BY recid
> HAVING MAX(expDate)='1/25/06'
> UNION ALL
> select recID, Max(curDate) m, expdate from --I added this line
> (SELECT * FROM #temp as X
> WHERE
> recid NOT IN --the list of recid from beginning
> (SELECT recid FROM #temp GROUP BY recid
> HAVING MAX(expDate)='1/25/06')
> AND
> expDate = '1/25/06'
> AND curDate >
> (select curDate from #temp as Y
> where X.recid = Y.recid
> and Y.expDate =
> (select max(expDate) from #temp as Z
> where X.recid = Z.recid))) t1
> Group By recid, expdate
> --this gives
> recID curDate expDate
> 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
> 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> 2 2006-01-08 00:00:00.000 2006-01-25 00:00:00.000
> 6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000
>
> "Rich" wrote:
>|||Well, OJ's less verbose query isn't working on my actual data because I am
picking up rows with expDates that are not Max(expDate) for a given recID
where the curDate is not the Max(curDate) for the given recID and expDate.
I
need to pick up rows where the Max(expDate) is 1/25/06 or if a recID
contains a newer Max(expDate) than 1/25/06, I pick that row/recID up if the
corresponding curDate for the row with expdate = 1/25/06 is greater than th
e
curdate for the row containing the newer expDate for the given recID. So it
looks like I will have to go with the more verbose query. I was hoping it
would be a simple query, but no such luck. Oh well, at least one of the
solutions will work for my data.
"Rich" wrote:
> Thanks all for your replies. OJ's solution is less verbose than the one I
> was working with, but at least we all got the idea what I was trying to do
-
> at least I am starting to get the idea. And Roy is correct on making the
> data more complex to cover more twists. Believe me, the actual data is
> quite a bit more complex, but at least now I am chiseling down the queries
> that I need. Start out with verbose till I understand what I am looking f
or
> and then drill down to the less verbose.
> "Rich" wrote:
>

Problem with User Defined Function

I wrote a UDF to get a rolling average based upon a date passed to the UDF. The error I get is:

Server: Msg 102, Level 15, State 1, Procedure fn_RollAverage, Line 26
Incorrect syntax near ')'.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION fn_RollAverage(@.CURVE_DATE AS SMALLDATETIME)
RETURNS @.TBLRESULT TABLE
(US0001M_Index AS FLOAT,
US0003M_INDEX AS FLOAT,
US0006M_INDEX AS FLOAT,
US0012M_INDEX AS FLOAT,
usswap2_index AS FLOAT,
usswap3_index AS FLOAT,
usswap4_index AS FLOAT,
usswap5_index AS FLOAT,
usswap6_index AS FLOAT,
usswap7_index AS FLOAT,
usswap8_index AS FLOAT,
usswap9_index AS FLOAT,
usswap10_index AS FLOAT,
usswap11_index AS FLOAT,
usswap12_index AS FLOAT,
usswap13_index AS FLOAT,
usswap14_index AS FLOAT,
usswap15_index AS FLOAT,
usswap20_index AS FLOAT,
usswap25_index AS FLOAT,
usswap30_index AS FLOAT)

AS BEGIN

DECLARE @.BEG_DATE AS SMALLDATETIME

SET @.BEG_DATE = (@.CURVE_DATE - 30)

INSERT @.TBLRESULT(
US0001M_Index,
US0003M_INDEX,
US0006M_INDEX,
US0012M_INDEX,
usswap2_index,
usswap3_index,
usswap4_index,
usswap5_index,
usswap6_index,
usswap7_index,
usswap8_index,
usswap9_index,
usswap10_index,
usswap11_index,
usswap12_index,
usswap13_index,
usswap14_index,
usswap15_index,
usswap20_index,
usswap25_index,
usswap30_index)

SELECT
AVG(US0001M_Index) AS US0001M_Index,
AVG(US0003M_INDEX) AS US0003M_INDEX,
AVG(US0006M_INDEX) AS US0006M_INDEX,
AVG(US0012M_INDEX) AS US0012M_INDEX,
AVG(usswap2_index) AS usswap2_index,
AVG(usswap3_index) AS usswap3_index,
AVG(usswap4_index) AS usswap4_index,
AVG(usswap5_index) AS usswap5_index,
AVG(usswap6_index) AS usswap6_index,
AVG(usswap7_index) AS usswap7_index,
AVG(usswap8_index) AS usswap8_index,
AVG(usswap9_index) AS usswap9_index,
AVG(usswap10_index) AS usswap10_index,
AVG(usswap11_index) AS usswap11_index,
AVG(usswap12_index) AS usswap12_index,
AVG(usswap13_index) AS usswap13_index,
AVG(usswap14_index) AS usswap14_index,
AVG(usswap15_index) AS usswap15_index,
AVG(usswap20_index) AS usswap20_index,
AVG(usswap25_index) AS usswap25_index,
AVG(usswap30_index) AS usswap30_index

FROM dbo.LiborSwap
WHERE CURVE_DATE BETWEEN @.BEG_DATE AND @.CURVE_DATE

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

DECLARE @.TBLRESULT TABLE
(US0001M_Index FLOAT,
US0003M_INDEX FLOAT,
US0006M_INDEX FLOAT

...)

|||

The " AS " keyword is not valid in the @.TBLRESULT table declaration.

|||Thanks thanks a thousand thanks

Problem with update query based on subquery

Hello all!
I have created a update query, which looks like this:
UPDATE NMR.dbo.NMR_wpisy
INNER JOIN
(SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
Machine]) AS Confirmed_Production_Machine,
Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
ProductionConfirmation ON (N.workcenter_sortowanie =
NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
= ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR)
ON NMR.dbo.NMR_wpisy.nr_NMR=N.nr_NMR SET
NMR.dbo.NMR_wpisy.czas_machine_sortowanie =
[Confirmed_Production_Machine], NMR.dbo.NMR_wpisy.czas_labour_sortowanie
= [Confirmed_Production_Labor]
Could you modify these query to be right, because there is the error? I
would be very grateful for it...
Please have a look what I would achieve:
I want to sum double or more values from the table using such query
(let`s call it query1):
SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
Machine]) AS Confirmed_Production_Machine,
Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
ProductionConfirmation ON (N.workcenter_sortowanie =
NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
= ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR
This query works fine. After that I want to update the values of
NMR_wpisy table which are in relations with nr_NMR field from the above
query1 and there (in query1) are the values which I would like to to
update the fields. I don`t know how to do that. I was trying to do it on
MS Access and copy this whole query into the MS SQL but if the subquery
exists I cannot do that.
Please help
I would be very grateful for it
Best regards
Marcin
*** Sent via Developersdex http://www.examnotes.net ***Try,
UPDATE a
SET
a.czas_machine_sortowanie = b.[Confirmed_Production_Machine],
a.czas_labour_sortowanie = b.[Confirmed_Production_Labor]
from
NMR.dbo.NMR_wpisy as a
INNER JOIN
(
SELECT
N.nr_NMR,
Sum(ProductionConfirmation.[Confirmed Production Machine]) AS
Confirmed_Production_Machine,
Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
Confirmed_Production_Labor
FROM
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
GROUP BY
N.nr_NMR
) as b
ON b.nr_NMR = a.nr_NMR
-- or
UPDATE NMR.dbo.NMR_wpisy
SET
NMR.dbo.NMR_wpisy.czas_machine_sortowanie = isnull(
(
SELECT
Sum(ProductionConfirmation.[Confirmed Production Machine]) AS
Confirmed_Production_Machine
FROM
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
where
N.nr_NMR = NMR.dbo.NMR_wpisy.nr_NMR
GROUP BY
N.nr_NMR
), 0),
NMR.dbo.NMR_wpisy.czas_labour_sortowanie = isnull(
(
SELECT
Sum(ProductionConfirmation.[Confirmed Production Labor])
FROM
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
where
N.nr_NMR = NMR.dbo.NMR_wpisy.nr_NMR
GROUP BY
N.nr_NMR
), 0)
where
exists(
select
*
from
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
where
N.nr_NMR = NMR.dbo.NMR_wpisy.nr_NMR
)
go
AMB
"Marcin Zmyslowski" wrote:

> Hello all!
> I have created a update query, which looks like this:
> UPDATE NMR.dbo.NMR_wpisy
> INNER JOIN
> (SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
> Machine]) AS Confirmed_Production_Machine,
> Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
> Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
> ProductionConfirmation ON (N.workcenter_sortowanie =
> NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
> = ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR)
> ON NMR.dbo.NMR_wpisy.nr_NMR=N.nr_NMR SET
> NMR.dbo.NMR_wpisy.czas_machine_sortowanie =
> [Confirmed_Production_Machine], NMR.dbo.NMR_wpisy.czas_labour_sortowanie
> = [Confirmed_Production_Labor]
> Could you modify these query to be right, because there is the error? I
> would be very grateful for it...
> Please have a look what I would achieve:
> I want to sum double or more values from the table using such query
> (let`s call it query1):
> SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
> Machine]) AS Confirmed_Production_Machine,
> Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
> Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
> ProductionConfirmation ON (N.workcenter_sortowanie =
> NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
> = ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR
> This query works fine. After that I want to update the values of
> NMR_wpisy table which are in relations with nr_NMR field from the above
> query1 and there (in query1) are the values which I would like to to
> update the fields. I don`t know how to do that. I was trying to do it on
> MS Access and copy this whole query into the MS SQL but if the subquery
> exists I cannot do that.
> Please help
> I would be very grateful for it
> Best regards
> Marcin
>
> *** Sent via Developersdex http://www.examnotes.net ***
>