473,320 Members | 1,841 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query a table?

TP
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.
Nov 12 '05 #1
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;
Nov 12 '05 #2
"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.
Nov 12 '05 #3
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.

Nov 12 '05 #4
AK
> 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
Nov 12 '05 #5
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
Nov 12 '05 #6
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.

Nov 12 '05 #7
AK
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?
Nov 12 '05 #8
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
Nov 12 '05 #9
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...


Nov 12 '05 #10
"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.
Nov 12 '05 #11
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
Nov 12 '05 #12
The term Index-Sargable is also 'commonly' used.

PM
Nov 12 '05 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
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...
3
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...
4
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...
6
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 ...
3
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...
24
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...
5
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...
2
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...
0
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...
11
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....
0
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
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....
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.