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

Thread: DB2 Query

  1. #1
    Join Date
    Sep 2010
    Posts
    17

    Unanswered: DB2 Query

    Hi Folks,
    I worte a join in DB2 which is fetching unique Emp IDs but also using a FETCH FIRST 1 ROWS ONLY... My requirement is that every time i use this query, i want to get a different EMP id - how do i write a query for that? help plz?

    Thanks
    Han

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Do you want to randomly return only one row from the query?

    Andy

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Easy

    Try it and try to understand how it works and you win:

    Code:
    with id_tbl (EmpID) as
    (select 1 from sysibm.sysdummy1 union all
    select 2 from sysibm.sysdummy1 union all
    select 50 from sysibm.sysdummy1 union all
    select 77 from sysibm.sysdummy1 union all
    select 7777 from sysibm.sysdummy1
    )
    select EmpID from 
    (select rand() rnd, EmpID from id_tbl) rr
    order by rnd 
    fetch first row only
    Lenny

  4. #4
    Join Date
    Sep 2010
    Posts
    17
    Andy,
    I want the rows to be fetched sequentially not random. My query returned say 5 rows with 5 emp ids - when i run the query every time, it should fetch next row from the result set(which has 5 rows)....


    Lenny,
    I'll try executing the code tomorrow at work and will let you know how it went....


    Thanks much!
    Han

  5. #5
    Join Date
    Sep 2010
    Posts
    17

    Db2 Query - No luck

    Tried the below query - no luck guys! - any help?

    WITH Region.Table name (column name) AS
    (select 1 from sysibm.sysdummy1 union all
    select 2 from sysibm.sysdummy1 union all
    select 50 from sysibm.sysdummy1 union all
    select 77 from sysibm.sysdummy1 union all
    select 7777 from sysibm.sysdummy1)
    select column name from
    (select rand() rnd, column name from Region.Table name) rr
    fetch first row only;

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down Too bad to be true

    Quote Originally Posted by HanNew2DB2 View Post
    Tried the below query - no luck guys! - any help?

    WITH Region.Table name (column name) AS
    (select 1 from sysibm.sysdummy1 union all
    select 2 from sysibm.sysdummy1 union all
    select 50 from sysibm.sysdummy1 union all
    select 77 from sysibm.sysdummy1 union all
    select 7777 from sysibm.sysdummy1)
    select column name from
    (select rand() rnd, column name from Region.Table name) rr
    fetch first row only;
    It was an example. Because I don't have your table.

    Code:
    select column_name from 
    (select rand() rnd, column_name from Region.Table_name) rr
    order by rnd
    fetch first row only
    If you want to try how my example works, you don't need to use REGION:
    You have to do just (no spaces in table name and column names).

    Also you forget the main part of query: ORDER BY:

    Code:
    WITH Table_name (column_name) AS
    (select 1 from sysibm.sysdummy1 union all
    select 2 from sysibm.sysdummy1 union all
    select 50 from sysibm.sysdummy1 union all
    select 77 from sysibm.sysdummy1 union all
    select 7777 from sysibm.sysdummy1)
           select column_name from 
    (select rand() rnd, column_name from Table_name) rr
    order by rnd
    fetch first row only;
    Lenny
    Last edited by Lenny77; 10-04-10 at 19:08.

  7. #7
    Join Date
    Sep 2010
    Posts
    17
    Lenny, your Query works fine, but the values are hardcoded? every time i run the query, i get the values(emp ids) that are hardcoded in the select statemet - am i not getting something? please bear with me, new to DB2 and trying to work my way thru....

    Han
    ....................
    Quote Originally Posted by Lenny77 View Post
    It was an example. Because I don't have your table.

    Code:
    select column_name from 
    (select rand() rnd, column_name from Region.Table_name) rr
    order by rnd
    fetch first row only
    If you want to try how my example works, you don't need to use REGION:
    You have to do just (no spaces in table name and column names).

    Also you forget the main part of query: ORDER BY:

    Code:
    WITH Table_name (column_name) AS
    (select 1 from sysibm.sysdummy1 union all
    select 2 from sysibm.sysdummy1 union all
    select 50 from sysibm.sysdummy1 union all
    select 77 from sysibm.sysdummy1 union all
    select 7777 from sysibm.sysdummy1)
           select column_name from 
    (select rand() rnd, column_name from Table_name) rr
    order by rnd
    fetch first row only;
    Lenny

  8. #8
    Join Date
    Sep 2010
    Posts
    17
    Lenny,
    The eaxact below query is geting emp id's..... using this query in a function, which will be called as needed basis... for this, my join should return a new emp id for every run..... that's requirement - not sure how to fit your code in the join i wrote?
    Query:
    SELECT First_name, Last_name, SUBSTR(Date_MM_DD_TX,1,2)|| '/' || SUBSTR(Date_MM_DD_TX,2,2), SUBSTR(Emply_ID,5,5) AS Emp_ID FROM (Region.Table_name1 AS A INNER JOIN egion.Table_name2 AS B
    ON egion.Table_name1.Dept_ID = egion.Table_name2.Sub_Dept_ID
    AND A.RGSTRN_FL = 'N' AND A.PIN_CD = 'A'
    LEFT OUTER JOIN egion.Table_name3 as C
    ON C.Dept_ID = A.Dept_ID)
    FETCH FIRST 5 ROWS ONLY;
    Output of the query:

    fname1 lname1 12/20 12345
    fname2 lname2 12/21 12346
    fname3 lname3 12/20 12378
    fname4 lname4 10/20 18955
    fname5 lname5 12/20 11111

    My requirement is: For the first run, i should get Emp id = 12345
    For the second run, i should get Emp id = 12346
    For the third run, i should get Emp id = 12378 and so on..... how do i do this guyz?

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink

    Quote Originally Posted by HanNew2DB2 View Post
    Lenny, your Query works fine, but the values are hardcoded? every time i run the query, i get the values(emp ids) that are hardcoded in the select statemet - am i not getting something? please bear with me, new to DB2 and trying to work my way thru....

    Han
    ....................
    Dear new Han !

    They are hordcoded only to shown you how it works.
    You have to use this method to get id from your table.

  10. #10
    Join Date
    Sep 2010
    Posts
    17
    Oops that was dumb of me! of course i'll have to replace the values with my fields -
    U R The man or what! it worked - kewl, thanks much my friend....
    xie xie(pronounced as sheh sheh - means ThanQ)
    Han

    Quote Originally Posted by Lenny77 View Post
    Dear new Han !

    They are hordcoded only to shown you how it works.
    You have to use this method to get id from your table.

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by HanNew2DB2 View Post
    xie xie(pronounced as sheh sheh - means ThanQ)
    Lenny, it's now your turn to say "you're welcome" in your language

  12. #12
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Oh, girl !

    I can say POZHALUSTA to him....

  13. #13
    Join Date
    Sep 2010
    Posts
    17

    Ne za chto

    Thanks Guys! - but a new problem cropped up......
    Modified the query to get Distinct EMP ids -
    [Note:unable to get the results without using Region]
    WITH T1 (EMP_ID)AS
    (select DISTINCT(SUBSTR(EMP_ID,5,5))from R1.T1 union all
    select DISTINCT(SUBSTR(EMP_ID,5,5)) from Region.T1 union all
    select DISTINCT(SUBSTR(EMP_ID,5,5)) from Region.T1 union all
    select DISTINCT(SUBSTR(EMP_ID,5,5)) from Region.T1 union all
    select DISTINCT(SUBSTR(EMP_ID,5,5)) from Region.T1)
    select EMP_ID AS US_EMP_ID from
    (select rand() rnd, EMP_ID from T1) rr
    order by rnd
    fetch first 5 rows only;
    The above query works fine but the emp ids retrieved are from all geographic regions - i need the emp ids only for US domestic region for now... the below Join will filter the emp ids, but unable to squeze in this JOIN into the above query - tried a million times, but still syntax errors, unidentified errors etc.... could you plase fit in my join in your query?
    Here's the Join:
    SELECT SUBSTR(EMP_ID,5,5) AS US_EMP_ID from R1.T2 AS A INNER JOIN R1.T3 AS B
    ON R1.T2.DEP_ID = R1.T3.US_DEP_ID
    AND A.RGSTRN_FL = 'N' AND A.SEQN_STATUS_CD = 'A'
    LEFT OUTER JOIN R1.T1 as C
    ON C.DEP_ID = A.PO_ID

    Also, to my surprise, i get null values for emp id field and how to prevent Null values from displaying?
    Your help is highly appreciated!
    Na zdorove!
    Han
    --------------------------------------------------------------------------
    Quote Originally Posted by Lenny77 View Post
    Oh, girl !

    I can say POZHALUSTA to him....

  14. #14
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool

    Quote Originally Posted by HanNew2DB2 View Post
    Thanks Guys! - but a new problem cropped up......
    Modified the query to get Distinct EMP ids -
    [Note:unable to get the results without using Region]
    WITH T1 (EMP_ID)AS
    (select DISTINCT(SUBSTR(EMP_ID,5,5))from R1.T1 union all
    select DISTINCT(SUBSTR(EMP_ID,5,5)) from Region.T1 union all
    select DISTINCT(SUBSTR(EMP_ID,5,5)) from Region.T1 union all
    select DISTINCT(SUBSTR(EMP_ID,5,5)) from Region.T1 union all
    select DISTINCT(SUBSTR(EMP_ID,5,5)) from Region.T1)
    select EMP_ID AS US_EMP_ID from
    (select rand() rnd, EMP_ID from T1) rr
    order by rnd
    fetch first 5 rows only;
    The above query works fine but the emp ids retrieved are from all geographic regions - i need the emp ids only for US domestic region for now... the below Join will filter the emp ids, but unable to squeze in this JOIN into the above query - tried a million times, but still syntax errors, unidentified errors etc.... could you plase fit in my join in your query?
    Here's the Join:
    SELECT SUBSTR(EMP_ID,5,5) AS US_EMP_ID from R1.T2 AS A INNER JOIN R1.T3 AS B
    ON R1.T2.DEP_ID = R1.T3.US_DEP_ID
    AND A.RGSTRN_FL = 'N' AND A.SEQN_STATUS_CD = 'A'
    LEFT OUTER JOIN R1.T1 as C
    ON C.DEP_ID = A.PO_ID

    Also, to my surprise, i get null values for emp id field and how to prevent Null values from displaying?
    Your help is highly appreciated!
    Na zdorove!
    Han
    --------------------------------------------------------------------------
    With you I can lost the last what I have (my head):

    Code:
    select DISTINCT(SUBSTR(EMP_ID,5,5))from R1.T1 union all
    select DISTINCT(SUBSTR(EMP_ID,5,5)) from Region.T1 union all
    select DISTINCT(SUBSTR(EMP_ID,5,5)) from Region.T1 union all
    select DISTINCT(SUBSTR(EMP_ID,5,5)) from Region.T1 union all
    select DISTINCT(SUBSTR(EMP_ID,5,5)) from Region.T1
    What is the different between selects ?

    Also:

    Code:
    select SUBSTR(EMP_ID,5,5) from R1.T1 
    union all
    select SUBSTR(EMP_ID,5,5) from Region.T1
    would return to you the same RS.

    About your query:

    Code:
    SELECT SUBSTR(EMP_ID,5,5) AS US_EMP_ID 
    from 
    R1.T2 AS A 
    JOIN 
    R1.T3 AS B
    ON R1.T2.DEP_ID      = R1.T3.US_DEP_ID
    AND A.RGSTRN_FL      = 'N' 
    AND A.SEQN_STATUS_CD = 'A'
    LEFT JOIN 
    R1.T1 as C
    ON C.DEP_ID = A.PO_ID
    
    Where EMP_ID is not NULL
    Lenny

  15. #15
    Join Date
    Sep 2010
    Posts
    17

    Unhappy

    Lenny:
    The first one was just a repeat of lines from copy paste - plz ignore that. The other part is the issue:
    With T1 (EMP_ID) AS
    (select SUBSTR(EMP_ID,5,5) from R1.T1
    union all
    select SUBSTR(EMP_ID,5,5) from Region.T1)
    '[my query -modifed by u, which works perfect]
    SELECT SUBSTR(EMP_ID,5,5) AS US_EMP_ID
    from
    R1.T2 AS A
    JOIN
    R1.T3 AS B
    ON R1.T2.DEP_ID = R1.T3.US_DEP_ID
    AND A.RGSTRN_FL = 'N'
    AND A.SEQN_STATUS_CD = 'A'
    LEFT JOIN
    R1.T1 as C
    ON C.DEP_ID = A.PO_ID
    Where EMP_ID is not NULL
    select EMP_ID AS US_EMP_ID from
    (select rand() rnd, EMP_ID from T1) rr
    order by rnd
    fetch first 5 rows only;

    The whole idea is to fetch different EmP ids for every run - but above not happening----
    i know this is dumb - unable to integrate my query in the WITH command u sent -

Posting Permissions

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