Query for search engine

On Fri, 21 Jan 2005 11:51:02 -0800, Ray wrote:

>Sample data:
>iParseID iPageID sPhrase iR
ank
>22592 daacf052-122a-446b-99c4-ead865dd468f home 33
>22593 2cb2ebf2-4268-498a-9128-d223f09253e7 page 43
>22594 2cb2ebf2-4268-498a-9128-d223f09253e7 home 15
>22595 588e6a38-5f0e-4704-87f5-e5d17f6033f0 trade 24
>22596 e51b431c-4b29-4316-8f45-0ac24b8fc812 home 23
>22597 daacf052-122a-446b-99c4-ead865dd468f london 51
>When I do a search for "london home" then I only want the iPageID of record
>22597 returned and not for 22594 nor 22596.
>Both records 22594 and 22596 has "home" as a value for sParse, but there ar
e
>no other records where the iPageID is the same as for records 22594 and 225
96
>and has "london" as the value for sPhrase.
>I hope this makes sence.
Hi Ray,
I think it does. The only thing I don't know what rank to use for the
ordering, since the iPageID of rows 22597 and 22592 is associated with
iRank 33 and iRank 23. My guess is that you want the lowest of the two.
Try if this code works for you:
SELECT a.iPageID
FROM tbParse AS a
INNER JOIN tbParse AS b
ON b.iPageID = a.iPageID
WHERE a.sPhrase LIKE '%searchword1%'
AND b.sPhrase LIKE '%searchword2%'
ORDER BY CASE WHEN a.iRank < b.iRank THEN a.iRank ELSE b.iRank END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)I take both your and Michael's comments onboard, but what if there are more
than two search phrases? Do I have to build the query string dynamically
according to the number of search phrases or is their an easier way?
I have not test either of the queries, as it is already 11:30pm, but will
give it a go in the morning and get back with the results.
Thanks in advance!
"Hugo Kornelis" wrote:

> On Fri, 21 Jan 2005 11:51:02 -0800, Ray wrote:
>
> Hi Ray,
> I think it does. The only thing I don't know what rank to use for the
> ordering, since the iPageID of rows 22597 and 22592 is associated with
> iRank 33 and iRank 23. My guess is that you want the lowest of the two.
> Try if this code works for you:
> SELECT a.iPageID
> FROM tbParse AS a
> INNER JOIN tbParse AS b
> ON b.iPageID = a.iPageID
> WHERE a.sPhrase LIKE '%searchword1%'
> AND b.sPhrase LIKE '%searchword2%'
> ORDER BY CASE WHEN a.iRank < b.iRank THEN a.iRank ELSE b.iRank END
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Fri, 21 Jan 2005 15:33:03 -0800, Ray wrote:

>I take both your and Michael's comments onboard, but what if there are more
>than two search phrases? Do I have to build the query string dynamically
>according to the number of search phrases or is their an easier way?
Hi Ray,
Thinking it over, there might be another way that is easier expanded.
SELECT iPageID, MIN(iRank) AS Rank
FROM tbParse
WHERE sPhrase LIKE '%searchword1%'
OR sPhrase LIKE '%searchword2%'
GROUP BY iPageID
HAVING COUNT(*) = 2
ORDER BY Rank
(untested)
If your number of search strings varies, you can put the search strings in
a table and change the query as follows:
SELECT tbParse.iPageID, MIN(tbParse.iRank) AS Rank
FROM tbParse
INNER JOIN SearchWords
ON tbParse.sPhrase LIKE '%' + SearchWords.Word + '%'
GROUP BY tbParse.iPageID
HAVING COUNT(*) = (SELECT COUNT(*) FROM SearchWords)
ORDER BY Rank
(untested)
I also second Michael's thoughts: if sPhrase is indexed, consider using
sPhrase LIKE 'searchword%' (only a wildcard % at the end)
or even
sPhrase = 'searchword' (test for equality only).
unless you enjoy prolonged waits while SQL Server is busy...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Brilliant! Just what I was looking for.
Thanks for all your replies. The help is much appreciated.
Ray
"Hugo Kornelis" wrote:

> On Fri, 21 Jan 2005 15:33:03 -0800, Ray wrote:
>
> Hi Ray,
> Thinking it over, there might be another way that is easier expanded.
> SELECT iPageID, MIN(iRank) AS Rank
> FROM tbParse
> WHERE sPhrase LIKE '%searchword1%'
> OR sPhrase LIKE '%searchword2%'
> GROUP BY iPageID
> HAVING COUNT(*) = 2
> ORDER BY Rank
> (untested)
> If your number of search strings varies, you can put the search strings in
> a table and change the query as follows:
> SELECT tbParse.iPageID, MIN(tbParse.iRank) AS Rank
> FROM tbParse
> INNER JOIN SearchWords
> ON tbParse.sPhrase LIKE '%' + SearchWords.Word + '%'
> GROUP BY tbParse.iPageID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM SearchWords)
> ORDER BY Rank
> (untested)
> I also second Michael's thoughts: if sPhrase is indexed, consider using
> sPhrase LIKE 'searchword%' (only a wildcard % at the end)
> or even
> sPhrase = 'searchword' (test for equality only).
> unless you enjoy prolonged waits while SQL Server is busy...
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

query engine statitistics

Hi, Is there an esay way to gather information about how many times each
stored procedures has been called , the average execution time , etc ..
I know profiling can give you punctual info about this .. however i need
aggregated info over a determined period of time
thank in advance
best regards
Enrico Sabbadin
MTS/COM+/VBCOM/.NET FAQ: http://www.sabbasoft.com
BLOG: http://www.sabbasoft.com/myblog
Check out sys.dm_exec_query_stats if you are on 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<x> wrote in message news:OvENi8jcHHA.4820@.TK2MSFTNGP06.phx.gbl...
> Hi, Is there an esay way to gather information about how many times each stored procedures has
> been called , the average execution time , etc ..
> I know profiling can give you punctual info about this .. however i need aggregated info over a
> determined period of time
> thank in advance
> best regards
> --
> Enrico Sabbadin
> MTS/COM+/VBCOM/.NET FAQ: http://www.sabbasoft.com
> BLOG: http://www.sabbasoft.com/myblog
>

query engine statitistics

Hi, Is there an esay way to gather information about how many times each
stored procedures has been called , the average execution time , etc ..
I know profiling can give you punctual info about this .. however i need
aggregated info over a determined period of time
thank in advance
best regards
Enrico Sabbadin
MTS/COM+/VBCOM/.NET FAQ: http://www.sabbasoft.com
BLOG: http://www.sabbasoft.com/myblogCheck out sys.dm_exec_query_stats if you are on 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<x> wrote in message news:OvENi8jcHHA.4820@.TK2MSFTNGP06.phx.gbl...
> Hi, Is there an esay way to gather information about how many times each s
tored procedures has
> been called , the average execution time , etc ..
> I know profiling can give you punctual info about this .. however i need a
ggregated info over a
> determined period of time
> thank in advance
> best regards
> --
> Enrico Sabbadin
> MTS/COM+/VBCOM/.NET FAQ: http://www.sabbasoft.com
> BLOG: http://www.sabbasoft.com/myblog
>

query engine statitistics

Hi, Is there an esay way to gather information about how many times each
stored procedures has been called , the average execution time , etc ..
I know profiling can give you punctual info about this .. however i need
aggregated info over a determined period of time
thank in advance
best regards
--
Enrico Sabbadin
MTS/COM+/VBCOM/.NET FAQ: http://www.sabbasoft.com
BLOG: http://www.sabbasoft.com/myblogCheck out sys.dm_exec_query_stats if you are on 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<x> wrote in message news:OvENi8jcHHA.4820@.TK2MSFTNGP06.phx.gbl...
> Hi, Is there an esay way to gather information about how many times each stored procedures has
> been called , the average execution time , etc ..
> I know profiling can give you punctual info about this .. however i need aggregated info over a
> determined period of time
> thank in advance
> best regards
> --
> Enrico Sabbadin
> MTS/COM+/VBCOM/.NET FAQ: http://www.sabbasoft.com
> BLOG: http://www.sabbasoft.com/myblog
>

Query Engine Error?

I have a report which displays some customer information and their transactions. The data is coming from an MS Access Database. The user must specify a customer either by customer Number or by their Last Name. I have C#.NET code which does this. Unfortunatley the data doesn't display on the report if the customer doesn't have any transactions. I asked another developer and he said to change the joins to right joins. The closest option i have is right outer join so i selected that. When i do this and run the report i get "Query Engine Error". It also displays the filepath to the rpt file. The database has been built up over a number of years and is not well normalized. Therefore i have more tables in the report then I would like. The database is quite large and would take alot of time to normalize that database and the program using the database would have to be altered as well. As you may have guessed, that code has also been developed over a number of years and again would take alot of time to change that. Is there an easy solution to my problem. I am not too experienced with Crystal Reports and any help would be greatly appreciated.Run the right join query in Access and see if it works

Query Engine Error again

I have crystal reports in my VS2005 project. We have wrapped dll for report printing, but using version 9.2.3300.0. So I got Error "Query Engine Error" when printing. If I use Crystal Reports 10.2.3600.0, It works fine.
How can I print report (10.2.3600.0) by wrapped dll (9.2.3300.0)?Did you miss any dlls?
Do very database and check

query engine error 21000

Can anyone tell me why I am getting this error when I try to view my report:

Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=,>,>= or when the subquery is used as an

Hi there...looks like one of the queries being executed to retrieve data for the report contains an invalid use of a subquery that returns more than 1 record in a scalar context. For example, something like what follows below is illegel if tableb contains more than one row, since the subquery is being used in/expected to act in scalar context (i.e. a single column, single row result set):

select cola from tablea where colb = (select colb from tableb)

If you have the query, maybe we could help some more...

query engine error

Can anyone tell me why I am getting this error when I try to view my report:

Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=,>,>= or when the subquery is used as anThis is a problem in the SQL-query.

Like the error says, the query has a subquery that returns multiple values (more than one row and column). Subqueries like that can't be in the column list or after the listed operators.

Make sure that the subquery returns only one value.

For example, next query won't work, as the subquery would return many rows (assuming there are many rows in the Suppliers-table):
SELECT ProductID, (SELECT SupplierID FROM Suppliers)
FROM Products

Similarly, the next query will cause the same error:
SELECT ProductID

FROM Products
WHERE SupplierID = (SELECT SupplierID FROM Suppliers)

The solution is to make the subquery to return only one value using WHERE or ORDER BY + TOP 1 statements, for example.|||

Sometime this might fit your need to limit the returned value to only a single on. Sometime you might WANt to return more than one value, then you will either have to use a JOIN, correlated query, or the IN operator like

Similarly, the next query will cause the same error:
SELECT ProductID
FROM Products
WHERE SupplierID IN (SELECT SupplierID FROM Suppliers)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||True. There are lot's of possibilities what he wants to achieve. A correlated subquery could be one alternative, too. Hard to tell without seeing the original query or requirement first :)

Query engine error

Can anyone tell me why I am getting this error when I try to view my report:
Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever]
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <=,>,>= or when the subquery is used as an expression.
thanks in advance!
It would help to see your query.
But I expect that it is something like:
Select (select col from tbl where key = topkey) as subcol
from toptable
If the subquery could return more than one value, the query will error out.
Russel Loski, MCSD.Net
"hale" wrote:

> Can anyone tell me why I am getting this error when I try to view my report:
> Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever]
> Subquery returned more than 1 value. This is not permitted when the subquery
> follows =, !=, <, <=,>,>= or when the subquery is used as an expression.
>
> --
> thanks in advance!
>

query engine error

"query engine error" in ...4trerf-fr454-5453-ffftre4.rpt"
Usually what is the problem when i get this message error,when is loading a report into a reportviewer?
thanksThis may be due to the improper link between the tables used. Check for the link between the tables properly.

Ashok|||I am have 7 reports in my app and none of my reports work on the deployment machine. Could there be anything else wrong with it? It works fine on my dev machine.

Sue

Query Engine Error

Hi
We have a table change in the DB2 database. I mean an old table
is being replaced with a new one. I updated the reports by
replacing the old table column names with the new ones in all
the reports. all the reports are running fine. there's one
report which is giving Query Engine Error. it pops up a window
which says 'The Query can not be performed. An illegal link
cycle was detected'.Please help me in finding out the issue.
Thanks in Advance

Regards
MadhaviDid you verify database inside your report file?|||I did verify. And its not showing any error. But i am not able to see the SQL also.
When i say 'Show SQL' i get the same error as when i run the report.|||I did verify. And its not showing any error. But i am not able to see the SQL also.
When i say 'Show SQL' i get the same error as when i run the report.
If you couldn't see the SQL statement, it means that table structure inside
rpt file corrupted. You need to recreate them.

Query Engine Error

Hi All,

I am trying to build an application in VS.Net which uses Crystal Report 11 to build the rpt file. I have an XML and XSD file as my datasources. I want to view the report in PDF format. Now my source code looks like:

ReportDocument doc = new ReportDocument();
string fileName = Server.MapPath("MyReport.rpt");
doc.Load(fileName);

DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("MyXML.xml"));

doc.SetDataSource(ds);

ExportOptions exportOpts = doc.ExportOptions;
exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat;
exportOpts.ExportDestinationType = ExportDestinationType.DiskFile;
exportOpts.DestinationOptions = new DiskFileDestinationOptions();

DiskFileDestinationOptions diskOpts = new DiskFileDestinationOptions();
((DiskFileDestinationOptions)doc.ExportOptions.DestinationOptions).DiskFileName = Server.MapPath("MyPDF.pdf");

doc.Export();

But it gives me the error
"Query Engine Error: 'c:\inetpub\wwwroot\MyWebsite\MyReport.rpt'"

I tried out all the forums but could not find any solution. Someone told me that it is due to xsd file error. But in Crystal Report it is running perfectly OK. Can anyone help.did you figure out what happened? I am getting this error during my deployment, but on my local PC it works fine.

bev

Query engine error

Can anyone tell me why I am getting this error when I try to view my report:
Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][S
QL Sever]
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <=,>,>= or when the subquery is used as an expression.
thanks in advance!It would help to see your query.
But I expect that it is something like:
Select (select col from tbl where key = topkey) as subcol
from toptable
If the subquery could return more than one value, the query will error out.
Russel Loski, MCSD.Net
"hale" wrote:

> Can anyone tell me why I am getting this error when I try to view my repor
t:
> Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][
;SQL Sever]
> Subquery returned more than 1 value. This is not permitted when the subque
ry
> follows =, !=, <, <=,>,>= or when the subquery is used as an expression.
>
> --
> thanks in advance!
>