[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Select statement not returning same number of rows in SQL Query Analyzer vs. ASP page (FP)

Posted on 2004-10-21
23
Medium Priority
?
325 Views
Last Modified: 2006-11-17
Hello:

I have a select statement that joins three tables.  The statement is working fine in SQL Query Analyzer (returning correct number of rows), however, the same query ran on a search form on an ASP page returns 53 fewer rows?  I have tried putting in more rows in the db, and same result through the asp page.

My table are items and prods, and my statement is as follows:
SELECT     prods.ref,items.person,items.size,dbo_items.unit,
                      prods.trname,prods.mfr,prods.catnum,items.rm_id,
                      items.expiry
FROM         items INNER JOIN
                      prods ON items.product = prods.product

This statement ran on SQL query analyzer returns 10738 rows, in the asp page it returns 10685 rows.

Any reason why this would happen? The asp page is created with FrontPage, using the above custome query as the record source.  The connection is fine, the database is fine, and all columns and tables exist.

The results from the Query Analyzer are the correct returns results.

Let me know if you need more information.

Thank you!

B
0
Comment
Question by:Jan Phelps
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 6
  • 2
  • +1
23 Comments
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 12372681
are you sure this is not just a paging problem in your ASP page or something like that?

do you always get the same results?
0
 

Author Comment

by:Jan Phelps
ID: 12372694
BlackTigerX:

Yes.  I just tried the same thing.  Added x number of new rows to db, still getting 53 more rows returned in query analyzer....

B
0
 

Author Comment

by:Jan Phelps
ID: 12372907
Some additional information:

I also have some search boxes defined. In FP, they get defined by adding a Where clause like this:

WHERE (prods.mfr LIKE '%::prods.mfr::%')
AND (items.unit LIKE '%::items.unit::%')
AND (items.person LIKE '%::items.person::%')
AND (items.rm_id LIKE '%::items.rm_id::%')

When I run this asp page with this full query:
SELECT     prods.ref,items.person,items.size,dbo_items.unit,
                      prods.trname,prods.mfr,prods.catnum,items.rm_id,
                      items.expiry
FROM         items INNER JOIN
                      prods ON items.product = prods.product
WHERE (prods.mfr LIKE '%::prods.mfr::%')
AND (items.unit LIKE '%::items.unit::%')
AND (items.person LIKE '%::items.person::%')
AND (items.rm_id LIKE '%::items.rm_id::%')

It returns 53 fewer rows.  However, if I just run the select statement without the Where clause, it will return everything.  Now, my question is this:  Can I assume that all data in the table(s) are available for searching (based upon my search terms) even if it returns fewer rows when no search terms are specified?  Is the Where clause here already restricting what is shown?  

Any help/advice is appreciated.

Thanks.

B
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 5

Expert Comment

by:svid
ID: 12373681
1. Do a response.write on the page and compare the statement with what you ran in Query Analyzer. If the two statements are the same, you should not get different results.

2. Adding an additional WHERE clause only on the page could definitely alter the results you obtain. Based on the info given abopve, it definitely looks like one of the columns (mfr/unit/person/em_id is not what you specified in the respective clauses).
0
 

Author Comment

by:Jan Phelps
ID: 12373870
svid:
How would I do a response.write on the page?  I am new to this and picking up things as I go...

The additional where clause is used by FrontPage to generate search boxes for the specific searches.  What I do not understand is that when I leave them empty, then they should show all rows??

Again, if I run the select statment without the Where clause in the ASP page, I get all rows returned.  I have a feeling, though, that it will be able to search through all chemicals.  I will do a test by deleting all but 10 records, and then see how many are displayed.

Any additional info is greatly appreciated!

Thanks.

B
0
 
LVL 5

Expert Comment

by:svid
ID: 12373915
On the ASP page say you have

<%
Dim strSQL
strSQL = "select ...."
conn.execute strsql
%>

just add a Response.Write strSQL before the conn.execute

Your where clause expects all prods.mfr to have the pattern '%::prods.mfr::%' (and similarly for the other columns). If there are rows that don't have this pattern, your select will not return them, which is why the difference in row counts
0
 

Author Comment

by:Jan Phelps
ID: 12374066
From the where clause, what exactly does the '%::prods.mfr::%' pattern mean in terms on straight english?  I thought it would mean whatever is entered in the search boxes?  These first where clause was added by FP, while I added the other ones in order to get more search boxes on the page.

Since this was made by FrontPage wizzard, it will not let me add in any response.write strSQL in the code, and it does not contain the <% dim strSQL...%> either...

Thanks.

B
0
 
LVL 5

Expert Comment

by:svid
ID: 12374153
OOPS,

WHERE colA like '%XYZ%' means all rows where col1A has the string 'XYZ' in it (anywhere in the column) will be selected.

Sorry I don't know what "::prods.mfr::" means but if thats the SQL statement sent, it will return all rows with that particular value in the column.

Can you maybe post a snippet of the code. I have never used the FP wizard so I am not familar with it.
0
 

Author Comment

by:Jan Phelps
ID: 12374347
svid:

I just realized something, and the way the query works: you are correct, and I now understood what you mean.  The where clause is assuming there is a value for all the records, and it will not return those records where one of the fields in the Where clause is null.

Now that I was able to understand what you meant svid and see it for myself, is there a way to make that query so as to return all rows, not matter if some of the columns do not have a value for a specific search criteria?

Thank you!

B
0
 
LVL 5

Expert Comment

by:svid
ID: 12374398
I'm confused.

Do you want to return the rows without checking for the existence of the search criteria? In that case, just remove the WHERE clause.

Or

do you want to say
column_name is null or column_name like '%XYZ%'

So it will return all rows that have either a null value or XYZ somewhere in the column value

Or

Do you want to return all rows that satisfies at least one condition, in which case replace AND s with OR s.

Explain.
0
 

Author Comment

by:Jan Phelps
ID: 12374522
I'll just throw in a full page created by FP wizzard.  Used Northwind sample db.

<html>

<head>
<% ' FP_ASP -- ASP Automatically generated by a Frontpage Component. Do not Edit.
FP_LCID = 1033 %>
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<% ' FP_ASP -- ASP Automatically generated by a Frontpage Component. Do not Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title></title>
</head>

<body background="bkgr2.jpg">

<p><font face="sans-serif" size="2"><span style="font-variant: small-caps"><b>
<img border="0" src="logo_0.png"></b></span></font></p>
<p><font face="sans-serif" size="4"><span style="font-variant: small-caps"><b>
search form</b></span></font></p>
<form BOTID="0" METHOD="POST" ACTION="thursday6.asp">
  <table BORDER="0">
    <tr>
      <td><b>c</b></td>
      <td>
      <input TYPE="TEXT" NAME="c" VALUE="<%=Server.HtmlEncode(Request("c"))%>" size="20"></td>
    </tr>
  </table>
  <p><br>
  <input TYPE="Submit"><input TYPE="Reset"><!--webbot bot="SaveAsASP" clientside suggestedext="asp" preview=" " --></p>
  <p>&nbsp;</p>
</form>
<table border="1">
  <thead>
    <tr>
      <td><b>CustomerID</b></td>
      <td><b>CompanyName</b></td>
      <td><b>ContactName</b></td>
      <td><b>ContactTitle</b></td>
      <td><b>Address</b></td>
      <td><b>City</b></td>
      <td><b>Region</b></td>
      <td><b>PostalCode</b></td>
      <td><b>Country</b></td>
      <td><b>Phone</b></td>
      <td><b>Fax</b></td>
    </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart" s-columnnames="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-columntypes="202,202,202,202,202,202,202,202,202,202,202" s-dataconnection="Sample" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="FALSE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource="Customers" s-displaycolumns="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-criteria="[CustomerID] LK {c} +" s-order s-sql="SELECT * FROM Customers WHERE (CustomerID LIKE '::c::')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="c=" s-norecordsfound="No records returned. Please modify your search criteria." i-maxrecords="0" i-groupsize="50" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Customers WHERE (CustomerID LIKE '::c::')"
fp_sDefault="c="
fp_sNoRecords="<tr><td colspan=11 align=left width=""100%"">No records returned. Please modify your search criteria.</td></tr>"
fp_sDataConn="Sample"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=50
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&CustomerID=202&CompanyName=202&ContactName=202&ContactTitle=202&Address=202&City=202&Region=202&PostalCode=202&Country=202&Phone=202&Fax=202&"
fp_iDisplayCols=11
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="19758" --><tr>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-column="CustomerID" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;CustomerID&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"CustomerID")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="23468" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-column="CompanyName" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;CompanyName&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"CompanyName")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="29451" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-column="ContactName" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;ContactName&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"ContactName")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="29292" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-column="ContactTitle" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;ContactTitle&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"ContactTitle")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="32621" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-column="Address" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Address&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Address")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="12103" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-column="City" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;City&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"City")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="4267" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-column="Region" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Region&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Region")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="15114" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-column="PostalCode" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;PostalCode&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"PostalCode")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="28780" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-column="Country" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Country&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Country")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="13796" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-column="Phone" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Phone&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Phone")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="8388" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax" s-column="Fax" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Fax&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Fax")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="1677" --></td>
    </tr>
    <!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="50" clientside tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;TR&gt;&lt;TD ALIGN=LEFT VALIGN=MIDDLE COLSPAN=64&gt;&lt;NOBR&gt;&lt;INPUT TYPE=Button VALUE=&quot;  |&lt;  &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;   &lt;  &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;  &gt;   &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;  &gt;|  &quot;&gt;  [1/50]&lt;/NOBR&gt;&lt;BR&gt;&lt;/td&gt;&lt;/tr&gt;" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="62730" --></tbody>
</table>

</body>

</html>
0
 
LVL 5

Expert Comment

by:svid
ID: 12374538
That doesn't clarify the question. I was wondering what exactly was the requirement.
0
 

Author Comment

by:Jan Phelps
ID: 12374619
>>do you want to say
>>column_name is null or column_name like '%XYZ%'

>>So it will return all rows that have either a null value or XYZ somewhere in the column value

This is what I want.  I want to be able to return ALL rows if no search criteria is entered in and submitted.  There are items that will have one criteria satisfied, but not the other ones (by having a null value).  However, I tried using OR instead of AND, and that does not work. It would then actually only search based upon information entered in the first search box (the one immediately before the OR clause).

I gotta go for the day. Will be back in the morning.

Thank you for your time svid.  Hopefully I'll get through this one tomorrow.

B
0
 

Author Comment

by:Jan Phelps
ID: 12374646
I put in the FP code since you wanted to see a snippet of what it does. Was not meant to try to clarify my question. Feel free to delete that whole post if you wish.

B
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 12374671
Try this variation on your SELECT statement:

SELECT     prods.ref,items.person,items.size,dbo_items.unit,
                      prods.trname,prods.mfr,prods.catnum,items.rm_id,
                      items.expiry
FROM         items INNER JOIN
                      prods ON items.product = prods.product
WHERE ((prods.mfr LIKE '%::prods.mfr::%') OR ('::prods.mfr::' = ''))
AND ((items.unit LIKE '%::items.unit::%') OR ('::items.unit::' = ''))
AND ((items.person LIKE '%::items.person::%') OR ('::items.person::' = ''))
AND ((items.rm_id LIKE '%::items.rm_id::%') OR ('::items.rm_id::') = ''))

The second parameter on each line basically says "Select all of the records if I don't specify something here"
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 400 total points
ID: 12374696
For your sample, the line:

fp_sQry="SELECT * FROM Customers WHERE (CustomerID LIKE '::c::')"

would be

fp_sQry="SELECT * FROM Customers WHERE ((CustomerID LIKE '%::c::%') OR ('::c::' = ''))"
0
 

Author Comment

by:Jan Phelps
ID: 12380119
Hello bhess1.

When I try to modify the select statement as per your suggestion, I am getting this error:
Database Results Error
The operation failed. If this continues, please contact your server administrator.

When I remove the OR clauses, just to test that I do have a good connection, I do get returned rows (with the same problem as before).

I am putting it in exactly as you show above.

Thanks.

B
0
 
LVL 5

Assisted Solution

by:svid
svid earned 400 total points
ID: 12382264
Can you print out the query?

Also refer to this

http://www.kbalertz.com/kb_828905.aspx

which explains this as an issue in FP 2003
0
 

Author Comment

by:Jan Phelps
ID: 12382859
svid:

None of the columns are autonumber fields and none of them are numeric fields. They are all char fields.

What do you mean if I can print out the query?

Thank you.

B
0
 

Author Comment

by:Jan Phelps
ID: 12384642
Well, update:
I found that when I took this query:

SELECT     prods.ref,items.person,items.size,dbo_items.unit,
                      prods.trname,prods.mfr,prods.catnum,items.rm_id,
                      items.expiry
FROM         items INNER JOIN
                      prods ON items.product = prods.product
WHERE ((prods.mfr LIKE '%::prods.mfr::%') OR ('::prods.mfr::' = ''))
AND ((items.unit LIKE '%::items.unit::%') OR ('::items.unit::' = ''))
AND ((items.person LIKE '%::items.person::%') OR ('::items.person::' = ''))
AND ((items.rm_id LIKE '%::items.rm_id::%') OR ('::items.rm_id::') = ''))

and changed it to this:
SELECT     prods.ref,items.person,items.size,dbo_items.unit,
                      prods.trname,prods.mfr,prods.catnum,items.rm_id,
                      items.expiry
FROM         items INNER JOIN
                      prods ON items.product = prods.product
WHERE ((prods.mfr LIKE '::prods.mfr::') OR ('::prods.mfr::' = ''))
OR ((items.unit LIKE '::items.unit::') OR ('::items.unit::' = ''))
OR ((items.person LIKE '::items.person::') OR ('::items.person::' = ''))
OR ((items.rm_id LIKE '::items.rm_id::') OR ('::items.rm_id::') = ''))

It would return all rows when % is entered as the search term for all the search boxes. It als works
on single searches where if I only search for e.g. person like this: ah%, it will return all person
that starts with ah%.  

This is good, at least for now.  However, I am loosing the filtering function of the AND
clause, and that kinda sucks.

B
0
 

Author Comment

by:Jan Phelps
ID: 12436492
Please advice if I am to give out points or not.  It was not solved, but I did get some new ideas,
and get it working halfway.

Can I give out partial points?  I would like both svid and bhess1 to get some points for their
efforts.

Thank you.

B
0

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question