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

Do you want to randomly return only one row from the query?

Andy

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

Lenny

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

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.

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:

Lenny

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
....................

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?

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.

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

Lenny, it's now your turn to say "you're welcome" in your language

Oh, girl !

I can say POZHALUSTA to him....

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):

What is the different between selects ?

Also:

would return to you the same RS.

About your query:

Lenny

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 -