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 8 2235
Roger wrote: 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
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.
"Roger" <wo**********@gmail.com> wrote in message
news:11**********************@y41g2000cwy.googlegr oups.com... 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.
how can i accomplish what I am trying to do ?
Mark A wrote: "Roger" <wo**********@gmail.com> wrote in message news:11**********************@y41g2000cwy.googlegr oups.com... 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.
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.
Roger wrote: 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
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.
Brian Tkatch wrote: 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
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.
Tonkuma wrote: Brian Tkatch wrote: 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 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.
Heh, Tonk. :)
Thanx for the correction. I think i just made some assumptions.
B.
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
Roger wrote: 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jeremy |
last post by:
I am relatively inexperienced with SQL, and I am trying to learn how
to analyze some data with it.
I have a table with the following information.
COMPANY ID , DATE, MarektValue
I would like...
|
by: Mike |
last post by:
Hello,
I have a few rather urgent questions that I hope someone can help with (I
need to figure this out prior to a meeting tomorrow.) First, a bit of
background: The company I work for is...
|
by: Thomasb |
last post by:
With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.
I have...
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I...
|
by: Brad |
last post by:
I need to select the Top 50 records from a table of over 10,000 records and
all is working just fine, but I have a what if question.
What if the 50th record's value matches the 51st record (and...
| |
by: Regnab |
last post by:
I often want to query the database with certain parameters in my query
which I do not want included in the query result.
The problem is when I want to group on 1, sum the 2nd (and group by)
and...
|
by: GreatAlterEgo |
last post by:
Hi,
This is my query which is embedded in a COBOL program.
EXEC SQL
SELECT DATE, AGE, DURATION, AMT
INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT
FROM TAB1
WHERE CODE = :KEY.CODE
AND...
|
by: JJ |
last post by:
How do I set one field to have the updated timestamp, and another to have
the created timestamp?
I want to do this directly from code generated from DB Designer if
possible?!
JJ
|
by: rbg |
last post by:
I did use query plans to find out more. ( Please see the thread BELOW)
I have a question on this, if someone can help me with that it will be
great.
In my SQL query that selects data from table,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |