Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Fri Oct 08, 2004 1:05 pm Post subject: Sel rows in same table based where condition equal 1st Sel
Hi, Ive been browsing this board for a while, decided to join.
I have a query(that works) I run in SPUFI. Is there a more efficent way to get my results? The query checks for a condition in the current period, if found then searches previous period for another conditon, then returns cols. for both(or more) hits. This is all in the same table. I also tried searching without any luck.
Thanks!
Code:
SELECT DISTINCT
A.EMPRNO, A.SSNO, A.FIRST99, A.OVER99, A.LNAME, A.FNAME, A.ACCTNO,
A.PSTPERIOD,
C.EMPRNO, C.SSNO, C.FIRST99, C.OVER99, C.LNAME, C.FNAME, C.ACCTNO,
C.PSTPERIOD
FROM HOURS.HRSDTL A
LEFT JOIN (SELECT *
FROM HOURS.HRSDTL C
WHERE C.EMPRNO = 10200 AND
C.PSTPERIOD BETWEEN DATE('10/01/2003') AND
('02/01/2004')) C ON A.SSNO = C.SSNO
WHERE (A.PSTPERIOD = '2003-10-01' AND
A.EMPRNO IN (29300, 1100)) AND
A.SSNO IN (SELECT B.SSNO
FROM HOURS.HRSDTL B
WHERE A.SSNO = B.SSNO AND
B.EMPRNO = 10200 AND
B.PSTPERIOD BETWEEN DATE('10/01/2003') AND ('02/01/2004'))
ORDER BY C.ACCTNO, C.SSNO;
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Sat Oct 09, 2004 6:11 am Post subject:
Nascar9,
welcome aboard. I think a case statement can be used to select the data instead of a left join. However can you put down your requirements with an example data?
You are also using harcoded values for the dates? Do you want to automate that part also ?
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Mon Oct 11, 2004 11:59 am Post subject:
Thanks Koluso!
All data is in same table.
1. Find all rows with:
PSTPERIOD = '2003-10-01' and EMPRNO = 29300 or 1100
2. Use the rows from 1 and search for rows:
SSNO is equal and EMPRNO = 10200 and
PSTPERIOD BETWEEN DATE('10/01/2003') AND ('02/01/2004'
3. Return EMPRNO, SSNO, FIRST99, OVER99 LNAME, FNAME, ACCTNO, PSTPERIOD FOR BOTH 1 AND 2.
4. DISTINCT is not required, nice to have.
5. Dates ranges are fixed at this point, maybe a variable in future.
Here is a couple of rows from the query.
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Mon Oct 11, 2004 12:19 pm Post subject:
Nascar9,
Try the following sql. I used a UNION clause to get the desired results.
Code:
SELECT EMPRNO
,SSNO
,FIRST99
,OVER99
,LNAME
,FNAME
,ACCTNO
,PSTPERIOD
FROM TABLE
WHERE EMPRNO IN ('29300', '1100')
AND PSTPERIOD = '2003-10-01'
UNION
SELECT EMPRNO
,SSNO
,FIRST99
,OVER99
,LNAME
,FNAME
,ACCTNO
,PSTPERIOD
FROM TABLE
WHERE SSNO IN (SELECT SSNO
FROM TABLE
WHERE EMPRNO IN ('29300', '1100')
AND PSTPERIOD = '2003-10-01')
AND PSTPERIOD BETWEEN '2003-10-01' AND '2004-02-01'
;
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Mon Oct 11, 2004 12:56 pm Post subject:
Kolusu, I ran your SQL tweeked like this:
Code:
SELECT EMPRNO
,SSNO
,FIRST99
,OVER99
,LNAME
,FNAME
,ACCTNO
,PSTPERIOD
FROM HOURS.HRSDTL
WHERE EMPRNO IN (29300, 1100)
AND PSTPERIOD = '2003-10-01'
UNION
SELECT EMPRNO
,SSNO
,FIRST99
,OVER99
,LNAME
,FNAME
,ACCTNO
,PSTPERIOD
FROM HOURS.HRSDTL
WHERE SSNO IN (SELECT SSNO
FROM HOURS.HRSDTL
WHERE EMPRNO IN (29300, 1100)
AND PSTPERIOD = '2003-10-01')
AND PSTPERIOD BETWEEN '2003-10-01' AND '2004-02-01'
AND EMPRNO = 10200
;
The results were every row in the first select, and the rows that matched in union condition.
The requirerments are if first condiotion TRUE, then check for second condition. If both are true then output information.
I do like how the output format is display, stacked. Just need to eliminate the un-needed rows.
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Mon Oct 11, 2004 2:50 pm Post subject:
Nascar9,
Quote:
The results were every row in the first select, and the rows that matched in union condition.
Not exactly true. The UNION clause eliminates the duplicates from the query. so the result set may also contain some records from the 2nd query also.
Quote:
The requirerments are if first condiotion TRUE, then check for second condition. If both are true then output information.
The query above is indeed checking for both the conditions to be true. The query in the second select statement makes sure that 1st condition is true.
Quote:
Researching the 'UNION' I'm not sure it will do what is necessary.
UNION is used eliminate duplicates. For a better understanding of the query , run each query individually and then compare the results to the query using UNION. You will be able to find out the duplicates eliminated.
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Mon Oct 11, 2004 3:12 pm Post subject:
Here are that stats from both queries:
UNION:
DSNE610I NUMBER OF ROWS DISPLAYED IS 42275
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+--
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+--
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 49
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 43797
Left JOIN:
DSNE610I NUMBER OF ROWS DISPLAYED IS 38
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-----
---------+---------+---------+---------+---------+---------+-----
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+-----
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 19
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 74
What appear is UNION wrote out every matching condition. Maybe I'm missing something, but the doc I'm looking at seems to back this up.
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Mon Oct 11, 2004 3:20 pm Post subject:
Nascar9,
Add a DISTINCT verb in the subquery of the second sql.
Code:
WHERE SSNO IN (SELECT DISTINCT SSNO
FROM HOURS.HRSDTL
WHERE EMPRNO IN (29300, 1100)
AND PSTPERIOD = '2003-10-01')
AND PSTPERIOD BETWEEN '2003-10-01' AND '2004-02-01'
AND EMPRNO = 10200
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
