Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Db2 Sql Query

  1. #1
    Join Date
    Mar 2003
    Posts
    69

    Unanswered: Db2 Sql Query

    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.

  2. #2
    Join Date
    Mar 2003
    Posts
    69
    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.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: Db2 Sql Query

    Anto,
    Try:

    SELECT * from ... where seq_i = 1

    Andy

    Originally posted by antodomnic
    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.

  4. #4
    Join Date
    Mar 2003
    Posts
    69
    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.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

    Originally posted by antodomnic
    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.

  6. #6
    Join Date
    Mar 2003
    Posts
    69
    I posted as 4th reply. Do you need more clarification?.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Anto,

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

    Andy


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

  8. #8
    Join Date
    Mar 2003
    Posts
    69
    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

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Anto,
    SELECT app_id from mytable where seq_id = 1;

    Andy

    Originally posted by antodomnic
    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

  10. #10
    Join Date
    Mar 2003
    Posts
    69
    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.

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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


    Originally posted by antodomnic
    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.

  12. #12
    Join Date
    Mar 2003
    Posts
    69
    that's great.. It works.. thankx...

  13. #13
    Join Date
    Mar 2003
    Posts
    69
    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?

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

    Originally posted by antodomnic
    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?

  15. #15
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    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,
    Rodney Krick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •