question regarding OR clause with fietch first 1 row only

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roger - 21 Jun 2006 22:05 GMT

I have a question regarding the behaviour of sql with OR and fetch
first 1 rows only :

I have a table with data :

ACNO NAME TELNO CITY
ZIP
1000 tim 8887778888 denver
1000 tim 8887778888 denver
56465
1000
denver
2000 1112223333
11111
2000
11111
3000 jack
3000 jack 9998887777 NYC
22222

I want to select the rows with more data . My sql will look like this :

select *
from table 1
where
(ACNO = 1000 and NAME = 'tim' and TELNO = '888777888' and CITY =
'denver' and
ZIP = 56465)
OR
(ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY =
'denver' and
ZIP = ' ')
or
(ACNO = 1000 and NAME = 'tim' and TELNO =' 8887778888' and CITY = ' '
and
ZIP = ' ' )
or
(ACNO = 1000 and NAME = 'tim' and TELNO =' ' and CITY = ' ' and
ZIP = ' ' )
OR
(ACNO = 1000 and NAME = ' ' and TELNO =' ' and CITY = ' ' and
ZIP = ' ' )
FETCH FIRST 1 ROW ONLY ;

my aim is to get row # 2 ie
1000 tim 8887778888 denver
56465

the row with most amount of data. But I am getting row #1 ie
1000 tim 8887778888 denver

How can I accomplish this ? I was under the impression that DB2 will
fetch records in the order of the WHERE clause.

Can anybody help ? This is on DB2 V7 on Z/os , but I think the answer
will be the same for all DB2s

Thanks

Reply to this Message

Bob Stearns - 21 Jun 2006 22:26 GMT

> I have a question regarding the behaviour of sql with OR and fetch
> first 1 rows only :
[quoted text clipped - 54 lines]
>
> Thanks

I don't see a way to do this with just ORs in 1 SELECT. Make each branch
into a SELECT, adding something like 1 as goodness, 2 as goodness, etc.,
to each SELECT indicating how much data each SELECT is getting. UNION
all of the SELECTS, ORDER BY goodness then FETCH FIRST 1 ROW ONLY.

Reply to this Message

Mark A - 22 Jun 2006 00:08 GMT

> How can I accomplish this ? I was under the impression that DB2 will
> fetch records in the order of the WHERE clause.

No, DB2 fetches the rows in the manner that the optimizer thinks is most
efficient. That should not make any difference to you if the SQL statement
is functionally correct according to relational semantics.

Reply to this Message

Roger - 22 Jun 2006 13:10 GMT

how can i accomplish what I am trying to do ?

> > How can I accomplish this ? I was under the impression that DB2 will
> > fetch records in the order of the WHERE clause.
>
> No, DB2 fetches the rows in the manner that the optimizer thinks is most
> efficient. That should not make any difference to you if the SQL statement
> is functionally correct according to relational semantics.

Reply to this Message

Tonkuma - 22 Jun 2006 15:46 GMT

Yor sample data were folded. So, I'm not sure my interpretation is
right.
Any way, this is my understasndings and one sample solution.

----------------------- Commands Entered ------------------------
CREATE TABLE Roger.table
(ACNO SMALLINT NOT NULL
,NAME VARCHAR(10)
,TELNO CHAR(10)
,CITY VARCHAR(10)
,ZIP CHAR(5)
);
------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered --------------------
select *
from Roger.table;
--------------------------------------------------------------------

ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888 denver
1000 tim 8887778888 denver 56465
1000 denver
2000 1112223333 11111
2000 11111
3000 jack
3000 jack 9998887777 NYC 22222

7 record(s) selected.

------------------------- Commands Entered -------------------------
select *
from Roger.table
where
(ACNO = 1000 and NAME = 'tim' and TELNO = '888777888'
and CITY = 'denver' and ZIP = '56465')
OR
(ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888'
and CITY = 'denver' and ZIP = ' ')
or
(ACNO = 1000 and NAME = 'tim' and TELNO =' 8887778888'
and CITY = ' ' and ZIP = ' ' )
or
(ACNO = 1000 and NAME = 'tim' and TELNO =' '
and CITY = ' ' and ZIP = ' ' )
OR
(ACNO = 1000 and NAME = ' ' and TELNO =' '
and CITY = ' ' and ZIP = ' ' )
ORDER BY
ACNO, NAME DESC, TELNO, CITY, ZIP
FETCH FIRST 1 ROW ONLY ;
--------------------------------------------------------------------

ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888 denver

1 record(s) selected.

Reply to this Message

Brian Tkatch - 22 Jun 2006 16:10 GMT

> I have a question regarding the behaviour of sql with OR and fetch
> first 1 rows only :
[quoted text clipped - 54 lines]
>
> Thanks

DECLARE GLOBAL TEMPORARY TABLE Test (ACNO INT, NAME CHAR(10), TELNO
CHAR(10), CITY CHAR(10), ZIP CHAR(5))
INSERT INTO SESSION.Test(ACNO, NAME, TELNO, CITY, ZIP) VALUES (1000,
'tim', '8887778888', 'denver', '')
INSERT INTO SESSION.Test(ACNO, NAME, TELNO, CITY, ZIP) VALUES (1000,
'tim', '8887778888', 'denver', '56465')
INSERT INTO SESSION.Test(ACNO, NAME, TELNO, CITY, ZIP) VALUES (1000,
'', '', 'denver', '')
INSERT INTO SESSION.Test(ACNO, NAME, TELNO, CITY, ZIP) VALUES (2000,
'', '1112223333', '', '11111')
INSERT INTO SESSION.Test(ACNO, NAME, TELNO, CITY, ZIP) VALUES (2000,
'', '', '', '11111')
INSERT INTO SESSION.Test(ACNO, NAME, TELNO, CITY, ZIP) VALUES (3000,
'jack', '', '', '')
INSERT INTO SESSION.Test(ACNO, NAME, TELNO, CITY, ZIP) VALUES (3000,
'jack', '9998887777', 'NYC', '22222')

With your query, you can add a number and UNION, then ORDER BY the
number.

SELECT 1, ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE
ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY =
'denver' and ZIP = '56465' \
UNION SELECT 2, ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE
ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY =
'denver' and ZIP = '' \
UNION SELECT 3, ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE
ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY = '' and
ZIP = '' \
UNION SELECT 4, ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE
ACNO = 1000 and NAME = 'tim' and TELNO = '' and CITY = '' and ZIP = ''
\
UNION SELECT 5, ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE
ACNO = 1000 and NAME = '' and TELNO = '' and CITY = '' and ZIP = '' \
ORDER BY 1 \
FETCH FIRST ROW ONLY

Though, there is no reason to do that. You should be able to use IN and
ORDER BY DESC:

SELECT ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE ACNO = 1000
\
and NAME IN ('tim', '') and TELNO IN ('8887778888', '') and CITY IN
('denver', '') and ZIP IN('56465', '') \
ORDER BY NAME DESC, TELNO DESC, CITY DESC, ZIP DESC \
FETCH FIRST ROW ONLY

B.

Reply to this Message

Tonkuma - 25 Jun 2006 13:26 GMT

> Though, there is no reason to do that. You should be able to use IN and
> ORDER BY DESC:
[quoted text clipped - 5 lines]
> ORDER BY NAME DESC, TELNO DESC, CITY DESC, ZIP DESC \
> FETCH FIRST ROW ONLY

1) I made some mistakes(For example:first condition for TELNO =
'888777888', and forgot some DESC keyword).
Here is a corrected query.
------------------------- Commands Entered -------------------------
select *
from Roger.table
where
(ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888'
and CITY = 'denver' and ZIP = '56465')
OR
(ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888'
and CITY = 'denver' and ZIP = ' ')
OR
(ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888'
and CITY = ' ' and ZIP = ' ' )
OR
(ACNO = 1000 and NAME = 'tim' and TELNO =' '
and CITY = ' ' and ZIP = ' ' )
OR
(ACNO = 1000 and NAME = ' ' and TELNO =' '
and CITY = ' ' and ZIP = ' ' )
ORDER BY
ACNO DESC, NAME DESC, TELNO DESC, CITY DESC, ZIP DESC
FETCH FIRST 1 ROW ONLY ;
--------------------------------------------------------------------

ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888 denver 56465

1 record(s) selected.

2) If all data are such that if a column is blank then later columns
also all blank, your query will work well.
In other words, there is no following data.
ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888 56465

But, If there were such data, your query would return incorrect result.
I want show you this with very simple example.

If there are only two following data.

ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888 56465
1000 tim 8887778888

Your query returns

ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888 56465

But, this data not match any WHERE condition of Roger's original query.

My query returns

ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888

This match with 3rd condition of Roger's original query.
(ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY = ' '
and
ZIP = ' ' )

So, it depends on the characteristics of data that your query works
well or not.

Reply to this Message

Brian Tkatch - 26 Jun 2006 16:47 GMT

> > Though, there is no reason to do that. You should be able to use IN and
> > ORDER BY DESC:
[quoted text clipped - 76 lines]
> So, it depends on the characteristics of data that your query works
> well or not.

Heh, Tonk. :)

Thanx for the correction. I think i just made some assumptions.

B.

Reply to this Message

Phil Sherman - 26 Jun 2006 17:10 GMT

The result you are searching for is difficult to discern from the
limited information you have provided. Given the following data; which
row has "more" data?
ACNO    NAME    TELNO        CITY        ZIP
1000    tim    8887778888    denver
1000    tim            denver        56465

If the NAME, TELNO, CITY, and ZIP are slways the same for each account
(ACNO) and you actually want to get all of the information available
from multiple rows - not quite what you stated in the original request;
then the following is an interesting alternative:

select ACNO, max(NAME), max(TELNO), max(CITY), max(ZIP) from table
group by ACNO;

This will yield one row for each ACNO, containing as much information as
can be gathered from multiple source rows. Qualification can be added to
the query to limit the results to specific ACNOs.

Phil Sherman

> I have a question regarding the behaviour of sql with OR and fetch
> first 1 rows only :
[quoted text clipped - 54 lines]
>
> Thanks

Reply to this Message