Looks like a silly question.
But how do I execute this,

I have a table in which I have a field called seq_i which can contain values greater than 1.
I need to execute a sql query which can extract all the values which are equal to 1 and not greater than 1 ( remember I can store any number from 1 to n ( n stands for any)).
Also I don't want to use sub queries, worst case I can go. But Is there a way I can extract directly.


Thanks.
Anto. A.R.

One thing, I forget to add.. All the row's which has values more than 1, has values starting from 1. So it is like multiple inserts for the particular ID, which has row sequence ID starting with 1,2,3, .... . I need to take the rows which has only with 1.

Anto,
Try:

SELECT * from ... where seq_i = 1

Andy

But this will select records which has values 2. Here is my situation.

I have a table with app_id, Seq_id, Seq_data. For a particular app Id, I may have multiple inserts ie my seq_data holds max 4000 char a row.
I have some times data which can come up to 20,000. So my db2 v 6.1 on os 390, inserts multiple rows like,

app_id seq_id seq_data
21 1 some data.....
21 2 some data.....
21 3 some data.....


In some scenarios, I have only one row which contains the seq_id "1". I just want to get this.

Hope this clarify you.
Thanks,
Anto.

Anto,
I reiterate what I posted.
The "where seq_id = 1" will return all rows where
seq_id has 1 as a value, none others.

If this is not what you want, please restate what you are after.

Andy

I posted as 4th reply. Do you need more clarification?.

Anto,

Give me an example of the data, and what you expect to get back. Also restate what you want in text.

Andy

Ok, I think still I am not clearly explained.

I have a table , with app_id, seq_id,seq_data.


It has values like the bellow
--------------------------------

One Record
---------------

app_id , seq_id , seq_data
21 , 1 , some_data ....
21 , 2 , some_data1...
21 , 3 , some_data2...

Second Record
-------------------
app_id , seq_id , seq_data
22 , 1 , second_data....


Third Record
----------------

app_id , seq_id , seq_data
23 , 1 , third_data....
23 , 2 , third_data1...


Now I need to exclue the app_id which has greater than 1 ie 2, 3 etc.. I need to extract app_id's which has seq_id only "1". What's the sql query i need to execute.

Thanks

Anto,
SELECT app_id from mytable where seq_id = 1;

Andy

Noops. This will fetch the records which has the seq_id "2", "3" for the same app_id.

Note that a same app_id contains different seq_id starting from 1 to n.
Now, I need to get all the app_id's which has only "1". If I execute the query,
select * from .. where seq_id=1.

This will fetch all the app_id ie 21,22,23. But I should get only 22 because this is the one satisfy my requirement.

Anto,
I think I figured out what you are after.
You want only those app_id that ONLY have a seq_id of 1,
Not all of the rows that have seq_id = 1;

with temp1 as (select app_id,max(seq_id) as seq_id from mytable group by app_id) select app_id from temp1 wgere seq_id = 1;

I do not think you can get away from a subselect here.
Andy

that's great.. It works.. thankx...

In my local database, the query executed well. But when I try to ran against db2 v6.1 running under OS/390 it is not able to execute, it throws the error

SqlCode=-199 Error: illeagal use of keyword as, token is <hexstring> <charstring> <graphstring> was expected
Can't i run the script against 6.1?. My local database is 7.2 If not what's the alternative solution for my problem?

I have not used V6.x but you can Try:

Select app_id from mytable where (app_id,seq_id) not in (select app_id,seq_id from mytable where seq_id > 1)

Andy

Andy and Anto,

this construct (where (app_id,seq_id) not in ...) doesn't work at OS390 V6.1 either. For both cases, you'll have to wait until DB2 UDB V8 for zOS.
Using version 6 for OS390, you need a subselect.

SELECT ...
FROM mytable M1
WHERE seq_id = 1
AND NOT EXISTS
(SELECT 1
FROM mytable M2
WHERE M2.app_id = M1.app_id
AND M2.seq_id > 1)

HTH,