Hi,
I have been given the name of a table. IT has all kinds of information
that is relevant to the user. The problem is that the user does not
exactly know which column this value he is searching for resides in.
Is there any way in db2 or databases in general that I can present
just one input field (browser) and then search all columnms of the
table?
Please advice.
Thanks.
TP. 12 3436
TP wrote: Hi,
I have been given the name of a table. IT has all kinds of information that is relevant to the user. The problem is that the user does not exactly know which column this value he is searching for resides in.
Is there any way in db2 or databases in general that I can present just one input field (browser) and then search all columnms of the table?
Please advice.
Thanks.
TP.
db2 describe table xxxx;
db2 select * from xxxxx;
"TP" <tp******@hotmail.com> wrote in message
news:59**************************@posting.google.c om... Hi,
I have been given the name of a table. IT has all kinds of information that is relevant to the user. The problem is that the user does not exactly know which column this value he is searching for resides in.
Is there any way in db2 or databases in general that I can present just one input field (browser) and then search all columnms of the table?
Please advice.
Thanks.
TP.
You would have to list all the columns in the predicate with an OR clause.
Actually there is a simple solution as long as you want to search a
single pattern - use the string concatenation and locate:
case sensitive:
SELECT A.* FROM MYTAB.SEARCH A WHERE COALESCE( LOCATE ( 'pattern',
COL1||COL2||COL3 ) , 0) > 0 ;
case insensitive:
SELECT A.* FROM MYTAB.SEARCH A WHERE COALESCE( LOCATE (
UCASE('pattern'), UCASE(COL1||COL2||COL3), 0 ) > 0;
use COALESCE(COLNAME, ' ') for nullable columns when concatenating -
to make sure the concatenation result is okay.
"Mark A" <ma@switchboard.net> wrote in message news:<xE****************@news.uswest.net>... "TP" <tp******@hotmail.com> wrote in message news:59**************************@posting.google.c om... Hi,
I have been given the name of a table. IT has all kinds of information that is relevant to the user. The problem is that the user does not exactly know which column this value he is searching for resides in.
Is there any way in db2 or databases in general that I can present just one input field (browser) and then search all columnms of the table?
Please advice.
Thanks.
TP.
You would have to list all the columns in the predicate with an OR clause.
> Actually there is a simple solution as long as you want to search a single pattern - use the string concatenation and locate:
case sensitive: SELECT A.* FROM MYTAB.SEARCH A WHERE COALESCE( LOCATE ( 'pattern', COL1||COL2||COL3 ) , 0) > 0 ; case insensitive: SELECT A.* FROM MYTAB.SEARCH A WHERE COALESCE( LOCATE ( UCASE('pattern'), UCASE(COL1||COL2||COL3), 0 ) > 0;
use COALESCE(COLNAME, ' ') for nullable columns when concatenating - to make sure the concatenation result is okay.
this is just plain wrong:
a row whith these values
COL1 ='p'
COL2 = 'att'
COL3='ern'
meets your criteria, but it shouldn't
AK <ak************@yahoo.com> wrote: Actually there is a simple solution as long as you want to search a single pattern - use the string concatenation and locate:
case sensitive: SELECT A.* FROM MYTAB.SEARCH A WHERE COALESCE( LOCATE ( 'pattern', COL1||COL2||COL3 ) , 0) > 0 ; case insensitive: SELECT A.* FROM MYTAB.SEARCH A WHERE COALESCE( LOCATE ( UCASE('pattern'), UCASE(COL1||COL2||COL3), 0 ) > 0;
use COALESCE(COLNAME, ' ') for nullable columns when concatenating - to make sure the concatenation result is okay.
this is just plain wrong: a row whith these values COL1 ='p' COL2 = 'att' COL3='ern'
meets your criteria, but it shouldn't
Right, you could use some sort of unique delimiter then:
LOCATE ( '#' || 'pattern' || '#', '#' || col1 || '#' || col2 || '#' || col3
|| '#')
Of course, that delimiter must not occur in any of the strings stored in the
columns.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Like Knut's example, using a delimiter when concatenating the searched
columns may be a good idea if you don't want your pattern to span
columns, however padding the search pattern itself with the delimiter
will result in only the rows that where the full text of the column
matchers the pattern (which may or may not be what you want). BTW, a
problem with LOCATE is that you cannot use wildcards in the pattern
expression - you can use a common table expression to use the LIKE
predicate.
WITH CONCATSTR (A, B, C, D) AS ( SELECT COL1, COL2, COL3,
COL1||'#'||COL2||'#'||COL3 FROM SEARCH )
SELECT A B,C FROM CONCATSTR WHERE D LIKE 'patter%' ;
But then again - it may be better to just use or's in your where
clause i.e. (COL1 like 'pattern' OR COL2 like 'pattern' etc)
Knut Stolze <st****@de.ibm.com> wrote in message news:<c1**********@fsuj29.rz.uni-jena.de>... AK <ak************@yahoo.com> wrote:
Actually there is a simple solution as long as you want to search a single pattern - use the string concatenation and locate:
case sensitive: SELECT A.* FROM MYTAB.SEARCH A WHERE COALESCE( LOCATE ( 'pattern', COL1||COL2||COL3 ) , 0) > 0 ; case insensitive: SELECT A.* FROM MYTAB.SEARCH A WHERE COALESCE( LOCATE ( UCASE('pattern'), UCASE(COL1||COL2||COL3), 0 ) > 0;
use COALESCE(COLNAME, ' ') for nullable columns when concatenating - to make sure the concatenation result is okay.
this is just plain wrong: a row whith these values COL1 ='p' COL2 = 'att' COL3='ern'
meets your criteria, but it shouldn't
Right, you could use some sort of unique delimiter then:
LOCATE ( '#' || 'pattern' || '#', '#' || col1 || '#' || col2 || '#' || col3 || '#')
Of course, that delimiter must not occur in any of the strings stored in the columns.
Knut, Right, you could use some sort of unique delimiter then:
LOCATE ( '#' || 'pattern' || '#', '#' || col1 || '#' || col2 || '#' || col3 || '#')
Of course, that delimiter must not occur in any of the strings stored in the columns.
this wiill work, but I think youredicate is not sargeable
What do you think?
AK <ak************@yahoo.com> wrote: Right, you could use some sort of unique delimiter then:
LOCATE ( '#' || 'pattern' || '#', '#' || col1 || '#' || col2 || '#' || col3 || '#')
Of course, that delimiter must not occur in any of the strings stored in the columns.
this wiill work, but I think youredicate is not sargeable What do you think?
To be honest, I don't know what sargeable predicates are...
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
sargeable = search arguement(-able) - from the DB2 documents web site:
SARGable refers to a term that can be used as a search argument.
Knut Stolze wrote: AK <ak************@yahoo.com> wrote:
Right, you could use some sort of unique delimiter then:
LOCATE ( '#' || 'pattern' || '#', '#' || col1 || '#' || col2 || '#' || col3 || '#')
Of course, that delimiter must not occur in any of the strings stored in the columns.
this wiill work, but I think youredicate is not sargeable What do you think?
To be honest, I don't know what sargeable predicates are...
"Blair Adamache" <ba*******@2muchspam.yahoo.com> wrote in message
news:c1**********@hanover.torolab.ibm.com... sargeable = search arguement(-able) - from the DB2 documents web site:
SARGable refers to a term that can be used as a search argument.
In DB2 mainframe, predicates are defined as Stage 1 (sargable) or Stage 2.
Stage 1 predicates are more efficient since these include indexable
predicates and other predicates handled before stage 2 predicates are
applied on the returned data rows.
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote: sargeable = search arguement(-able) - from the DB2 documents web site:
SARGable refers to a term that can be used as a search argument.
Thanks, Blair!
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
The term Index-Sargable is also 'commonly' used.
PM This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Harvey |
last post by:
Hi,
I try to write an asp query form that lets client search any text-string and
display all pages in my web server that contain the text. I have IIS 6.0 on a
server 2003. The MSDN site says...
|
by: GL |
last post by:
Hi,
Is there a way to add a field to an existing table using a query of
some sort (without needing to manually add a field to the table). I
know how to do it with a make table query, but I have...
|
by: Brian |
last post by:
I hope this will make sense. I'm trying to filter the records in a
table based on records in a 2nd table. The trick is, I can't use a
query. I'm trying to filter down the number of records going...
|
by: jjturon |
last post by:
Can anyone help me??
I am trying to pass a Select Query variable to a table using Dlookup
and return the value to same select query but to another field.
Ex.
SalesManID ...
|
by: cover |
last post by:
I have a table with 50 fields that receive input depending on whether
that input came in from a 'shaker' form or a 'conveyor' form. Input
from the 'conveyor' form might populate 25 fields while...
|
by: clare at snyder.on.ca |
last post by:
I have a SQL query I need to design to select name and email addresses
for policies that are due and not renewed in a given time period. The
problem is, the database keeps the information for every...
|
by: Wired Hosting News |
last post by:
I tried to be breif and give a scenario so as not to be overlooked because
it was soooo long. Let me give you real world.
I am a manufacturer of goods and produce 11 items that are distributed...
|
by: John T Ingato |
last post by:
If I run a query that calculates the sales data for all the inventory level
in a table, how do I go about saving that sales data into a permanent table?
I plan I using this sales data often, and...
|
by: phlype.johnson |
last post by:
I'm struggling to find the best query from performance point of view
and readability for a normalized DB design.
To illustrate better my question on whether normalized designs lead to
more complex...
|
by: funky |
last post by:
hello,
I've got a big problem ad i'm not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |